[Home] [Help]
PACKAGE BODY: APPS.XTR_MAINTAIN_DDA_P
Source
1 PACKAGE BODY XTR_MAINTAIN_DDA_P AS
2 /* $Header: xtrprc1b.pls 120.9.12010000.4 2009/12/11 07:54:20 nipant ship $ */
3
4 -- Package that Inserts and Maintains the Deal Date Amount Rows for Deals.
5 --
6 -- This procedure is called by a DB trigger on table DEALS whenever a Deals record is
7 -- UPDATED, DELETED or INSERTED.
8 -- Deal Types currently handled in this procedure are FX, FXO, FRA, NI, IRO, FUT, SWPTN
9 --
10 -- Note Bond code is included below in the main body but not yet tested - this is why
11 -- the first what if excludes bonds at the moment
12 --
13
14
15 procedure MAINTAIN_DDA_PROC (
16 P_ACTION IN VARCHAR2,
17 P_DEAL_TYPE IN VARCHAR2,
18 P_DEAL_NO IN NUMBER,
19 P_TRANSACTION_NUMBER IN NUMBER,
20 P_STATUS_CODE IN VARCHAR2,
21 P_DEAL_SUBTYPE IN VARCHAR2,
22 P_COMPANY_CODE IN VARCHAR2,
23 P_CPARTY_CODE IN VARCHAR2,
24 P_CLIENT_CODE IN VARCHAR2,
25 P_LIMIT_CODE IN VARCHAR2,
26 P_PRODUCT_TYPE IN VARCHAR2,
27 P_PORTFOLIO_CODE IN VARCHAR2,
28 P_CURRENCY IN VARCHAR2,
29 P_CURRENCY_BUY IN VARCHAR2,
30 P_CURRENCY_SELL IN VARCHAR2,
31 P_SWAP_DEPO_FLAG IN VARCHAR2,
32 P_DEALER_CODE IN VARCHAR2,
33 P_DEAL_DATE IN DATE,
34 P_START_DATE IN DATE,
35 P_MATURITY_DATE IN DATE,
36 P_INTEREST_RATE IN NUMBER,
37 P_FACE_VALUE_AMOUNT IN NUMBER,
38 P_FACE_VALUE_HCE_AMOUNT IN NUMBER,
39 P_CPARTY_ACCOUNT_NO IN VARCHAR2,
40 P_OLD_CPARTY_ACCOUNT_NO IN VARCHAR2,
41 P_SETTLE_ACCOUNT_NO IN VARCHAR2,
42 P_OLD_SETTLE_ACCOUNT_NO IN VARCHAR2,
43 P_SETTLE_ACTION IN VARCHAR2,
44 P_SETTLE_AMOUNT IN NUMBER,
45 P_SETTLE_HCE_AMOUNT IN NUMBER,
46 P_SETTLE_RATE IN NUMBER,
47 P_EXERCISE_PRICE IN NUMBER,
48 P_SETTLE_DATE IN DATE,
49 P_PREMIUM_ACTION IN VARCHAR2,
50 P_PREMIUM_DATE IN DATE,
51 P_PREMIUM_AMOUNT IN NUMBER,
52 P_PREMIUM_HCE_AMOUNT IN NUMBER,
53 P_PREMIUM_ACCOUNT_NO IN VARCHAR2,
54 P_OLD_PREMIUM_ACCOUNT_NO IN VARCHAR2,
55 P_TRANSACTION_RATE IN NUMBER,
56 P_INSERT_FOR_CASHFLOW IN VARCHAR2,
57 P_KNOCK_TYPE IN VARCHAR2,
58 P_KNOCK_INSERT_TYPE IN VARCHAR2,
59 P_SELL_AMOUNT IN NUMBER,
60 P_BUY_AMOUNT IN NUMBER,
61 P_SELL_HCE_AMOUNT IN NUMBER,
62 P_BUY_HCE_AMOUNT IN NUMBER,
63 P_SELL_ACCOUNT_NO IN VARCHAR2,
64 P_OLD_SELL_ACCOUNT_NO IN VARCHAR2,
65 P_BUY_ACCOUNT_NO IN VARCHAR2,
66 P_OLD_BUY_ACCOUNT_NO IN VARCHAR2,
67 P_VALUE_DATE IN DATE,
68 P_EXPIRY_DATE IN DATE,
69 P_OPTION_COMMENCEMENT IN DATE,
70 P_COMMENTS IN VARCHAR2,
71 P_OLD_STATUS_CODE IN VARCHAR2,
72 P_QUICK_INPUT IN VARCHAR2,
73 P_START_AMOUNT IN NUMBER,
74 P_START_HCE_AMOUNT IN NUMBER,
75 P_MATURITY_AMOUNT IN NUMBER,
76 P_MATURITY_HCE_AMOUNT IN NUMBER,
77 P_MATURITY_ACCOUNT_NO IN VARCHAR2,
78 P_OLD_MATURITY_ACCOUNT_NO IN VARCHAR2,
79 P_MATURITY_BALANCE_AMOUNT IN NUMBER,
80 P_MATURITY_BALANCE_HCE_AMOUNT IN NUMBER,
81 P_INTEREST_AMOUNT IN NUMBER,
82 P_INTEREST_HCE_AMOUNT IN NUMBER,
83 P_RISKPARTY_LIMIT_CODE IN VARCHAR2,
84 P_RISKPARTY_CODE IN VARCHAR2,
85 P_BOND_ISSUE IN VARCHAR2,
86 P_COUPON_ACTION IN VARCHAR2,
87 P_ACCRUED_INTEREST_PRICE IN NUMBER,
88 P_CUM_COUPON_DATE IN DATE,
89 P_NEXT_COUPON_DATE IN DATE,
90 P_COUPON_RATE IN NUMBER,
91 P_FREQUENCY IN NUMBER,
92 P_ACCEPTOR_CODE IN VARCHAR2,
93 P_CAPITAL_PRICE IN NUMBER,
94 P_PREMIUM_CURRENCY IN VARCHAR2,
95 P_CONTRACT_RATE IN NUMBER,
96 P_CONTRACT_COMMISSION IN NUMBER,
97 P_CONTRACT_FEES IN NUMBER,
98 P_BASE_RATE IN NUMBER,
99 P_NI_PROFIT_LOSS IN NUMBER,
100 P_RATE_FIXING_DATE IN DATE,
101 P_PROFIT_LOSS IN NUMBER,
102 P_OLD_PROFIT_LOSS IN NUMBER,
103 P_FX_RO_PD_RATE IN NUMBER,
104 P_OLD_FX_RO_PD_RATE IN NUMBER,
105 P_FX_M1_DEAL_NO IN NUMBER,
106 P_OLD_FX_M1_DEAL_NO IN NUMBER) is
107
108 --
109 cursor C_GET_COUNTRY (pc_party_code varchar2) is
110 select country_code
111 from XTR_parties_V
112 where party_code = pc_party_code;
113 cursor C_LIMIT_WEIGHTING (v_deal_type VARCHAR2, v_deal_subtype VARCHAR2) is
114 select nvl(limit_weighting,100)
115 from xtr_fx_period_weightings_v
116 where deal_type = v_deal_type
117 and deal_subtype = v_deal_subtype;
118 v_weighting number;
119 ---
120 v_country_code varchar2(25);
121 v_utilised_amount number;
122 v_hce_utilised_amt number;
123 v_hce_amt number;
124 v_limit_party varchar2(7);
125 --
126 v_amount_indic NUMBER :=1;
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;
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;
141 P_PREM_VALUE NUMBER;
142 P_BOND_YR_BASIS NUMBER;
143 P_CALC_TYPE VARCHAR2(15);
144 P_RIC_CODE VARCHAR2(20);
145 --
146 --cursor CPARTY_ACCT_NOS is
147 --select ACCOUNT_NUMBER
148 -- from XTR_BANK_ACCOUNTS
149 --where PARTY_CODE = P_CPARTY_CODE
150 -- and BANK_SHORT_CODE = P_CPARTY_REF
151 --and CURRENCY = decode(P_DEAL_TYPE,'FX',P_CURRENCY_SELL,P_CURRENCY);
152 --
153 cursor COM is
154 select CURRENCY_FIRST||'/'||CURRENCY_SECOND,CURRENCY_FIRST
155 from XTR_BUY_SELL_COMBINATIONS
156 where (CURRENCY_BUY = P_CURRENCY_BUY and CURRENCY_SELL = P_CURRENCY_SELL)
157 or (CURRENCY_BUY = P_CURRENCY_SELL and CURRENCY_SELL = P_CURRENCY_BUY);
158 --
159 l_combin VARCHAR2(31);
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 --
169 l_dummy number;
170 --
171 coupon_date DATE;
172 l_start_date DATE;
173 coupon NUMBER;
174 coupon_hce NUMBER;
175 hce_rate NUMBER;
176 round_fac NUMBER;
177 l_trans_num NUMBER;
178 --
179 l_amount NUMBER;
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 --
189 cursor RND_FAC is
190 select m.ROUNDING_FACTOR
191 from XTR_PRO_PARAM_V p,
192 XTR_MASTER_CURRENCIES_V m
193 where p.PARAM_NAME = 'SYSTEM_FUNCTION_CCY'
194 and m.CURRENCY = p.PARAM_VALUE;
195 --
196 cursor HCE(c_currency varchar2) is
197 select s.HCE_RATE
198 from XTR_MASTER_CURRENCIES s
199 where s.CURRENCY = c_currency;
200
201 cursor CHK_ISSUE is
202 select YEAR_BASIS,CALC_TYPE,RIC_CODE
203 from XTR_BOND_ISSUES
204 where BOND_ISSUE_CODE = P_BOND_ISSUE
205 and AUTHORISED = 'Y'
206 and ((ISSUER=P_COMPANY_CODE and P_DEAL_SUBTYPE='ISSUE') or P_DEAL_SUBTYPE<>'ISSUE')
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 --
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
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
231 and amount_type = 'PREMIUM'
232 and date_type = 'PREMIUM';
233
234 dda_premium_v number;
235
236 -- Bug 8561305 Ends
237 --
238 l_sysdate DATE;
239 l_user VARCHAR2(10);
240 --
241
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
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'.
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
265 */
266
267 If P_Settle_Account_No <> P_Old_Settle_Account_No then
268 v_settle_account_no := P_Settle_Account_No;
269 Else
270 v_settle_account_no := null;
271 End if;
272
273 If P_Premium_Account_No <> P_Old_Premium_Account_No then
274 v_premium_account_no := P_Premium_Account_No;
275 Else
276 v_Premium_account_no := null;
277 End if;
278
279 If P_maturity_Account_No <> P_Old_maturity_Account_No then
280 v_maturity_account_no := P_maturity_Account_No;
281 Else
282 v_maturity_account_no := null;
283 End if;
284
285 If P_Buy_Account_No <> P_Old_Buy_Account_No then
286 v_Buy_account_no := P_Buy_Account_No;
287 Else
288 v_Buy_account_no := null;
289 End if;
290
291 If P_Sell_Account_No <> P_Old_Sell_Account_No then
292 v_sell_account_no := P_Sell_Account_No;
293 Else
294 v_sell_account_no := null;
295 End if;
296
297 If P_CPARTY_ACCOUNT_NO <> P_OLD_CPARTY_ACCOUNT_NO then
298 v_cparty_account_no := P_CPARTY_ACCOUNT_NO;
299 Else
300 v_cparty_account_no := null;
301 End if;
302
303
304 -- note although there is sections below for BONDS we have excluded it here at a higher level
305 -- because code below will need testing
306 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
307 xtr_debug_pkg.debug('Before MAINTAIN_DDA_PROC on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
308 END IF;
309 if P_DEAL_TYPE <> 'BOND' then
310 l_sysdate := trunc(SYSDATE);
311 l_user := fnd_global.user_id;
312 --
313 open COM;
314 fetch COM INTO l_combin,base_ccy;
315 close COM;
316 --
317 --P_CPARTY_ACCOUNT := NULL;
318 --
319 --open CPARTY_ACCT_NOS;
320 -- fetch CPARTY_ACCT_NOS INTO P_CPARTY_ACCOUNT;
321 --close CPARTY_ACCT_NOS;
322 --
323 -- Rounding factors
324 open RND_FAC;
325 fetch RND_FAC INTO round_fac;
326 close RND_FAC;
327 round_fac :=nvl(round_fac,5);
328 --
329 -- Home currency rate
330 open HCE(P_CURRENCY);
331 fetch HCE INTO hce_rate;
332 close HCE;
333 --
334 hce_rate := nvl(hce_rate,1);
335 --
336 if P_DEAL_TYPE = 'BOND' then
337 open CHK_ISSUE;
338 fetch CHK_ISSUE INTO P_BOND_YR_BASIS,P_CALC_TYPE,P_RIC_CODE;
339 close CHK_ISSUE;
340 if P_COUPON_ACTION = 'CUM' then
341 P_INT_VALUE := nvl(round(P_ACCRUED_INTEREST_PRICE * P_MATURITY_AMOUNT / 100,nvl(round_fac,2)),0);
342 else
343 P_INT_VALUE := 0;
344 end if;
345 P_PREM_VALUE := nvl(round((P_CAPITAL_PRICE - 100) * P_MATURITY_AMOUNT / 100,nvl(round_fac,2)),0);
346 end if;
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,
356 product_type,status_code,portfolio_code,
357 dealer_code,client_code,cparty_code,settle,limit_code,
358 limit_party,commence_date,quick_input)
359 values ('FRA','FACEVAL','COMENCE',
360 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
361 P_FACE_VALUE_AMOUNT,P_FACE_VALUE_HCE_AMOUNT,
362 P_START_DATE,P_INTEREST_RATE,0,
363 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
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,
373 status_code,dealer_code,client_code,cparty_code,settle,
374 portfolio_code,commence_date,QUICK_INPUT)
375 values ('FRA','N/A','MATURE',
376 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
377 0,0,P_MATURITY_DATE,P_INTEREST_RATE,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
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,
387 status_code,dealer_code,client_code,cparty_code,settle,
388 portfolio_code,commence_date,QUICK_INPUT)
389 values ('FRA','N/A','DEALT',
390 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
391 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
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,
401 status_code,dealer_code,client_code,cparty_code,settle,
402 portfolio_code,commence_date,QUICK_INPUT,LIMIT_CODE,LIMIT_PARTY)
403 values ('FRA','N/A','LIMIT',
404 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
405 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
406 P_START_DATE,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
407 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
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,
417 status_code,dealer_code,client_code,cparty_code,settle,
418 portfolio_code,commence_date,QUICK_INPUT)
419 values ('FRA','FACEVAL','RATESET',
420 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
421 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
422 P_RATE_FIXING_DATE,P_SETTLE_RATE,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
423 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
424 P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
425 end if;
426
427 -- Dummy row for deal dealt date, this allows a journal action
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,
437 status_code,cparty_code,settle,client_code,
438 limit_code,limit_party,portfolio_code,dealer_code,currency_combination,QUICK_INPUT)
439 values(P_DEAL_TYPE,'FXOBUY','EXPIRY',
440 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
441 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
442 P_EXPIRY_DATE,P_TRANSACTION_RATE,0,P_COMPANY_CODE,
443 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
444 P_CPARTY_CODE,'N',P_CLIENT_CODE,
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,
454 status_code,cparty_code,settle,
455 client_code,portfolio_code,limit_code,limit_party,dealer_code,currency_combination,QUICK_INPUT)
456 values(P_DEAL_TYPE,'FXOSELL','EXPIRY',P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
457 P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,P_EXPIRY_DATE,P_TRANSACTION_RATE,0,
458 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
459 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
460 decode(P_CURRENCY_SELL,substr(upper(l_combin),1,3),nvl(P_LIMIT_CODE,'NILL'),NULL),
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,
470 deal_subtype,product_type,status_code,cparty_code,settle,
471 client_code,portfolio_code,dealer_code,currency_combination,
472 exposure_ref_date,QUICK_INPUT)
473 values(P_DEAL_TYPE,'FXOBUY','VALUE',
474 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
475 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
476 P_VALUE_DATE,P_TRANSACTION_RATE,
477 decode(nvl(P_INSERT_FOR_CASHFLOW,'N'),'Y',P_BUY_AMOUNT,0),
478 P_COMPANY_CODE,P_BUY_ACCOUNT_NO,P_DEAL_SUBTYPE,
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,
488 deal_subtype,product_type,status_code,cparty_code,settle,
489 client_code,portfolio_code,dealer_code,currency_combination,
490 exposure_ref_date,QUICK_INPUT)
491 values(P_DEAL_TYPE,'FXOSELL','VALUE',
492 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
493 P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,
494 P_VALUE_DATE,P_TRANSACTION_RATE,
495 decode(nvl(P_INSERT_FOR_CASHFLOW,'N'),'Y',(-1)*P_SELL_AMOUNT,0),
496 P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_DEAL_SUBTYPE,
497 P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
498 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
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,
508 cparty_account_no,deal_subtype,product_type,status_code,
509 cparty_code,settle,client_code,portfolio_code,dealer_code,QUICK_INPUT)
510 values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
511 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
512 P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
513 P_PREMIUM_DATE,0,decode(P_PREMIUM_ACTION,
514 'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
515 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
516 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
517 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
518 'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
519 P_DEALER_CODE,P_QUICK_INPUT);
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,
529 status_code,dealer_code,client_code,cparty_code,settle,
530 portfolio_code,QUICK_INPUT)
531 values('FXO','N/A','DEALT',
532 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
533 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
534 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
535 P_CLIENT_CODE,P_CPARTY_CODE,'N',
536 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
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,
546 cparty_account_no,deal_subtype,product_type,status_code,
547 cparty_code,settle,client_code,portfolio_code,dealer_code,QUICK_INPUT)
548 values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
549 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
550 P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
551 P_PREMIUM_DATE,0,decode(P_PREMIUM_ACTION,
552 'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
553 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
554 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
555 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
556 'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,P_QUICK_INPUT);
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,
566 status_code,dealer_code,client_code,cparty_code,settle,
567 portfolio_code,QUICK_INPUT)
568 values('FXO','N/A','DEALT',
569 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
570 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
571 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
572 P_CLIENT_CODE,P_CPARTY_CODE,'N',
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,
582 account_no,deal_subtype,product_type,status_code,
583 dealer_code,cparty_code,client_code,portfolio_code,
584 settle,limit_code,limit_party,currency_combination,QUICK_INPUT)
585 values(P_DEAL_TYPE,'BUY','VALUE',
586 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
587 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,P_VALUE_DATE,
588 P_TRANSACTION_RATE,decode(P_SWAP_DEPO_FLAG,'B',0,P_BUY_AMOUNT),P_COMPANY_CODE,
589 P_BUY_ACCOUNT_NO,P_DEAL_SUBTYPE,nvl(P_PRODUCT_TYPE,'NOT APPLIC'),
590 P_STATUS_CODE,P_DEALER_CODE,P_CPARTY_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,
600 account_no,cparty_account_no,deal_subtype,
601 product_type,status_code,dealer_code,cparty_code,
602 client_code,portfolio_code,settle,limit_code,limit_party,currency_combination,QUICK_INPUT)
603 values(P_DEAL_TYPE,'SELL','VALUE',
604 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
605 P_SELL_AMOUNT,nvl(P_BUY_HCE_AMOUNT,0),
606 P_VALUE_DATE,P_TRANSACTION_RATE,decode(P_SWAP_DEPO_FLAG,'S',0,(-1) * P_SELL_AMOUNT),
607 P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
608 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
609 P_DEALER_CODE,P_CPARTY_CODE,P_CLIENT_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,
619 account_no,cparty_account_no,deal_subtype,
620 product_type,status_code,dealer_code,cparty_code,
621 client_code,portfolio_code,settle,currency_combination,QUICK_INPUT)
622 values(P_DEAL_TYPE,'N/A','DEALT',
623 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
624 P_SELL_AMOUNT,nvl(P_SELL_HCE_AMOUNT,0),
625 P_DEAL_DATE,P_TRANSACTION_RATE,0,
626 P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
627 P_DEAL_SUBTYPE,nvl(P_PRODUCT_TYPE,'NOT APPLIC'),P_STATUS_CODE,
628 P_DEALER_CODE,P_CPARTY_CODE,P_CLIENT_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,
638 cparty_account_no,status_code,portfolio_code,dealer_code,
639 client_code,deal_subtype,cparty_code,settle,product_type,
640 commence_date,quick_input,limit_code)
641 values('NI','COMENCE','COMENCE',
642 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
643 P_START_AMOUNT,P_START_HCE_AMOUNT,P_START_DATE,P_INTEREST_RATE,
644 decode(nvl(P_KNOCK_TYPE,'N'),'N',decode(P_DEAL_SUBTYPE,'BUY',-1,'COVER',-1,1)*P_START_AMOUNT,0), -- Bug 3776211
645 P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,
646 P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
647 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
648 P_CPARTY_CODE,'N',P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT,
649 decode(P_RISKPARTY_LIMIT_CODE, null,
650 decode(P_DEAL_SUBTYPE,'SELL','NILL',NULL),NULL)); -- jhung bug 1477157
651 -- decode(P_DEAL_SUBTYPE,'ISSUE',NULL,'NILL'),NULL)); -- AW 9/24/99 Bug 996572
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,
661 cparty_account_no,status_code,portfolio_code,dealer_code,
662 client_code,deal_subtype,cparty_code,settle,product_type,commence_date,quick_input)
663 values('NI','INTL_FV',decode(P_DEAL_SUBTYPE,'SELL','COMENCE','COVER','COMENCE','MATURE'),
664 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
665 P_MATURITY_AMOUNT,P_MATURITY_HCE_AMOUNT,
666 decode(P_DEAL_SUBTYPE,'SELL',P_START_DATE,'COVER',P_START_DATE,P_MATURITY_DATE),
667 p_INTEREST_RATE,0,
668 P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
669 P_STATUS_CODE,P_PORTFOLIO_CODE,
670 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
671 P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT);
672 --
673 if P_DEAL_SUBTYPE in ('BUY','SHORT','ISSUE') and
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,
683 cparty_account_no,status_code,portfolio_code,dealer_code,
684 client_code,deal_subtype,cparty_code,settle,product_type,
685 limit_code,limit_party,commence_date,quick_input)
686 values('NI','BAL_FV','MATURE',
687 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
688 P_MATURITY_BALANCE_AMOUNT,
689 P_MATURITY_BALANCE_HCE_AMOUNT,P_MATURITY_DATE,
690 -- P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,-1) *
691 P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,'SELL',0,-1) *
692 P_MATURITY_BALANCE_AMOUNT,P_COMPANY_CODE,
693 P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
694 P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
695 P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
696 P_PRODUCT_TYPE,
697 nvl(P_RISKPARTY_LIMIT_CODE, decode(P_LIMIT_CODE, null, decode(P_DEAL_SUBTYPE,
698 'BUY','NILL','ISSUE', 'NILL', NULL), P_LIMIT_CODE)),
699 nvl(P_RISKPARTY_CODE,decode(P_DEAL_SUBTYPE,'BUY',P_CPARTY_CODE,null)), --jhung bug 1477157
700 P_START_DATE,P_QUICK_INPUT);
701 end if;
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,
711 dealer_code,client_code,deal_subtype,cparty_code,
712 settle,product_type,commence_date,quick_input)
713 values('NI','INT','COMENCE',
714 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
715 P_INTEREST_AMOUNT,P_INTEREST_HCE_AMOUNT,
716 P_START_DATE,P_INTEREST_RATE,0,
717 P_COMPANY_CODE,P_PORTFOLIO_CODE,P_STATUS_CODE,P_DEALER_CODE,
718 P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
719 P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT);
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,
729 deal_subtype,product_type,status_code,dealer_code,
730 client_code,cparty_code,portfolio_code,commence_date,quick_input)
731 values('NI','N/A','DEALT',
732 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
733 P_START_AMOUNT,P_START_HCE_AMOUNT,P_DEAL_DATE,0,0,P_COMPANY_CODE,
734 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
735 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
736 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
737
738 ------------
739 -- NEW NI --
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,
749 cparty_account_no,status_code,portfolio_code,dealer_code,
750 client_code,deal_subtype,cparty_code,settle,product_type,
751 commence_date,quick_input)
752 values('NI','BALCOM','MATURE',
753 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
754 P_START_AMOUNT,
755 P_START_HCE_AMOUNT,P_MATURITY_DATE,
756 P_INTEREST_RATE, 0,
757 P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,
758 P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
759 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
760 P_CPARTY_CODE,'N',P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT);
761 end if;
762
763 elsif P_DEAL_TYPE = 'BOND' then
764 --
765 if P_DEAL_SUBTYPE <> 'SELL' then
766 if nvl(P_CUM_COUPON_DATE,P_NEXT_COUPON_DATE) is NOT NULL then
767 if P_COUPON_ACTION = 'CUM' then
768 coupon_date := P_CUM_COUPON_DATE;
769 else
770 coupon_date := P_NEXT_COUPON_DATE;
771 end if;
772 l_start_date := add_months(coupon_date,-(12 / P_FREQUENCY));
773 LOOP
774 /* This needs to be replaced with increment method for transaction number.
775 open TRANS_NUM;
776 fetch TRANS_NUM INTO l_trans_num;
777 close TRANS_NUM;
778 */
779 if upper(P_CALC_TYPE) = 'VARIABLE COUPON' then
780 coupon := round(P_MATURITY_AMOUNT * P_COUPON_RATE * (coupon_date -
781 l_start_date) / (nvl(P_BOND_YR_BASIS,365)
782 * 100),round_fac);
783 else
784 coupon := round(P_MATURITY_AMOUNT * (P_COUPON_RATE / 100) /
785 nvl(P_FREQUENCY,2),round_fac);
786 end if;
787 coupon_hce := round(coupon / hce_rate,round_fac);
788 --
789 insert into XTR_ROLLOVER_TRANSACTIONS
790 (deal_number,transaction_number,deal_type,start_date,no_of_days,
791 maturity_date,interest_rate,interest,interest_hce,deal_subtype,
792 product_type,company_code,cparty_code,client_code,currency,
793 deal_date,status_code,created_by,created_on,settle_date)
794 values(P_DEAL_NO,l_trans_num,'BOND',l_start_date,(coupon_date -
795 l_start_date),coupon_date,P_COUPON_RATE,coupon,coupon_hce,
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,
805 dealer_code,client_code,deal_subtype,cparty_code,settle,product_type)
806 values
807 ('BOND','COUPON','COUPON',P_DEAL_NO,l_trans_num,P_DEAL_DATE,
808 P_CURRENCY,coupon,coupon_hce,coupon_date,P_COUPON_RATE,
809 decode(P_DEAL_SUBTYPE,'BUY',1,-1) * coupon,P_COMPANY_CODE,
810 P_MATURITY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
811 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,P_ACCEPTOR_CODE,'N',
812 P_PRODUCT_TYPE);
813 --
814 l_start_date := coupon_date;
815 coupon_date := add_months(coupon_date,(12 / P_FREQUENCY));
816 EXIT WHEN coupon_date > P_MATURITY_DATE;
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,
826 cparty_account_no,status_code,portfolio_code,dealer_code,
827 client_code,deal_subtype,cparty_code,settle,product_type)
828 values('BOND','COMENCE','COMENCE',
829 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
830 P_START_AMOUNT,P_START_HCE_AMOUNT,
831 P_START_DATE,P_INTEREST_RATE,
832 decode(P_DEAL_SUBTYPE,'BUY',-1,1) * P_START_AMOUNT,
833 P_COMPANY_CODE,P_MATURITY_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,
843 cparty_account_no,status_code,portfolio_code,dealer_code,
844 client_code,deal_subtype,cparty_code,settle,product_type,
845 limit_code,limit_party)
846 values('BOND','INTL_FV','MATURE',
847 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
848 P_MATURITY_AMOUNT,P_MATURITY_HCE_AMOUNT,
849 P_MATURITY_DATE,P_INTEREST_RATE,
850 decode(P_DEAL_SUBTYPE,'BUY',1,-1) *
851 P_MATURITY_AMOUNT,P_COMPANY_CODE,
852 P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
853 P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
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,
863 portfolio_code,dealer_code,client_code,deal_subtype,
864 cparty_code,settle,product_type)
865 values('BOND','INT','COMENCE',
866 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
867 P_INT_VALUE,round(P_INT_VALUE/hce_rate,round_fac),P_START_DATE,
868 P_INTEREST_RATE,0,P_COMPANY_CODE,P_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,
878 portfolio_code,dealer_code,client_code,deal_subtype,
879 cparty_code,settle,product_type)
880 values('BOND',decode(sign(P_PREM_VALUE),-1,'DISC','PREMIUM'),
881 'COMENCE',P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
882 abs(P_PREM_VALUE),abs(round(P_PREM_VALUE/hce_rate,round_fac)),
883 P_START_DATE,P_INTEREST_RATE,0,
884 P_COMPANY_CODE,P_STATUS_CODE,P_PORTFOLIO_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,
894 status_code,dealer_code,client_code,cparty_code,settle,
895 limit_code,limit_party,portfolio_code)
896 values('IRO','N/A','LIMIT',
897 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
898 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
899 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
900 P_EXPIRY_DATE,0,
901 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_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,
911 status_code,dealer_code,client_code,cparty_code,settle,
912 portfolio_code)
913 values('IRO','FACEVAL','EXPIRY',
914 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
915 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
916 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
917 P_EXPIRY_DATE,P_INTEREST_RATE,0,
918 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_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,
928 company_code,account_no,action_code,cparty_account_no,
929 deal_subtype,product_type,status_code,dealer_code,
930 client_code,cparty_code,settle,portfolio_code)
931 values('IRO','PREMIUM','PREMIUM',
932 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
933 nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
934 nvl(P_PREMIUM_DATE,P_START_DATE),0,
935 decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
936 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
937 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,''),
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,
947 deal_subtype,product_type,status_code,dealer_code,
948 client_code,cparty_code,portfolio_code)
949 values('IRO','FACEVAL','DEALT',
950 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
951 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
952 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
953 P_DEAL_DATE,0,0,P_COMPANY_CODE,
954 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
955 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
956 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
957 -- AW Bug 894751 American Option
958 /*
959 values('IRO','N/A','DEALT',
960 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
961 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,
962 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_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,
972 deal_subtype,product_type,status_code,dealer_code,
973 client_code,cparty_code,portfolio_code)
974 values('IRO','FACEVAL','COMENCE',
975 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
976 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
977 0,P_START_DATE,0,0,P_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,
987 deal_subtype,product_type,status_code,dealer_code,
988 client_code,cparty_code,portfolio_code)
989 values('IRO','N/A','MATURE',
990 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
991 0,0,P_MATURITY_DATE,0,0,P_COMPANY_CODE,
992 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_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,
1002 status_code,dealer_code,client_code,cparty_code,settle,
1003 limit_code,limit_party,portfolio_code)
1004 values('BDO','N/A','LIMIT',
1005 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1006 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
1007 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
1008 P_EXPIRY_DATE,0,
1009 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_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,
1019 status_code,dealer_code,client_code,cparty_code,settle,
1020 portfolio_code)
1021 values('BDO','FACEVAL','EXPIRY',
1022 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1023 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
1024 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
1025 P_EXPIRY_DATE,P_INTEREST_RATE,0,
1026 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_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,
1036 company_code,account_no,action_code,cparty_account_no,
1037 deal_subtype,product_type,status_code,dealer_code,
1038 client_code,cparty_code,settle,portfolio_code)
1039 values('BDO','PREMIUM','PREMIUM',
1040 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1041 nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
1042 nvl(P_PREMIUM_DATE,P_START_DATE),0,
1043 decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
1044 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
1045 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,''),
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,
1055 deal_subtype,product_type,status_code,dealer_code,
1056 client_code,cparty_code,portfolio_code)
1057 values('BDO','FACEVAL','DEALT',
1058 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1059 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
1060 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
1061 P_DEAL_DATE,0,0,P_COMPANY_CODE,
1062 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1063 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1064 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1065 -- AW Bug 894751
1066 /*
1067 values('BDO','N/A','DEALT',
1068 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1069 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,
1070 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_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,
1080 deal_subtype,product_type,status_code,dealer_code,
1081 client_code,cparty_code,portfolio_code)
1082 values('BDO','FACEVAL','COMENCE',
1083 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1084 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
1085 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
1086 P_START_DATE,0,0,P_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,
1096 deal_subtype,product_type,status_code,dealer_code,
1097 client_code,cparty_code,portfolio_code)
1098 values('BDO','N/A','MATURE',
1099 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1100 0,0,P_MATURITY_DATE,0,0,P_COMPANY_CODE,
1101 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_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,
1111 status_code,dealer_code,client_code,cparty_code,settle,
1112 limit_code,limit_party,portfolio_code,contract_code)
1113 values('FUT','FACEVAL','EXPIRY',
1114 P_DEAL_NO,1,P_DEAL_DATE,
1115 nvl(P_CURRENCY_BUY,'N/A'),
1116 nvl(P_START_AMOUNT,0),nvl(P_START_HCE_AMOUNT,
1117 P_START_AMOUNT),P_EXPIRY_DATE,nvl(P_TRANSACTION_RATE,P_CONTRACT_RATE),0,
1118 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1119 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
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,
1129 cparty_account_no,deal_subtype,product_type,status_code,
1130 dealer_code,client_code,cparty_code,settle,
1131 portfolio_code,contract_code)
1132 values('FUT','PREMIUM','SETTLE',
1133 P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
1134 nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) ,nvl(P_PREMIUM_HCE_AMOUNT,0),
1135 P_PREMIUM_DATE,0,-(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
1136 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,'PAY',
1137 P_CPARTY_ACCOUNT_NO,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1138 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_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,
1148 status_code,dealer_code,client_code,cparty_code,settle,
1149 portfolio_code)
1150 values('FUT','DEALT','DEALT',
1151 P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
1152 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
1153 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
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,
1163 status_code,dealer_code,client_code,cparty_code,settle,
1164 limit_code,limit_party,portfolio_code)
1165 values('SWPTN','N/A','LIMIT',
1166 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1167 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
1168 P_EXPIRY_DATE,0,
1169 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_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,
1179 status_code,dealer_code,client_code,cparty_code,settle,
1180 portfolio_code)
1181 values('SWPTN','FACEVAL','EXPIRY',
1182 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1183 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
1184 P_EXPIRY_DATE,P_INTEREST_RATE,0,
1185 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_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,
1195 deal_subtype,product_type,status_code,dealer_code,
1196 client_code,cparty_code,settle,portfolio_code)
1197 values('SWPTN','PREMIUM','PREMIUM',
1198 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1199 nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
1200 nvl(P_PREMIUM_DATE,P_START_DATE),0,
1201 decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
1202 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
1203 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,''),
1204 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
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,
1214 status_code,dealer_code,client_code,cparty_code,settle,portfolio_code)
1215 values('SWPTN','FACEVAL','DEALT',
1216 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1217 nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
1218 P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
1219 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
1220 P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1221 -- AW Bug 894751
1222 /*
1223 values('SWPTN','N/A','DEALT',
1224 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1225 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
1226 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
1227 P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1228 */
1229 -- Settlement Details
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,
1239 cparty_account_no,deal_subtype,product_type,status_code,
1240 dealer_code,client_code,cparty_code,settle,portfolio_code)
1241 values('SWPTN','SETTLE','SETTLE',
1242 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1243 nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
1244 nvl(P_SETTLE_DATE,P_START_DATE),0,
1245 decode(P_SETTLE_ACTION,'PAY',-(1),1) * nvl(P_SETTLE_AMOUNT,0),
1246 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,P_SETTLE_ACTION,
1247 nvl(v_settle_ac,P_CPARTY_ACCOUNT_NO), --BUG 2910654
1248 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_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,
1258 status_code,dealer_code,client_code,cparty_code,settle,
1259 portfolio_code)
1260 values('SWPTN','FACEVAL','COMENCE',
1261 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1262 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
1263 P_START_DATE,P_INTEREST_RATE,0,
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,
1273 status_code,dealer_code,client_code,cparty_code,settle,portfolio_code)
1274 values('SWPTN','N/A','MATURE',
1275 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1276 0,0,P_MATURITY_DATE,P_INTEREST_RATE,0,
1277 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1278 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1279 P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1280 -- end of deal types for inserting
1281 end if;
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
1291 delete from XTR_ROLLOVER_TRANSACTIONS
1292 where deal_number = P_DEAL_NO;
1293 elsif P_DEAL_TYPE = 'FX' then
1294 -- Swapdepo transactions
1295 update XTR_ROLLOVER_TRANSACTIONS
1296 set STATUS_CODE = 'CANCELLED'
1297 where deal_number = P_DEAL_NO;
1298 end if;
1299 --
1300 if P_DEAL_TYPE = 'NI' then
1301 update XTR_PARCEL_SPLITS
1302 set status_code = 'CANCELLED'
1303 where deal_no = P_DEAL_NO
1304 and parcel_size = nvl(parcel_remaining,0);
1305 /*
1306 ------------
1307 -- NEW NI --
1308 ------------
1309 update XTR_ROLLOVER_TRANSACTIONS
1310 set STATUS_CODE = 'CANCELLED'
1311 where DEAL_NUMBER = P_DEAL_NO
1312 and TRANS_CLOSEOUT_NO is null;
1313 */
1314 end if;
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),
1324 transaction_rate = decode(DATE_TYPE,'COMENCE',P_INTEREST_RATE,'MATURE',P_INTEREST_RATE,
1325 'RATESET',P_SETTLE_RATE,transaction_rate),
1326 transaction_date = P_DEAL_DATE,
1327 currency = P_CURRENCY,
1328 company_code = P_COMPANY_CODE,
1329 deal_subtype = P_DEAL_SUBTYPE,
1330 product_type = P_PRODUCT_TYPE,
1331 portfolio_code = P_PORTFOLIO_CODE,
1332 status_code = P_STATUS_CODE,
1333 dealer_code = P_DEALER_CODE,
1334 client_code = P_CLIENT_CODE,
1335 cparty_code = P_CPARTY_CODE,
1336 action_code = NULL,
1337 limit_code = decode(P_SETTLE_DATE,NULL,decode(DATE_TYPE,'LIMIT',nvl(P_LIMIT_CODE,'NILL'),NULL),NULL),
1338 limit_party = decode(P_SETTLE_DATE,NULL,decode(DATE_TYPE,'LIMIT',P_CPARTY_CODE,NULL),NULL)
1339 where DEAL_NUMBER = P_DEAL_NO
1340 and DEAL_TYPE = 'FRA'
1341 and AMOUNT_TYPE <> 'SETTLE'
1342 and DATE_TYPE <> 'LIMIT';
1343 --
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,
1353 transaction_date = P_DEAL_DATE,
1354 currency = P_CURRENCY,
1355 company_code = P_COMPANY_CODE,
1356 deal_subtype = P_DEAL_SUBTYPE,
1357 product_type = P_PRODUCT_TYPE,
1358 portfolio_code = P_PORTFOLIO_CODE,
1359 dealer_code = P_DEALER_CODE,
1360 client_code = P_CLIENT_CODE,
1361 cparty_code = P_CPARTY_CODE,
1362 limit_code = nvl(P_LIMIT_CODE,'NILL'),
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)),
1372 CPARTY_ACCOUNT_NO = nvl(v_cparty_account_no, cparty_account_no),
1373 ACCOUNT_NO = nvl(v_SETTLE_ACCOUNT_NO, account_no),
1374 AMOUNT_DATE = P_SETTLE_DATE,
1375 STATUS_CODE = P_STATUS_CODE,
1376 TRANSACTION_RATE = P_SETTLE_RATE,
1377 transaction_date = P_DEAL_DATE,
1378 currency = P_CURRENCY,
1379 company_code = P_COMPANY_CODE,
1380 deal_subtype = P_DEAL_SUBTYPE,
1381 product_type = P_PRODUCT_TYPE,
1382 portfolio_code = P_PORTFOLIO_CODE,
1383 dealer_code = P_DEALER_CODE,
1384 client_code = P_CLIENT_CODE,
1385 cparty_code = P_CPARTY_CODE
1386 where DEAL_NUMBER = P_DEAL_NO
1387 and AMOUNT_TYPE = 'SETTLE'
1388 and DATE_TYPE = 'SETTLE'
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,
1398 deal_subtype,product_type,status_code,portfolio_code,
1399 dealer_code,client_code,cparty_code,settle)
1400 values('FRA','SETTLE','SETTLE',
1401 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1402 nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
1403 P_SETTLE_DATE,nvl(P_SETTLE_RATE,0),
1404 decode(P_SETTLE_ACTION,'PAY',-1,1) *
1405 nvl(P_SETTLE_AMOUNT,0),P_COMPANY_CODE,
1406 P_SETTLE_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,P_SETTLE_ACTION,P_DEAL_SUBTYPE,
1407 P_PRODUCT_TYPE,P_STATUS_CODE,P_PORTFOLIO_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),
1417 -- for bug 965188 part 2 account number should not be updated at all
1418 limit_code = decode(P_STATUS_CODE,'CURRENT',
1419 decode(amount_type,'FXOBUY',
1420 decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1421 decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL)),NULL),
1422 limit_party = decode(P_STATUS_CODE,'CURRENT',
1423 decode(amount_type,'FXOBUY',
1424 decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
1425 decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL)),NULL),
1426 transaction_date = P_DEAL_DATE,
1427 currency = decode(amount_type,'FXOBUY',P_CURRENCY_BUY,P_CURRENCY_SELL),
1428 company_code = P_COMPANY_CODE,
1429 deal_subtype = P_DEAL_SUBTYPE,
1430 product_type = P_PRODUCT_TYPE,
1431 portfolio_code = P_PORTFOLIO_CODE,
1432 status_code = P_STATUS_CODE,
1433 dealer_code = P_DEALER_CODE,
1434 client_code = P_CLIENT_CODE,
1435 cparty_code = P_CPARTY_CODE
1436 where deal_number = P_DEAL_NO
1437 and date_type = 'EXPIRY'
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,
1447 status_code,cparty_code,settle,client_code,
1448 limit_code,limit_party,portfolio_code,dealer_code,currency_combination,QUICK_INPUT)
1449 values(P_DEAL_TYPE,'FXOBUY','EXPIRY',
1450 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1451 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
1452 P_EXPIRY_DATE,P_TRANSACTION_RATE,0,P_COMPANY_CODE,
1453 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1454 P_CPARTY_CODE,'N',P_CLIENT_CODE,
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,
1464 status_code,cparty_code,settle,
1465 client_code,portfolio_code,limit_code,limit_party,dealer_code,currency_combination,QUICK_INPUT)
1466 values(P_DEAL_TYPE,'FXOSELL','EXPIRY',P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
1467 P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,P_EXPIRY_DATE,P_TRANSACTION_RATE,0,
1468 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
1469 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
1470 decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1471 decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),
1472 P_DEALER_CODE,l_combin,P_QUICK_INPUT);
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,
1482 deal_subtype = P_DEAL_SUBTYPE,
1483 product_type = P_PRODUCT_TYPE,
1484 portfolio_code = P_PORTFOLIO_CODE,
1485 status_code = P_STATUS_CODE,
1486 dealer_code = P_DEALER_CODE,
1487 client_code = P_CLIENT_CODE,
1488 cparty_code = P_CPARTY_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),
1498 account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
1499 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
1500 transaction_date = P_DEAL_DATE,
1501 currency = P_CURRENCY,
1502 company_code = P_COMPANY_CODE,
1503 deal_subtype = P_DEAL_SUBTYPE,
1504 product_type = P_PRODUCT_TYPE,
1505 portfolio_code = P_PORTFOLIO_CODE,
1506 status_code = P_STATUS_CODE,
1507 dealer_code = P_DEALER_CODE,
1508 client_code = P_CLIENT_CODE,
1509 cparty_code = P_CPARTY_CODE,
1510 action_code = P_PREMIUM_ACTION
1511 where deal_number = P_DEAL_NO
1512 and date_type = 'PREMIUM'
1513 and deal_type = P_DEAL_TYPE;
1514 --
1515 if SQL%NOTFOUND AND P_CURRENCY IS NOT NULL
1516 and P_PREMIUM_ACTION is NOT NULL
1517 and nvl(P_PREMIUM_AMOUNT,0) > 0 and P_STATUS_CODE <> 'CANCELLED'
1518 then
1519 --
1520 --Bug 8561305 Starts
1521 open CHK_FXO_PREMIUM;
1522 fetch CHK_FXO_PREMIUM into dda_premium_v;
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,
1532 cparty_account_no,deal_subtype,product_type,status_code,
1533 cparty_code,settle,client_code,portfolio_code,dealer_code)
1534 values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
1535 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1536 P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
1537 P_PREMIUM_DATE,0,decode(P_PREMIUM_ACTION,
1538 'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
1539 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,'SETTLE',
1540 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
1541 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
1542 'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE);
1543
1544 end if;
1545 -- Bug 8561305 ends;
1546 end if;
1547 --
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),
1557 cashflow_amount = decode(status_code,'CURRENT',
1558 decode(DATE_TYPE,'EXPIRY',0,
1559 decode(P_INSERT_FOR_CASHFLOW,'Y',(-1) * P_SELL_AMOUNT,0))
1560 ,0),
1561 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1562 limit_code = decode(status_code,'EXERCISED',NULL,
1563 decode(DATE_TYPE,'EXPIRY',
1564 decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),NULL)),
1565 limit_party = decode(DATE_TYPE,'EXPIRY',decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),NULL),
1566 transaction_date = P_DEAL_DATE,
1567 currency = P_CURRENCY_SELL,
1568 company_code = P_COMPANY_CODE,
1569 deal_subtype = P_DEAL_SUBTYPE,
1570 product_type = P_PRODUCT_TYPE,
1571 portfolio_code = P_PORTFOLIO_CODE,
1572 status_code = P_STATUS_CODE,
1573 dealer_code = P_DEALER_CODE,
1574 client_code = P_CLIENT_CODE,
1575 cparty_code = P_CPARTY_CODE
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,
1585 deal_subtype,product_type,status_code,cparty_code,settle,
1586 client_code,portfolio_code,dealer_code,currency_combination,
1587 exposure_ref_date)
1588 values(P_DEAL_TYPE,'FXOBUY','VALUE',
1589 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1590 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
1591 P_VALUE_DATE,P_TRANSACTION_RATE,P_BUY_AMOUNT,
1592 P_COMPANY_CODE,P_BUY_ACCOUNT_NO,P_DEAL_SUBTYPE,
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,
1602 deal_subtype,product_type,status_code,cparty_code,settle,
1603 client_code,portfolio_code,dealer_code,currency_combination,
1604 exposure_ref_date)
1605 values(P_DEAL_TYPE,'FXOSELL','VALUE',
1606 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
1607 P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,
1608 P_VALUE_DATE,P_TRANSACTION_RATE,(-1) * P_SELL_AMOUNT,
1609 P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_DEAL_SUBTYPE,
1610 P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
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;
1620 --
1621 if P_STATUS_CODE = 'EXPIRED' then
1622
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,
1632 cparty_account_no,deal_subtype,product_type,status_code,
1633 cparty_code,settle,client_code,portfolio_code,dealer_code)
1634 values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
1635 P_DEAL_NO,1,sysdate,P_CURRENCY,
1636 P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
1637 P_EXPIRY_DATE,0,decode(P_PREMIUM_ACTION,
1638 'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
1639 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
1640 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
1641 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_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
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 --
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),
1679 --Bug 8561305 ends
1680 TRANSACTION_DATE = trunc(sysdate)
1681 where DEAL_NUMBER = P_DEAL_NO
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';
1691 --Bug 8561305 ends
1692 --
1693 end if;
1694 elsif P_DEAL_TYPE = 'FX' then
1695
1696 /*--------------------------- RVALLAMS FX REARCH ----------------------------- */
1697
1698 IF ( (P_STATUS_CODE = 'CLOSED' and nvl(P_OLD_STATUS_CODE,'XXX') <> 'CLOSED'
1699 AND P_PROFIT_LOSS IS NOT NULL AND P_OLD_PROFIT_LOSS IS NULL
1700 AND P_FX_RO_PD_RATE IS NOT NULL AND P_OLD_FX_RO_PD_RATE IS NULL)
1701 OR
1702 (P_STATUS_CODE = 'CLOSED' AND P_OLD_STATUS_CODE = 'CLOSED'
1703 AND P_FX_M1_DEAL_NO IS NOT NULL AND P_OLD_FX_M1_DEAL_NO IS NOT NULL
1704 AND P_PROFIT_LOSS IS NOT NULL AND P_OLD_PROFIT_LOSS IS NOT NULL
1705 AND P_FX_RO_PD_RATE IS NOT NULL AND P_OLD_FX_RO_PD_RATE IS NOT NULL
1706 AND P_FX_M1_DEAL_NO <> P_OLD_FX_M1_DEAL_NO
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,
1716 CASHFLOW_AMOUNT = 0,
1717 DATE_TYPE = 'ROLLPRE',
1718 ACCOUNT_NO = nvl(v_BUY_ACCOUNT_NO, account_no ),
1719 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
1720 LIMIT_CODE = NULL,
1721 limit_party = decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
1722 currency = P_CURRENCY_BUY,
1723 currency_combination = l_combin,
1724 product_type = P_PRODUCT_TYPE,
1725 company_code = P_COMPANY_CODE,
1726 cparty_code = P_CPARTY_CODE,
1727 client_code = P_CLIENT_CODE,
1728 dealer_code = P_DEALER_CODE,
1729 transaction_rate = P_TRANSACTION_RATE,
1730 transaction_date = P_DEAL_DATE
1731 WHERE DEAL_NUMBER = P_DEAL_NO
1732 AND DEAL_TYPE = P_DEAL_TYPE
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,
1742 DATE_TYPE = 'ROLLPRE',
1743 STATUS_CODE = P_STATUS_CODE,
1744 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1745 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
1746 LIMIT_CODE = NULL,
1747 limit_party = decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),
1748 currency = P_CURRENCY_SELL,
1749 currency_combination = l_combin,
1750 product_type = P_PRODUCT_TYPE,
1751 portfolio_code = P_PORTFOLIO_CODE,
1752 company_code = P_COMPANY_CODE,
1753 cparty_code = P_CPARTY_CODE,
1754 client_code = P_CLIENT_CODE,
1755 dealer_code = P_DEALER_CODE,
1756 transaction_rate = P_TRANSACTION_RATE,
1757 transaction_date = P_DEAL_DATE
1758 where deal_number = P_DEAL_NO
1759 and amount_type = 'SELL'
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,
1769 currency = P_CURRENCY_SELL,
1770 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1771 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no ),
1772 product_type = P_PRODUCT_TYPE,
1773 portfolio_code = P_PORTFOLIO_CODE,
1774 company_code = P_COMPANY_CODE,
1775 cparty_code = P_CPARTY_CODE,
1776 client_code = P_CLIENT_CODE,
1777 dealer_code = P_DEALER_CODE,
1778 transaction_rate = P_TRANSACTION_RATE,
1779 transaction_date = P_DEAL_DATE,
1780 currency_combination = l_combin
1781 where deal_number = P_DEAL_NO
1782 and amount_type = 'N/A'
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,
1792 CASHFLOW_AMOUNT = P_BUY_AMOUNT,
1793 ACCOUNT_NO = nvl(v_BUY_ACCOUNT_NO, account_no),
1794 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
1795 LIMIT_CODE = decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1796 limit_party = decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
1797 currency = P_CURRENCY_BUY,
1798 currency_combination = l_combin,
1799 product_type = P_PRODUCT_TYPE,
1800 company_code = P_COMPANY_CODE,
1801 cparty_code = P_CPARTY_CODE,
1802 client_code = P_CLIENT_CODE,
1803 dealer_code = P_DEALER_CODE,
1804 transaction_rate = P_TRANSACTION_RATE,
1805 transaction_date = P_DEAL_DATE
1806 where deal_number = P_DEAL_NO
1807 and amount_type = 'BUY'
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,
1817 STATUS_CODE = P_STATUS_CODE,
1818 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1819 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
1820 LIMIT_CODE = decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1821 limit_party = decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),
1822 currency = P_CURRENCY_SELL,
1823 currency_combination = l_combin,
1824 product_type = P_PRODUCT_TYPE,
1825 portfolio_code = P_PORTFOLIO_CODE,
1826 company_code = P_COMPANY_CODE,
1827 cparty_code = P_CPARTY_CODE,
1828 client_code = P_CLIENT_CODE,
1829 dealer_code = P_DEALER_CODE,
1830 transaction_rate = P_TRANSACTION_RATE,
1831 transaction_date = P_DEAL_DATE
1832 where deal_number = P_DEAL_NO
1833 and amount_type = 'SELL'
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,
1843 currency = P_CURRENCY_SELL,
1844 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1845 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
1846 product_type = P_PRODUCT_TYPE,
1847 portfolio_code = P_PORTFOLIO_CODE,
1848 company_code = P_COMPANY_CODE,
1849 cparty_code = P_CPARTY_CODE,
1850 client_code = P_CLIENT_CODE,
1851 dealer_code = P_DEALER_CODE,
1852 transaction_rate = P_TRANSACTION_RATE,
1853 transaction_date = P_DEAL_DATE,
1854 currency_combination = l_combin
1855 where deal_number = P_DEAL_NO
1856 and amount_type = 'N/A'
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,
1866 CPARTY_CODE = P_CPARTY_CODE,
1867 PRODUCT_TYPE = P_PRODUCT_TYPE,
1868 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
1869 DEAL_SUBTYPE = P_DEAL_SUBTYPE,
1870 DEALER_CODE = P_DEALER_CODE,
1871 CLIENT_CODE = P_CLIENT_CODE,
1872 TRANSACTION_RATE = P_INTEREST_RATE,
1873 TRANSACTION_DATE = P_DEAL_DATE,
1874 CURRENCY = P_CURRENCY,
1875 LIMIT_CODE = decode(AMOUNT_TYPE,'COMENCE',
1876 decode(P_RISKPARTY_LIMIT_CODE,NULL,'NILL',NULL),
1877 'BAL_FV',
1878 decode(P_RISKPARTY_LIMIT_CODE,NULL,NULL,P_RISKPARTY_LIMIT_CODE),
1879 LIMIT_CODE),
1880 LIMIT_PARTY = decode(AMOUNT_TYPE,'COMENCE',
1881 NULL,
1882 'BAL_FV',
1883 decode(P_RISKPARTY_LIMIT_CODE,NULL,NULL,P_RISKPARTY_CODE),
1884 LIMIT_PARTY),
1885 AMOUNT_DATE = decode(AMOUNT_TYPE,'COMENCE',P_START_DATE,
1886 'BAL_FV',P_MATURITY_DATE,
1887 'INTL_FV',decode(P_DEAL_SUBTYPE,'SELL',P_START_DATE,
1888 'COVER',P_START_DATE,P_MATURITY_DATE),
1889 'INT',P_START_DATE,
1890 AMOUNT_DATE),
1891 AMOUNT = decode(AMOUNT_TYPE,'COMENCE',nvl(P_START_AMOUNT,0),
1892 'BAL_FV',nvl(P_MATURITY_BALANCE_AMOUNT,0),
1893 'INTL_FV', nvl(P_MATURITY_AMOUNT,0),
1894 'INT', nvl(P_INTEREST_AMOUNT,0),
1895 AMOUNT),
1896 HCE_AMOUNT = decode(AMOUNT_TYPE,'COMENCE', nvl(P_START_HCE_AMOUNT,0),
1897 'BAL_FV', nvl(P_MATURITY_BALANCE_HCE_AMOUNT,0),
1898 'INTL_FV', nvl(P_MATURITY_HCE_AMOUNT,0),
1899 'INT', nvl(P_INTEREST_HCE_AMOUNT,0),
1900 HCE_AMOUNT),
1901 CASHFLOW_AMOUNT = decode(AMOUNT_TYPE,'COMENCE',
1902 decode(nvl(P_KNOCK_TYPE,'N'),'N',decode(P_DEAL_SUBTYPE,'BUY',-1,'COVER',-1,1)*nvl(P_START_AMOUNT,0),0),
1903 -- 'BAL_FV', decode(P_DEAL_SUBTYPE,'BUY',1,-1) * nvl(P_MATURITY_BALANCE_AMOUNT,0),
1904 'BAL_FV', decode(P_DEAL_SUBTYPE,'BUY',1,'SELL',0,-1) * nvl(P_MATURITY_BALANCE_AMOUNT,0),
1905 CASHFLOW_AMOUNT) -- bug 3776211
1906 where DEAL_NUMBER = P_DEAL_NO
1907 and DEAL_TYPE = P_DEAL_TYPE;
1908 /* AW 9/24/99 Bug 996572
1909 LIMIT_CODE = decode(AMOUNT_TYPE,'COMENCE',
1910 decode(P_DEAL_SUBTYPE,'SELL',nvl(nvl(P_RISKPARTY_LIMIT_CODE,P_LIMIT_CODE),'NILL'),NULL),
1911 'BAL_FV',
1912 decode(P_DEAL_SUBTYPE,'SELL',NULL,nvl(nvl(P_RISKPARTY_LIMIT_CODE,P_LIMIT_CODE),'NILL')),
1913 LIMIT_CODE),
1914 LIMIT_PARTY = decode(AMOUNT_TYPE,'COMENCE', decode(P_DEAL_SUBTYPE,'SELL',P_RISKPARTY_CODE,NULL),
1915 'BAL_FV', decode(P_DEAL_SUBTYPE,'SELL',NULL,P_RISKPARTY_CODE),
1916 LIMIT_PARTY),
1917 */
1918 --
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,
1928 cparty_account_no,status_code,portfolio_code,dealer_code,
1929 client_code,deal_subtype,cparty_code,settle,product_type,
1930 limit_code,limit_party,commence_date,quick_input)
1931 values('NI','BAL_FV','MATURE',
1932 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1933 P_MATURITY_BALANCE_AMOUNT,
1934 P_MATURITY_BALANCE_HCE_AMOUNT,P_MATURITY_DATE,
1935 -- P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,-1) *
1936 P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,'SELL',0,-1) *
1937 P_MATURITY_BALANCE_AMOUNT,P_COMPANY_CODE,
1938 P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
1939 P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
1940 P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
1941 P_PRODUCT_TYPE,decode(P_DEAL_SUBTYPE,'SELL',NULL,nvl(nvl(P_RISKPARTY_LIMIT_CODE,P_LIMIT_CODE),'NILL')),
1942 decode(P_DEAL_SUBTYPE,'SELL',NULL,P_RISKPARTY_CODE),P_START_DATE,P_QUICK_INPUT);
1943 end if;
1944 close CHK_NI_BAL_FV;
1945 end if;
1946 --
1947
1948 ------------
1949 -- NEW NI --
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'),
1959 CASHFLOW_AMOUNT = 0
1960 where DEAL_NUMBER = P_DEAL_NO
1961 and AMOUNT_TYPE = 'REAL'
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,
1971 deal_subtype,product_type,settle,cparty_code,client_code,
1972 portfolio_code,action_code)
1973 values('NI','REAL','REVAL',
1974 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1975 abs(nvl(P_NI_PROFIT_LOSS,0)),abs(nvl(round(P_NI_PROFIT_LOSS/hce_rate,2),0)),
1976 P_START_DATE,P_INTEREST_RATE,0,P_COMPANY_CODE,
1977 P_STATUS_CODE,P_DEALER_CODE,P_DEAL_SUBTYPE,
1978 P_PRODUCT_TYPE,'N',P_CPARTY_CODE,P_CLIENT_CODE,
1979 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),decode(sign(nvl(P_NI_PROFIT_LOSS,0)),-1,'LOSS','PROFIT'));
1980 end if;
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,
1990 transaction_date = P_DEAL_DATE,
1991 account_no = nvl(v_MATURITY_ACCOUNT_NO, account_no ),
1992 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no)
1993 where deal_number = P_DEAL_NO
1994 and amount_date >= L_SYSDATE
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,
2004 transaction_date = P_DEAL_DATE,
2005 account_no = nvl(v_MATURITY_ACCOUNT_NO, account_no),
2006 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
2007 limit_code = nvl(P_LIMIT_CODE,'NILL')
2008 where deal_number = P_DEAL_NO
2009 and amount_type = 'INTL_FV'
2010 and date_type = 'MATURE'
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
2020 where deal_number = P_DEAL_NO
2021 and amount_type = 'INT'
2022 and date_type = 'COMENCE'
2023 and deal_type = P_DEAL_TYPE
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
2033 where deal_number = P_DEAL_NO
2034 and amount_type IN('DISC','PREMIUM')
2035 and date_type = 'COMENCE'
2036 and deal_type = P_DEAL_TYPE
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,
2046 transaction_date = P_DEAL_DATE,
2047 currency = P_CURRENCY,
2048 company_code = P_COMPANY_CODE,
2049 deal_subtype = P_DEAL_SUBTYPE,
2050 product_type = P_PRODUCT_TYPE,
2051 status_code = P_STATUS_CODE,
2052 dealer_code = P_DEALER_CODE,
2053 client_code = P_CLIENT_CODE,
2054 cparty_code = P_CPARTY_CODE,
2055 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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,
2065 currency = P_CURRENCY,
2066 cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
2067 company_code = P_COMPANY_CODE,
2068 account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
2069 cparty_account_no = decode(P_PREMIUM_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),''),
2070 deal_subtype = P_DEAL_SUBTYPE,
2071 product_type = P_PRODUCT_TYPE,
2072 status_code = P_STATUS_CODE,
2073 dealer_code = P_DEALER_CODE,
2074 client_code = P_CLIENT_CODE,
2075 cparty_code = P_CPARTY_CODE,
2076 action_code = P_PREMIUM_ACTION,
2077 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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,
2087 deal_subtype = P_DEAL_SUBTYPE,
2088 product_type = P_PRODUCT_TYPE,
2089 status_code = P_STATUS_CODE,
2090 dealer_code = P_DEALER_CODE,
2091 client_code = P_CLIENT_CODE,
2092 cparty_code = P_CPARTY_CODE,
2093 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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,
2103 deal_subtype = P_DEAL_SUBTYPE,
2104 product_type = P_PRODUCT_TYPE,
2105 status_code = P_STATUS_CODE,
2106 dealer_code = P_DEALER_CODE,
2107 client_code = P_CLIENT_CODE,
2108 cparty_code = P_CPARTY_CODE,
2109 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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,
2119 deal_subtype = P_DEAL_SUBTYPE,
2120 product_type = P_PRODUCT_TYPE,
2121 status_code = P_STATUS_CODE,
2122 dealer_code = P_DEALER_CODE,
2123 client_code = P_CLIENT_CODE,
2124 cparty_code = P_CPARTY_CODE,
2125 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2126 where deal_number = P_DEAL_NO
2127 and deal_type = 'IRO'
2128 and date_type = 'COMENCE';
2129 --
2130 if P_STATUS_CODE = 'EXERCISED' then
2131
2132 --Bug 3060946 Removed call to xtr_fps2_p.standing_settlement
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,
2142 currency = P_CURRENCY,
2143 company_code = P_COMPANY_CODE,
2144 deal_subtype = P_DEAL_SUBTYPE,
2145 product_type = P_PRODUCT_TYPE,
2146 status_code = P_STATUS_CODE,
2147 dealer_code = P_DEALER_CODE,
2148 client_code = P_CLIENT_CODE,
2149 cparty_code = P_CPARTY_CODE,
2150 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2151 limit_code = nvl(P_LIMIT_CODE,'NILL'),
2152 limit_party = P_CPARTY_CODE
2153 where deal_number = P_DEAL_NO
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 --
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),
2168 transaction_rate = P_SETTLE_RATE,
2169 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2170 transaction_date = P_DEAL_DATE,
2171 currency = P_CURRENCY,
2172 company_code = P_COMPANY_CODE,
2173 action_code = P_SETTLE_ACTION,
2174 --Bug 3060946 Removed assignment to cparty_account_no
2175 deal_subtype = P_DEAL_SUBTYPE,
2176 product_type = P_PRODUCT_TYPE,
2177 status_code = P_STATUS_CODE,
2178 dealer_code = P_DEALER_CODE,
2179 client_code = P_CLIENT_CODE,
2180 cparty_code = P_CPARTY_CODE,
2181 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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
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,
2196 transaction_date = P_DEAL_DATE,
2197 currency = P_CURRENCY,
2198 company_code = P_COMPANY_CODE,
2199 deal_subtype = P_DEAL_SUBTYPE,
2200 product_type = P_PRODUCT_TYPE,
2201 status_code = P_STATUS_CODE,
2202 dealer_code = P_DEALER_CODE,
2203 client_code = P_CLIENT_CODE,
2204 cparty_code = P_CPARTY_CODE,
2205 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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,
2215 currency = P_CURRENCY,
2216 cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
2217 company_code = P_COMPANY_CODE,
2218 account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
2219 cparty_account_no = decode(P_PREMIUM_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),''),
2220 deal_subtype = P_DEAL_SUBTYPE,
2221 product_type = P_PRODUCT_TYPE,
2222 status_code = P_STATUS_CODE,
2223 dealer_code = P_DEALER_CODE,
2224 client_code = P_CLIENT_CODE,
2225 cparty_code = P_CPARTY_CODE,
2226 action_code = P_PREMIUM_ACTION,
2227 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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,
2237 deal_subtype = P_DEAL_SUBTYPE,
2238 product_type = P_PRODUCT_TYPE,
2239 status_code = P_STATUS_CODE,
2240 dealer_code = P_DEALER_CODE,
2241 client_code = P_CLIENT_CODE,
2242 cparty_code = P_CPARTY_CODE,
2243 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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,
2253 deal_subtype = P_DEAL_SUBTYPE,
2254 product_type = P_PRODUCT_TYPE,
2255 status_code = P_STATUS_CODE,
2256 dealer_code = P_DEALER_CODE,
2257 client_code = P_CLIENT_CODE,
2258 cparty_code = P_CPARTY_CODE,
2259 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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,
2269 deal_subtype = P_DEAL_SUBTYPE,
2270 product_type = P_PRODUCT_TYPE,
2271 status_code = P_STATUS_CODE,
2272 dealer_code = P_DEALER_CODE,
2273 client_code = P_CLIENT_CODE,
2274 cparty_code = P_CPARTY_CODE,
2275 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2276 where deal_number = P_DEAL_NO
2277 and deal_type = 'BDO'
2278 and date_type = 'COMENCE';
2279 --
2280 if P_STATUS_CODE = 'EXERCISED' then
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,
2299 currency = P_CURRENCY,
2300 company_code = P_COMPANY_CODE,
2301 deal_subtype = P_DEAL_SUBTYPE,
2302 product_type = P_PRODUCT_TYPE,
2303 status_code = P_STATUS_CODE,
2304 dealer_code = P_DEALER_CODE,
2305 client_code = P_CLIENT_CODE,
2306 cparty_code = P_CPARTY_CODE,
2307 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2308 limit_code = nvl(P_LIMIT_CODE,'NILL'),
2309 limit_party = P_CPARTY_CODE
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),
2319 transaction_rate = P_SETTLE_RATE,
2320 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2321 transaction_date = P_DEAL_DATE,
2322 currency = P_CURRENCY,
2323 company_code = P_COMPANY_CODE,
2324 action_code = P_SETTLE_ACTION,
2325 cparty_account_no = nvl(v_settle_ac,nvl(v_cparty_account_no, cparty_account_no)), --Bug 2855642
2326 deal_subtype = P_DEAL_SUBTYPE,
2327 product_type = P_PRODUCT_TYPE,
2328 status_code = P_STATUS_CODE,
2329 dealer_code = P_DEALER_CODE,
2330 client_code = P_CLIENT_CODE,
2331 cparty_code = P_CPARTY_CODE,
2332 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2333 where deal_number = P_DEAL_NO
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,
2343 cparty_account_no,deal_subtype,product_type,status_code,
2344 dealer_code,client_code,cparty_code,settle,portfolio_code)
2345 values('BDO','SETTLE','SETTLE',
2346 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
2347 nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
2348 nvl(P_SETTLE_DATE,P_START_DATE),P_SETTLE_RATE,
2349 decode(P_SETTLE_ACTION,'PAY',-(1),1) * nvl(P_SETTLE_AMOUNT,0),
2350 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,P_SETTLE_ACTION,
2351 nvl(v_settle_ac,P_CPARTY_ACCOUNT_NO), --Bug 2855642
2352 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
2353 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
2354 end if;
2355 else -- AW Bug 894751
2356 -- JT bug 1312363 while validating after exercise the insert was giving
2357 -- duplicate row error a select is added to check whether the row is already
2358 -- existing or not. if no data found then insert the record
2359
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,
2369 status_code,dealer_code,client_code,cparty_code,settle,
2370 portfolio_code,QUICK_INPUT)
2371 values('BDO','N/A','SETTLE',
2372 P_DEAL_NO,1,trunc(sysdate),P_CURRENCY,
2373 0,0,trunc(sysdate),0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
2374 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
2375 P_CLIENT_CODE,P_CPARTY_CODE,'N',
2376 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
2377 end if;
2378 close CHK_BDO_SETTLE_ROWS;
2379 end if;
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,
2389 limit_code = nvl(P_LIMIT_CODE,'NILL'),
2390 transaction_date = P_DEAL_DATE,
2391 currency = P_CURRENCY_BUY,
2392 company_code = P_COMPANY_CODE,
2393 deal_subtype = P_DEAL_SUBTYPE,
2394 product_type = P_PRODUCT_TYPE,
2395 status_code = P_STATUS_CODE,
2396 dealer_code = P_DEALER_CODE,
2397 client_code = P_CLIENT_CODE,
2398 cparty_code = P_CPARTY_CODE,
2399 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2400 limit_party = P_CPARTY_CODE,
2401 contract_code = P_BOND_ISSUE
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,
2411 transaction_date = P_DEAL_DATE,
2412 currency = P_PREMIUM_CURRENCY,
2413 company_code = P_COMPANY_CODE,
2414 deal_subtype = P_DEAL_SUBTYPE,
2415 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2416 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
2417 product_type = P_PRODUCT_TYPE,
2418 status_code = P_STATUS_CODE,
2419 dealer_code = P_DEALER_CODE,
2420 client_code = P_CLIENT_CODE,
2421 cparty_code = P_CPARTY_CODE,
2422 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2423 contract_code = P_BOND_ISSUE
2424 where deal_number = P_DEAL_NO
2425 and deal_type = 'FUT'
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,
2435 cparty_account_no,deal_subtype,product_type,status_code,
2436 dealer_code,client_code,cparty_code,settle,
2437 portfolio_code,contract_code)
2438 values('FUT','PREMIUM','SETTLE',
2439 P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
2440 nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) ,nvl(P_PREMIUM_HCE_AMOUNT,0),
2441 P_PREMIUM_DATE,0,-(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
2442 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,'PAY',P_CPARTY_ACCOUNT_NO,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
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,
2452 deal_subtype = P_DEAL_SUBTYPE,
2453 product_type = P_PRODUCT_TYPE,
2454 status_code = P_STATUS_CODE,
2455 dealer_code = P_DEALER_CODE,
2456 client_code = P_CLIENT_CODE,
2457 cparty_code = P_CPARTY_CODE,
2458 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2459 where deal_number = P_DEAL_NO
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 =
2469 decode(P_SETTLE_ACTION,'PAY',(-1),1) * P_SETTLE_AMOUNT,
2470 transaction_rate = nvl(P_BASE_RATE,P_SETTLE_RATE),
2471 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2472 cparty_account_no = decode(P_SETTLE_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),NULL),
2473 action_code = P_SETTLE_ACTION,
2474 currency = P_PREMIUM_CURRENCY,
2475 company_code = P_COMPANY_CODE,
2476 deal_subtype = P_DEAL_SUBTYPE,
2477 product_type = P_PRODUCT_TYPE,
2478 status_code = P_STATUS_CODE,
2479 dealer_code = P_DEALER_CODE,
2480 client_code = P_CLIENT_CODE,
2481 cparty_code = P_CPARTY_CODE,
2482 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2483 contract_code = P_BOND_ISSUE
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,
2493 cparty_account_no,deal_subtype,product_type,status_code,
2494 dealer_code,client_code,cparty_code,settle,
2495 portfolio_code,contract_code)
2496 values('FUT','SETTLE','SETTLE',
2497 P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
2498 nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
2499 P_SETTLE_DATE,
2500 nvl(P_BASE_RATE,P_SETTLE_RATE),decode(P_SETTLE_ACTION,'PAY',-(1),1) * nvl(P_SETTLE_AMOUNT,0),
2501 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,P_SETTLE_ACTION,
2502 decode(P_SETTLE_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,NULL),
2503 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_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;
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,
2520 'FACEVAL',P_FACE_VALUE_AMOUNT,amount),
2521 hce_amount = decode(amount_type,'N/A',0,
2522 'FACEVAL',P_FACE_VALUE_HCE_AMOUNT,hce_amount),
2523 transaction_date = P_DEAL_DATE,
2524 transaction_rate = P_INTEREST_RATE,
2525 company_code = P_COMPANY_CODE,
2526 deal_subtype = P_DEAL_SUBTYPE,
2527 product_type = P_PRODUCT_TYPE,
2528 status_code = P_STATUS_CODE,
2529 dealer_code = P_DEALER_CODE,
2530 client_code = P_CLIENT_CODE,
2531 cparty_code = P_CPARTY_CODE,
2532 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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),
2542 currency = P_CURRENCY_BUY,
2543 transaction_date = P_DEAL_DATE,
2544 transaction_rate = P_INTEREST_RATE,
2545 company_code = P_COMPANY_CODE,
2546 deal_subtype = P_DEAL_SUBTYPE,
2547 product_type = P_PRODUCT_TYPE,
2548 status_code = P_STATUS_CODE,
2549 dealer_code = P_DEALER_CODE,
2550 client_code = P_CLIENT_CODE,
2551 cparty_code = P_CPARTY_CODE,
2552 action_code = P_PREMIUM_ACTION,
2553 account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
2554 cparty_account_no= decode(P_PREMIUM_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),''),
2555 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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,
2565 status_code = P_STATUS_CODE,
2566 transaction_date = P_DEAL_DATE,
2567 currency = P_CURRENCY,
2568 company_code = P_COMPANY_CODE,
2569 deal_subtype = P_DEAL_SUBTYPE,
2570 product_type = P_PRODUCT_TYPE,
2571 dealer_code = P_DEALER_CODE,
2572 client_code = P_CLIENT_CODE,
2573 cparty_code = P_CPARTY_CODE,
2574 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2575 where deal_number = P_DEAL_NO
2576 and deal_type = 'SWPTN'
2577 and date_type = 'EXPIRY';
2578 --
2579 if P_STATUS_CODE = 'EXERCISED' then
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,
2598 currency = P_CURRENCY,
2599 company_code = P_COMPANY_CODE,
2600 deal_subtype = P_DEAL_SUBTYPE,
2601 product_type = P_PRODUCT_TYPE,
2602 dealer_code = P_DEALER_CODE,
2603 client_code = P_CLIENT_CODE,
2604 cparty_code = P_CPARTY_CODE,
2605 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2606 limit_code = nvl(P_LIMIT_CODE,'NILL'),
2607 limit_party = P_CPARTY_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),
2617 transaction_rate = P_SETTLE_RATE,
2618 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2619 --Bug 3060946 Removed assignement to cparty_account_no
2620 status_code = P_STATUS_CODE,
2621 currency = P_CURRENCY,
2622 company_code = P_COMPANY_CODE,
2623 deal_subtype = P_DEAL_SUBTYPE,
2624 product_type = P_PRODUCT_TYPE,
2625 dealer_code = P_DEALER_CODE,
2626 client_code = P_CLIENT_CODE,
2627 cparty_code = P_CPARTY_CODE,
2628 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
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
2638 -- duplicate row error a select is added to check whether the row is already
2639 -- existing or not. if no data found then insert the record
2640
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,
2650 status_code,dealer_code,client_code,cparty_code,settle,
2651 portfolio_code,QUICK_INPUT)
2652 values('SWPTN','N/A','SETTLE',
2653 P_DEAL_NO,1,trunc(sysdate),P_CURRENCY,
2654 0,0,trunc(sysdate),0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
2655 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
2656 P_CLIENT_CODE,P_CPARTY_CODE,'N',
2657 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
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
2667 and date_type <> 'LIMIT';
2668 end if;
2669 end if;
2670 end if;
2671 end if;
2672 end if;
2673 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2674 xtr_debug_pkg.debug('After MAINTAIN_DDA_PROC on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
2675 END IF;
2676
2677 end MAINTAIN_DDA_PROC;
2678
2679
2680 END XTR_MAINTAIN_DDA_P;