[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: --
227: begin
228:
229: /* code below added by Ilavenil to fix the bug # 2065586
230: Let us assume, a deal is created using the deal input form. At the time of creation of deal the
231: account numbers are set. Say for example the account number is 'AAA'. Xtr_Deals, Xtr_Deal_Date_Amounts
232: will store the account number 'AAA'. User then goes to settlement form
233: and goes for a different account number for settlement. Say for example the account number is 'BBB'.
234: Xtr_Deal_Date_Amounts will now save 'BBB' instead of 'AAA' for the same deal no.
235:
230: Let us assume, a deal is created using the deal input form. At the time of creation of deal the
231: account numbers are set. Say for example the account number is 'AAA'. Xtr_Deals, Xtr_Deal_Date_Amounts
232: will store the account number 'AAA'. User then goes to settlement form
233: and goes for a different account number for settlement. Say for example the account number is 'BBB'.
234: Xtr_Deal_Date_Amounts will now save 'BBB' instead of 'AAA' for the same deal no.
235:
236: Case i : User then goes to the input form and queries the same deal number. In future the user may be allowed to update
237: the deal no for the deal. In that case, let us assume that the user modified the deal account number as
238: 'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
234: Xtr_Deal_Date_Amounts will now save 'BBB' instead of 'AAA' for the same deal no.
235:
236: Case i : User then goes to the input form and queries the same deal number. In future the user may be allowed to update
237: the deal no for the deal. In that case, let us assume that the user modified the deal account number as
238: 'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
239:
240: Case ii : User goes to input form and queries the deal number. User updates a field other than account numbers.
241: The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
242: Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
237: the deal no for the deal. In that case, let us assume that the user modified the deal account number as
238: 'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
239:
240: Case ii : User goes to input form and queries the deal number. User updates a field other than account numbers.
241: The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
242: Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
243:
244: So, we always check whether in Xtr_Deals, the old account number <> new account number. If yes, then
245: update Xtr_Deal_Date_Amounts to new account number of Xtr_Deals. If no, then leave Xtr_Deal_Date_Amounts
238: 'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
239:
240: Case ii : User goes to input form and queries the deal number. User updates a field other than account numbers.
241: The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
242: Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
243:
244: So, we always check whether in Xtr_Deals, the old account number <> new account number. If yes, then
245: update Xtr_Deal_Date_Amounts to new account number of Xtr_Deals. If no, then leave Xtr_Deal_Date_Amounts
246: account number as is.
241: The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
242: Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
243:
244: So, we always check whether in Xtr_Deals, the old account number <> new account number. If yes, then
245: update Xtr_Deal_Date_Amounts to new account number of Xtr_Deals. If no, then leave Xtr_Deal_Date_Amounts
246: account number as is.
247:
248: We basically do not want to overwrite an account number set at settlement by original account number.
249:
332: --
333: -- For insert of new Rows
334: if P_ACTION = 'INSERT' and P_STATUS_CODE = 'CURRENT' then
335: if P_DEAL_TYPE = 'FRA' then
336: insert into XTR_DEAL_DATE_AMOUNTS
337: (deal_type,amount_type,date_type,
338: deal_number,transaction_number,transaction_date,currency,
339: amount,hce_amount,amount_date,transaction_rate,
340: cashflow_amount,company_code,deal_subtype,
349: P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
350: P_CLIENT_CODE,P_CPARTY_CODE,'N',
351: NULL,P_CPARTY_CODE,P_START_DATE,P_QUICK_INPUT);
352: -- Dummy row for deal maturity date, this allows a journal action, accruals
353: insert into XTR_DEAL_DATE_AMOUNTS
354: (deal_type,amount_type,date_type,
355: deal_number,transaction_number,transaction_date,currency,
356: amount,hce_amount,amount_date,transaction_rate,
357: cashflow_amount,company_code,deal_subtype,product_type,
363: P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
364: P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
365: -- Dummy row for deal dealt date, this allows a journal action
366: -- to be set up for a premium to occur on the deal date (date type DEALT)
367: insert into XTR_DEAL_DATE_AMOUNTS
368: (deal_type,amount_type,date_type,
369: deal_number,transaction_number,transaction_date,currency,
370: amount,hce_amount,amount_date,transaction_rate,
371: cashflow_amount,company_code,deal_subtype,product_type,
377: P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
378: P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
379: -- Row for date_type of LIMIT so that the risk between the settlement and
380: -- exercise date is acknowledged
381: insert into XTR_DEAL_DATE_AMOUNTS
382: (deal_type,amount_type,date_type,
383: deal_number,transaction_number,transaction_date,currency,
384: amount,hce_amount,amount_date,
385: cashflow_amount,company_code,deal_subtype,product_type,
393: P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT,
394: nvl(P_LIMIT_CODE,'NILL'),P_CPARTY_CODE);
395: if P_RATE_FIXING_DATE is not null then
396: -- Dummy row for deal rate set date
397: insert into XTR_DEAL_DATE_AMOUNTS
398: (deal_type,amount_type,date_type,
399: deal_number,transaction_number,transaction_date,currency,
400: amount,hce_amount,amount_date,transaction_rate,
401: cashflow_amount,company_code,deal_subtype,product_type,
413:
414: elsif P_DEAL_TYPE = 'FXO' then
415:
416: if nvl(P_KNOCK_TYPE,'O') = 'O' then
417: insert into XTR_DEAL_DATE_AMOUNTS
418: (deal_type,amount_type,date_type,
419: deal_number,transaction_number,transaction_date,currency,
420: amount,hce_amount,amount_date,transaction_rate,
421: cashflow_amount,company_code,deal_subtype,product_type,
430: decode(P_CURRENCY_BUY,substr(upper(l_combin),1,3),nvl(P_LIMIT_CODE,'NILL'),NULL),
431: decode(P_CURRENCY_BUY,substr(upper(l_combin),1,3),P_CPARTY_CODE,NULL),
432: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,l_combin,P_QUICK_INPUT);
433: --
434: insert into XTR_DEAL_DATE_AMOUNTS
435: (deal_type,amount_type,date_type,
436: deal_number,transaction_number,transaction_date,currency,
437: amount,hce_amount,amount_date,transaction_rate,
438: cashflow_amount,company_code,deal_subtype,product_type,
446: decode(P_CURRENCY_SELL,substr(upper(l_combin),1,3),P_CPARTY_CODE,NULL),
447: P_DEALER_CODE,l_combin,P_QUICK_INPUT);
448: -- Value Date Amounts (To be used in cashflow Projections if indicated as reqd in deal input
449: if nvl(P_INSERT_FOR_CASHFLOW,'N') = 'Y' then
450: insert into XTR_DEAL_DATE_AMOUNTS
451: (deal_type,amount_type,date_type,
452: deal_number,transaction_number,transaction_date,currency,
453: amount,hce_amount,amount_date,transaction_rate,
454: cashflow_amount,company_code,account_no,
464: P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
465: P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
466: P_DEALER_CODE,l_combin,P_START_DATE,P_QUICK_INPUT);
467: --
468: insert into XTR_DEAL_DATE_AMOUNTS
469: (deal_type,amount_type,date_type,
470: deal_number,transaction_number,transaction_date,currency,
471: amount,hce_amount,amount_date,transaction_rate,
472: cashflow_amount,company_code,account_no,
484: P_DEALER_CODE,l_combin,P_START_DATE,P_QUICK_INPUT);
485: end if;
486: --
487: if P_CURRENCY IS NOT NULL and P_PREMIUM_ACTION is NOT NULL and nvl(P_PREMIUM_AMOUNT,0) > 0 then
488: insert into XTR_DEAL_DATE_AMOUNTS
489: (deal_type,amount_type,date_type,
490: deal_number,transaction_number,transaction_date,currency,
491: amount,hce_amount,amount_date,transaction_rate,
492: cashflow_amount,company_code,account_no,action_code,
505: end if;
506: -- Dummy rows for deal dealt and commence dates, this allows a journal action
507: -- to be set up for a premium to occur on the deal date (date type DEALT) or
508: -- the commencment date of the option
509: insert into XTR_DEAL_DATE_AMOUNTS
510: (deal_type,amount_type,date_type,
511: deal_number,transaction_number,transaction_date,currency,
512: amount,hce_amount,amount_date,transaction_rate,
513: cashflow_amount,company_code,deal_subtype,product_type,
522: --
523: elsif P_KNOCK_TYPE = 'I' then
524: if P_CURRENCY IS NOT NULL and P_PREMIUM_ACTION is NOT NULL and nvl(P_PREMIUM_AMOUNT,0) > 0 then
525: -- ** WARNING ** a copy of the next insert also appears in UPDATING.
526: insert into XTR_DEAL_DATE_AMOUNTS
527: (deal_type,amount_type,date_type,
528: deal_number,transaction_number,transaction_date,currency,
529: amount,hce_amount,amount_date,transaction_rate,
530: cashflow_amount,company_code,account_no,action_code,
542: end if;
543: -- Dummy rows for deal dealt and commence dates, this allows a journal action
544: -- to be set up for a premium to occur on the deal date (date type DEALT) or
545: -- the commencment date of the option
546: insert into XTR_DEAL_DATE_AMOUNTS
547: (deal_type,amount_type,date_type,
548: deal_number,transaction_number,transaction_date,currency,
549: amount,hce_amount,amount_date,transaction_rate,
550: cashflow_amount,company_code,deal_subtype,product_type,
558: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
559: --
560: end if;
561: elsif P_DEAL_TYPE = 'FX' then
562: insert into XTR_DEAL_DATE_AMOUNTS
563: (deal_type,amount_type,date_type,
564: deal_number,transaction_number,transaction_date,
565: currency,amount,hce_amount,amount_date,
566: transaction_rate,cashflow_amount,company_code,
576: P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',
577: decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
578: decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE),l_combin,P_QUICK_INPUT);
579: --
580: insert into XTR_DEAL_DATE_AMOUNTS
581: (deal_type,amount_type,date_type,
582: deal_number,transaction_number,transaction_date,
583: currency,amount,hce_amount,amount_date,
584: transaction_rate,cashflow_amount,company_code,
595: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',
596: decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
597: decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE),l_combin,P_QUICK_INPUT);
598: --
599: insert into XTR_DEAL_DATE_AMOUNTS
600: (deal_type,amount_type,date_type,
601: deal_number,transaction_number,transaction_date,
602: currency,amount,hce_amount,amount_date,
603: transaction_rate,cashflow_amount,company_code,
614: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',l_combin,P_QUICK_INPUT);
615: --
616: elsif P_DEAL_TYPE = 'NI' then
617:
618: insert into XTR_DEAL_DATE_AMOUNTS
619: (deal_type,amount_type,date_type,
620: deal_number,transaction_number,transaction_date,currency,
621: amount,hce_amount,amount_date,transaction_rate,
622: cashflow_amount,company_code,account_no,
637:
638: -----------------------------------------------------------------------------
639: -- Initial Maturity Face Value Details , before for sell status_code='CLOSED'
640: -----------------------------------------------------------------------------
641: insert into XTR_DEAL_DATE_AMOUNTS
642: (deal_type,amount_type,date_type,
643: deal_number,transaction_number,transaction_date,currency,
644: amount,hce_amount,amount_date,transaction_rate,
645: cashflow_amount,company_code,account_no,
659: P_MATURITY_BALANCE_AMOUNT is NOT NULL then /* in Pro0340 this column is null */
660: -----------------------------
661: -- BALance Face Value Details
662: -----------------------------
663: insert into XTR_DEAL_DATE_AMOUNTS
664: (deal_type,amount_type,date_type,
665: deal_number,transaction_number,transaction_date,currency,
666: amount,hce_amount,amount_date,transaction_rate,
667: cashflow_amount,company_code,account_no,
687:
688: ---------------------
689: -- INTerest Details
690: ---------------------
691: insert into XTR_DEAL_DATE_AMOUNTS
692: (deal_type,amount_type,date_type,deal_number,
693: transaction_number,transaction_date,currency,
694: amount,hce_amount,amount_date,transaction_rate,
695: cashflow_amount,company_code,portfolio_code,status_code,
705:
706: --------------------------
707: -- Deal DEALT on Details
708: --------------------------
709: insert into XTR_DEAL_DATE_AMOUNTS
710: (deal_type,amount_type,date_type,
711: deal_number,transaction_number,transaction_date,currency,
712: amount,hce_amount,amount_date,transaction_rate,
713: cashflow_amount,company_code,
725: ---------------------------------
726: -- New Date Types/Amount Types --
727: ---------------------------------
728: if P_DEAL_SUBTYPE in ('BUY','SHORT') then
729: insert into XTR_DEAL_DATE_AMOUNTS
730: (deal_type,amount_type,date_type,
731: deal_number,transaction_number,transaction_date,currency,
732: amount,hce_amount,amount_date,transaction_rate,
733: cashflow_amount,company_code,account_no,
781: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_COMPANY_CODE,P_CPARTY_CODE,
782: P_CLIENT_CODE,P_CURRENCY,P_DEAL_DATE,'CURRENT',L_USER,
783: L_SYSDATE,coupon_date);
784: --
785: insert into XTR_DEAL_DATE_AMOUNTS
786: (deal_type,amount_type,date_type,
787: deal_number,transaction_number,transaction_date,currency,
788: amount,hce_amount,amount_date,transaction_rate,
789: cashflow_amount,company_code,account_no,status_code,portfolio_code,
802: END LOOP;
803: end if;
804: end if;
805: --
806: insert into XTR_DEAL_DATE_AMOUNTS
807: (deal_type,amount_type,date_type,
808: deal_number,transaction_number,transaction_date,currency,
809: amount,hce_amount,amount_date,transaction_rate,
810: cashflow_amount,company_code,account_no,
819: P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
820: P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
821: P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
822: --
823: insert into XTR_DEAL_DATE_AMOUNTS
824: (deal_type,amount_type,date_type,
825: deal_number,transaction_number,transaction_date,currency,
826: amount,hce_amount,amount_date,transaction_rate,
827: cashflow_amount,company_code,account_no,
839: P_CLIENT_CODE,P_DEAL_SUBTYPE,P_ACCEPTOR_CODE,'N',
840: P_PRODUCT_TYPE,nvl(P_LIMIT_CODE,'NILL'),P_ACCEPTOR_CODE);
841: -- if P_COUPON_ACTION = 'CUM' then
842: -- Accrued Int values
843: insert into XTR_DEAL_DATE_AMOUNTS
844: (deal_type,amount_type,date_type,
845: deal_number,transaction_number,transaction_date,currency,
846: amount,hce_amount,amount_date,transaction_rate,
847: cashflow_amount,company_code,status_code,
854: P_PORTFOLIO_CODE,P_DEALER_CODE,P_CLIENT_CODE,
855: P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
856: --- end if;
857: -- Premium / Discount 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,
870: P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
871: P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
872: elsif P_DEAL_TYPE = 'IRO' then
873: -- Limit Row / Limit Amount Details
874: insert into XTR_DEAL_DATE_AMOUNTS
875: (deal_type,amount_type,date_type,
876: deal_number,transaction_number,transaction_date,currency,
877: amount,hce_amount,amount_date,
878: cashflow_amount,company_code,deal_subtype,product_type,
887: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
888: P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
889: P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
890: -- Expiry Date / Face Value Amount Details
891: insert into XTR_DEAL_DATE_AMOUNTS
892: (deal_type,amount_type,date_type,
893: deal_number,transaction_number,transaction_date,currency,
894: amount,hce_amount,amount_date,transaction_rate,
895: cashflow_amount,company_code,deal_subtype,product_type,
904: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
905: P_CPARTY_CODE,'N',
906: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
907: -- Premium Date / Premium Amount Details
908: insert into XTR_DEAL_DATE_AMOUNTS
909: (deal_type,amount_type,date_type,
910: deal_number,transaction_number,transaction_date,currency,
911: amount,hce_amount,amount_date,transaction_rate,
912: cashflow_amount,
923: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
924: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
925: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
926: -- Deal Dealt on Details
927: insert into XTR_DEAL_DATE_AMOUNTS
928: (deal_type,amount_type,date_type,
929: deal_number,transaction_number,transaction_date,currency,
930: amount,hce_amount,amount_date,transaction_rate,
931: cashflow_amount,company_code,
948: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
949: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
950: */
951: -- Physical Commencement Date Details
952: insert into XTR_DEAL_DATE_AMOUNTS
953: (deal_type,amount_type,date_type,
954: deal_number,transaction_number,transaction_date,currency,
955: amount,hce_amount,amount_date,transaction_rate,
956: cashflow_amount,company_code,
963: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
964: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
965: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
966: -- Physical Maturity 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_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
979: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
980: elsif P_DEAL_TYPE = 'BDO' then
981: -- Limit Row / Limit Amount 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,
986: cashflow_amount,company_code,deal_subtype,product_type,
995: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
996: P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
997: P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
998: -- Expiry Date / Face Value Amount Details
999: insert into XTR_DEAL_DATE_AMOUNTS
1000: (deal_type,amount_type,date_type,
1001: deal_number,transaction_number,transaction_date,currency,
1002: amount,hce_amount,amount_date,transaction_rate,
1003: cashflow_amount,company_code,deal_subtype,product_type,
1012: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1013: P_CPARTY_CODE,'N',
1014: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1015: -- Premium Date / Premium Amount Details
1016: insert into XTR_DEAL_DATE_AMOUNTS
1017: (deal_type,amount_type,date_type,
1018: deal_number,transaction_number,transaction_date,currency,
1019: amount,hce_amount,amount_date,transaction_rate,
1020: cashflow_amount,
1031: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1032: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
1033: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1034: -- Deal Dealt on Details
1035: insert into XTR_DEAL_DATE_AMOUNTS
1036: (deal_type,amount_type,date_type,
1037: deal_number,transaction_number,transaction_date,currency,
1038: amount,hce_amount,amount_date,transaction_rate,
1039: cashflow_amount,company_code,
1056: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1057: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1058: */
1059: -- Physical Commencement Date Details
1060: insert into XTR_DEAL_DATE_AMOUNTS
1061: (deal_type,amount_type,date_type,
1062: deal_number,transaction_number,transaction_date,currency,
1063: amount,hce_amount,amount_date,transaction_rate,
1064: cashflow_amount,company_code,
1072: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1073: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1074: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1075: -- Physical Maturity Date Details
1076: insert into XTR_DEAL_DATE_AMOUNTS
1077: (deal_type,amount_type,date_type,
1078: deal_number,transaction_number,transaction_date,currency,
1079: amount,hce_amount,amount_date,transaction_rate,
1080: cashflow_amount,company_code,
1087: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1088: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1089: elsif P_DEAL_TYPE = 'FUT' then
1090: -- Face Value Amount
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,deal_subtype,product_type,
1105: P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
1106: P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
1107: if nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) > 0 then
1108: -- Settlement Amount
1109: insert into XTR_DEAL_DATE_AMOUNTS
1110: (deal_type,amount_type,date_type,
1111: deal_number,transaction_number,transaction_date,currency,
1112: amount,hce_amount,amount_date,transaction_rate,
1113: cashflow_amount,company_code,account_no,action_code,
1124: P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
1125: end if;
1126: -- Dummy row for deal dealt date, this allows a journal action
1127: -- to be set up for a premium to occur on the deal date (date type DEALT)
1128: insert into XTR_DEAL_DATE_AMOUNTS
1129: (deal_type,amount_type,date_type,
1130: deal_number,transaction_number,transaction_date,currency,
1131: amount,hce_amount,amount_date,transaction_rate,
1132: cashflow_amount,company_code,deal_subtype,product_type,
1139: P_CLIENT_CODE,P_CPARTY_CODE,'N',
1140: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1141: elsif P_DEAL_TYPE = 'SWPTN' then
1142: -- Limit Row / Limit Details
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,
1147: cashflow_amount,company_code,deal_subtype,product_type,
1155: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1156: P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
1157: P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1158: -- Face Value Amount / Expiry Date Details
1159: insert into XTR_DEAL_DATE_AMOUNTS
1160: (deal_type,amount_type,date_type,
1161: deal_number,transaction_number,transaction_date,currency,
1162: amount,hce_amount,amount_date,transaction_rate,
1163: cashflow_amount,company_code,deal_subtype,product_type,
1171: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1172: P_CPARTY_CODE,'N',
1173: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1174: -- Premium Details
1175: insert into XTR_DEAL_DATE_AMOUNTS
1176: (deal_type,amount_type,date_type,
1177: deal_number,transaction_number,transaction_date,currency,
1178: amount,hce_amount,amount_date,transaction_rate,
1179: cashflow_amount,company_code,account_no,action_code,cparty_account_no,
1190: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
1191: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1192: -- Dummy row for deal dealt date, this allows a journal action
1193: -- to be set up for a premium to occur on the deal date (date type DEALT)
1194: insert into XTR_DEAL_DATE_AMOUNTS
1195: (deal_type,amount_type,date_type,
1196: deal_number,transaction_number,transaction_date,currency,
1197: amount,hce_amount,amount_date,transaction_rate,
1198: cashflow_amount,company_code,deal_subtype,product_type,
1215: if P_SETTLE_DATE is NOT NULL then
1216: xtr_fps2_p.standing_settlements(P_CPARTY_CODE,P_CURRENCY,'SWPTN',
1217: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,'SETTLE',
1218: v_settle_ref,v_settle_ac);
1219: insert into XTR_DEAL_DATE_AMOUNTS
1220: (deal_type,amount_type,date_type,
1221: deal_number,transaction_number,transaction_date,currency,
1222: amount,hce_amount,amount_date,transaction_rate,
1223: cashflow_amount,company_code,account_no,action_code,
1234: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
1235: nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1236: end if;
1237: -- Underlying Physical Start Details
1238: insert into XTR_DEAL_DATE_AMOUNTS
1239: (deal_type,amount_type,date_type,
1240: deal_number,transaction_number,transaction_date,currency,
1241: amount,hce_amount,amount_date,transaction_rate,
1242: cashflow_amount,company_code,deal_subtype,product_type,
1249: P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1250: P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1251: P_CPARTY_CODE,'N',P_PORTFOLIO_CODE);
1252: -- Underlying Physical Maturity 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,
1267: --
1268: elsif P_ACTION = 'UPDATE' then
1269: -- Delete rows in DDA for cancelled transactions
1270: if P_STATUS_CODE = 'CANCELLED' then
1271: delete from XTR_DEAL_DATE_AMOUNTS
1272: where deal_number = P_DEAL_NO;
1273: --
1274: if P_DEAL_TYPE = 'BOND' then
1275: -- coupons
1300: --
1301: -- Where not cancelled
1302: else
1303: if P_DEAL_TYPE = 'FRA' then
1304: update XTR_DEAL_DATE_AMOUNTS
1305: set amount = decode(AMOUNT_TYPE,'FACEVAL',P_FACE_VALUE_AMOUNT,amount),
1306: hce_amount = decode(AMOUNT_TYPE,'FACEVAL',P_FACE_VALUE_HCE_AMOUNT,hce_amount),
1307: amount_date = decode(DATE_TYPE,'COMENCE',P_START_DATE,'MATURE',P_MATURITY_DATE,'DEALT',P_DEAL_DATE,
1308: 'RATESET',P_RATE_FIXING_DATE,amount_date),
1329: if P_SETTLE_DATE IS NOT NULL then
1330: open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
1331: fetch C_LIMIT_WEIGHTING into v_weighting;
1332: close C_LIMIT_WEIGHTING;
1333: update XTR_DEAL_DATE_AMOUNTS
1334: set AMOUNT = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
1335: HCE_AMOUNT = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
1336: AMOUNT_DATE = P_SETTLE_DATE,
1337: STATUS_CODE = P_STATUS_CODE,
1348: limit_party = P_CPARTY_CODE
1349: where DEAL_NUMBER = P_DEAL_NO
1350: and DATE_TYPE = 'LIMIT'
1351: and DEAL_TYPE = 'FRA';
1352: update XTR_DEAL_DATE_AMOUNTS
1353: set AMOUNT = nvl(P_SETTLE_AMOUNT,0),
1354: ACTION_CODE = P_SETTLE_ACTION,
1355: HCE_AMOUNT = nvl(P_SETTLE_HCE_AMOUNT,0),
1356: CASHFLOW_AMOUNT = (decode(P_SETTLE_ACTION,'PAY',-1,1) * nvl(P_SETTLE_AMOUNT,0)),
1374: and DEAL_TYPE = 'FRA';
1375: --
1376: if SQL%NOTFOUND then
1377: -- Settlement Row
1378: insert into XTR_DEAL_DATE_AMOUNTS
1379: (deal_type,amount_type,date_type,
1380: deal_number,transaction_number,transaction_date,currency,
1381: amount,hce_amount,amount_date,transaction_rate,
1382: cashflow_amount,company_code,account_no,cparty_account_no,action_code,
1393: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N');
1394: end if;
1395: end if;
1396: elsif P_DEAL_TYPE = 'FXO' THEN
1397: update XTR_DEAL_DATE_AMOUNTS
1398: set amount = decode(amount_type,'FXOBUY',P_BUY_AMOUNT,P_SELL_AMOUNT),
1399: hce_amount = decode(amount_type,'FXOBUY',P_BUY_HCE_AMOUNT,P_SELL_HCE_AMOUNT),
1400: amount_date = P_EXPIRY_DATE,
1401: -- account_no = decode(amount_type,'FXOBUY',P_BUY_ACCOUNT_NO,P_SELL_ACCOUNT_NO),
1423: and deal_type = P_DEAL_TYPE;
1424:
1425: if SQL%NOTFOUND then
1426: if P_STATUS_CODE='CURRENT' and nvl(P_KNOCK_TYPE,'@#@')='I' and nvl(P_KNOCK_INSERT_TYPE,'D')='E'then
1427: insert into XTR_DEAL_DATE_AMOUNTS
1428: (deal_type,amount_type,date_type,
1429: deal_number,transaction_number,transaction_date,currency,
1430: amount,hce_amount,amount_date,transaction_rate,
1431: cashflow_amount,company_code,deal_subtype,product_type,
1440: decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1441: decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
1442: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,l_combin,P_QUICK_INPUT);
1443: --
1444: insert into XTR_DEAL_DATE_AMOUNTS
1445: (deal_type,amount_type,date_type,
1446: deal_number,transaction_number,transaction_date,currency,
1447: amount,hce_amount,amount_date,transaction_rate,
1448: cashflow_amount,company_code,deal_subtype,product_type,
1458: end if;
1459: end if;
1460:
1461: --
1462: update XTR_DEAL_DATE_AMOUNTS
1463: set amount_date = P_DEAL_DATE,
1464: transaction_date = P_DEAL_DATE,
1465: currency = P_CURRENCY_BUY,
1466: company_code = P_COMPANY_CODE,
1474: where deal_number = P_DEAL_NO
1475: and date_type = 'DEALT'
1476: and deal_type = P_DEAL_TYPE;
1477: --
1478: update XTR_DEAL_DATE_AMOUNTS
1479: set amount = P_PREMIUM_AMOUNT,
1480: hce_amount = P_PREMIUM_HCE_AMOUNT,
1481: amount_date = P_PREMIUM_DATE,
1482: cashflow_amount = DECODE(P_PREMIUM_ACTION,'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
1501: and P_PREMIUM_ACTION is NOT NULL
1502: and nvl(P_PREMIUM_AMOUNT,0) > 0 and P_STATUS_CODE <> 'CANCELLED'
1503: then
1504: --
1505: insert into XTR_DEAL_DATE_AMOUNTS
1506: (deal_type,amount_type,date_type,
1507: deal_number,transaction_number,transaction_date,currency,
1508: amount,hce_amount,amount_date,transaction_rate,
1509: cashflow_amount,company_code,account_no,action_code,
1523: if P_STATUS_CODE='CURRENT'
1524: and (nvl(P_KNOCK_TYPE,'O')='O'
1525: or (P_KNOCK_TYPE='I' and nvl(P_KNOCK_INSERT_TYPE,'D')='E'))
1526: and P_INSERT_FOR_CASHFLOW = 'Y' then
1527: update XTR_DEAL_DATE_AMOUNTS
1528: set amount = P_SELL_AMOUNT,
1529: hce_amount = P_SELL_HCE_AMOUNT,
1530: amount_date = decode(date_type,'VALUE',P_VALUE_DATE,P_EXPIRY_DATE),
1531: exposure_ref_date = decode(date_type,'VALUE',P_START_DATE,exposure_ref_date),
1551: where deal_number = P_DEAL_NO
1552: and date_type = 'VALUE'
1553: and deal_type = P_DEAL_TYPE;
1554: if SQL%NOTFOUND then
1555: insert into XTR_DEAL_DATE_AMOUNTS
1556: (deal_type,amount_type,date_type,
1557: deal_number,transaction_number,transaction_date,currency,
1558: amount,hce_amount,amount_date,transaction_rate,
1559: cashflow_amount,company_code,account_no,
1568: P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
1569: P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
1570: P_DEALER_CODE,l_combin,P_START_DATE);
1571: --
1572: insert into XTR_DEAL_DATE_AMOUNTS
1573: (deal_type,amount_type,date_type,
1574: deal_number,transaction_number,transaction_date,currency,
1575: amount,hce_amount,amount_date,transaction_rate,
1576: cashflow_amount,company_code,account_no,
1586: P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
1587: P_DEALER_CODE,l_combin,P_START_DATE);
1588: end if;
1589: else
1590: delete from XTR_DEAL_DATE_AMOUNTS
1591: where deal_number = P_DEAL_NO
1592: and deal_type = P_DEAL_TYPE
1593: and date_type = 'VALUE';
1594: end if;
1593: and date_type = 'VALUE';
1594: end if;
1595: --
1596: if P_STATUS_CODE = 'EXPIRED' then
1597: delete from XTR_DEAL_DATE_AMOUNTS
1598: where deal_number = P_DEAL_NO
1599: and deal_type = P_DEAL_TYPE
1600: and date_type in ('VALUE','EXPIRY');
1601: elsif P_STATUS_CODE = 'EXERCISED' then
1599: and deal_type = P_DEAL_TYPE
1600: and date_type in ('VALUE','EXPIRY');
1601: elsif P_STATUS_CODE = 'EXERCISED' then
1602: -- AW Bug 894751 American Option
1603: delete from XTR_DEAL_DATE_AMOUNTS
1604: where deal_number = P_DEAL_NO
1605: and deal_type = P_DEAL_TYPE
1606: and date_type = 'VALUE';
1607: --
1604: where deal_number = P_DEAL_NO
1605: and deal_type = P_DEAL_TYPE
1606: and date_type = 'VALUE';
1607: --
1608: update XTR_DEAL_DATE_AMOUNTS
1609: set DATE_TYPE = 'SETTLE',
1610: AMOUNT_DATE = trunc(sysdate),
1611: TRANSACTION_DATE = trunc(sysdate)
1612: where DEAL_NUMBER = P_DEAL_NO
1630: ))
1631:
1632: THEN
1633:
1634: UPDATE XTR_DEAL_DATE_AMOUNTS
1635: SET AMOUNT = P_BUY_AMOUNT,
1636: HCE_AMOUNT = nvl(P_BUY_HCE_AMOUNT,0),
1637: STATUS_CODE = P_STATUS_CODE,
1638: AMOUNT_DATE = P_START_DATE,
1656: AND DATE_TYPE = 'VALUE'
1657: AND AMOUNT_TYPE = 'BUY'
1658: AND TRANSACTION_NUMBER = 1;
1659:
1660: update XTR_DEAL_DATE_AMOUNTS
1661: set amount = P_SELL_AMOUNT,
1662: hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1663: amount_date = P_START_DATE,
1664: cashflow_amount = 0,
1683: and deal_type = P_DEAL_TYPE
1684: AND date_type = 'VALUE'
1685: and transaction_number = 1;
1686:
1687: update XTR_DEAL_DATE_AMOUNTS
1688: set amount = P_SELL_AMOUNT,
1689: hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1690: status_code = P_STATUS_CODE,
1691: amount_date = P_DEAL_DATE,
1706: and deal_type = P_DEAL_TYPE;
1707:
1708: /*--------------------------- RVALLAMS FX REARCH ----------------------------- */
1709: ELSE
1710: update XTR_DEAL_DATE_AMOUNTS
1711: set AMOUNT = P_BUY_AMOUNT,
1712: HCE_AMOUNT = nvl(P_BUY_HCE_AMOUNT,0),
1713: AMOUNT_DATE = P_VALUE_DATE,
1714: STATUS_CODE = P_STATUS_CODE,
1731: and deal_type = P_DEAL_TYPE
1732: AND date_type = 'VALUE'
1733: and transaction_number = 1;
1734: --
1735: update XTR_DEAL_DATE_AMOUNTS
1736: set amount = P_SELL_AMOUNT,
1737: hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1738: amount_date = P_VALUE_DATE,
1739: cashflow_amount = (-1) * P_SELL_AMOUNT,
1757: and deal_type = P_DEAL_TYPE
1758: AND date_type = 'VALUE'
1759: and transaction_number = 1;
1760: --
1761: update XTR_DEAL_DATE_AMOUNTS
1762: set amount = P_SELL_AMOUNT,
1763: hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1764: status_code = P_STATUS_CODE,
1765: amount_date = P_DEAL_DATE,
1780: and deal_type = P_DEAL_TYPE;
1781: END IF;
1782: --
1783: elsif P_DEAL_TYPE = 'NI' then
1784: update XTR_DEAL_DATE_AMOUNTS
1785: set ACCOUNT_NO = nvl(v_MATURITY_ACCOUNT_NO, account_no),
1786: STATUS_CODE = P_STATUS_CODE,
1787: CPARTY_ACCOUNT_NO = nvl(v_cparty_account_no, cparty_account_no),
1788: COMMENCE_DATE = P_START_DATE,
1842: if P_DEAL_SUBTYPE in ('BUY','SHORT','ISSUE') then
1843: open CHK_NI_BAL_FV;
1844: fetch CHK_NI_BAL_FV into l_dummy;
1845: if CHK_NI_BAL_FV%NOTFOUND and nvl(P_MATURITY_BALANCE_AMOUNT,0) <> 0 then
1846: insert into XTR_DEAL_DATE_AMOUNTS
1847: (deal_type,amount_type,date_type,
1848: deal_number,transaction_number,transaction_date,currency,
1849: amount,hce_amount,amount_date,transaction_rate,
1850: cashflow_amount,company_code,account_no,
1873: ------------
1874: /* Not required.
1875: ---- 06/08/99 Apply PL to dda for sale deal. refer to bug 904365
1876: if P_DEAL_SUBTYPE='SELL' then
1877: update XTR_DEAL_DATE_AMOUNTS
1878: set AMOUNT_DATE = P_START_DATE,
1879: AMOUNT = abs(nvl(P_NI_PROFIT_LOSS,0)),
1880: HCE_AMOUNT = abs(nvl(round(P_NI_PROFIT_LOSS/hce_rate,2),0)),
1881: ACTION_CODE = decode(sign(nvl(P_NI_PROFIT_LOSS,0)),-1,'LOSS','PROFIT'),
1885: and DATE_TYPE ='REVAL';
1886: --
1887: if SQL%NOTFOUND and nvl(P_NI_PROFIT_LOSS,0) <> 0 then
1888: -- Set the profit and loss info into the database...
1889: insert into XTR_DEAL_DATE_AMOUNTS
1890: (deal_type,amount_type,date_type,
1891: deal_number,transaction_number,transaction_date,currency,
1892: amount,hce_amount,amount_date,transaction_rate,
1893: cashflow_amount,company_code,status_code,dealer_code,
1904: end if;
1905: */
1906: --
1907: elsif P_DEAL_TYPE = 'BOND' then
1908: update XTR_DEAL_DATE_AMOUNTS
1909: set amount = P_START_AMOUNT,
1910: hce_amount = P_START_HCE_AMOUNT,
1911: cashflow_amount = decode(P_DEAL_SUBTYPE,'BUY',1,-1) * P_START_AMOUNT,
1912: transaction_rate = P_INTEREST_RATE,
1918: and amount_type = 'COMENCE'
1919: and date_type = 'COMENCE'
1920: and deal_type = P_DEAL_TYPE;
1921: --
1922: update XTR_DEAL_DATE_AMOUNTS
1923: set amount = P_MATURITY_AMOUNT,
1924: hce_amount = P_MATURITY_HCE_AMOUNT,
1925: cashflow_amount = decode(P_DEAL_SUBTYPE,'BUY',1,-1) * P_MATURITY_AMOUNT,
1926: transaction_rate = P_INTEREST_RATE,
1934: and deal_type = P_DEAL_TYPE
1935: and amount_date >= L_SYSDATE;
1936: --
1937: if P_INT_VALUE is not null then
1938: update XTR_DEAL_DATE_AMOUNTS
1939: set amount = P_INT_VALUE,
1940: hce_amount = round(P_INT_VALUE/hce_rate,round_fac),
1941: transaction_date = P_DEAL_DATE,
1942: transaction_rate = P_INTEREST_RATE
1947: and amount_date >= L_SYSDATE;
1948: end if;
1949: --
1950: if nvl(P_PREM_VALUE,0) <> 0 then
1951: update XTR_DEAL_DATE_AMOUNTS
1952: set amount = abs(P_PREM_VALUE),
1953: hce_amount = abs(round(P_PREM_VALUE/hce_rate,round_fac)),
1954: transaction_date = P_DEAL_DATE,
1955: transaction_rate = P_INTEREST_RATE
1960: and amount_date >= L_SYSDATE;
1961: end if;
1962: --
1963: elsif P_DEAL_TYPE = 'IRO' then
1964: update XTR_DEAL_DATE_AMOUNTS
1965: set amount = nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
1966: hce_amount = nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
1967: amount_date = P_EXPIRY_DATE,
1968: transaction_rate = P_INTEREST_RATE,
1979: where deal_number = P_DEAL_NO
1980: and deal_type = 'IRO'
1981: and date_type = 'EXPIRY';
1982: --
1983: update XTR_DEAL_DATE_AMOUNTS
1984: set amount = nvl(P_PREMIUM_AMOUNT,0),
1985: hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
1986: amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
1987: transaction_date = P_DEAL_DATE,
2001: where deal_number = P_DEAL_NO
2002: and deal_type = 'IRO'
2003: and date_type = 'PREMIUM';
2004: --
2005: update XTR_DEAL_DATE_AMOUNTS
2006: set amount_date = P_DEAL_DATE,
2007: transaction_date = P_DEAL_DATE,
2008: currency = P_CURRENCY,
2009: company_code = P_COMPANY_CODE,
2017: where deal_number = P_DEAL_NO
2018: and deal_type = 'IRO'
2019: and date_type = 'DEALT';
2020: --
2021: update XTR_DEAL_DATE_AMOUNTS
2022: set amount_date = P_MATURITY_DATE,
2023: transaction_date = P_DEAL_DATE,
2024: currency = P_CURRENCY,
2025: company_code = P_COMPANY_CODE,
2033: where deal_number = P_DEAL_NO
2034: and deal_type = 'IRO'
2035: and date_type = 'MATURE';
2036: --
2037: update XTR_DEAL_DATE_AMOUNTS
2038: set amount_date = P_START_DATE,
2039: transaction_date = P_DEAL_DATE,
2040: currency = P_CURRENCY,
2041: company_code = P_COMPANY_CODE,
2056:
2057: open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
2058: fetch C_LIMIT_WEIGHTING into v_weighting;
2059: close C_LIMIT_WEIGHTING;
2060: update XTR_DEAL_DATE_AMOUNTS
2061: set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
2062: hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
2063: amount_date = P_SETTLE_DATE,
2064: transaction_date = P_DEAL_DATE,
2077: and deal_type = 'IRO'
2078: and date_type = 'LIMIT';
2079: --
2080: -- AW Bug 894751
2081: delete from XTR_DEAL_DATE_AMOUNTS
2082: where deal_number = P_DEAL_NO
2083: and deal_type = 'IRO'
2084: and date_type = 'EXPIRY';
2085: --
2082: where deal_number = P_DEAL_NO
2083: and deal_type = 'IRO'
2084: and date_type = 'EXPIRY';
2085: --
2086: update XTR_DEAL_DATE_AMOUNTS
2087: set amount = nvl(P_SETTLE_AMOUNT,0),
2088: hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
2089: amount_date = P_SETTLE_DATE,
2090: cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
2105: where deal_number = P_DEAL_NO
2106: and deal_type = 'IRO'
2107: and date_type = 'SETTLE';
2108: --
2109: --Bug 3060946 Removed 'Insert into XTR_DEAL_DATE_AMOUNTS' statement
2110: --
2111: end if;
2112: --
2113: elsif P_DEAL_TYPE = 'BDO' then
2110: --
2111: end if;
2112: --
2113: elsif P_DEAL_TYPE = 'BDO' then
2114: update XTR_DEAL_DATE_AMOUNTS
2115: set amount = nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
2116: hce_amount = nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
2117: amount_date = P_EXPIRY_DATE,
2118: transaction_rate = P_INTEREST_RATE,
2129: where deal_number = P_DEAL_NO
2130: and deal_type = 'BDO'
2131: and date_type = 'EXPIRY';
2132: --
2133: update XTR_DEAL_DATE_AMOUNTS
2134: set amount = nvl(P_PREMIUM_AMOUNT,0),
2135: hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2136: amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
2137: transaction_date = P_DEAL_DATE,
2151: where deal_number = P_DEAL_NO
2152: and deal_type = 'BDO'
2153: and date_type = 'PREMIUM';
2154: --
2155: update XTR_DEAL_DATE_AMOUNTS
2156: set amount_date = P_DEAL_DATE,
2157: transaction_date = P_DEAL_DATE,
2158: currency = P_CURRENCY,
2159: company_code = P_COMPANY_CODE,
2167: where deal_number = P_DEAL_NO
2168: and deal_type = 'BDO'
2169: and date_type = 'DEALT';
2170: --
2171: update XTR_DEAL_DATE_AMOUNTS
2172: set amount_date = P_MATURITY_DATE,
2173: transaction_date = P_DEAL_DATE,
2174: currency = P_CURRENCY,
2175: company_code = P_COMPANY_CODE,
2183: where deal_number = P_DEAL_NO
2184: and deal_type = 'BDO'
2185: and date_type = 'MATURE';
2186: --
2187: update XTR_DEAL_DATE_AMOUNTS
2188: set amount_date = P_START_DATE,
2189: transaction_date = P_DEAL_DATE,
2190: currency = P_CURRENCY,
2191: company_code = P_COMPANY_CODE,
2204: xtr_fps2_p.standing_settlements(P_CPARTY_CODE,P_CURRENCY,'BDO',
2205: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,'SETTLE',
2206: v_settle_ref,v_settle_ac);
2207: -- AW Bug 894751
2208: delete from XTR_DEAL_DATE_AMOUNTS
2209: where deal_number = P_DEAL_NO
2210: and deal_type = 'BDO'
2211: and date_type = 'EXPIRY';
2212: --
2213: if P_EXERCISE_PRICE is not null then
2214: open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
2215: fetch C_LIMIT_WEIGHTING into v_weighting;
2216: close C_LIMIT_WEIGHTING;
2217: update XTR_DEAL_DATE_AMOUNTS
2218: set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
2219: hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
2220: amount_date = P_SETTLE_DATE,
2221: transaction_date = P_DEAL_DATE,
2233: where deal_number = P_DEAL_NO
2234: and deal_type = 'BDO'
2235: and date_type = 'LIMIT';
2236: --
2237: update XTR_DEAL_DATE_AMOUNTS
2238: set amount = nvl(P_SETTLE_AMOUNT,0),
2239: hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
2240: amount_date = P_SETTLE_DATE,
2241: cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
2257: and deal_type = 'BDO'
2258: and date_type = 'SETTLE';
2259: --
2260: if SQL%NOTFOUND and P_SETTLE_DATE is NOT NULL then
2261: insert into XTR_DEAL_DATE_AMOUNTS
2262: (deal_type,amount_type,date_type,
2263: deal_number,transaction_number,transaction_date,currency,
2264: amount,hce_amount,amount_date,transaction_rate,
2265: cashflow_amount,company_code,account_no,action_code,
2283: open CHK_BDO_SETTLE_ROWS;
2284: fetch CHK_BDO_SETTLE_ROWS into l_dummy;
2285:
2286: if CHK_BDO_SETTLE_ROWS%NOTFOUND then
2287: insert into XTR_DEAL_DATE_AMOUNTS
2288: (deal_type,amount_type,date_type,
2289: deal_number,transaction_number,transaction_date,currency,
2290: amount,hce_amount,amount_date,transaction_rate,
2291: cashflow_amount,company_code,deal_subtype,product_type,
2303: --
2304: end if;
2305: --
2306: elsif P_DEAL_TYPE = 'FUT' then
2307: update XTR_DEAL_DATE_AMOUNTS
2308: set amount = P_START_AMOUNT,
2309: hce_amount = P_START_HCE_AMOUNT,
2310: transaction_rate = nvl(P_TRANSACTION_RATE,P_CONTRACT_RATE),
2311: amount_date = P_EXPIRY_DATE,
2325: where deal_number = P_DEAL_NO
2326: and deal_type = 'FUT'
2327: and amount_type = 'FACEVAL';
2328: --
2329: update XTR_DEAL_DATE_AMOUNTS
2330: set amount = nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0),
2331: hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2332: cashflow_amount = -(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
2333: amount_date = P_PREMIUM_DATE,
2349: and amount_type = 'PREMIUM';
2350: --
2351: if SQL%NOTFOUND and nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) > 0 then
2352: -- Settlement Amount
2353: insert into XTR_DEAL_DATE_AMOUNTS
2354: (deal_type,amount_type,date_type,
2355: deal_number,transaction_number,transaction_date,currency,
2356: amount,hce_amount,amount_date,transaction_rate,
2357: cashflow_amount,company_code,account_no,action_code,
2366: 'CURRENT',P_DEALER_CODE,P_CLIENT_CODE,
2367: P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
2368: end if;
2369: --
2370: update XTR_DEAL_DATE_AMOUNTS
2371: set amount_date = P_DEAL_DATE,
2372: transaction_date = P_DEAL_DATE,
2373: currency = P_PREMIUM_CURRENCY,
2374: company_code = P_COMPANY_CODE,
2383: and deal_type = 'FUT'
2384: and amount_type = 'DEALT';
2385: --
2386: if P_SETTLE_ACTION is NOT NULL then
2387: update XTR_DEAL_DATE_AMOUNTS
2388: set amount = P_SETTLE_AMOUNT,
2389: hce_amount = P_SETTLE_HCE_AMOUNT,
2390: amount_date = P_SETTLE_DATE,
2391: cashflow_amount =
2407: where deal_number = P_DEAL_NO
2408: and deal_type = 'FUT'
2409: and amount_type = 'SETTLE';
2410: if SQL%NOTFOUND then
2411: insert into XTR_DEAL_DATE_AMOUNTS
2412: (deal_type,amount_type,date_type,
2413: deal_number,transaction_number,transaction_date,currency,
2414: amount,hce_amount,amount_date,transaction_rate,
2415: cashflow_amount,company_code,account_no,action_code,
2427: P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
2428: nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
2429: end if;
2430: --
2431: update XTR_DEAL_DATE_AMOUNTS
2432: set limit_code = NULL
2433: where deal_number = P_DEAL_NO
2434: and limit_code is NOT NULL;
2435: end if;
2434: and limit_code is NOT NULL;
2435: end if;
2436: --
2437: elsif P_DEAL_TYPE = 'SWPTN' then
2438: update XTR_DEAL_DATE_AMOUNTS
2439: set amount_date = decode(date_type,'DEALT',P_DEAL_DATE,
2440: 'COMENCE',P_START_DATE,
2441: 'MATURE',P_MATURITY_DATE,amount_date),
2442: amount = decode(amount_type,'N/A',0,
2456: where deal_number = P_DEAL_NO
2457: and deal_type = 'SWPTN'
2458: and date_type not in ('EXPIRY','PREMIUM','SETTLE');
2459: --
2460: update XTR_DEAL_DATE_AMOUNTS
2461: set amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
2462: amount = nvl(P_PREMIUM_AMOUNT,0),
2463: hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2464: cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1)*nvl(P_PREMIUM_AMOUNT,0),
2479: where deal_number = P_DEAL_NO
2480: and deal_type = 'SWPTN'
2481: and date_type = 'PREMIUM';
2482: --
2483: update XTR_DEAL_DATE_AMOUNTS
2484: set amount = P_FACE_VALUE_AMOUNT,
2485: hce_amount = P_FACE_VALUE_HCE_AMOUNT,
2486: amount_date = P_EXPIRY_DATE,
2487: transaction_rate = P_INTEREST_RATE,
2503: xtr_fps2_p.standing_settlements(P_CPARTY_CODE,P_CURRENCY,'SWPTN',
2504: P_DEAL_SUBTYPE,P_PRODUCT_TYPE,'SETTLE',
2505: v_settle_ref,v_settle_ac);
2506: -- AW Bug 894751
2507: delete from XTR_DEAL_DATE_AMOUNTS
2508: where deal_number = P_DEAL_NO
2509: and deal_type = 'SWPTN'
2510: and date_type = 'EXPIRY';
2511: --
2512: if P_SETTLE_RATE is not null then
2513: open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
2514: fetch C_LIMIT_WEIGHTING into v_weighting;
2515: close C_LIMIT_WEIGHTING;
2516: update XTR_DEAL_DATE_AMOUNTS
2517: set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
2518: hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
2519: amount_date = P_SETTLE_DATE,
2520: status_code = P_STATUS_CODE,
2531: where deal_number = P_DEAL_NO
2532: and deal_type = 'SWPTN'
2533: and date_type = 'LIMIT';
2534: --
2535: update XTR_DEAL_DATE_AMOUNTS
2536: set amount = nvl(P_SETTLE_AMOUNT,0),
2537: hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
2538: amount_date = P_SETTLE_DATE,
2539: cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
2552: where deal_number = P_DEAL_NO
2553: and deal_type = 'SWPTN'
2554: and date_type = 'SETTLE';
2555: --
2556: --Bug 3060946 Removed 'insert into XTR_DEAL_DATE_AMOUNTS' statement
2557: --
2558: else -- AW Bug 894751
2559: -- This is for creating swap.
2560: -- JT bug 1312363 while validating after exercise the insert was giving
2564: open CHK_SWPTN_SETTLE_ROWS;
2565: fetch CHK_SWPTN_SETTLE_ROWS into l_dummy;
2566:
2567: if CHK_SWPTN_SETTLE_ROWS%NOTFOUND then
2568: insert into XTR_DEAL_DATE_AMOUNTS
2569: (deal_type,amount_type,date_type,
2570: deal_number,transaction_number,transaction_date,currency,
2571: amount,hce_amount,amount_date,transaction_rate,
2572: cashflow_amount,company_code,deal_subtype,product_type,
2581: end if;
2582: close CHK_SWPTN_SETTLE_ROWS;
2583: end if;
2584: --
2585: update XTR_DEAL_DATE_AMOUNTS
2586: set limit_code = NULL,
2587: status_code = P_STATUS_CODE
2588: where deal_number = P_DEAL_NO
2589: and limit_code is not null