[Home] [Help]
127: v_contra_ccy VARCHAR2(15) :=NULL;
128: v_settle_ref VARCHAR2(80) :=NULL;
129: v_settle_ac VARCHAR2(20):=NULL;
130:
131: v_cparty_account_no xtr_deal_date_amounts.cparty_account_no%type;
132: v_settle_account_no xtr_deal_date_amounts.account_no%type;
133: v_premium_account_no xtr_deal_date_amounts.account_no%type;
134: v_maturity_account_no xtr_deal_date_amounts.account_no%type;
135: v_buy_account_no xtr_deal_date_amounts.account_no%type;
128: v_settle_ref VARCHAR2(80) :=NULL;
129: v_settle_ac VARCHAR2(20):=NULL;
130:
131: v_cparty_account_no xtr_deal_date_amounts.cparty_account_no%type;
132: v_settle_account_no xtr_deal_date_amounts.account_no%type;
133: v_premium_account_no xtr_deal_date_amounts.account_no%type;
134: v_maturity_account_no xtr_deal_date_amounts.account_no%type;
135: v_buy_account_no xtr_deal_date_amounts.account_no%type;
136: v_sell_account_no xtr_deal_date_amounts.account_no%type;
129: v_settle_ac VARCHAR2(20):=NULL;
130:
131: v_cparty_account_no xtr_deal_date_amounts.cparty_account_no%type;
132: v_settle_account_no xtr_deal_date_amounts.account_no%type;
133: v_premium_account_no xtr_deal_date_amounts.account_no%type;
134: v_maturity_account_no xtr_deal_date_amounts.account_no%type;
135: v_buy_account_no xtr_deal_date_amounts.account_no%type;
136: v_sell_account_no xtr_deal_date_amounts.account_no%type;
137:
130:
131: v_cparty_account_no xtr_deal_date_amounts.cparty_account_no%type;
132: v_settle_account_no xtr_deal_date_amounts.account_no%type;
133: v_premium_account_no xtr_deal_date_amounts.account_no%type;
134: v_maturity_account_no xtr_deal_date_amounts.account_no%type;
135: v_buy_account_no xtr_deal_date_amounts.account_no%type;
136: v_sell_account_no xtr_deal_date_amounts.account_no%type;
137:
138: -- Non Base Table Columns
131: v_cparty_account_no xtr_deal_date_amounts.cparty_account_no%type;
132: v_settle_account_no xtr_deal_date_amounts.account_no%type;
133: v_premium_account_no xtr_deal_date_amounts.account_no%type;
134: v_maturity_account_no xtr_deal_date_amounts.account_no%type;
135: v_buy_account_no xtr_deal_date_amounts.account_no%type;
136: v_sell_account_no xtr_deal_date_amounts.account_no%type;
137:
138: -- Non Base Table Columns
139: --P_CPARTY_ACCOUNT VARCHAR2(20);
132: v_settle_account_no xtr_deal_date_amounts.account_no%type;
133: v_premium_account_no xtr_deal_date_amounts.account_no%type;
134: v_maturity_account_no xtr_deal_date_amounts.account_no%type;
135: v_buy_account_no xtr_deal_date_amounts.account_no%type;
136: v_sell_account_no xtr_deal_date_amounts.account_no%type;
137:
138: -- Non Base Table Columns
139: --P_CPARTY_ACCOUNT VARCHAR2(20);
140: P_INT_VALUE NUMBER;
160: base_ccy VARCHAR2(15);
161: --
162: cursor CFLOW is
163: select 1
164: from XTR_DEAL_DATE_AMOUNTS_V
165: where DEAL_NUMBER = P_DEAL_NO
166: and AMOUNT_TYPE = 'FXOBUY'
167: and DATE_TYPE = 'VALUE';
168: --
180: l_hce_amount NUMBER;
181: --
182: cursor CHK_BDO_SETTLE_ROWS is
183: select 1
184: from XTR_DEAL_DATE_AMOUNTS
185: where deal_number = P_DEAL_NO and
186: deal_type = 'BDO' and
187: date_type = 'SETTLE';
188: --
207: and nvl(BOND_OR_DEBENTURE_ISSUE,'B') = 'B';
208: --
209: cursor CHK_NI_BAL_FV is
210: select 1
211: from XTR_DEAL_DATE_AMOUNTS
212: where DEAL_NUMBER = P_DEAL_NO
213: and AMOUNT_TYPE = 'BAL_FV'
214: and DATE_TYPE = 'MATURE';
215: --
214: and DATE_TYPE = 'MATURE';
215: --
216: cursor CHK_SWPTN_SETTLE_ROWS is
217: select 1
218: from XTR_DEAL_DATE_AMOUNTS
219: where deal_number = P_DEAL_NO and
220: deal_type = 'SWPTN' and
221: date_type = 'SETTLE';
222:
222:
223: -- Bug 8561305 Starts
224:
225: cursor CHK_FXO_PREMIUM is
226: select 1 from xtr_deal_date_amounts
227: where company_code =P_COMPANY_CODE
228: and deal_number = P_DEAL_NO
229: and deal_type = 'FXO'
230: and transaction_number = 1
242: begin
243:
244: /* code below added by Ilavenil to fix the bug # 2065586
245: Let us assume, a deal is created using the deal input form. At the time of creation of deal the
246: account numbers are set. Say for example the account number is 'AAA'. Xtr_Deals, Xtr_Deal_Date_Amounts
247: will store the account number 'AAA'. User then goes to settlement form
248: and goes for a different account number for settlement. Say for example the account number is 'BBB'.
249: Xtr_Deal_Date_Amounts will now save 'BBB' instead of 'AAA' for the same deal no.
250:
245: Let us assume, a deal is created using the deal input form. At the time of creation of deal the
246: account numbers are set. Say for example the account number is 'AAA'. Xtr_Deals, Xtr_Deal_Date_Amounts
247: will store the account number 'AAA'. User then goes to settlement form
248: and goes for a different account number for settlement. Say for example the account number is 'BBB'.
249: Xtr_Deal_Date_Amounts will now save 'BBB' instead of 'AAA' for the same deal no.
250:
251: Case i : User then goes to the input form and queries the same deal number. In future the user may be allowed to update
252: the deal no for the deal. In that case, let us assume that the user modified the deal account number as
253: 'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
249: Xtr_Deal_Date_Amounts will now save 'BBB' instead of 'AAA' for the same deal no.
250:
251: Case i : User then goes to the input form and queries the same deal number. In future the user may be allowed to update
252: the deal no for the deal. In that case, let us assume that the user modified the deal account number as
253: 'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
254:
255: Case ii : User goes to input form and queries the deal number. User updates a field other than account numbers.
256: The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
257: Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
252: the deal no for the deal. In that case, let us assume that the user modified the deal account number as
253: 'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
254:
255: Case ii : User goes to input form and queries the deal number. User updates a field other than account numbers.
256: The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
257: Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
258:
259: So, we always check whether in Xtr_Deals, the old account number <> new account number. If yes, then
260: update Xtr_Deal_Date_Amounts to new account number of Xtr_Deals. If no, then leave Xtr_Deal_Date_Amounts
253: 'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
254:
255: Case ii : User goes to input form and queries the deal number. User updates a field other than account numbers.
256: The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
257: Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
258:
259: So, we always check whether in Xtr_Deals, the old account number <> new account number. If yes, then
260: update Xtr_Deal_Date_Amounts to new account number of Xtr_Deals. If no, then leave Xtr_Deal_Date_Amounts
261: account number as is.
256: The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
257: Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
258:
259: So, we always check whether in Xtr_Deals, the old account number <> new account number. If yes, then
260: update Xtr_Deal_Date_Amounts to new account number of Xtr_Deals. If no, then leave Xtr_Deal_Date_Amounts
261: account number as is.
262:
263: We basically do not want to overwrite an account number set at settlement by original account number.
264:
347: --
348: -- For insert of new Rows
349: if P_ACTION = 'INSERT' and P_STATUS_CODE = 'CURRENT' then
350: if P_DEAL_TYPE = 'FRA' then
351: insert into XTR_DEAL_DATE_AMOUNTS
352: (deal_type,amount_type,date_type,
353: deal_number,transaction_number,transaction_date,currency,
354: amount,hce_amount,amount_date,transaction_rate,
355: cashflow_amount,company_code,deal_subtype,
364: P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
365: P_CLIENT_CODE,P_CPARTY_CODE,'N',
366: NULL,P_CPARTY_CODE,P_START_DATE,P_QUICK_INPUT);
367: -- Dummy row for deal maturity date, this allows a journal action, accruals
368: insert into XTR_DEAL_DATE_AMOUNTS
369: (deal_type,amount_type,date_type,
370: deal_number,transaction_number,transaction_date,currency,
371: amount,hce_amount,amount_date,transaction_rate,
372: cashflow_amount,company_code,deal_subtype,product_type,
378: P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
379: P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
380: -- Dummy row for deal dealt date, this allows a journal action
381: -- to be set up for a premium to occur on the deal date (date type DEALT)
382: insert into XTR_DEAL_DATE_AMOUNTS
383: (deal_type,amount_type,date_type,
384: deal_number,transaction_number,transaction_date,currency,
385: amount,hce_amount,amount_date,transaction_rate,
386: cashflow_amount,company_code,deal_subtype,product_type,
392: P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
393: P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
394: -- Row for date_type of LIMIT so that the risk between the settlement and
395: -- exercise date is acknowledged
396: insert into XTR_DEAL_DATE_AMOUNTS
397: (deal_type,amount_type,date_type,
398: deal_number,transaction_number,transaction_date,currency,
399: amount,hce_amount,amount_date,
400: cashflow_amount,company_code,deal_subtype,product_type,
408: P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT,
409: nvl(P_LIMIT_CODE,'NILL'),P_CPARTY_CODE);
410: if P_RATE_FIXING_DATE is not null then
411: -- Dummy row for deal rate set date
412: insert into XTR_DEAL_DATE_AMOUNTS
413: (deal_type,amount_type,date_type,
414: deal_number,transaction_number,transaction_date,currency,
415: amount,hce_amount,amount_date,transaction_rate,
416: cashflow_amount,company_code,deal_subtype,product_type,
428:
429: elsif P_DEAL_TYPE = 'FXO' then
430:
431: if nvl(P_KNOCK_TYPE,'O') = 'O' then
432: insert into XTR_DEAL_DATE_AMOUNTS
433: (deal_type,amount_type,date_type,
434: deal_number,transaction_number,transaction_date,currency,
435: amount,hce_amount,amount_date,transaction_rate,
436: cashflow_amount,company_code,deal_subtype,product_type,
445: decode(P_CURRENCY_BUY,substr(upper(l_combin),1,3),nvl(P_LIMIT_CODE,'NILL'),NULL),
446: decode(P_CURRENCY_BUY,substr(upper(l_combin),1,3),P_CPARTY_CODE,NULL),
447: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,l_combin,P_QUICK_INPUT);
448: --
449: insert into XTR_DEAL_DATE_AMOUNTS
450: (deal_type,amount_type,date_type,
451: deal_number,transaction_number,transaction_date,currency,
452: amount,hce_amount,amount_date,transaction_rate,
453: cashflow_amount,company_code,deal_subtype,product_type,
461: decode(P_CURRENCY_SELL,substr(upper(l_combin),1,3),P_CPARTY_CODE,NULL),
462: P_DEALER_CODE,l_combin,P_QUICK_INPUT);
463: -- Value Date Amounts (To be used in cashflow Projections if indicated as reqd in deal input
464: if nvl(P_INSERT_FOR_CASHFLOW,'N') = 'Y' then
465: insert into XTR_DEAL_DATE_AMOUNTS
466: (deal_type,amount_type,date_type,
467: deal_number,transaction_number,transaction_date,currency,
468: amount,hce_amount,amount_date,transaction_rate,
469: cashflow_amount,company_code,account_no,
479: P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
480: P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
481: P_DEALER_CODE,l_combin,P_START_DATE,P_QUICK_INPUT);
482: --
483: insert into XTR_DEAL_DATE_AMOUNTS
484: (deal_type,amount_type,date_type,
485: deal_number,transaction_number,transaction_date,currency,
486: amount,hce_amount,amount_date,transaction_rate,
487: cashflow_amount,company_code,account_no,
499: P_DEALER_CODE,l_combin,P_START_DATE,P_QUICK_INPUT);
500: end if;
501: --
502: if P_CURRENCY IS NOT NULL and P_PREMIUM_ACTION is NOT NULL and nvl(P_PREMIUM_AMOUNT,0) > 0 then
503: insert into XTR_DEAL_DATE_AMOUNTS
504: (deal_type,amount_type,date_type,
505: deal_number,transaction_number,transaction_date,currency,
506: amount,hce_amount,amount_date,transaction_rate,
507: cashflow_amount,company_code,account_no,action_code,
520: end if;
521: -- Dummy rows for deal dealt and commence dates, this allows a journal action
522: -- to be set up for a premium to occur on the deal date (date type DEALT) or
523: -- the commencment date of the option
524: insert into XTR_DEAL_DATE_AMOUNTS
525: (deal_type,amount_type,date_type,
526: deal_number,transaction_number,transaction_date,currency,
527: amount,hce_amount,amount_date,transaction_rate,
528: cashflow_amount,company_code,deal_subtype,product_type,
537: --
538: elsif P_KNOCK_TYPE = 'I' then
539: if P_CURRENCY IS NOT NULL and P_PREMIUM_ACTION is NOT NULL and nvl(P_PREMIUM_AMOUNT,0) > 0 then
540: -- ** WARNING ** a copy of the next insert also appears in UPDATING.
541: insert into XTR_DEAL_DATE_AMOUNTS
542: (deal_type,amount_type,date_type,
543: deal_number,transaction_number,transaction_date,currency,
544: amount,hce_amount,amount_date,transaction_rate,
545: cashflow_amount,company_code,account_no,action_code,
557: end if;
558: -- Dummy rows for deal dealt and commence dates, this allows a journal action
559: -- to be set up for a premium to occur on the deal date (date type DEALT) or
560: -- the commencment date of the option
561: insert into XTR_DEAL_DATE_AMOUNTS
562: (deal_type,amount_type,date_type,
563: deal_number,transaction_number,transaction_date,currency,
564: amount,hce_amount,amount_date,transaction_rate,
565: cashflow_amount,company_code,deal_subtype,product_type,
573: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
574: --
575: end if;
576: elsif P_DEAL_TYPE = 'FX' then
577: insert into XTR_DEAL_DATE_AMOUNTS
578: (deal_type,amount_type,date_type,
579: deal_number,transaction_number,transaction_date,
580: currency,amount,hce_amount,amount_date,
581: transaction_rate,cashflow_amount,company_code,
591: P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',
592: decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
593: decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE),l_combin,P_QUICK_INPUT);
594: --
595: insert into XTR_DEAL_DATE_AMOUNTS
596: (deal_type,amount_type,date_type,
597: deal_number,transaction_number,transaction_date,
598: currency,amount,hce_amount,amount_date,
599: transaction_rate,cashflow_amount,company_code,
610: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',
611: decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
612: decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE),l_combin,P_QUICK_INPUT);
613: --
614: insert into XTR_DEAL_DATE_AMOUNTS
615: (deal_type,amount_type,date_type,
616: deal_number,transaction_number,transaction_date,
617: currency,amount,hce_amount,amount_date,
618: transaction_rate,cashflow_amount,company_code,
629: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',l_combin,P_QUICK_INPUT);
630: --
631: elsif P_DEAL_TYPE = 'NI' then
632:
633: insert into XTR_DEAL_DATE_AMOUNTS
634: (deal_type,amount_type,date_type,
635: deal_number,transaction_number,transaction_date,currency,
636: amount,hce_amount,amount_date,transaction_rate,
637: cashflow_amount,company_code,account_no,
652:
653: -----------------------------------------------------------------------------
654: -- Initial Maturity Face Value Details , before for sell status_code='CLOSED'
655: -----------------------------------------------------------------------------
656: insert into XTR_DEAL_DATE_AMOUNTS
657: (deal_type,amount_type,date_type,
658: deal_number,transaction_number,transaction_date,currency,
659: amount,hce_amount,amount_date,transaction_rate,
660: cashflow_amount,company_code,account_no,
674: P_MATURITY_BALANCE_AMOUNT is NOT NULL then /* in Pro0340 this column is null */
675: -----------------------------
676: -- BALance Face Value Details
677: -----------------------------
678: insert into XTR_DEAL_DATE_AMOUNTS
679: (deal_type,amount_type,date_type,
680: deal_number,transaction_number,transaction_date,currency,
681: amount,hce_amount,amount_date,transaction_rate,
682: cashflow_amount,company_code,account_no,
702:
703: ---------------------
704: -- INTerest Details
705: ---------------------
706: insert into XTR_DEAL_DATE_AMOUNTS
707: (deal_type,amount_type,date_type,deal_number,
708: transaction_number,transaction_date,currency,
709: amount,hce_amount,amount_date,transaction_rate,
710: cashflow_amount,company_code,portfolio_code,status_code,
720:
721: --------------------------
722: -- Deal DEALT on Details
723: --------------------------
724: insert into XTR_DEAL_DATE_AMOUNTS
725: (deal_type,amount_type,date_type,
726: deal_number,transaction_number,transaction_date,currency,
727: amount,hce_amount,amount_date,transaction_rate,
728: cashflow_amount,company_code,
740: ---------------------------------
741: -- New Date Types/Amount Types --
742: ---------------------------------
743: if P_DEAL_SUBTYPE in ('BUY','SHORT') then
744: insert into XTR_DEAL_DATE_AMOUNTS
745: (deal_type,amount_type,date_type,
746: deal_number,transaction_number,transaction_date,currency,
747: amount,hce_amount,amount_date,transaction_rate,
748: cashflow_amount,company_code,account_no,
796: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_COMPANY_CODE,P_CPARTY_CODE,
797: P_CLIENT_CODE,P_CURRENCY,P_DEAL_DATE,'CURRENT',L_USER,
798: L_SYSDATE,coupon_date);
799: --
800: insert into XTR_DEAL_DATE_AMOUNTS
801: (deal_type,amount_type,date_type,
802: deal_number,transaction_number,transaction_date,currency,
803: amount,hce_amount,amount_date,transaction_rate,
804: cashflow_amount,company_code,account_no,status_code,portfolio_code,
817: END LOOP;
818: end if;
819: end if;
820: --
821: insert into XTR_DEAL_DATE_AMOUNTS
822: (deal_type,amount_type,date_type,
823: deal_number,transaction_number,transaction_date,currency,
824: amount,hce_amount,amount_date,transaction_rate,
825: cashflow_amount,company_code,account_no,
834: P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
835: P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
836: P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
837: --
838: insert into XTR_DEAL_DATE_AMOUNTS
839: (deal_type,amount_type,date_type,
840: deal_number,transaction_number,transaction_date,currency,
841: amount,hce_amount,amount_date,transaction_rate,
842: cashflow_amount,company_code,account_no,
854: P_CLIENT_CODE,P_DEAL_SUBTYPE,P_ACCEPTOR_CODE,'N',
855: P_PRODUCT_TYPE,nvl(P_LIMIT_CODE,'NILL'),P_ACCEPTOR_CODE);
856: -- if P_COUPON_ACTION = 'CUM' then
857: -- Accrued Int values
858: insert into XTR_DEAL_DATE_AMOUNTS
859: (deal_type,amount_type,date_type,
860: deal_number,transaction_number,transaction_date,currency,
861: amount,hce_amount,amount_date,transaction_rate,
862: cashflow_amount,company_code,status_code,
869: P_PORTFOLIO_CODE,P_DEALER_CODE,P_CLIENT_CODE,
870: P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
871: --- end if;
872: -- Premium / Discount Values
873: insert into XTR_DEAL_DATE_AMOUNTS
874: (deal_type,amount_type,date_type,
875: deal_number,transaction_number,transaction_date,currency,
876: amount,hce_amount,amount_date,transaction_rate,
877: cashflow_amount,company_code,status_code,
885: P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
886: P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
887: elsif P_DEAL_TYPE = 'IRO' then
888: -- Limit Row / Limit Amount Details
889: insert into XTR_DEAL_DATE_AMOUNTS
890: (deal_type,amount_type,date_type,
891: deal_number,transaction_number,transaction_date,currency,
892: amount,hce_amount,amount_date,
893: cashflow_amount,company_code,deal_subtype,product_type,
902: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
903: P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
904: P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
905: -- Expiry Date / Face Value Amount Details
906: insert into XTR_DEAL_DATE_AMOUNTS
907: (deal_type,amount_type,date_type,
908: deal_number,transaction_number,transaction_date,currency,
909: amount,hce_amount,amount_date,transaction_rate,
910: cashflow_amount,company_code,deal_subtype,product_type,
919: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
920: P_CPARTY_CODE,'N',
921: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
922: -- Premium Date / Premium Amount Details
923: insert into XTR_DEAL_DATE_AMOUNTS
924: (deal_type,amount_type,date_type,
925: deal_number,transaction_number,transaction_date,currency,
926: amount,hce_amount,amount_date,transaction_rate,
927: cashflow_amount,
938: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
939: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
940: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
941: -- Deal Dealt on Details
942: insert into XTR_DEAL_DATE_AMOUNTS
943: (deal_type,amount_type,date_type,
944: deal_number,transaction_number,transaction_date,currency,
945: amount,hce_amount,amount_date,transaction_rate,
946: cashflow_amount,company_code,
963: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
964: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
965: */
966: -- Physical Commencement Date Details
967: insert into XTR_DEAL_DATE_AMOUNTS
968: (deal_type,amount_type,date_type,
969: deal_number,transaction_number,transaction_date,currency,
970: amount,hce_amount,amount_date,transaction_rate,
971: cashflow_amount,company_code,
978: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
979: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
980: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
981: -- Physical Maturity Date Details
982: insert into XTR_DEAL_DATE_AMOUNTS
983: (deal_type,amount_type,date_type,
984: deal_number,transaction_number,transaction_date,currency,
985: amount,hce_amount,amount_date,transaction_rate,
986: cashflow_amount,company_code,
993: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
994: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
995: elsif P_DEAL_TYPE = 'BDO' then
996: -- Limit Row / Limit Amount Details
997: insert into XTR_DEAL_DATE_AMOUNTS
998: (deal_type,amount_type,date_type,
999: deal_number,transaction_number,transaction_date,currency,
1000: amount,hce_amount,amount_date,
1001: cashflow_amount,company_code,deal_subtype,product_type,
1010: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1011: P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
1012: P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1013: -- Expiry Date / Face Value Amount Details
1014: insert into XTR_DEAL_DATE_AMOUNTS
1015: (deal_type,amount_type,date_type,
1016: deal_number,transaction_number,transaction_date,currency,
1017: amount,hce_amount,amount_date,transaction_rate,
1018: cashflow_amount,company_code,deal_subtype,product_type,
1027: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1028: P_CPARTY_CODE,'N',
1029: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1030: -- Premium Date / Premium Amount Details
1031: insert into XTR_DEAL_DATE_AMOUNTS
1032: (deal_type,amount_type,date_type,
1033: deal_number,transaction_number,transaction_date,currency,
1034: amount,hce_amount,amount_date,transaction_rate,
1035: cashflow_amount,
1046: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1047: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
1048: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1049: -- Deal Dealt on Details
1050: insert into XTR_DEAL_DATE_AMOUNTS
1051: (deal_type,amount_type,date_type,
1052: deal_number,transaction_number,transaction_date,currency,
1053: amount,hce_amount,amount_date,transaction_rate,
1054: cashflow_amount,company_code,
1071: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1072: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1073: */
1074: -- Physical Commencement Date Details
1075: insert into XTR_DEAL_DATE_AMOUNTS
1076: (deal_type,amount_type,date_type,
1077: deal_number,transaction_number,transaction_date,currency,
1078: amount,hce_amount,amount_date,transaction_rate,
1079: cashflow_amount,company_code,
1087: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1088: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1089: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1090: -- Physical Maturity Date Details
1091: insert into XTR_DEAL_DATE_AMOUNTS
1092: (deal_type,amount_type,date_type,
1093: deal_number,transaction_number,transaction_date,currency,
1094: amount,hce_amount,amount_date,transaction_rate,
1095: cashflow_amount,company_code,
1102: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1103: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1104: elsif P_DEAL_TYPE = 'FUT' then
1105: -- Face Value Amount
1106: insert into XTR_DEAL_DATE_AMOUNTS
1107: (deal_type,amount_type,date_type,
1108: deal_number,transaction_number,transaction_date,currency,
1109: amount,hce_amount,amount_date,transaction_rate,
1110: cashflow_amount,company_code,deal_subtype,product_type,
1120: P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
1121: P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
1122: if nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) > 0 then
1123: -- Settlement Amount
1124: insert into XTR_DEAL_DATE_AMOUNTS
1125: (deal_type,amount_type,date_type,
1126: deal_number,transaction_number,transaction_date,currency,
1127: amount,hce_amount,amount_date,transaction_rate,
1128: cashflow_amount,company_code,account_no,action_code,
1139: P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
1140: end if;
1141: -- Dummy row for deal dealt date, this allows a journal action
1142: -- to be set up for a premium to occur on the deal date (date type DEALT)
1143: insert into XTR_DEAL_DATE_AMOUNTS
1144: (deal_type,amount_type,date_type,
1145: deal_number,transaction_number,transaction_date,currency,
1146: amount,hce_amount,amount_date,transaction_rate,
1147: cashflow_amount,company_code,deal_subtype,product_type,
1154: P_CLIENT_CODE,P_CPARTY_CODE,'N',
1155: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1156: elsif P_DEAL_TYPE = 'SWPTN' then
1157: -- Limit Row / Limit Details
1158: insert into XTR_DEAL_DATE_AMOUNTS
1159: (deal_type,amount_type,date_type,
1160: deal_number,transaction_number,transaction_date,currency,
1161: amount,hce_amount,amount_date,
1162: cashflow_amount,company_code,deal_subtype,product_type,
1170: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1171: P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
1172: P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1173: -- Face Value Amount / Expiry Date Details
1174: insert into XTR_DEAL_DATE_AMOUNTS
1175: (deal_type,amount_type,date_type,
1176: deal_number,transaction_number,transaction_date,currency,
1177: amount,hce_amount,amount_date,transaction_rate,
1178: cashflow_amount,company_code,deal_subtype,product_type,
1186: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1187: P_CPARTY_CODE,'N',
1188: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1189: -- Premium Details
1190: insert into XTR_DEAL_DATE_AMOUNTS
1191: (deal_type,amount_type,date_type,
1192: deal_number,transaction_number,transaction_date,currency,
1193: amount,hce_amount,amount_date,transaction_rate,
1194: cashflow_amount,company_code,account_no,action_code,cparty_account_no,
1205: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
1206: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1207: -- Dummy row for deal dealt date, this allows a journal action
1208: -- to be set up for a premium to occur on the deal date (date type DEALT)
1209: insert into XTR_DEAL_DATE_AMOUNTS
1210: (deal_type,amount_type,date_type,
1211: deal_number,transaction_number,transaction_date,currency,
1212: amount,hce_amount,amount_date,transaction_rate,
1213: cashflow_amount,company_code,deal_subtype,product_type,
1230: if P_SETTLE_DATE is NOT NULL then
1231: xtr_fps2_p.standing_settlements(P_CPARTY_CODE,P_CURRENCY,'SWPTN',
1232: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,'SETTLE',
1233: v_settle_ref,v_settle_ac);
1234: insert into XTR_DEAL_DATE_AMOUNTS
1235: (deal_type,amount_type,date_type,
1236: deal_number,transaction_number,transaction_date,currency,
1237: amount,hce_amount,amount_date,transaction_rate,
1238: cashflow_amount,company_code,account_no,action_code,
1249: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
1250: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1251: end if;
1252: -- Underlying Physical Start Details
1253: insert into XTR_DEAL_DATE_AMOUNTS
1254: (deal_type,amount_type,date_type,
1255: deal_number,transaction_number,transaction_date,currency,
1256: amount,hce_amount,amount_date,transaction_rate,
1257: cashflow_amount,company_code,deal_subtype,product_type,
1264: P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1265: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1266: P_CPARTY_CODE,'N',P_PORTFOLIO_CODE);
1267: -- Underlying Physical Maturity Details
1268: insert into XTR_DEAL_DATE_AMOUNTS
1269: (deal_type,amount_type,date_type,
1270: deal_number,transaction_number,transaction_date,currency,
1271: amount,hce_amount,amount_date,transaction_rate,
1272: cashflow_amount,company_code,deal_subtype,product_type,
1282: --
1283: elsif P_ACTION = 'UPDATE' then
1284: -- Delete rows in DDA for cancelled transactions
1285: if P_STATUS_CODE = 'CANCELLED' then
1286: delete from XTR_DEAL_DATE_AMOUNTS
1287: where deal_number = P_DEAL_NO;
1288: --
1289: if P_DEAL_TYPE = 'BOND' then
1290: -- coupons
1315: --
1316: -- Where not cancelled
1317: else
1318: if P_DEAL_TYPE = 'FRA' then
1319: update XTR_DEAL_DATE_AMOUNTS
1320: set amount = decode(AMOUNT_TYPE,'FACEVAL',P_FACE_VALUE_AMOUNT,amount),
1321: hce_amount = decode(AMOUNT_TYPE,'FACEVAL',P_FACE_VALUE_HCE_AMOUNT,hce_amount),
1322: amount_date = decode(DATE_TYPE,'COMENCE',P_START_DATE,'MATURE',P_MATURITY_DATE,'DEALT',P_DEAL_DATE,
1323: 'RATESET',P_RATE_FIXING_DATE,amount_date),
1344: if P_SETTLE_DATE IS NOT NULL then
1345: open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
1346: fetch C_LIMIT_WEIGHTING into v_weighting;
1347: close C_LIMIT_WEIGHTING;
1348: update XTR_DEAL_DATE_AMOUNTS
1349: set AMOUNT = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
1350: HCE_AMOUNT = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
1351: AMOUNT_DATE = P_SETTLE_DATE,
1352: STATUS_CODE = P_STATUS_CODE,
1363: limit_party = P_CPARTY_CODE
1364: where DEAL_NUMBER = P_DEAL_NO
1365: and DATE_TYPE = 'LIMIT'
1366: and DEAL_TYPE = 'FRA';
1367: update XTR_DEAL_DATE_AMOUNTS
1368: set AMOUNT = nvl(P_SETTLE_AMOUNT,0),
1369: ACTION_CODE = P_SETTLE_ACTION,
1370: HCE_AMOUNT = nvl(P_SETTLE_HCE_AMOUNT,0),
1371: CASHFLOW_AMOUNT = (decode(P_SETTLE_ACTION,'PAY',-1,1) * nvl(P_SETTLE_AMOUNT,0)),
1389: and DEAL_TYPE = 'FRA';
1390: --
1391: if SQL%NOTFOUND then
1392: -- Settlement Row
1393: insert into XTR_DEAL_DATE_AMOUNTS
1394: (deal_type,amount_type,date_type,
1395: deal_number,transaction_number,transaction_date,currency,
1396: amount,hce_amount,amount_date,transaction_rate,
1397: cashflow_amount,company_code,account_no,cparty_account_no,action_code,
1408: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N');
1409: end if;
1410: end if;
1411: elsif P_DEAL_TYPE = 'FXO' THEN
1412: update XTR_DEAL_DATE_AMOUNTS
1413: set amount = decode(amount_type,'FXOBUY',P_BUY_AMOUNT,P_SELL_AMOUNT),
1414: hce_amount = decode(amount_type,'FXOBUY',P_BUY_HCE_AMOUNT,P_SELL_HCE_AMOUNT),
1415: amount_date = P_EXPIRY_DATE,
1416: -- account_no = decode(amount_type,'FXOBUY',P_BUY_ACCOUNT_NO,P_SELL_ACCOUNT_NO),
1438: and deal_type = P_DEAL_TYPE;
1439:
1440: if SQL%NOTFOUND then
1441: if P_STATUS_CODE='CURRENT' and nvl(P_KNOCK_TYPE,'@#@')='I' and nvl(P_KNOCK_INSERT_TYPE,'D')='E'then
1442: insert into XTR_DEAL_DATE_AMOUNTS
1443: (deal_type,amount_type,date_type,
1444: deal_number,transaction_number,transaction_date,currency,
1445: amount,hce_amount,amount_date,transaction_rate,
1446: cashflow_amount,company_code,deal_subtype,product_type,
1455: decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1456: decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
1457: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,l_combin,P_QUICK_INPUT);
1458: --
1459: insert into XTR_DEAL_DATE_AMOUNTS
1460: (deal_type,amount_type,date_type,
1461: deal_number,transaction_number,transaction_date,currency,
1462: amount,hce_amount,amount_date,transaction_rate,
1463: cashflow_amount,company_code,deal_subtype,product_type,
1473: end if;
1474: end if;
1475:
1476: --
1477: update XTR_DEAL_DATE_AMOUNTS
1478: set amount_date = P_DEAL_DATE,
1479: transaction_date = P_DEAL_DATE,
1480: currency = P_CURRENCY_BUY,
1481: company_code = P_COMPANY_CODE,
1489: where deal_number = P_DEAL_NO
1490: and date_type = 'DEALT'
1491: and deal_type = P_DEAL_TYPE;
1492: --
1493: update XTR_DEAL_DATE_AMOUNTS
1494: set amount = P_PREMIUM_AMOUNT,
1495: hce_amount = P_PREMIUM_HCE_AMOUNT,
1496: amount_date = P_PREMIUM_DATE,
1497: cashflow_amount = DECODE(P_PREMIUM_ACTION,'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
1523: close CHK_FXO_PREMIUM;
1524:
1525: if (dda_premium_v <> 1) then
1526:
1527: insert into XTR_DEAL_DATE_AMOUNTS
1528: (deal_type,amount_type,date_type,
1529: deal_number,transaction_number,transaction_date,currency,
1530: amount,hce_amount,amount_date,transaction_rate,
1531: cashflow_amount,company_code,account_no,action_code,
1548: if P_STATUS_CODE='CURRENT'
1549: and (nvl(P_KNOCK_TYPE,'O')='O'
1550: or (P_KNOCK_TYPE='I' and nvl(P_KNOCK_INSERT_TYPE,'D')='E'))
1551: and P_INSERT_FOR_CASHFLOW = 'Y' then
1552: update XTR_DEAL_DATE_AMOUNTS
1553: set amount = P_SELL_AMOUNT,
1554: hce_amount = P_SELL_HCE_AMOUNT,
1555: amount_date = decode(date_type,'VALUE',P_VALUE_DATE,P_EXPIRY_DATE),
1556: exposure_ref_date = decode(date_type,'VALUE',P_START_DATE,exposure_ref_date),
1576: where deal_number = P_DEAL_NO
1577: and date_type = 'VALUE'
1578: and deal_type = P_DEAL_TYPE;
1579: if SQL%NOTFOUND then
1580: insert into XTR_DEAL_DATE_AMOUNTS
1581: (deal_type,amount_type,date_type,
1582: deal_number,transaction_number,transaction_date,currency,
1583: amount,hce_amount,amount_date,transaction_rate,
1584: cashflow_amount,company_code,account_no,
1593: P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
1594: P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
1595: P_DEALER_CODE,l_combin,P_START_DATE);
1596: --
1597: insert into XTR_DEAL_DATE_AMOUNTS
1598: (deal_type,amount_type,date_type,
1599: deal_number,transaction_number,transaction_date,currency,
1600: amount,hce_amount,amount_date,transaction_rate,
1601: cashflow_amount,company_code,account_no,
1611: P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
1612: P_DEALER_CODE,l_combin,P_START_DATE);
1613: end if;
1614: else
1615: delete from XTR_DEAL_DATE_AMOUNTS
1616: where deal_number = P_DEAL_NO
1617: and deal_type = P_DEAL_TYPE
1618: and date_type = 'VALUE';
1619: end if;
1623: -- Bug 8561305 Starts
1624:
1625: if p_deal_type = 'FXO' then
1626: if (P_KNOCK_TYPE='I' and P_KNOCK_INSERT_TYPE IS NULL) then
1627: insert into XTR_DEAL_DATE_AMOUNTS
1628: (deal_type,amount_type,date_type,
1629: deal_number,transaction_number,transaction_date,currency,
1630: amount,hce_amount,amount_date,transaction_rate,
1631: cashflow_amount,company_code,account_no,action_code,
1642: 'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE);
1643:
1644: else
1645:
1646: delete from XTR_DEAL_DATE_AMOUNTS
1647: where deal_number = P_DEAL_NO
1648: and deal_type = P_DEAL_TYPE
1649: and date_type = 'VALUE';
1650:
1647: where deal_number = P_DEAL_NO
1648: and deal_type = P_DEAL_TYPE
1649: and date_type = 'VALUE';
1650:
1651: update XTR_DEAL_DATE_AMOUNTS
1652: set AMOUNT_DATE = P_EXPIRY_DATE
1653: where DEAL_NUMBER = P_DEAL_NO
1654: and DEAL_TYPE = P_DEAL_TYPE
1655: and DATE_TYPE = 'EXPIRY';
1656:
1657: end if;
1658:
1659: else
1660: delete from XTR_DEAL_DATE_AMOUNTS
1661: where deal_number = P_DEAL_NO
1662: and deal_type = P_DEAL_TYPE
1663: and date_type in ('VALUE','EXPIRY');
1664: end if;
1665: --Bug 8561305 ends.
1666:
1667: elsif P_STATUS_CODE = 'EXERCISED' then
1668: -- AW Bug 894751 American Option
1669: delete from XTR_DEAL_DATE_AMOUNTS
1670: where deal_number = P_DEAL_NO
1671: and deal_type = P_DEAL_TYPE
1672: and date_type = 'VALUE';
1673: --
1670: where deal_number = P_DEAL_NO
1671: and deal_type = P_DEAL_TYPE
1672: and date_type = 'VALUE';
1673: --
1674: update XTR_DEAL_DATE_AMOUNTS
1675: set DATE_TYPE = 'SETTLE',
1676: --BUG 8561305 starts
1677: AMOUNT_DATE = p_settle_date,
1678: --AMOUNT_DATE = trunc(sysdate),
1682: and DEAL_TYPE = P_DEAL_TYPE
1683: and DATE_TYPE = 'EXPIRY';
1684:
1685: --Bug 8561305 starts
1686: update XTR_DEAL_DATE_AMOUNTS
1687: set AMOUNT_DATE = p_settle_date
1688: where DEAL_NUMBER = P_DEAL_NO
1689: and DEAL_TYPE = P_DEAL_TYPE
1690: and DATE_TYPE = 'SETTLE';
1707: ))
1708:
1709: THEN
1710:
1711: UPDATE XTR_DEAL_DATE_AMOUNTS
1712: SET AMOUNT = P_BUY_AMOUNT,
1713: HCE_AMOUNT = nvl(P_BUY_HCE_AMOUNT,0),
1714: STATUS_CODE = P_STATUS_CODE,
1715: AMOUNT_DATE = P_START_DATE,
1733: AND DATE_TYPE = 'VALUE'
1734: AND AMOUNT_TYPE = 'BUY'
1735: AND TRANSACTION_NUMBER = 1;
1736:
1737: update XTR_DEAL_DATE_AMOUNTS
1738: set amount = P_SELL_AMOUNT,
1739: hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1740: amount_date = P_START_DATE,
1741: cashflow_amount = 0,
1760: and deal_type = P_DEAL_TYPE
1761: AND date_type = 'VALUE'
1762: and transaction_number = 1;
1763:
1764: update XTR_DEAL_DATE_AMOUNTS
1765: set amount = P_SELL_AMOUNT,
1766: hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1767: status_code = P_STATUS_CODE,
1768: amount_date = P_DEAL_DATE,
1783: and deal_type = P_DEAL_TYPE;
1784:
1785: /*--------------------------- RVALLAMS FX REARCH ----------------------------- */
1786: ELSE
1787: update XTR_DEAL_DATE_AMOUNTS
1788: set AMOUNT = P_BUY_AMOUNT,
1789: HCE_AMOUNT = nvl(P_BUY_HCE_AMOUNT,0),
1790: AMOUNT_DATE = P_VALUE_DATE,
1791: STATUS_CODE = P_STATUS_CODE,
1808: and deal_type = P_DEAL_TYPE
1809: AND date_type = 'VALUE'
1810: and transaction_number = 1;
1811: --
1812: update XTR_DEAL_DATE_AMOUNTS
1813: set amount = P_SELL_AMOUNT,
1814: hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1815: amount_date = P_VALUE_DATE,
1816: cashflow_amount = (-1) * P_SELL_AMOUNT,
1834: and deal_type = P_DEAL_TYPE
1835: AND date_type = 'VALUE'
1836: and transaction_number = 1;
1837: --
1838: update XTR_DEAL_DATE_AMOUNTS
1839: set amount = P_SELL_AMOUNT,
1840: hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1841: status_code = P_STATUS_CODE,
1842: amount_date = P_DEAL_DATE,
1857: and deal_type = P_DEAL_TYPE;
1858: END IF;
1859: --
1860: elsif P_DEAL_TYPE = 'NI' then
1861: update XTR_DEAL_DATE_AMOUNTS
1862: set ACCOUNT_NO = nvl(v_MATURITY_ACCOUNT_NO, account_no),
1863: STATUS_CODE = P_STATUS_CODE,
1864: CPARTY_ACCOUNT_NO = nvl(v_cparty_account_no, cparty_account_no),
1865: COMMENCE_DATE = P_START_DATE,
1919: if P_DEAL_SUBTYPE in ('BUY','SHORT','ISSUE') then
1920: open CHK_NI_BAL_FV;
1921: fetch CHK_NI_BAL_FV into l_dummy;
1922: if CHK_NI_BAL_FV%NOTFOUND and nvl(P_MATURITY_BALANCE_AMOUNT,0) <> 0 then
1923: insert into XTR_DEAL_DATE_AMOUNTS
1924: (deal_type,amount_type,date_type,
1925: deal_number,transaction_number,transaction_date,currency,
1926: amount,hce_amount,amount_date,transaction_rate,
1927: cashflow_amount,company_code,account_no,
1950: ------------
1951: /* Not required.
1952: ---- 06/08/99 Apply PL to dda for sale deal. refer to bug 904365
1953: if P_DEAL_SUBTYPE='SELL' then
1954: update XTR_DEAL_DATE_AMOUNTS
1955: set AMOUNT_DATE = P_START_DATE,
1956: AMOUNT = abs(nvl(P_NI_PROFIT_LOSS,0)),
1957: HCE_AMOUNT = abs(nvl(round(P_NI_PROFIT_LOSS/hce_rate,2),0)),
1958: ACTION_CODE = decode(sign(nvl(P_NI_PROFIT_LOSS,0)),-1,'LOSS','PROFIT'),
1962: and DATE_TYPE ='REVAL';
1963: --
1964: if SQL%NOTFOUND and nvl(P_NI_PROFIT_LOSS,0) <> 0 then
1965: -- Set the profit and loss info into the database...
1966: insert into XTR_DEAL_DATE_AMOUNTS
1967: (deal_type,amount_type,date_type,
1968: deal_number,transaction_number,transaction_date,currency,
1969: amount,hce_amount,amount_date,transaction_rate,
1970: cashflow_amount,company_code,status_code,dealer_code,
1981: end if;
1982: */
1983: --
1984: elsif P_DEAL_TYPE = 'BOND' then
1985: update XTR_DEAL_DATE_AMOUNTS
1986: set amount = P_START_AMOUNT,
1987: hce_amount = P_START_HCE_AMOUNT,
1988: cashflow_amount = decode(P_DEAL_SUBTYPE,'BUY',1,-1) * P_START_AMOUNT,
1989: transaction_rate = P_INTEREST_RATE,
1995: and amount_type = 'COMENCE'
1996: and date_type = 'COMENCE'
1997: and deal_type = P_DEAL_TYPE;
1998: --
1999: update XTR_DEAL_DATE_AMOUNTS
2000: set amount = P_MATURITY_AMOUNT,
2001: hce_amount = P_MATURITY_HCE_AMOUNT,
2002: cashflow_amount = decode(P_DEAL_SUBTYPE,'BUY',1,-1) * P_MATURITY_AMOUNT,
2003: transaction_rate = P_INTEREST_RATE,
2011: and deal_type = P_DEAL_TYPE
2012: and amount_date >= L_SYSDATE;
2013: --
2014: if P_INT_VALUE is not null then
2015: update XTR_DEAL_DATE_AMOUNTS
2016: set amount = P_INT_VALUE,
2017: hce_amount = round(P_INT_VALUE/hce_rate,round_fac),
2018: transaction_date = P_DEAL_DATE,
2019: transaction_rate = P_INTEREST_RATE
2024: and amount_date >= L_SYSDATE;
2025: end if;
2026: --
2027: if nvl(P_PREM_VALUE,0) <> 0 then
2028: update XTR_DEAL_DATE_AMOUNTS
2029: set amount = abs(P_PREM_VALUE),
2030: hce_amount = abs(round(P_PREM_VALUE/hce_rate,round_fac)),
2031: transaction_date = P_DEAL_DATE,
2032: transaction_rate = P_INTEREST_RATE
2037: and amount_date >= L_SYSDATE;
2038: end if;
2039: --
2040: elsif P_DEAL_TYPE = 'IRO' then
2041: update XTR_DEAL_DATE_AMOUNTS
2042: set amount = nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
2043: hce_amount = nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
2044: amount_date = P_EXPIRY_DATE,
2045: transaction_rate = P_INTEREST_RATE,
2056: where deal_number = P_DEAL_NO
2057: and deal_type = 'IRO'
2058: and date_type = 'EXPIRY';
2059: --
2060: update XTR_DEAL_DATE_AMOUNTS
2061: set amount = nvl(P_PREMIUM_AMOUNT,0),
2062: hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2063: amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
2064: transaction_date = P_DEAL_DATE,
2078: where deal_number = P_DEAL_NO
2079: and deal_type = 'IRO'
2080: and date_type = 'PREMIUM';
2081: --
2082: update XTR_DEAL_DATE_AMOUNTS
2083: set amount_date = P_DEAL_DATE,
2084: transaction_date = P_DEAL_DATE,
2085: currency = P_CURRENCY,
2086: company_code = P_COMPANY_CODE,
2094: where deal_number = P_DEAL_NO
2095: and deal_type = 'IRO'
2096: and date_type = 'DEALT';
2097: --
2098: update XTR_DEAL_DATE_AMOUNTS
2099: set amount_date = P_MATURITY_DATE,
2100: transaction_date = P_DEAL_DATE,
2101: currency = P_CURRENCY,
2102: company_code = P_COMPANY_CODE,
2110: where deal_number = P_DEAL_NO
2111: and deal_type = 'IRO'
2112: and date_type = 'MATURE';
2113: --
2114: update XTR_DEAL_DATE_AMOUNTS
2115: set amount_date = P_START_DATE,
2116: transaction_date = P_DEAL_DATE,
2117: currency = P_CURRENCY,
2118: company_code = P_COMPANY_CODE,
2133:
2134: open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
2135: fetch C_LIMIT_WEIGHTING into v_weighting;
2136: close C_LIMIT_WEIGHTING;
2137: update XTR_DEAL_DATE_AMOUNTS
2138: set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
2139: hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
2140: amount_date = P_SETTLE_DATE,
2141: transaction_date = P_DEAL_DATE,
2154: and deal_type = 'IRO'
2155: and date_type = 'LIMIT';
2156: --
2157: -- AW Bug 894751
2158: delete from XTR_DEAL_DATE_AMOUNTS
2159: where deal_number = P_DEAL_NO
2160: and deal_type = 'IRO'
2161: and date_type = 'EXPIRY';
2162: --
2159: where deal_number = P_DEAL_NO
2160: and deal_type = 'IRO'
2161: and date_type = 'EXPIRY';
2162: --
2163: update XTR_DEAL_DATE_AMOUNTS
2164: set amount = nvl(P_SETTLE_AMOUNT,0),
2165: hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
2166: amount_date = P_SETTLE_DATE,
2167: cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
2182: where deal_number = P_DEAL_NO
2183: and deal_type = 'IRO'
2184: and date_type = 'SETTLE';
2185: --
2186: --Bug 3060946 Removed 'Insert into XTR_DEAL_DATE_AMOUNTS' statement
2187: --
2188: end if;
2189: --
2190: elsif P_DEAL_TYPE = 'BDO' then
2187: --
2188: end if;
2189: --
2190: elsif P_DEAL_TYPE = 'BDO' then
2191: update XTR_DEAL_DATE_AMOUNTS
2192: set amount = nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
2193: hce_amount = nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
2194: amount_date = P_EXPIRY_DATE,
2195: transaction_rate = P_INTEREST_RATE,
2206: where deal_number = P_DEAL_NO
2207: and deal_type = 'BDO'
2208: and date_type = 'EXPIRY';
2209: --
2210: update XTR_DEAL_DATE_AMOUNTS
2211: set amount = nvl(P_PREMIUM_AMOUNT,0),
2212: hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2213: amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
2214: transaction_date = P_DEAL_DATE,
2228: where deal_number = P_DEAL_NO
2229: and deal_type = 'BDO'
2230: and date_type = 'PREMIUM';
2231: --
2232: update XTR_DEAL_DATE_AMOUNTS
2233: set amount_date = P_DEAL_DATE,
2234: transaction_date = P_DEAL_DATE,
2235: currency = P_CURRENCY,
2236: company_code = P_COMPANY_CODE,
2244: where deal_number = P_DEAL_NO
2245: and deal_type = 'BDO'
2246: and date_type = 'DEALT';
2247: --
2248: update XTR_DEAL_DATE_AMOUNTS
2249: set amount_date = P_MATURITY_DATE,
2250: transaction_date = P_DEAL_DATE,
2251: currency = P_CURRENCY,
2252: company_code = P_COMPANY_CODE,
2260: where deal_number = P_DEAL_NO
2261: and deal_type = 'BDO'
2262: and date_type = 'MATURE';
2263: --
2264: update XTR_DEAL_DATE_AMOUNTS
2265: set amount_date = P_START_DATE,
2266: transaction_date = P_DEAL_DATE,
2267: currency = P_CURRENCY,
2268: company_code = P_COMPANY_CODE,
2281: xtr_fps2_p.standing_settlements(P_CPARTY_CODE,P_CURRENCY,'BDO',
2282: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,'SETTLE',
2283: v_settle_ref,v_settle_ac);
2284: -- AW Bug 894751
2285: delete from XTR_DEAL_DATE_AMOUNTS
2286: where deal_number = P_DEAL_NO
2287: and deal_type = 'BDO'
2288: and date_type = 'EXPIRY';
2289: --
2290: if P_EXERCISE_PRICE is not null then
2291: open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
2292: fetch C_LIMIT_WEIGHTING into v_weighting;
2293: close C_LIMIT_WEIGHTING;
2294: update XTR_DEAL_DATE_AMOUNTS
2295: set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
2296: hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
2297: amount_date = P_SETTLE_DATE,
2298: transaction_date = P_DEAL_DATE,
2310: where deal_number = P_DEAL_NO
2311: and deal_type = 'BDO'
2312: and date_type = 'LIMIT';
2313: --
2314: update XTR_DEAL_DATE_AMOUNTS
2315: set amount = nvl(P_SETTLE_AMOUNT,0),
2316: hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
2317: amount_date = P_SETTLE_DATE,
2318: cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
2334: and deal_type = 'BDO'
2335: and date_type = 'SETTLE';
2336: --
2337: if SQL%NOTFOUND and P_SETTLE_DATE is NOT NULL then
2338: insert into XTR_DEAL_DATE_AMOUNTS
2339: (deal_type,amount_type,date_type,
2340: deal_number,transaction_number,transaction_date,currency,
2341: amount,hce_amount,amount_date,transaction_rate,
2342: cashflow_amount,company_code,account_no,action_code,
2360: open CHK_BDO_SETTLE_ROWS;
2361: fetch CHK_BDO_SETTLE_ROWS into l_dummy;
2362:
2363: if CHK_BDO_SETTLE_ROWS%NOTFOUND then
2364: insert into XTR_DEAL_DATE_AMOUNTS
2365: (deal_type,amount_type,date_type,
2366: deal_number,transaction_number,transaction_date,currency,
2367: amount,hce_amount,amount_date,transaction_rate,
2368: cashflow_amount,company_code,deal_subtype,product_type,
2380: --
2381: end if;
2382: --
2383: elsif P_DEAL_TYPE = 'FUT' then
2384: update XTR_DEAL_DATE_AMOUNTS
2385: set amount = P_START_AMOUNT,
2386: hce_amount = P_START_HCE_AMOUNT,
2387: transaction_rate = nvl(P_TRANSACTION_RATE,P_CONTRACT_RATE),
2388: amount_date = P_EXPIRY_DATE,
2402: where deal_number = P_DEAL_NO
2403: and deal_type = 'FUT'
2404: and amount_type = 'FACEVAL';
2405: --
2406: update XTR_DEAL_DATE_AMOUNTS
2407: set amount = nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0),
2408: hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2409: cashflow_amount = -(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
2410: amount_date = P_PREMIUM_DATE,
2426: and amount_type = 'PREMIUM';
2427: --
2428: if SQL%NOTFOUND and nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) > 0 then
2429: -- Settlement Amount
2430: insert into XTR_DEAL_DATE_AMOUNTS
2431: (deal_type,amount_type,date_type,
2432: deal_number,transaction_number,transaction_date,currency,
2433: amount,hce_amount,amount_date,transaction_rate,
2434: cashflow_amount,company_code,account_no,action_code,
2443: 'CURRENT',P_DEALER_CODE,P_CLIENT_CODE,
2444: P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
2445: end if;
2446: --
2447: update XTR_DEAL_DATE_AMOUNTS
2448: set amount_date = P_DEAL_DATE,
2449: transaction_date = P_DEAL_DATE,
2450: currency = P_PREMIUM_CURRENCY,
2451: company_code = P_COMPANY_CODE,
2460: and deal_type = 'FUT'
2461: and amount_type = 'DEALT';
2462: --
2463: if P_SETTLE_ACTION is NOT NULL then
2464: update XTR_DEAL_DATE_AMOUNTS
2465: set amount = P_SETTLE_AMOUNT,
2466: hce_amount = P_SETTLE_HCE_AMOUNT,
2467: amount_date = P_SETTLE_DATE,
2468: cashflow_amount =
2484: where deal_number = P_DEAL_NO
2485: and deal_type = 'FUT'
2486: and amount_type = 'SETTLE';
2487: if SQL%NOTFOUND then
2488: insert into XTR_DEAL_DATE_AMOUNTS
2489: (deal_type,amount_type,date_type,
2490: deal_number,transaction_number,transaction_date,currency,
2491: amount,hce_amount,amount_date,transaction_rate,
2492: cashflow_amount,company_code,account_no,action_code,
2504: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
2505: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
2506: end if;
2507: --
2508: update XTR_DEAL_DATE_AMOUNTS
2509: set limit_code = NULL
2510: where deal_number = P_DEAL_NO
2511: and limit_code is NOT NULL;
2512: end if;
2511: and limit_code is NOT NULL;
2512: end if;
2513: --
2514: elsif P_DEAL_TYPE = 'SWPTN' then
2515: update XTR_DEAL_DATE_AMOUNTS
2516: set amount_date = decode(date_type,'DEALT',P_DEAL_DATE,
2517: 'COMENCE',P_START_DATE,
2518: 'MATURE',P_MATURITY_DATE,amount_date),
2519: amount = decode(amount_type,'N/A',0,
2533: where deal_number = P_DEAL_NO
2534: and deal_type = 'SWPTN'
2535: and date_type not in ('EXPIRY','PREMIUM','SETTLE');
2536: --
2537: update XTR_DEAL_DATE_AMOUNTS
2538: set amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
2539: amount = nvl(P_PREMIUM_AMOUNT,0),
2540: hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2541: cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1)*nvl(P_PREMIUM_AMOUNT,0),
2556: where deal_number = P_DEAL_NO
2557: and deal_type = 'SWPTN'
2558: and date_type = 'PREMIUM';
2559: --
2560: update XTR_DEAL_DATE_AMOUNTS
2561: set amount = P_FACE_VALUE_AMOUNT,
2562: hce_amount = P_FACE_VALUE_HCE_AMOUNT,
2563: amount_date = P_EXPIRY_DATE,
2564: transaction_rate = P_INTEREST_RATE,
2580: xtr_fps2_p.standing_settlements(P_CPARTY_CODE,P_CURRENCY,'SWPTN',
2581: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,'SETTLE',
2582: v_settle_ref,v_settle_ac);
2583: -- AW Bug 894751
2584: delete from XTR_DEAL_DATE_AMOUNTS
2585: where deal_number = P_DEAL_NO
2586: and deal_type = 'SWPTN'
2587: and date_type = 'EXPIRY';
2588: --
2589: if P_SETTLE_RATE is not null then
2590: open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
2591: fetch C_LIMIT_WEIGHTING into v_weighting;
2592: close C_LIMIT_WEIGHTING;
2593: update XTR_DEAL_DATE_AMOUNTS
2594: set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
2595: hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
2596: amount_date = P_SETTLE_DATE,
2597: status_code = P_STATUS_CODE,
2608: where deal_number = P_DEAL_NO
2609: and deal_type = 'SWPTN'
2610: and date_type = 'LIMIT';
2611: --
2612: update XTR_DEAL_DATE_AMOUNTS
2613: set amount = nvl(P_SETTLE_AMOUNT,0),
2614: hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
2615: amount_date = P_SETTLE_DATE,
2616: cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
2629: where deal_number = P_DEAL_NO
2630: and deal_type = 'SWPTN'
2631: and date_type = 'SETTLE';
2632: --
2633: --Bug 3060946 Removed 'insert into XTR_DEAL_DATE_AMOUNTS' statement
2634: --
2635: else -- AW Bug 894751
2636: -- This is for creating swap.
2637: -- JT bug 1312363 while validating after exercise the insert was giving
2641: open CHK_SWPTN_SETTLE_ROWS;
2642: fetch CHK_SWPTN_SETTLE_ROWS into l_dummy;
2643:
2644: if CHK_SWPTN_SETTLE_ROWS%NOTFOUND then
2645: insert into XTR_DEAL_DATE_AMOUNTS
2646: (deal_type,amount_type,date_type,
2647: deal_number,transaction_number,transaction_date,currency,
2648: amount,hce_amount,amount_date,transaction_rate,
2649: cashflow_amount,company_code,deal_subtype,product_type,
2658: end if;
2659: close CHK_SWPTN_SETTLE_ROWS;
2660: end if;
2661: --
2662: update XTR_DEAL_DATE_AMOUNTS
2663: set limit_code = NULL,
2664: status_code = P_STATUS_CODE
2665: where deal_number = P_DEAL_NO
2666: and limit_code is not null