[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.2 2008/08/06 10:44:10 srsampat 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 l_sysdate DATE;
224 l_user VARCHAR2(10);
225 --
226
227 begin
228
229 /* code below added by Ilavenil to fix the bug # 2065586
230 Let us assume, a deal is created using the deal input form. At the time of creation of deal the
231 account numbers are set. Say for example the account number is 'AAA'. Xtr_Deals, Xtr_Deal_Date_Amounts
232 will store the account number 'AAA'. User then goes to settlement form
233 and goes for a different account number for settlement. Say for example the account number is 'BBB'.
234 Xtr_Deal_Date_Amounts will now save 'BBB' instead of 'AAA' for the same deal no.
235
236 Case i : User then goes to the input form and queries the same deal number. In future the user may be allowed to update
237 the deal no for the deal. In that case, let us assume that the user modified the deal account number as
238 'CCC' instead of 'AAA'. Now, Xtr_Deal_Date_Amounts is to be set to 'CCC'.
239
240 Case ii : User goes to input form and queries the deal number. User updates a field other than account numbers.
241 The updation will be carried to Xtr_Deal_Date_Amounts by this procedure. Now, account number in
242 Xtr_Deal_Date_amounts is to be set as 'BBB' only. It should not be overwritten by 'AAA'.
243
244 So, we always check whether in Xtr_Deals, the old account number <> new account number. If yes, then
245 update Xtr_Deal_Date_Amounts to new account number of Xtr_Deals. If no, then leave Xtr_Deal_Date_Amounts
246 account number as is.
247
248 We basically do not want to overwrite an account number set at settlement by original account number.
249
250 */
251
252 If P_Settle_Account_No <> P_Old_Settle_Account_No then
253 v_settle_account_no := P_Settle_Account_No;
254 Else
255 v_settle_account_no := null;
256 End if;
257
258 If P_Premium_Account_No <> P_Old_Premium_Account_No then
259 v_premium_account_no := P_Premium_Account_No;
260 Else
261 v_Premium_account_no := null;
262 End if;
263
264 If P_maturity_Account_No <> P_Old_maturity_Account_No then
265 v_maturity_account_no := P_maturity_Account_No;
266 Else
267 v_maturity_account_no := null;
268 End if;
269
270 If P_Buy_Account_No <> P_Old_Buy_Account_No then
271 v_Buy_account_no := P_Buy_Account_No;
272 Else
273 v_Buy_account_no := null;
274 End if;
275
276 If P_Sell_Account_No <> P_Old_Sell_Account_No then
277 v_sell_account_no := P_Sell_Account_No;
278 Else
279 v_sell_account_no := null;
280 End if;
281
282 If P_CPARTY_ACCOUNT_NO <> P_OLD_CPARTY_ACCOUNT_NO then
283 v_cparty_account_no := P_CPARTY_ACCOUNT_NO;
284 Else
285 v_cparty_account_no := null;
286 End if;
287
288
289 -- note although there is sections below for BONDS we have excluded it here at a higher level
290 -- because code below will need testing
291 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
292 xtr_debug_pkg.debug('Before MAINTAIN_DDA_PROC on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
293 END IF;
294 if P_DEAL_TYPE <> 'BOND' then
295 l_sysdate := trunc(SYSDATE);
296 l_user := fnd_global.user_id;
297 --
298 open COM;
299 fetch COM INTO l_combin,base_ccy;
300 close COM;
301 --
302 --P_CPARTY_ACCOUNT := NULL;
303 --
304 --open CPARTY_ACCT_NOS;
305 -- fetch CPARTY_ACCT_NOS INTO P_CPARTY_ACCOUNT;
306 --close CPARTY_ACCT_NOS;
307 --
308 -- Rounding factors
309 open RND_FAC;
310 fetch RND_FAC INTO round_fac;
311 close RND_FAC;
312 round_fac :=nvl(round_fac,5);
313 --
314 -- Home currency rate
315 open HCE(P_CURRENCY);
316 fetch HCE INTO hce_rate;
317 close HCE;
318 --
319 hce_rate := nvl(hce_rate,1);
320 --
321 if P_DEAL_TYPE = 'BOND' then
322 open CHK_ISSUE;
323 fetch CHK_ISSUE INTO P_BOND_YR_BASIS,P_CALC_TYPE,P_RIC_CODE;
324 close CHK_ISSUE;
325 if P_COUPON_ACTION = 'CUM' then
326 P_INT_VALUE := nvl(round(P_ACCRUED_INTEREST_PRICE * P_MATURITY_AMOUNT / 100,nvl(round_fac,2)),0);
327 else
328 P_INT_VALUE := 0;
329 end if;
330 P_PREM_VALUE := nvl(round((P_CAPITAL_PRICE - 100) * P_MATURITY_AMOUNT / 100,nvl(round_fac,2)),0);
331 end if;
332 --
333 -- For insert of new Rows
334 if P_ACTION = 'INSERT' and P_STATUS_CODE = 'CURRENT' then
335 if P_DEAL_TYPE = 'FRA' then
336 insert into XTR_DEAL_DATE_AMOUNTS
337 (deal_type,amount_type,date_type,
338 deal_number,transaction_number,transaction_date,currency,
339 amount,hce_amount,amount_date,transaction_rate,
340 cashflow_amount,company_code,deal_subtype,
341 product_type,status_code,portfolio_code,
342 dealer_code,client_code,cparty_code,settle,limit_code,
343 limit_party,commence_date,quick_input)
344 values ('FRA','FACEVAL','COMENCE',
345 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
346 P_FACE_VALUE_AMOUNT,P_FACE_VALUE_HCE_AMOUNT,
347 P_START_DATE,P_INTEREST_RATE,0,
348 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
349 P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
350 P_CLIENT_CODE,P_CPARTY_CODE,'N',
351 NULL,P_CPARTY_CODE,P_START_DATE,P_QUICK_INPUT);
352 -- Dummy row for deal maturity date, this allows a journal action, accruals
353 insert into XTR_DEAL_DATE_AMOUNTS
354 (deal_type,amount_type,date_type,
355 deal_number,transaction_number,transaction_date,currency,
356 amount,hce_amount,amount_date,transaction_rate,
357 cashflow_amount,company_code,deal_subtype,product_type,
358 status_code,dealer_code,client_code,cparty_code,settle,
359 portfolio_code,commence_date,QUICK_INPUT)
360 values ('FRA','N/A','MATURE',
361 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
362 0,0,P_MATURITY_DATE,P_INTEREST_RATE,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
363 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
364 P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
365 -- Dummy row for deal dealt date, this allows a journal action
366 -- to be set up for a premium to occur on the deal date (date type DEALT)
367 insert into XTR_DEAL_DATE_AMOUNTS
368 (deal_type,amount_type,date_type,
369 deal_number,transaction_number,transaction_date,currency,
370 amount,hce_amount,amount_date,transaction_rate,
371 cashflow_amount,company_code,deal_subtype,product_type,
372 status_code,dealer_code,client_code,cparty_code,settle,
373 portfolio_code,commence_date,QUICK_INPUT)
374 values ('FRA','N/A','DEALT',
375 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
376 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
377 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
378 P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
379 -- Row for date_type of LIMIT so that the risk between the settlement and
380 -- exercise date is acknowledged
381 insert into XTR_DEAL_DATE_AMOUNTS
382 (deal_type,amount_type,date_type,
383 deal_number,transaction_number,transaction_date,currency,
384 amount,hce_amount,amount_date,
385 cashflow_amount,company_code,deal_subtype,product_type,
386 status_code,dealer_code,client_code,cparty_code,settle,
387 portfolio_code,commence_date,QUICK_INPUT,LIMIT_CODE,LIMIT_PARTY)
388 values ('FRA','N/A','LIMIT',
389 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
390 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
391 P_START_DATE,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 nvl(P_LIMIT_CODE,'NILL'),P_CPARTY_CODE);
395 if P_RATE_FIXING_DATE is not null then
396 -- Dummy row for deal rate set date
397 insert into XTR_DEAL_DATE_AMOUNTS
398 (deal_type,amount_type,date_type,
399 deal_number,transaction_number,transaction_date,currency,
400 amount,hce_amount,amount_date,transaction_rate,
401 cashflow_amount,company_code,deal_subtype,product_type,
402 status_code,dealer_code,client_code,cparty_code,settle,
403 portfolio_code,commence_date,QUICK_INPUT)
404 values ('FRA','FACEVAL','RATESET',
405 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
406 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
407 P_RATE_FIXING_DATE,P_SETTLE_RATE,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
408 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
409 P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
410 end if;
411
412 -- Dummy row for deal dealt date, this allows a journal action
413
414 elsif P_DEAL_TYPE = 'FXO' then
415
416 if nvl(P_KNOCK_TYPE,'O') = 'O' then
417 insert into XTR_DEAL_DATE_AMOUNTS
418 (deal_type,amount_type,date_type,
419 deal_number,transaction_number,transaction_date,currency,
420 amount,hce_amount,amount_date,transaction_rate,
421 cashflow_amount,company_code,deal_subtype,product_type,
422 status_code,cparty_code,settle,client_code,
423 limit_code,limit_party,portfolio_code,dealer_code,currency_combination,QUICK_INPUT)
424 values(P_DEAL_TYPE,'FXOBUY','EXPIRY',
425 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
426 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
427 P_EXPIRY_DATE,P_TRANSACTION_RATE,0,P_COMPANY_CODE,
428 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
429 P_CPARTY_CODE,'N',P_CLIENT_CODE,
430 decode(P_CURRENCY_BUY,substr(upper(l_combin),1,3),nvl(P_LIMIT_CODE,'NILL'),NULL),
431 decode(P_CURRENCY_BUY,substr(upper(l_combin),1,3),P_CPARTY_CODE,NULL),
432 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,l_combin,P_QUICK_INPUT);
433 --
434 insert into XTR_DEAL_DATE_AMOUNTS
435 (deal_type,amount_type,date_type,
436 deal_number,transaction_number,transaction_date,currency,
437 amount,hce_amount,amount_date,transaction_rate,
438 cashflow_amount,company_code,deal_subtype,product_type,
439 status_code,cparty_code,settle,
440 client_code,portfolio_code,limit_code,limit_party,dealer_code,currency_combination,QUICK_INPUT)
441 values(P_DEAL_TYPE,'FXOSELL','EXPIRY',P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
442 P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,P_EXPIRY_DATE,P_TRANSACTION_RATE,0,
443 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
444 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
445 decode(P_CURRENCY_SELL,substr(upper(l_combin),1,3),nvl(P_LIMIT_CODE,'NILL'),NULL),
446 decode(P_CURRENCY_SELL,substr(upper(l_combin),1,3),P_CPARTY_CODE,NULL),
447 P_DEALER_CODE,l_combin,P_QUICK_INPUT);
448 -- Value Date Amounts (To be used in cashflow Projections if indicated as reqd in deal input
449 if nvl(P_INSERT_FOR_CASHFLOW,'N') = 'Y' then
450 insert into XTR_DEAL_DATE_AMOUNTS
451 (deal_type,amount_type,date_type,
452 deal_number,transaction_number,transaction_date,currency,
453 amount,hce_amount,amount_date,transaction_rate,
454 cashflow_amount,company_code,account_no,
455 deal_subtype,product_type,status_code,cparty_code,settle,
456 client_code,portfolio_code,dealer_code,currency_combination,
457 exposure_ref_date,QUICK_INPUT)
458 values(P_DEAL_TYPE,'FXOBUY','VALUE',
459 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
460 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
461 P_VALUE_DATE,P_TRANSACTION_RATE,
462 decode(nvl(P_INSERT_FOR_CASHFLOW,'N'),'Y',P_BUY_AMOUNT,0),
463 P_COMPANY_CODE,P_BUY_ACCOUNT_NO,P_DEAL_SUBTYPE,
464 P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
465 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
466 P_DEALER_CODE,l_combin,P_START_DATE,P_QUICK_INPUT);
467 --
468 insert into XTR_DEAL_DATE_AMOUNTS
469 (deal_type,amount_type,date_type,
470 deal_number,transaction_number,transaction_date,currency,
471 amount,hce_amount,amount_date,transaction_rate,
472 cashflow_amount,company_code,account_no,
473 deal_subtype,product_type,status_code,cparty_code,settle,
474 client_code,portfolio_code,dealer_code,currency_combination,
475 exposure_ref_date,QUICK_INPUT)
476 values(P_DEAL_TYPE,'FXOSELL','VALUE',
477 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
478 P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,
479 P_VALUE_DATE,P_TRANSACTION_RATE,
480 decode(nvl(P_INSERT_FOR_CASHFLOW,'N'),'Y',(-1)*P_SELL_AMOUNT,0),
481 P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_DEAL_SUBTYPE,
482 P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
483 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
484 P_DEALER_CODE,l_combin,P_START_DATE,P_QUICK_INPUT);
485 end if;
486 --
487 if P_CURRENCY IS NOT NULL and P_PREMIUM_ACTION is NOT NULL and nvl(P_PREMIUM_AMOUNT,0) > 0 then
488 insert into XTR_DEAL_DATE_AMOUNTS
489 (deal_type,amount_type,date_type,
490 deal_number,transaction_number,transaction_date,currency,
491 amount,hce_amount,amount_date,transaction_rate,
492 cashflow_amount,company_code,account_no,action_code,
493 cparty_account_no,deal_subtype,product_type,status_code,
494 cparty_code,settle,client_code,portfolio_code,dealer_code,QUICK_INPUT)
495 values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
496 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
497 P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
498 P_PREMIUM_DATE,0,decode(P_PREMIUM_ACTION,
499 'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
500 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
501 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
502 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
503 'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
504 P_DEALER_CODE,P_QUICK_INPUT);
505 end if;
506 -- Dummy rows for deal dealt and commence dates, this allows a journal action
507 -- to be set up for a premium to occur on the deal date (date type DEALT) or
508 -- the commencment date of the option
509 insert into XTR_DEAL_DATE_AMOUNTS
510 (deal_type,amount_type,date_type,
511 deal_number,transaction_number,transaction_date,currency,
512 amount,hce_amount,amount_date,transaction_rate,
513 cashflow_amount,company_code,deal_subtype,product_type,
514 status_code,dealer_code,client_code,cparty_code,settle,
515 portfolio_code,QUICK_INPUT)
516 values('FXO','N/A','DEALT',
517 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
518 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
519 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
520 P_CLIENT_CODE,P_CPARTY_CODE,'N',
521 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
522 --
523 elsif P_KNOCK_TYPE = 'I' then
524 if P_CURRENCY IS NOT NULL and P_PREMIUM_ACTION is NOT NULL and nvl(P_PREMIUM_AMOUNT,0) > 0 then
525 -- ** WARNING ** a copy of the next insert also appears in UPDATING.
526 insert into XTR_DEAL_DATE_AMOUNTS
527 (deal_type,amount_type,date_type,
528 deal_number,transaction_number,transaction_date,currency,
529 amount,hce_amount,amount_date,transaction_rate,
530 cashflow_amount,company_code,account_no,action_code,
531 cparty_account_no,deal_subtype,product_type,status_code,
532 cparty_code,settle,client_code,portfolio_code,dealer_code,QUICK_INPUT)
533 values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
534 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
535 P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
536 P_PREMIUM_DATE,0,decode(P_PREMIUM_ACTION,
537 'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
538 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
539 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
540 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
541 'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,P_QUICK_INPUT);
542 end if;
543 -- Dummy rows for deal dealt and commence dates, this allows a journal action
544 -- to be set up for a premium to occur on the deal date (date type DEALT) or
545 -- the commencment date of the option
546 insert into XTR_DEAL_DATE_AMOUNTS
547 (deal_type,amount_type,date_type,
548 deal_number,transaction_number,transaction_date,currency,
549 amount,hce_amount,amount_date,transaction_rate,
550 cashflow_amount,company_code,deal_subtype,product_type,
551 status_code,dealer_code,client_code,cparty_code,settle,
552 portfolio_code,QUICK_INPUT)
553 values('FXO','N/A','DEALT',
554 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
555 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
556 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
557 P_CLIENT_CODE,P_CPARTY_CODE,'N',
558 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
559 --
560 end if;
561 elsif P_DEAL_TYPE = 'FX' then
562 insert into XTR_DEAL_DATE_AMOUNTS
563 (deal_type,amount_type,date_type,
564 deal_number,transaction_number,transaction_date,
565 currency,amount,hce_amount,amount_date,
566 transaction_rate,cashflow_amount,company_code,
567 account_no,deal_subtype,product_type,status_code,
568 dealer_code,cparty_code,client_code,portfolio_code,
569 settle,limit_code,limit_party,currency_combination,QUICK_INPUT)
570 values(P_DEAL_TYPE,'BUY','VALUE',
571 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
572 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,P_VALUE_DATE,
573 P_TRANSACTION_RATE,decode(P_SWAP_DEPO_FLAG,'B',0,P_BUY_AMOUNT),P_COMPANY_CODE,
574 P_BUY_ACCOUNT_NO,P_DEAL_SUBTYPE,nvl(P_PRODUCT_TYPE,'NOT APPLIC'),
575 P_STATUS_CODE,P_DEALER_CODE,P_CPARTY_CODE,
576 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',
577 decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
578 decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE),l_combin,P_QUICK_INPUT);
579 --
580 insert into XTR_DEAL_DATE_AMOUNTS
581 (deal_type,amount_type,date_type,
582 deal_number,transaction_number,transaction_date,
583 currency,amount,hce_amount,amount_date,
584 transaction_rate,cashflow_amount,company_code,
585 account_no,cparty_account_no,deal_subtype,
586 product_type,status_code,dealer_code,cparty_code,
587 client_code,portfolio_code,settle,limit_code,limit_party,currency_combination,QUICK_INPUT)
588 values(P_DEAL_TYPE,'SELL','VALUE',
589 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
590 P_SELL_AMOUNT,nvl(P_BUY_HCE_AMOUNT,0),
591 P_VALUE_DATE,P_TRANSACTION_RATE,decode(P_SWAP_DEPO_FLAG,'S',0,(-1) * P_SELL_AMOUNT),
592 P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
593 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
594 P_DEALER_CODE,P_CPARTY_CODE,P_CLIENT_CODE,
595 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',
596 decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
597 decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE),l_combin,P_QUICK_INPUT);
598 --
599 insert into XTR_DEAL_DATE_AMOUNTS
600 (deal_type,amount_type,date_type,
601 deal_number,transaction_number,transaction_date,
602 currency,amount,hce_amount,amount_date,
603 transaction_rate,cashflow_amount,company_code,
604 account_no,cparty_account_no,deal_subtype,
605 product_type,status_code,dealer_code,cparty_code,
606 client_code,portfolio_code,settle,currency_combination,QUICK_INPUT)
607 values(P_DEAL_TYPE,'N/A','DEALT',
608 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
609 P_SELL_AMOUNT,nvl(P_SELL_HCE_AMOUNT,0),
610 P_DEAL_DATE,P_TRANSACTION_RATE,0,
611 P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
612 P_DEAL_SUBTYPE,nvl(P_PRODUCT_TYPE,'NOT APPLIC'),P_STATUS_CODE,
613 P_DEALER_CODE,P_CPARTY_CODE,P_CLIENT_CODE,
614 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),'N',l_combin,P_QUICK_INPUT);
615 --
616 elsif P_DEAL_TYPE = 'NI' then
617
618 insert into XTR_DEAL_DATE_AMOUNTS
619 (deal_type,amount_type,date_type,
620 deal_number,transaction_number,transaction_date,currency,
621 amount,hce_amount,amount_date,transaction_rate,
622 cashflow_amount,company_code,account_no,
623 cparty_account_no,status_code,portfolio_code,dealer_code,
624 client_code,deal_subtype,cparty_code,settle,product_type,
625 commence_date,quick_input,limit_code)
626 values('NI','COMENCE','COMENCE',
627 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
628 P_START_AMOUNT,P_START_HCE_AMOUNT,P_START_DATE,P_INTEREST_RATE,
629 decode(nvl(P_KNOCK_TYPE,'N'),'N',decode(P_DEAL_SUBTYPE,'BUY',-1,'COVER',-1,1)*P_START_AMOUNT,0), -- Bug 3776211
630 P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,
631 P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
632 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
633 P_CPARTY_CODE,'N',P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT,
634 decode(P_RISKPARTY_LIMIT_CODE, null,
635 decode(P_DEAL_SUBTYPE,'SELL','NILL',NULL),NULL)); -- jhung bug 1477157
636 -- decode(P_DEAL_SUBTYPE,'ISSUE',NULL,'NILL'),NULL)); -- AW 9/24/99 Bug 996572
637
638 -----------------------------------------------------------------------------
639 -- Initial Maturity Face Value Details , before for sell status_code='CLOSED'
640 -----------------------------------------------------------------------------
641 insert into XTR_DEAL_DATE_AMOUNTS
642 (deal_type,amount_type,date_type,
643 deal_number,transaction_number,transaction_date,currency,
644 amount,hce_amount,amount_date,transaction_rate,
645 cashflow_amount,company_code,account_no,
646 cparty_account_no,status_code,portfolio_code,dealer_code,
647 client_code,deal_subtype,cparty_code,settle,product_type,commence_date,quick_input)
648 values('NI','INTL_FV',decode(P_DEAL_SUBTYPE,'SELL','COMENCE','COVER','COMENCE','MATURE'),
649 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
650 P_MATURITY_AMOUNT,P_MATURITY_HCE_AMOUNT,
651 decode(P_DEAL_SUBTYPE,'SELL',P_START_DATE,'COVER',P_START_DATE,P_MATURITY_DATE),
652 p_INTEREST_RATE,0,
653 P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
654 P_STATUS_CODE,P_PORTFOLIO_CODE,
655 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
656 P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT);
657 --
658 if P_DEAL_SUBTYPE in ('BUY','SHORT','ISSUE') and
659 P_MATURITY_BALANCE_AMOUNT is NOT NULL then /* in Pro0340 this column is null */
660 -----------------------------
661 -- BALance Face Value Details
662 -----------------------------
663 insert into XTR_DEAL_DATE_AMOUNTS
664 (deal_type,amount_type,date_type,
665 deal_number,transaction_number,transaction_date,currency,
666 amount,hce_amount,amount_date,transaction_rate,
667 cashflow_amount,company_code,account_no,
668 cparty_account_no,status_code,portfolio_code,dealer_code,
669 client_code,deal_subtype,cparty_code,settle,product_type,
670 limit_code,limit_party,commence_date,quick_input)
671 values('NI','BAL_FV','MATURE',
672 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
673 P_MATURITY_BALANCE_AMOUNT,
674 P_MATURITY_BALANCE_HCE_AMOUNT,P_MATURITY_DATE,
675 -- P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,-1) *
676 P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,'SELL',0,-1) *
677 P_MATURITY_BALANCE_AMOUNT,P_COMPANY_CODE,
678 P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
679 P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
680 P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
681 P_PRODUCT_TYPE,
682 nvl(P_RISKPARTY_LIMIT_CODE, decode(P_LIMIT_CODE, null, decode(P_DEAL_SUBTYPE,
683 'BUY','NILL','ISSUE', 'NILL', NULL), P_LIMIT_CODE)),
684 nvl(P_RISKPARTY_CODE,decode(P_DEAL_SUBTYPE,'BUY',P_CPARTY_CODE,null)), --jhung bug 1477157
685 P_START_DATE,P_QUICK_INPUT);
686 end if;
687
688 ---------------------
689 -- INTerest Details
690 ---------------------
691 insert into XTR_DEAL_DATE_AMOUNTS
692 (deal_type,amount_type,date_type,deal_number,
693 transaction_number,transaction_date,currency,
694 amount,hce_amount,amount_date,transaction_rate,
695 cashflow_amount,company_code,portfolio_code,status_code,
696 dealer_code,client_code,deal_subtype,cparty_code,
697 settle,product_type,commence_date,quick_input)
698 values('NI','INT','COMENCE',
699 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
700 P_INTEREST_AMOUNT,P_INTEREST_HCE_AMOUNT,
701 P_START_DATE,P_INTEREST_RATE,0,
702 P_COMPANY_CODE,P_PORTFOLIO_CODE,P_STATUS_CODE,P_DEALER_CODE,
703 P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
704 P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT);
705
706 --------------------------
707 -- Deal DEALT on Details
708 --------------------------
709 insert into XTR_DEAL_DATE_AMOUNTS
710 (deal_type,amount_type,date_type,
711 deal_number,transaction_number,transaction_date,currency,
712 amount,hce_amount,amount_date,transaction_rate,
713 cashflow_amount,company_code,
714 deal_subtype,product_type,status_code,dealer_code,
715 client_code,cparty_code,portfolio_code,commence_date,quick_input)
716 values('NI','N/A','DEALT',
717 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
718 P_START_AMOUNT,P_START_HCE_AMOUNT,P_DEAL_DATE,0,0,P_COMPANY_CODE,
719 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
720 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
721 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_START_DATE,P_QUICK_INPUT);
722
723 ------------
724 -- NEW NI --
725 ---------------------------------
726 -- New Date Types/Amount Types --
727 ---------------------------------
728 if P_DEAL_SUBTYPE in ('BUY','SHORT') then
729 insert into XTR_DEAL_DATE_AMOUNTS
730 (deal_type,amount_type,date_type,
731 deal_number,transaction_number,transaction_date,currency,
732 amount,hce_amount,amount_date,transaction_rate,
733 cashflow_amount,company_code,account_no,
734 cparty_account_no,status_code,portfolio_code,dealer_code,
735 client_code,deal_subtype,cparty_code,settle,product_type,
736 commence_date,quick_input)
737 values('NI','BALCOM','MATURE',
738 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
739 P_START_AMOUNT,
740 P_START_HCE_AMOUNT,P_MATURITY_DATE,
741 P_INTEREST_RATE, 0,
742 P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,
743 P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
744 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
745 P_CPARTY_CODE,'N',P_PRODUCT_TYPE,P_START_DATE,P_QUICK_INPUT);
746 end if;
747
748 elsif P_DEAL_TYPE = 'BOND' then
749 --
750 if P_DEAL_SUBTYPE <> 'SELL' then
751 if nvl(P_CUM_COUPON_DATE,P_NEXT_COUPON_DATE) is NOT NULL then
752 if P_COUPON_ACTION = 'CUM' then
753 coupon_date := P_CUM_COUPON_DATE;
754 else
755 coupon_date := P_NEXT_COUPON_DATE;
756 end if;
757 l_start_date := add_months(coupon_date,-(12 / P_FREQUENCY));
758 LOOP
759 /* This needs to be replaced with increment method for transaction number.
760 open TRANS_NUM;
761 fetch TRANS_NUM INTO l_trans_num;
762 close TRANS_NUM;
763 */
764 if upper(P_CALC_TYPE) = 'VARIABLE COUPON' then
765 coupon := round(P_MATURITY_AMOUNT * P_COUPON_RATE * (coupon_date -
766 l_start_date) / (nvl(P_BOND_YR_BASIS,365)
767 * 100),round_fac);
768 else
769 coupon := round(P_MATURITY_AMOUNT * (P_COUPON_RATE / 100) /
770 nvl(P_FREQUENCY,2),round_fac);
771 end if;
772 coupon_hce := round(coupon / hce_rate,round_fac);
773 --
774 insert into XTR_ROLLOVER_TRANSACTIONS
775 (deal_number,transaction_number,deal_type,start_date,no_of_days,
776 maturity_date,interest_rate,interest,interest_hce,deal_subtype,
777 product_type,company_code,cparty_code,client_code,currency,
778 deal_date,status_code,created_by,created_on,settle_date)
779 values(P_DEAL_NO,l_trans_num,'BOND',l_start_date,(coupon_date -
780 l_start_date),coupon_date,P_COUPON_RATE,coupon,coupon_hce,
781 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_COMPANY_CODE,P_CPARTY_CODE,
782 P_CLIENT_CODE,P_CURRENCY,P_DEAL_DATE,'CURRENT',L_USER,
783 L_SYSDATE,coupon_date);
784 --
785 insert into XTR_DEAL_DATE_AMOUNTS
786 (deal_type,amount_type,date_type,
787 deal_number,transaction_number,transaction_date,currency,
788 amount,hce_amount,amount_date,transaction_rate,
789 cashflow_amount,company_code,account_no,status_code,portfolio_code,
790 dealer_code,client_code,deal_subtype,cparty_code,settle,product_type)
791 values
792 ('BOND','COUPON','COUPON',P_DEAL_NO,l_trans_num,P_DEAL_DATE,
793 P_CURRENCY,coupon,coupon_hce,coupon_date,P_COUPON_RATE,
794 decode(P_DEAL_SUBTYPE,'BUY',1,-1) * coupon,P_COMPANY_CODE,
795 P_MATURITY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
796 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,P_ACCEPTOR_CODE,'N',
797 P_PRODUCT_TYPE);
798 --
799 l_start_date := coupon_date;
800 coupon_date := add_months(coupon_date,(12 / P_FREQUENCY));
801 EXIT WHEN coupon_date > P_MATURITY_DATE;
802 END LOOP;
803 end if;
804 end if;
805 --
806 insert into XTR_DEAL_DATE_AMOUNTS
807 (deal_type,amount_type,date_type,
808 deal_number,transaction_number,transaction_date,currency,
809 amount,hce_amount,amount_date,transaction_rate,
810 cashflow_amount,company_code,account_no,
811 cparty_account_no,status_code,portfolio_code,dealer_code,
812 client_code,deal_subtype,cparty_code,settle,product_type)
813 values('BOND','COMENCE','COMENCE',
814 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
815 P_START_AMOUNT,P_START_HCE_AMOUNT,
816 P_START_DATE,P_INTEREST_RATE,
817 decode(P_DEAL_SUBTYPE,'BUY',-1,1) * P_START_AMOUNT,
818 P_COMPANY_CODE,P_MATURITY_ACCOUNT_NO,
819 P_CPARTY_ACCOUNT_NO,P_STATUS_CODE,P_PORTFOLIO_CODE,
820 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
821 P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
822 --
823 insert into XTR_DEAL_DATE_AMOUNTS
824 (deal_type,amount_type,date_type,
825 deal_number,transaction_number,transaction_date,currency,
826 amount,hce_amount,amount_date,transaction_rate,
827 cashflow_amount,company_code,account_no,
828 cparty_account_no,status_code,portfolio_code,dealer_code,
829 client_code,deal_subtype,cparty_code,settle,product_type,
830 limit_code,limit_party)
831 values('BOND','INTL_FV','MATURE',
832 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
833 P_MATURITY_AMOUNT,P_MATURITY_HCE_AMOUNT,
834 P_MATURITY_DATE,P_INTEREST_RATE,
835 decode(P_DEAL_SUBTYPE,'BUY',1,-1) *
836 P_MATURITY_AMOUNT,P_COMPANY_CODE,
837 P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
838 P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
839 P_CLIENT_CODE,P_DEAL_SUBTYPE,P_ACCEPTOR_CODE,'N',
840 P_PRODUCT_TYPE,nvl(P_LIMIT_CODE,'NILL'),P_ACCEPTOR_CODE);
841 -- if P_COUPON_ACTION = 'CUM' then
842 -- Accrued Int values
843 insert into XTR_DEAL_DATE_AMOUNTS
844 (deal_type,amount_type,date_type,
845 deal_number,transaction_number,transaction_date,currency,
846 amount,hce_amount,amount_date,transaction_rate,
847 cashflow_amount,company_code,status_code,
848 portfolio_code,dealer_code,client_code,deal_subtype,
849 cparty_code,settle,product_type)
850 values('BOND','INT','COMENCE',
851 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
852 P_INT_VALUE,round(P_INT_VALUE/hce_rate,round_fac),P_START_DATE,
853 P_INTEREST_RATE,0,P_COMPANY_CODE,P_STATUS_CODE,
854 P_PORTFOLIO_CODE,P_DEALER_CODE,P_CLIENT_CODE,
855 P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
856 --- end if;
857 -- Premium / Discount Values
858 insert into XTR_DEAL_DATE_AMOUNTS
859 (deal_type,amount_type,date_type,
860 deal_number,transaction_number,transaction_date,currency,
861 amount,hce_amount,amount_date,transaction_rate,
862 cashflow_amount,company_code,status_code,
863 portfolio_code,dealer_code,client_code,deal_subtype,
864 cparty_code,settle,product_type)
865 values('BOND',decode(sign(P_PREM_VALUE),-1,'DISC','PREMIUM'),
866 'COMENCE',P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
867 abs(P_PREM_VALUE),abs(round(P_PREM_VALUE/hce_rate,round_fac)),
868 P_START_DATE,P_INTEREST_RATE,0,
869 P_COMPANY_CODE,P_STATUS_CODE,P_PORTFOLIO_CODE,
870 P_DEALER_CODE,P_CLIENT_CODE,P_DEAL_SUBTYPE,
871 P_CPARTY_CODE,'N',P_PRODUCT_TYPE);
872 elsif P_DEAL_TYPE = 'IRO' then
873 -- Limit Row / Limit Amount Details
874 insert into XTR_DEAL_DATE_AMOUNTS
875 (deal_type,amount_type,date_type,
876 deal_number,transaction_number,transaction_date,currency,
877 amount,hce_amount,amount_date,
878 cashflow_amount,company_code,deal_subtype,product_type,
879 status_code,dealer_code,client_code,cparty_code,settle,
880 limit_code,limit_party,portfolio_code)
881 values('IRO','N/A','LIMIT',
882 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
883 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
884 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
885 P_EXPIRY_DATE,0,
886 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
887 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
888 P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
889 P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
890 -- Expiry Date / Face Value Amount Details
891 insert into XTR_DEAL_DATE_AMOUNTS
892 (deal_type,amount_type,date_type,
893 deal_number,transaction_number,transaction_date,currency,
894 amount,hce_amount,amount_date,transaction_rate,
895 cashflow_amount,company_code,deal_subtype,product_type,
896 status_code,dealer_code,client_code,cparty_code,settle,
897 portfolio_code)
898 values('IRO','FACEVAL','EXPIRY',
899 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
900 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
901 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
902 P_EXPIRY_DATE,P_INTEREST_RATE,0,
903 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
904 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
905 P_CPARTY_CODE,'N',
906 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
907 -- Premium Date / Premium Amount Details
908 insert into XTR_DEAL_DATE_AMOUNTS
909 (deal_type,amount_type,date_type,
910 deal_number,transaction_number,transaction_date,currency,
911 amount,hce_amount,amount_date,transaction_rate,
912 cashflow_amount,
913 company_code,account_no,action_code,cparty_account_no,
914 deal_subtype,product_type,status_code,dealer_code,
915 client_code,cparty_code,settle,portfolio_code)
916 values('IRO','PREMIUM','PREMIUM',
917 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
918 nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
919 nvl(P_PREMIUM_DATE,P_START_DATE),0,
920 decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
921 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
922 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,''),
923 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
924 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
925 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
926 -- Deal Dealt on Details
927 insert into XTR_DEAL_DATE_AMOUNTS
928 (deal_type,amount_type,date_type,
929 deal_number,transaction_number,transaction_date,currency,
930 amount,hce_amount,amount_date,transaction_rate,
931 cashflow_amount,company_code,
932 deal_subtype,product_type,status_code,dealer_code,
933 client_code,cparty_code,portfolio_code)
934 values('IRO','FACEVAL','DEALT',
935 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
936 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
937 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
938 P_DEAL_DATE,0,0,P_COMPANY_CODE,
939 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
940 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
941 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
942 -- AW Bug 894751 American Option
943 /*
944 values('IRO','N/A','DEALT',
945 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
946 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,
947 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
948 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
949 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
950 */
951 -- Physical Commencement Date Details
952 insert into XTR_DEAL_DATE_AMOUNTS
953 (deal_type,amount_type,date_type,
954 deal_number,transaction_number,transaction_date,currency,
955 amount,hce_amount,amount_date,transaction_rate,
956 cashflow_amount,company_code,
957 deal_subtype,product_type,status_code,dealer_code,
958 client_code,cparty_code,portfolio_code)
959 values('IRO','FACEVAL','COMENCE',
960 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
961 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
962 0,P_START_DATE,0,0,P_COMPANY_CODE,
963 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
964 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
965 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
966 -- Physical Maturity Date Details
967 insert into XTR_DEAL_DATE_AMOUNTS
968 (deal_type,amount_type,date_type,
969 deal_number,transaction_number,transaction_date,currency,
970 amount,hce_amount,amount_date,transaction_rate,
971 cashflow_amount,company_code,
972 deal_subtype,product_type,status_code,dealer_code,
973 client_code,cparty_code,portfolio_code)
974 values('IRO','N/A','MATURE',
975 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
976 0,0,P_MATURITY_DATE,0,0,P_COMPANY_CODE,
977 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
978 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
979 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
980 elsif P_DEAL_TYPE = 'BDO' then
981 -- Limit Row / Limit Amount Details
982 insert into XTR_DEAL_DATE_AMOUNTS
983 (deal_type,amount_type,date_type,
984 deal_number,transaction_number,transaction_date,currency,
985 amount,hce_amount,amount_date,
986 cashflow_amount,company_code,deal_subtype,product_type,
987 status_code,dealer_code,client_code,cparty_code,settle,
988 limit_code,limit_party,portfolio_code)
989 values('BDO','N/A','LIMIT',
990 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
991 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
992 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
993 P_EXPIRY_DATE,0,
994 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
995 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
996 P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
997 P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
998 -- Expiry Date / Face Value Amount Details
999 insert into XTR_DEAL_DATE_AMOUNTS
1000 (deal_type,amount_type,date_type,
1001 deal_number,transaction_number,transaction_date,currency,
1002 amount,hce_amount,amount_date,transaction_rate,
1003 cashflow_amount,company_code,deal_subtype,product_type,
1004 status_code,dealer_code,client_code,cparty_code,settle,
1005 portfolio_code)
1006 values('BDO','FACEVAL','EXPIRY',
1007 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1008 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
1009 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
1010 P_EXPIRY_DATE,P_INTEREST_RATE,0,
1011 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1012 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1013 P_CPARTY_CODE,'N',
1014 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1015 -- Premium Date / Premium Amount Details
1016 insert into XTR_DEAL_DATE_AMOUNTS
1017 (deal_type,amount_type,date_type,
1018 deal_number,transaction_number,transaction_date,currency,
1019 amount,hce_amount,amount_date,transaction_rate,
1020 cashflow_amount,
1021 company_code,account_no,action_code,cparty_account_no,
1022 deal_subtype,product_type,status_code,dealer_code,
1023 client_code,cparty_code,settle,portfolio_code)
1024 values('BDO','PREMIUM','PREMIUM',
1025 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1026 nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
1027 nvl(P_PREMIUM_DATE,P_START_DATE),0,
1028 decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
1029 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
1030 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,''),
1031 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1032 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
1033 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1034 -- Deal Dealt on Details
1035 insert into XTR_DEAL_DATE_AMOUNTS
1036 (deal_type,amount_type,date_type,
1037 deal_number,transaction_number,transaction_date,currency,
1038 amount,hce_amount,amount_date,transaction_rate,
1039 cashflow_amount,company_code,
1040 deal_subtype,product_type,status_code,dealer_code,
1041 client_code,cparty_code,portfolio_code)
1042 values('BDO','FACEVAL','DEALT',
1043 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1044 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
1045 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
1046 P_DEAL_DATE,0,0,P_COMPANY_CODE,
1047 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1048 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1049 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1050 -- AW Bug 894751
1051 /*
1052 values('BDO','N/A','DEALT',
1053 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1054 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,
1055 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1056 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1057 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1058 */
1059 -- Physical Commencement Date Details
1060 insert into XTR_DEAL_DATE_AMOUNTS
1061 (deal_type,amount_type,date_type,
1062 deal_number,transaction_number,transaction_date,currency,
1063 amount,hce_amount,amount_date,transaction_rate,
1064 cashflow_amount,company_code,
1065 deal_subtype,product_type,status_code,dealer_code,
1066 client_code,cparty_code,portfolio_code)
1067 values('BDO','FACEVAL','COMENCE',
1068 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1069 nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
1070 nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
1071 P_START_DATE,0,0,P_COMPANY_CODE,
1072 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1073 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1074 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1075 -- Physical Maturity Date Details
1076 insert into XTR_DEAL_DATE_AMOUNTS
1077 (deal_type,amount_type,date_type,
1078 deal_number,transaction_number,transaction_date,currency,
1079 amount,hce_amount,amount_date,transaction_rate,
1080 cashflow_amount,company_code,
1081 deal_subtype,product_type,status_code,dealer_code,
1082 client_code,cparty_code,portfolio_code)
1083 values('BDO','N/A','MATURE',
1084 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1085 0,0,P_MATURITY_DATE,0,0,P_COMPANY_CODE,
1086 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1087 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,
1088 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1089 elsif P_DEAL_TYPE = 'FUT' then
1090 -- Face Value Amount
1091 insert into XTR_DEAL_DATE_AMOUNTS
1092 (deal_type,amount_type,date_type,
1093 deal_number,transaction_number,transaction_date,currency,
1094 amount,hce_amount,amount_date,transaction_rate,
1095 cashflow_amount,company_code,deal_subtype,product_type,
1096 status_code,dealer_code,client_code,cparty_code,settle,
1097 limit_code,limit_party,portfolio_code,contract_code)
1098 values('FUT','FACEVAL','EXPIRY',
1099 P_DEAL_NO,1,P_DEAL_DATE,
1100 nvl(P_CURRENCY_BUY,'N/A'),
1101 nvl(P_START_AMOUNT,0),nvl(P_START_HCE_AMOUNT,
1102 P_START_AMOUNT),P_EXPIRY_DATE,nvl(P_TRANSACTION_RATE,P_CONTRACT_RATE),0,
1103 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1104 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1105 P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
1106 P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
1107 if nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) > 0 then
1108 -- Settlement Amount
1109 insert into XTR_DEAL_DATE_AMOUNTS
1110 (deal_type,amount_type,date_type,
1111 deal_number,transaction_number,transaction_date,currency,
1112 amount,hce_amount,amount_date,transaction_rate,
1113 cashflow_amount,company_code,account_no,action_code,
1114 cparty_account_no,deal_subtype,product_type,status_code,
1115 dealer_code,client_code,cparty_code,settle,
1116 portfolio_code,contract_code)
1117 values('FUT','PREMIUM','SETTLE',
1118 P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
1119 nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) ,nvl(P_PREMIUM_HCE_AMOUNT,0),
1120 P_PREMIUM_DATE,0,-(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
1121 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,'PAY',
1122 P_CPARTY_ACCOUNT_NO,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1123 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1124 P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
1125 end if;
1126 -- Dummy row for deal dealt date, this allows a journal action
1127 -- to be set up for a premium to occur on the deal date (date type DEALT)
1128 insert into XTR_DEAL_DATE_AMOUNTS
1129 (deal_type,amount_type,date_type,
1130 deal_number,transaction_number,transaction_date,currency,
1131 amount,hce_amount,amount_date,transaction_rate,
1132 cashflow_amount,company_code,deal_subtype,product_type,
1133 status_code,dealer_code,client_code,cparty_code,settle,
1134 portfolio_code)
1135 values('FUT','DEALT','DEALT',
1136 P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
1137 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
1138 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
1139 P_CLIENT_CODE,P_CPARTY_CODE,'N',
1140 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1141 elsif P_DEAL_TYPE = 'SWPTN' then
1142 -- Limit Row / Limit Details
1143 insert into XTR_DEAL_DATE_AMOUNTS
1144 (deal_type,amount_type,date_type,
1145 deal_number,transaction_number,transaction_date,currency,
1146 amount,hce_amount,amount_date,
1147 cashflow_amount,company_code,deal_subtype,product_type,
1148 status_code,dealer_code,client_code,cparty_code,settle,
1149 limit_code,limit_party,portfolio_code)
1150 values('SWPTN','N/A','LIMIT',
1151 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1152 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
1153 P_EXPIRY_DATE,0,
1154 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1155 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1156 P_CPARTY_CODE,'N',nvl(P_LIMIT_CODE,'NILL'),
1157 P_CPARTY_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1158 -- Face Value Amount / Expiry Date Details
1159 insert into XTR_DEAL_DATE_AMOUNTS
1160 (deal_type,amount_type,date_type,
1161 deal_number,transaction_number,transaction_date,currency,
1162 amount,hce_amount,amount_date,transaction_rate,
1163 cashflow_amount,company_code,deal_subtype,product_type,
1164 status_code,dealer_code,client_code,cparty_code,settle,
1165 portfolio_code)
1166 values('SWPTN','FACEVAL','EXPIRY',
1167 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1168 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
1169 P_EXPIRY_DATE,P_INTEREST_RATE,0,
1170 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1171 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1172 P_CPARTY_CODE,'N',
1173 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1174 -- Premium Details
1175 insert into XTR_DEAL_DATE_AMOUNTS
1176 (deal_type,amount_type,date_type,
1177 deal_number,transaction_number,transaction_date,currency,
1178 amount,hce_amount,amount_date,transaction_rate,
1179 cashflow_amount,company_code,account_no,action_code,cparty_account_no,
1180 deal_subtype,product_type,status_code,dealer_code,
1181 client_code,cparty_code,settle,portfolio_code)
1182 values('SWPTN','PREMIUM','PREMIUM',
1183 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1184 nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
1185 nvl(P_PREMIUM_DATE,P_START_DATE),0,
1186 decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
1187 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,P_PREMIUM_ACTION,
1188 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,''),
1189 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1190 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
1191 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1192 -- Dummy row for deal dealt date, this allows a journal action
1193 -- to be set up for a premium to occur on the deal date (date type DEALT)
1194 insert into XTR_DEAL_DATE_AMOUNTS
1195 (deal_type,amount_type,date_type,
1196 deal_number,transaction_number,transaction_date,currency,
1197 amount,hce_amount,amount_date,transaction_rate,
1198 cashflow_amount,company_code,deal_subtype,product_type,
1199 status_code,dealer_code,client_code,cparty_code,settle,portfolio_code)
1200 values('SWPTN','FACEVAL','DEALT',
1201 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1202 nvl(P_PREMIUM_AMOUNT,0),nvl(P_PREMIUM_HCE_AMOUNT,0),
1203 P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
1204 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
1205 P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1206 -- AW Bug 894751
1207 /*
1208 values('SWPTN','N/A','DEALT',
1209 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1210 0,0,P_DEAL_DATE,0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
1211 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
1212 P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1213 */
1214 -- Settlement Details
1215 if P_SETTLE_DATE is NOT NULL then
1216 xtr_fps2_p.standing_settlements(P_CPARTY_CODE,P_CURRENCY,'SWPTN',
1217 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,'SETTLE',
1218 v_settle_ref,v_settle_ac);
1219 insert into XTR_DEAL_DATE_AMOUNTS
1220 (deal_type,amount_type,date_type,
1221 deal_number,transaction_number,transaction_date,currency,
1222 amount,hce_amount,amount_date,transaction_rate,
1223 cashflow_amount,company_code,account_no,action_code,
1224 cparty_account_no,deal_subtype,product_type,status_code,
1225 dealer_code,client_code,cparty_code,settle,portfolio_code)
1226 values('SWPTN','SETTLE','SETTLE',
1227 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1228 nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
1229 nvl(P_SETTLE_DATE,P_START_DATE),0,
1230 decode(P_SETTLE_ACTION,'PAY',-(1),1) * nvl(P_SETTLE_AMOUNT,0),
1231 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,P_SETTLE_ACTION,
1232 nvl(v_settle_ac,P_CPARTY_ACCOUNT_NO), --BUG 2910654
1233 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1234 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
1235 nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1236 end if;
1237 -- Underlying Physical Start Details
1238 insert into XTR_DEAL_DATE_AMOUNTS
1239 (deal_type,amount_type,date_type,
1240 deal_number,transaction_number,transaction_date,currency,
1241 amount,hce_amount,amount_date,transaction_rate,
1242 cashflow_amount,company_code,deal_subtype,product_type,
1243 status_code,dealer_code,client_code,cparty_code,settle,
1244 portfolio_code)
1245 values('SWPTN','FACEVAL','COMENCE',
1246 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1247 nvl(P_FACE_VALUE_AMOUNT,0),nvl(P_FACE_VALUE_HCE_AMOUNT,0),
1248 P_START_DATE,P_INTEREST_RATE,0,
1249 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1250 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1251 P_CPARTY_CODE,'N',P_PORTFOLIO_CODE);
1252 -- Underlying Physical Maturity Details
1253 insert into XTR_DEAL_DATE_AMOUNTS
1254 (deal_type,amount_type,date_type,
1255 deal_number,transaction_number,transaction_date,currency,
1256 amount,hce_amount,amount_date,transaction_rate,
1257 cashflow_amount,company_code,deal_subtype,product_type,
1258 status_code,dealer_code,client_code,cparty_code,settle,portfolio_code)
1259 values('SWPTN','N/A','MATURE',
1260 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1261 0,0,P_MATURITY_DATE,P_INTEREST_RATE,0,
1262 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
1263 P_STATUS_CODE,P_DEALER_CODE,P_CLIENT_CODE,
1264 P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
1265 -- end of deal types for inserting
1266 end if;
1267 --
1268 elsif P_ACTION = 'UPDATE' then
1269 -- Delete rows in DDA for cancelled transactions
1270 if P_STATUS_CODE = 'CANCELLED' then
1271 delete from XTR_DEAL_DATE_AMOUNTS
1272 where deal_number = P_DEAL_NO;
1273 --
1274 if P_DEAL_TYPE = 'BOND' then
1275 -- coupons
1276 delete from XTR_ROLLOVER_TRANSACTIONS
1277 where deal_number = P_DEAL_NO;
1278 elsif P_DEAL_TYPE = 'FX' then
1279 -- Swapdepo transactions
1280 update XTR_ROLLOVER_TRANSACTIONS
1281 set STATUS_CODE = 'CANCELLED'
1282 where deal_number = P_DEAL_NO;
1283 end if;
1284 --
1285 if P_DEAL_TYPE = 'NI' then
1286 update XTR_PARCEL_SPLITS
1287 set status_code = 'CANCELLED'
1288 where deal_no = P_DEAL_NO
1289 and parcel_size = nvl(parcel_remaining,0);
1290 /*
1291 ------------
1292 -- NEW NI --
1293 ------------
1294 update XTR_ROLLOVER_TRANSACTIONS
1295 set STATUS_CODE = 'CANCELLED'
1296 where DEAL_NUMBER = P_DEAL_NO
1297 and TRANS_CLOSEOUT_NO is null;
1298 */
1299 end if;
1300 --
1301 -- Where not cancelled
1302 else
1303 if P_DEAL_TYPE = 'FRA' then
1304 update XTR_DEAL_DATE_AMOUNTS
1305 set amount = decode(AMOUNT_TYPE,'FACEVAL',P_FACE_VALUE_AMOUNT,amount),
1306 hce_amount = decode(AMOUNT_TYPE,'FACEVAL',P_FACE_VALUE_HCE_AMOUNT,hce_amount),
1307 amount_date = decode(DATE_TYPE,'COMENCE',P_START_DATE,'MATURE',P_MATURITY_DATE,'DEALT',P_DEAL_DATE,
1308 'RATESET',P_RATE_FIXING_DATE,amount_date),
1309 transaction_rate = decode(DATE_TYPE,'COMENCE',P_INTEREST_RATE,'MATURE',P_INTEREST_RATE,
1310 'RATESET',P_SETTLE_RATE,transaction_rate),
1311 transaction_date = P_DEAL_DATE,
1312 currency = P_CURRENCY,
1313 company_code = P_COMPANY_CODE,
1314 deal_subtype = P_DEAL_SUBTYPE,
1315 product_type = P_PRODUCT_TYPE,
1316 portfolio_code = P_PORTFOLIO_CODE,
1317 status_code = P_STATUS_CODE,
1318 dealer_code = P_DEALER_CODE,
1319 client_code = P_CLIENT_CODE,
1320 cparty_code = P_CPARTY_CODE,
1321 action_code = NULL,
1322 limit_code = decode(P_SETTLE_DATE,NULL,decode(DATE_TYPE,'LIMIT',nvl(P_LIMIT_CODE,'NILL'),NULL),NULL),
1323 limit_party = decode(P_SETTLE_DATE,NULL,decode(DATE_TYPE,'LIMIT',P_CPARTY_CODE,NULL),NULL)
1324 where DEAL_NUMBER = P_DEAL_NO
1325 and DEAL_TYPE = 'FRA'
1326 and AMOUNT_TYPE <> 'SETTLE'
1327 and DATE_TYPE <> 'LIMIT';
1328 --
1329 if P_SETTLE_DATE IS NOT NULL then
1330 open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
1331 fetch C_LIMIT_WEIGHTING into v_weighting;
1332 close C_LIMIT_WEIGHTING;
1333 update XTR_DEAL_DATE_AMOUNTS
1334 set AMOUNT = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
1335 HCE_AMOUNT = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
1336 AMOUNT_DATE = P_SETTLE_DATE,
1337 STATUS_CODE = P_STATUS_CODE,
1338 transaction_date = P_DEAL_DATE,
1339 currency = P_CURRENCY,
1340 company_code = P_COMPANY_CODE,
1341 deal_subtype = P_DEAL_SUBTYPE,
1342 product_type = P_PRODUCT_TYPE,
1343 portfolio_code = P_PORTFOLIO_CODE,
1344 dealer_code = P_DEALER_CODE,
1345 client_code = P_CLIENT_CODE,
1346 cparty_code = P_CPARTY_CODE,
1347 limit_code = nvl(P_LIMIT_CODE,'NILL'),
1348 limit_party = P_CPARTY_CODE
1349 where DEAL_NUMBER = P_DEAL_NO
1350 and DATE_TYPE = 'LIMIT'
1351 and DEAL_TYPE = 'FRA';
1352 update XTR_DEAL_DATE_AMOUNTS
1353 set AMOUNT = nvl(P_SETTLE_AMOUNT,0),
1354 ACTION_CODE = P_SETTLE_ACTION,
1355 HCE_AMOUNT = nvl(P_SETTLE_HCE_AMOUNT,0),
1356 CASHFLOW_AMOUNT = (decode(P_SETTLE_ACTION,'PAY',-1,1) * nvl(P_SETTLE_AMOUNT,0)),
1357 CPARTY_ACCOUNT_NO = nvl(v_cparty_account_no, cparty_account_no),
1358 ACCOUNT_NO = nvl(v_SETTLE_ACCOUNT_NO, account_no),
1359 AMOUNT_DATE = P_SETTLE_DATE,
1360 STATUS_CODE = P_STATUS_CODE,
1361 TRANSACTION_RATE = P_SETTLE_RATE,
1362 transaction_date = P_DEAL_DATE,
1363 currency = P_CURRENCY,
1364 company_code = P_COMPANY_CODE,
1365 deal_subtype = P_DEAL_SUBTYPE,
1366 product_type = P_PRODUCT_TYPE,
1367 portfolio_code = P_PORTFOLIO_CODE,
1368 dealer_code = P_DEALER_CODE,
1369 client_code = P_CLIENT_CODE,
1370 cparty_code = P_CPARTY_CODE
1371 where DEAL_NUMBER = P_DEAL_NO
1372 and AMOUNT_TYPE = 'SETTLE'
1373 and DATE_TYPE = 'SETTLE'
1374 and DEAL_TYPE = 'FRA';
1375 --
1376 if SQL%NOTFOUND then
1377 -- Settlement Row
1378 insert into XTR_DEAL_DATE_AMOUNTS
1379 (deal_type,amount_type,date_type,
1380 deal_number,transaction_number,transaction_date,currency,
1381 amount,hce_amount,amount_date,transaction_rate,
1382 cashflow_amount,company_code,account_no,cparty_account_no,action_code,
1383 deal_subtype,product_type,status_code,portfolio_code,
1384 dealer_code,client_code,cparty_code,settle)
1385 values('FRA','SETTLE','SETTLE',
1386 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1387 nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
1388 P_SETTLE_DATE,nvl(P_SETTLE_RATE,0),
1389 decode(P_SETTLE_ACTION,'PAY',-1,1) *
1390 nvl(P_SETTLE_AMOUNT,0),P_COMPANY_CODE,
1391 P_SETTLE_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,P_SETTLE_ACTION,P_DEAL_SUBTYPE,
1392 P_PRODUCT_TYPE,P_STATUS_CODE,P_PORTFOLIO_CODE,
1393 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N');
1394 end if;
1395 end if;
1396 elsif P_DEAL_TYPE = 'FXO' THEN
1397 update XTR_DEAL_DATE_AMOUNTS
1398 set amount = decode(amount_type,'FXOBUY',P_BUY_AMOUNT,P_SELL_AMOUNT),
1399 hce_amount = decode(amount_type,'FXOBUY',P_BUY_HCE_AMOUNT,P_SELL_HCE_AMOUNT),
1400 amount_date = P_EXPIRY_DATE,
1401 -- account_no = decode(amount_type,'FXOBUY',P_BUY_ACCOUNT_NO,P_SELL_ACCOUNT_NO),
1402 -- for bug 965188 part 2 account number should not be updated at all
1403 limit_code = decode(P_STATUS_CODE,'CURRENT',
1404 decode(amount_type,'FXOBUY',
1405 decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1406 decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL)),NULL),
1407 limit_party = decode(P_STATUS_CODE,'CURRENT',
1408 decode(amount_type,'FXOBUY',
1409 decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
1410 decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL)),NULL),
1411 transaction_date = P_DEAL_DATE,
1412 currency = decode(amount_type,'FXOBUY',P_CURRENCY_BUY,P_CURRENCY_SELL),
1413 company_code = P_COMPANY_CODE,
1414 deal_subtype = P_DEAL_SUBTYPE,
1415 product_type = P_PRODUCT_TYPE,
1416 portfolio_code = P_PORTFOLIO_CODE,
1417 status_code = P_STATUS_CODE,
1418 dealer_code = P_DEALER_CODE,
1419 client_code = P_CLIENT_CODE,
1420 cparty_code = P_CPARTY_CODE
1421 where deal_number = P_DEAL_NO
1422 and date_type = 'EXPIRY'
1423 and deal_type = P_DEAL_TYPE;
1424
1425 if SQL%NOTFOUND then
1426 if P_STATUS_CODE='CURRENT' and nvl(P_KNOCK_TYPE,'@#@')='I' and nvl(P_KNOCK_INSERT_TYPE,'D')='E'then
1427 insert into XTR_DEAL_DATE_AMOUNTS
1428 (deal_type,amount_type,date_type,
1429 deal_number,transaction_number,transaction_date,currency,
1430 amount,hce_amount,amount_date,transaction_rate,
1431 cashflow_amount,company_code,deal_subtype,product_type,
1432 status_code,cparty_code,settle,client_code,
1433 limit_code,limit_party,portfolio_code,dealer_code,currency_combination,QUICK_INPUT)
1434 values(P_DEAL_TYPE,'FXOBUY','EXPIRY',
1435 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1436 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
1437 P_EXPIRY_DATE,P_TRANSACTION_RATE,0,P_COMPANY_CODE,
1438 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
1439 P_CPARTY_CODE,'N',P_CLIENT_CODE,
1440 decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1441 decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
1442 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE,l_combin,P_QUICK_INPUT);
1443 --
1444 insert into XTR_DEAL_DATE_AMOUNTS
1445 (deal_type,amount_type,date_type,
1446 deal_number,transaction_number,transaction_date,currency,
1447 amount,hce_amount,amount_date,transaction_rate,
1448 cashflow_amount,company_code,deal_subtype,product_type,
1449 status_code,cparty_code,settle,
1450 client_code,portfolio_code,limit_code,limit_party,dealer_code,currency_combination,QUICK_INPUT)
1451 values(P_DEAL_TYPE,'FXOSELL','EXPIRY',P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
1452 P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,P_EXPIRY_DATE,P_TRANSACTION_RATE,0,
1453 P_COMPANY_CODE,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
1454 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
1455 decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1456 decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),
1457 P_DEALER_CODE,l_combin,P_QUICK_INPUT);
1458 end if;
1459 end if;
1460
1461 --
1462 update XTR_DEAL_DATE_AMOUNTS
1463 set amount_date = P_DEAL_DATE,
1464 transaction_date = P_DEAL_DATE,
1465 currency = P_CURRENCY_BUY,
1466 company_code = P_COMPANY_CODE,
1467 deal_subtype = P_DEAL_SUBTYPE,
1468 product_type = P_PRODUCT_TYPE,
1469 portfolio_code = P_PORTFOLIO_CODE,
1470 status_code = P_STATUS_CODE,
1471 dealer_code = P_DEALER_CODE,
1472 client_code = P_CLIENT_CODE,
1473 cparty_code = P_CPARTY_CODE
1474 where deal_number = P_DEAL_NO
1475 and date_type = 'DEALT'
1476 and deal_type = P_DEAL_TYPE;
1477 --
1478 update XTR_DEAL_DATE_AMOUNTS
1479 set amount = P_PREMIUM_AMOUNT,
1480 hce_amount = P_PREMIUM_HCE_AMOUNT,
1481 amount_date = P_PREMIUM_DATE,
1482 cashflow_amount = DECODE(P_PREMIUM_ACTION,'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
1483 account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
1484 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
1485 transaction_date = P_DEAL_DATE,
1486 currency = P_CURRENCY,
1487 company_code = P_COMPANY_CODE,
1488 deal_subtype = P_DEAL_SUBTYPE,
1489 product_type = P_PRODUCT_TYPE,
1490 portfolio_code = P_PORTFOLIO_CODE,
1491 status_code = P_STATUS_CODE,
1492 dealer_code = P_DEALER_CODE,
1493 client_code = P_CLIENT_CODE,
1494 cparty_code = P_CPARTY_CODE,
1495 action_code = P_PREMIUM_ACTION
1496 where deal_number = P_DEAL_NO
1497 and date_type = 'PREMIUM'
1498 and deal_type = P_DEAL_TYPE;
1499 --
1500 if SQL%NOTFOUND AND P_CURRENCY IS NOT NULL
1501 and P_PREMIUM_ACTION is NOT NULL
1502 and nvl(P_PREMIUM_AMOUNT,0) > 0 and P_STATUS_CODE <> 'CANCELLED'
1503 then
1504 --
1505 insert into XTR_DEAL_DATE_AMOUNTS
1506 (deal_type,amount_type,date_type,
1507 deal_number,transaction_number,transaction_date,currency,
1508 amount,hce_amount,amount_date,transaction_rate,
1509 cashflow_amount,company_code,account_no,action_code,
1510 cparty_account_no,deal_subtype,product_type,status_code,
1511 cparty_code,settle,client_code,portfolio_code,dealer_code)
1512 values(P_DEAL_TYPE,'PREMIUM','PREMIUM',
1513 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1514 P_PREMIUM_AMOUNT,P_PREMIUM_HCE_AMOUNT,
1515 P_PREMIUM_DATE,0,decode(P_PREMIUM_ACTION,
1516 'PAY',(-1) * P_PREMIUM_AMOUNT,P_PREMIUM_AMOUNT),
1517 P_COMPANY_CODE,P_PREMIUM_ACCOUNT_NO,'SETTLE',
1518 decode(P_PREMIUM_ACTION,'PAY',P_CPARTY_ACCOUNT_NO),
1519 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,
1520 'N',P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_DEALER_CODE);
1521 end if;
1522 --
1523 if P_STATUS_CODE='CURRENT'
1524 and (nvl(P_KNOCK_TYPE,'O')='O'
1525 or (P_KNOCK_TYPE='I' and nvl(P_KNOCK_INSERT_TYPE,'D')='E'))
1526 and P_INSERT_FOR_CASHFLOW = 'Y' then
1527 update XTR_DEAL_DATE_AMOUNTS
1528 set amount = P_SELL_AMOUNT,
1529 hce_amount = P_SELL_HCE_AMOUNT,
1530 amount_date = decode(date_type,'VALUE',P_VALUE_DATE,P_EXPIRY_DATE),
1531 exposure_ref_date = decode(date_type,'VALUE',P_START_DATE,exposure_ref_date),
1532 cashflow_amount = decode(status_code,'CURRENT',
1533 decode(DATE_TYPE,'EXPIRY',0,
1534 decode(P_INSERT_FOR_CASHFLOW,'Y',(-1) * P_SELL_AMOUNT,0))
1535 ,0),
1536 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1537 limit_code = decode(status_code,'EXERCISED',NULL,
1538 decode(DATE_TYPE,'EXPIRY',
1539 decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),NULL)),
1540 limit_party = decode(DATE_TYPE,'EXPIRY',decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),NULL),
1541 transaction_date = P_DEAL_DATE,
1542 currency = P_CURRENCY_SELL,
1543 company_code = P_COMPANY_CODE,
1544 deal_subtype = P_DEAL_SUBTYPE,
1545 product_type = P_PRODUCT_TYPE,
1546 portfolio_code = P_PORTFOLIO_CODE,
1547 status_code = P_STATUS_CODE,
1548 dealer_code = P_DEALER_CODE,
1549 client_code = P_CLIENT_CODE,
1550 cparty_code = P_CPARTY_CODE
1551 where deal_number = P_DEAL_NO
1552 and date_type = 'VALUE'
1553 and deal_type = P_DEAL_TYPE;
1554 if SQL%NOTFOUND then
1555 insert into XTR_DEAL_DATE_AMOUNTS
1556 (deal_type,amount_type,date_type,
1557 deal_number,transaction_number,transaction_date,currency,
1558 amount,hce_amount,amount_date,transaction_rate,
1559 cashflow_amount,company_code,account_no,
1560 deal_subtype,product_type,status_code,cparty_code,settle,
1561 client_code,portfolio_code,dealer_code,currency_combination,
1562 exposure_ref_date)
1563 values(P_DEAL_TYPE,'FXOBUY','VALUE',
1564 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_BUY,
1565 P_BUY_AMOUNT,P_BUY_HCE_AMOUNT,
1566 P_VALUE_DATE,P_TRANSACTION_RATE,P_BUY_AMOUNT,
1567 P_COMPANY_CODE,P_BUY_ACCOUNT_NO,P_DEAL_SUBTYPE,
1568 P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
1569 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
1570 P_DEALER_CODE,l_combin,P_START_DATE);
1571 --
1572 insert into XTR_DEAL_DATE_AMOUNTS
1573 (deal_type,amount_type,date_type,
1574 deal_number,transaction_number,transaction_date,currency,
1575 amount,hce_amount,amount_date,transaction_rate,
1576 cashflow_amount,company_code,account_no,
1577 deal_subtype,product_type,status_code,cparty_code,settle,
1578 client_code,portfolio_code,dealer_code,currency_combination,
1579 exposure_ref_date)
1580 values(P_DEAL_TYPE,'FXOSELL','VALUE',
1581 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY_SELL,
1582 P_SELL_AMOUNT,P_SELL_HCE_AMOUNT,
1583 P_VALUE_DATE,P_TRANSACTION_RATE,(-1) * P_SELL_AMOUNT,
1584 P_COMPANY_CODE,P_SELL_ACCOUNT_NO,P_DEAL_SUBTYPE,
1585 P_PRODUCT_TYPE,P_STATUS_CODE,P_CPARTY_CODE,'N',
1586 P_CLIENT_CODE,nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
1587 P_DEALER_CODE,l_combin,P_START_DATE);
1588 end if;
1589 else
1590 delete from XTR_DEAL_DATE_AMOUNTS
1591 where deal_number = P_DEAL_NO
1592 and deal_type = P_DEAL_TYPE
1593 and date_type = 'VALUE';
1594 end if;
1595 --
1596 if P_STATUS_CODE = 'EXPIRED' then
1597 delete from XTR_DEAL_DATE_AMOUNTS
1598 where deal_number = P_DEAL_NO
1599 and deal_type = P_DEAL_TYPE
1600 and date_type in ('VALUE','EXPIRY');
1601 elsif P_STATUS_CODE = 'EXERCISED' then
1602 -- AW Bug 894751 American Option
1603 delete from XTR_DEAL_DATE_AMOUNTS
1604 where deal_number = P_DEAL_NO
1605 and deal_type = P_DEAL_TYPE
1606 and date_type = 'VALUE';
1607 --
1608 update XTR_DEAL_DATE_AMOUNTS
1609 set DATE_TYPE = 'SETTLE',
1610 AMOUNT_DATE = trunc(sysdate),
1611 TRANSACTION_DATE = trunc(sysdate)
1612 where DEAL_NUMBER = P_DEAL_NO
1613 and DEAL_TYPE = P_DEAL_TYPE
1614 and DATE_TYPE = 'EXPIRY';
1615 --
1616 end if;
1617 elsif P_DEAL_TYPE = 'FX' then
1618
1619 /*--------------------------- RVALLAMS FX REARCH ----------------------------- */
1620
1621 IF ( (P_STATUS_CODE = 'CLOSED' and nvl(P_OLD_STATUS_CODE,'XXX') <> 'CLOSED'
1622 AND P_PROFIT_LOSS IS NOT NULL AND P_OLD_PROFIT_LOSS IS NULL
1623 AND P_FX_RO_PD_RATE IS NOT NULL AND P_OLD_FX_RO_PD_RATE IS NULL)
1624 OR
1625 (P_STATUS_CODE = 'CLOSED' AND P_OLD_STATUS_CODE = 'CLOSED'
1626 AND P_FX_M1_DEAL_NO IS NOT NULL AND P_OLD_FX_M1_DEAL_NO IS NOT NULL
1627 AND P_PROFIT_LOSS IS NOT NULL AND P_OLD_PROFIT_LOSS IS NOT NULL
1628 AND P_FX_RO_PD_RATE IS NOT NULL AND P_OLD_FX_RO_PD_RATE IS NOT NULL
1629 AND P_FX_M1_DEAL_NO <> P_OLD_FX_M1_DEAL_NO
1630 ))
1631
1632 THEN
1633
1634 UPDATE XTR_DEAL_DATE_AMOUNTS
1635 SET AMOUNT = P_BUY_AMOUNT,
1636 HCE_AMOUNT = nvl(P_BUY_HCE_AMOUNT,0),
1637 STATUS_CODE = P_STATUS_CODE,
1638 AMOUNT_DATE = P_START_DATE,
1639 CASHFLOW_AMOUNT = 0,
1640 DATE_TYPE = 'ROLLPRE',
1641 ACCOUNT_NO = nvl(v_BUY_ACCOUNT_NO, account_no ),
1642 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
1643 LIMIT_CODE = NULL,
1644 limit_party = decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
1645 currency = P_CURRENCY_BUY,
1646 currency_combination = l_combin,
1647 product_type = P_PRODUCT_TYPE,
1648 company_code = P_COMPANY_CODE,
1649 cparty_code = P_CPARTY_CODE,
1650 client_code = P_CLIENT_CODE,
1651 dealer_code = P_DEALER_CODE,
1652 transaction_rate = P_TRANSACTION_RATE,
1653 transaction_date = P_DEAL_DATE
1654 WHERE DEAL_NUMBER = P_DEAL_NO
1655 AND DEAL_TYPE = P_DEAL_TYPE
1656 AND DATE_TYPE = 'VALUE'
1657 AND AMOUNT_TYPE = 'BUY'
1658 AND TRANSACTION_NUMBER = 1;
1659
1660 update XTR_DEAL_DATE_AMOUNTS
1661 set amount = P_SELL_AMOUNT,
1662 hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1663 amount_date = P_START_DATE,
1664 cashflow_amount = 0,
1665 DATE_TYPE = 'ROLLPRE',
1666 STATUS_CODE = P_STATUS_CODE,
1667 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1668 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
1669 LIMIT_CODE = NULL,
1670 limit_party = decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),
1671 currency = P_CURRENCY_SELL,
1672 currency_combination = l_combin,
1673 product_type = P_PRODUCT_TYPE,
1674 portfolio_code = P_PORTFOLIO_CODE,
1675 company_code = P_COMPANY_CODE,
1676 cparty_code = P_CPARTY_CODE,
1677 client_code = P_CLIENT_CODE,
1678 dealer_code = P_DEALER_CODE,
1679 transaction_rate = P_TRANSACTION_RATE,
1680 transaction_date = P_DEAL_DATE
1681 where deal_number = P_DEAL_NO
1682 and amount_type = 'SELL'
1683 and deal_type = P_DEAL_TYPE
1684 AND date_type = 'VALUE'
1685 and transaction_number = 1;
1686
1687 update XTR_DEAL_DATE_AMOUNTS
1688 set amount = P_SELL_AMOUNT,
1689 hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1690 status_code = P_STATUS_CODE,
1691 amount_date = P_DEAL_DATE,
1692 currency = P_CURRENCY_SELL,
1693 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1694 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no ),
1695 product_type = P_PRODUCT_TYPE,
1696 portfolio_code = P_PORTFOLIO_CODE,
1697 company_code = P_COMPANY_CODE,
1698 cparty_code = P_CPARTY_CODE,
1699 client_code = P_CLIENT_CODE,
1700 dealer_code = P_DEALER_CODE,
1701 transaction_rate = P_TRANSACTION_RATE,
1702 transaction_date = P_DEAL_DATE,
1703 currency_combination = l_combin
1704 where deal_number = P_DEAL_NO
1705 and amount_type = 'N/A'
1706 and deal_type = P_DEAL_TYPE;
1707
1708 /*--------------------------- RVALLAMS FX REARCH ----------------------------- */
1709 ELSE
1710 update XTR_DEAL_DATE_AMOUNTS
1711 set AMOUNT = P_BUY_AMOUNT,
1712 HCE_AMOUNT = nvl(P_BUY_HCE_AMOUNT,0),
1713 AMOUNT_DATE = P_VALUE_DATE,
1714 STATUS_CODE = P_STATUS_CODE,
1715 CASHFLOW_AMOUNT = P_BUY_AMOUNT,
1716 ACCOUNT_NO = nvl(v_BUY_ACCOUNT_NO, account_no),
1717 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
1718 LIMIT_CODE = decode(P_CURRENCY_BUY,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1719 limit_party = decode(P_CURRENCY_BUY,base_ccy,P_CPARTY_CODE,NULL),
1720 currency = P_CURRENCY_BUY,
1721 currency_combination = l_combin,
1722 product_type = P_PRODUCT_TYPE,
1723 company_code = P_COMPANY_CODE,
1724 cparty_code = P_CPARTY_CODE,
1725 client_code = P_CLIENT_CODE,
1726 dealer_code = P_DEALER_CODE,
1727 transaction_rate = P_TRANSACTION_RATE,
1728 transaction_date = P_DEAL_DATE
1729 where deal_number = P_DEAL_NO
1730 and amount_type = 'BUY'
1731 and deal_type = P_DEAL_TYPE
1732 AND date_type = 'VALUE'
1733 and transaction_number = 1;
1734 --
1735 update XTR_DEAL_DATE_AMOUNTS
1736 set amount = P_SELL_AMOUNT,
1737 hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1738 amount_date = P_VALUE_DATE,
1739 cashflow_amount = (-1) * P_SELL_AMOUNT,
1740 STATUS_CODE = P_STATUS_CODE,
1741 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1742 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
1743 LIMIT_CODE = decode(P_CURRENCY_SELL,base_ccy,nvl(P_LIMIT_CODE,'NILL'),NULL),
1744 limit_party = decode(P_CURRENCY_SELL,base_ccy,P_CPARTY_CODE,NULL),
1745 currency = P_CURRENCY_SELL,
1746 currency_combination = l_combin,
1747 product_type = P_PRODUCT_TYPE,
1748 portfolio_code = P_PORTFOLIO_CODE,
1749 company_code = P_COMPANY_CODE,
1750 cparty_code = P_CPARTY_CODE,
1751 client_code = P_CLIENT_CODE,
1752 dealer_code = P_DEALER_CODE,
1753 transaction_rate = P_TRANSACTION_RATE,
1754 transaction_date = P_DEAL_DATE
1755 where deal_number = P_DEAL_NO
1756 and amount_type = 'SELL'
1757 and deal_type = P_DEAL_TYPE
1758 AND date_type = 'VALUE'
1759 and transaction_number = 1;
1760 --
1761 update XTR_DEAL_DATE_AMOUNTS
1762 set amount = P_SELL_AMOUNT,
1763 hce_amount = nvl(P_SELL_HCE_AMOUNT,0),
1764 status_code = P_STATUS_CODE,
1765 amount_date = P_DEAL_DATE,
1766 currency = P_CURRENCY_SELL,
1767 account_no = nvl(v_SELL_ACCOUNT_NO, account_no),
1768 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
1769 product_type = P_PRODUCT_TYPE,
1770 portfolio_code = P_PORTFOLIO_CODE,
1771 company_code = P_COMPANY_CODE,
1772 cparty_code = P_CPARTY_CODE,
1773 client_code = P_CLIENT_CODE,
1774 dealer_code = P_DEALER_CODE,
1775 transaction_rate = P_TRANSACTION_RATE,
1776 transaction_date = P_DEAL_DATE,
1777 currency_combination = l_combin
1778 where deal_number = P_DEAL_NO
1779 and amount_type = 'N/A'
1780 and deal_type = P_DEAL_TYPE;
1781 END IF;
1782 --
1783 elsif P_DEAL_TYPE = 'NI' then
1784 update XTR_DEAL_DATE_AMOUNTS
1785 set ACCOUNT_NO = nvl(v_MATURITY_ACCOUNT_NO, account_no),
1786 STATUS_CODE = P_STATUS_CODE,
1787 CPARTY_ACCOUNT_NO = nvl(v_cparty_account_no, cparty_account_no),
1788 COMMENCE_DATE = P_START_DATE,
1789 CPARTY_CODE = P_CPARTY_CODE,
1790 PRODUCT_TYPE = P_PRODUCT_TYPE,
1791 PORTFOLIO_CODE = P_PORTFOLIO_CODE,
1792 DEAL_SUBTYPE = P_DEAL_SUBTYPE,
1793 DEALER_CODE = P_DEALER_CODE,
1794 CLIENT_CODE = P_CLIENT_CODE,
1795 TRANSACTION_RATE = P_INTEREST_RATE,
1796 TRANSACTION_DATE = P_DEAL_DATE,
1797 CURRENCY = P_CURRENCY,
1798 LIMIT_CODE = decode(AMOUNT_TYPE,'COMENCE',
1799 decode(P_RISKPARTY_LIMIT_CODE,NULL,'NILL',NULL),
1800 'BAL_FV',
1801 decode(P_RISKPARTY_LIMIT_CODE,NULL,NULL,P_RISKPARTY_LIMIT_CODE),
1802 LIMIT_CODE),
1803 LIMIT_PARTY = decode(AMOUNT_TYPE,'COMENCE',
1804 NULL,
1805 'BAL_FV',
1806 decode(P_RISKPARTY_LIMIT_CODE,NULL,NULL,P_RISKPARTY_CODE),
1807 LIMIT_PARTY),
1808 AMOUNT_DATE = decode(AMOUNT_TYPE,'COMENCE',P_START_DATE,
1809 'BAL_FV',P_MATURITY_DATE,
1810 'INTL_FV',decode(P_DEAL_SUBTYPE,'SELL',P_START_DATE,
1811 'COVER',P_START_DATE,P_MATURITY_DATE),
1812 'INT',P_START_DATE,
1813 AMOUNT_DATE),
1814 AMOUNT = decode(AMOUNT_TYPE,'COMENCE',nvl(P_START_AMOUNT,0),
1815 'BAL_FV',nvl(P_MATURITY_BALANCE_AMOUNT,0),
1816 'INTL_FV', nvl(P_MATURITY_AMOUNT,0),
1817 'INT', nvl(P_INTEREST_AMOUNT,0),
1818 AMOUNT),
1819 HCE_AMOUNT = decode(AMOUNT_TYPE,'COMENCE', nvl(P_START_HCE_AMOUNT,0),
1820 'BAL_FV', nvl(P_MATURITY_BALANCE_HCE_AMOUNT,0),
1821 'INTL_FV', nvl(P_MATURITY_HCE_AMOUNT,0),
1822 'INT', nvl(P_INTEREST_HCE_AMOUNT,0),
1823 HCE_AMOUNT),
1824 CASHFLOW_AMOUNT = decode(AMOUNT_TYPE,'COMENCE',
1825 decode(nvl(P_KNOCK_TYPE,'N'),'N',decode(P_DEAL_SUBTYPE,'BUY',-1,'COVER',-1,1)*nvl(P_START_AMOUNT,0),0),
1826 -- 'BAL_FV', decode(P_DEAL_SUBTYPE,'BUY',1,-1) * nvl(P_MATURITY_BALANCE_AMOUNT,0),
1827 'BAL_FV', decode(P_DEAL_SUBTYPE,'BUY',1,'SELL',0,-1) * nvl(P_MATURITY_BALANCE_AMOUNT,0),
1828 CASHFLOW_AMOUNT) -- bug 3776211
1829 where DEAL_NUMBER = P_DEAL_NO
1830 and DEAL_TYPE = P_DEAL_TYPE;
1831 /* AW 9/24/99 Bug 996572
1832 LIMIT_CODE = decode(AMOUNT_TYPE,'COMENCE',
1833 decode(P_DEAL_SUBTYPE,'SELL',nvl(nvl(P_RISKPARTY_LIMIT_CODE,P_LIMIT_CODE),'NILL'),NULL),
1834 'BAL_FV',
1835 decode(P_DEAL_SUBTYPE,'SELL',NULL,nvl(nvl(P_RISKPARTY_LIMIT_CODE,P_LIMIT_CODE),'NILL')),
1836 LIMIT_CODE),
1837 LIMIT_PARTY = decode(AMOUNT_TYPE,'COMENCE', decode(P_DEAL_SUBTYPE,'SELL',P_RISKPARTY_CODE,NULL),
1838 'BAL_FV', decode(P_DEAL_SUBTYPE,'SELL',NULL,P_RISKPARTY_CODE),
1839 LIMIT_PARTY),
1840 */
1841 --
1842 if P_DEAL_SUBTYPE in ('BUY','SHORT','ISSUE') then
1843 open CHK_NI_BAL_FV;
1844 fetch CHK_NI_BAL_FV into l_dummy;
1845 if CHK_NI_BAL_FV%NOTFOUND and nvl(P_MATURITY_BALANCE_AMOUNT,0) <> 0 then
1846 insert into XTR_DEAL_DATE_AMOUNTS
1847 (deal_type,amount_type,date_type,
1848 deal_number,transaction_number,transaction_date,currency,
1849 amount,hce_amount,amount_date,transaction_rate,
1850 cashflow_amount,company_code,account_no,
1851 cparty_account_no,status_code,portfolio_code,dealer_code,
1852 client_code,deal_subtype,cparty_code,settle,product_type,
1853 limit_code,limit_party,commence_date,quick_input)
1854 values('NI','BAL_FV','MATURE',
1855 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1856 P_MATURITY_BALANCE_AMOUNT,
1857 P_MATURITY_BALANCE_HCE_AMOUNT,P_MATURITY_DATE,
1858 -- P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,-1) *
1859 P_INTEREST_RATE,decode(P_DEAL_SUBTYPE,'BUY',1,'SELL',0,-1) *
1860 P_MATURITY_BALANCE_AMOUNT,P_COMPANY_CODE,
1861 P_MATURITY_ACCOUNT_NO,P_CPARTY_ACCOUNT_NO,
1862 P_STATUS_CODE,P_PORTFOLIO_CODE,P_DEALER_CODE,
1863 P_CLIENT_CODE,P_DEAL_SUBTYPE,P_CPARTY_CODE,'N',
1864 P_PRODUCT_TYPE,decode(P_DEAL_SUBTYPE,'SELL',NULL,nvl(nvl(P_RISKPARTY_LIMIT_CODE,P_LIMIT_CODE),'NILL')),
1865 decode(P_DEAL_SUBTYPE,'SELL',NULL,P_RISKPARTY_CODE),P_START_DATE,P_QUICK_INPUT);
1866 end if;
1867 close CHK_NI_BAL_FV;
1868 end if;
1869 --
1870
1871 ------------
1872 -- NEW NI --
1873 ------------
1874 /* Not required.
1875 ---- 06/08/99 Apply PL to dda for sale deal. refer to bug 904365
1876 if P_DEAL_SUBTYPE='SELL' then
1877 update XTR_DEAL_DATE_AMOUNTS
1878 set AMOUNT_DATE = P_START_DATE,
1879 AMOUNT = abs(nvl(P_NI_PROFIT_LOSS,0)),
1880 HCE_AMOUNT = abs(nvl(round(P_NI_PROFIT_LOSS/hce_rate,2),0)),
1881 ACTION_CODE = decode(sign(nvl(P_NI_PROFIT_LOSS,0)),-1,'LOSS','PROFIT'),
1882 CASHFLOW_AMOUNT = 0
1883 where DEAL_NUMBER = P_DEAL_NO
1884 and AMOUNT_TYPE = 'REAL'
1885 and DATE_TYPE ='REVAL';
1886 --
1887 if SQL%NOTFOUND and nvl(P_NI_PROFIT_LOSS,0) <> 0 then
1888 -- Set the profit and loss info into the database...
1889 insert into XTR_DEAL_DATE_AMOUNTS
1890 (deal_type,amount_type,date_type,
1891 deal_number,transaction_number,transaction_date,currency,
1892 amount,hce_amount,amount_date,transaction_rate,
1893 cashflow_amount,company_code,status_code,dealer_code,
1894 deal_subtype,product_type,settle,cparty_code,client_code,
1895 portfolio_code,action_code)
1896 values('NI','REAL','REVAL',
1897 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
1898 abs(nvl(P_NI_PROFIT_LOSS,0)),abs(nvl(round(P_NI_PROFIT_LOSS/hce_rate,2),0)),
1899 P_START_DATE,P_INTEREST_RATE,0,P_COMPANY_CODE,
1900 P_STATUS_CODE,P_DEALER_CODE,P_DEAL_SUBTYPE,
1901 P_PRODUCT_TYPE,'N',P_CPARTY_CODE,P_CLIENT_CODE,
1902 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),decode(sign(nvl(P_NI_PROFIT_LOSS,0)),-1,'LOSS','PROFIT'));
1903 end if;
1904 end if;
1905 */
1906 --
1907 elsif P_DEAL_TYPE = 'BOND' then
1908 update XTR_DEAL_DATE_AMOUNTS
1909 set amount = P_START_AMOUNT,
1910 hce_amount = P_START_HCE_AMOUNT,
1911 cashflow_amount = decode(P_DEAL_SUBTYPE,'BUY',1,-1) * P_START_AMOUNT,
1912 transaction_rate = P_INTEREST_RATE,
1913 transaction_date = P_DEAL_DATE,
1914 account_no = nvl(v_MATURITY_ACCOUNT_NO, account_no ),
1915 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no)
1916 where deal_number = P_DEAL_NO
1917 and amount_date >= L_SYSDATE
1918 and amount_type = 'COMENCE'
1919 and date_type = 'COMENCE'
1920 and deal_type = P_DEAL_TYPE;
1921 --
1922 update XTR_DEAL_DATE_AMOUNTS
1923 set amount = P_MATURITY_AMOUNT,
1924 hce_amount = P_MATURITY_HCE_AMOUNT,
1925 cashflow_amount = decode(P_DEAL_SUBTYPE,'BUY',1,-1) * P_MATURITY_AMOUNT,
1926 transaction_rate = P_INTEREST_RATE,
1927 transaction_date = P_DEAL_DATE,
1928 account_no = nvl(v_MATURITY_ACCOUNT_NO, account_no),
1929 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
1930 limit_code = nvl(P_LIMIT_CODE,'NILL')
1931 where deal_number = P_DEAL_NO
1932 and amount_type = 'INTL_FV'
1933 and date_type = 'MATURE'
1934 and deal_type = P_DEAL_TYPE
1935 and amount_date >= L_SYSDATE;
1936 --
1937 if P_INT_VALUE is not null then
1938 update XTR_DEAL_DATE_AMOUNTS
1939 set amount = P_INT_VALUE,
1940 hce_amount = round(P_INT_VALUE/hce_rate,round_fac),
1941 transaction_date = P_DEAL_DATE,
1942 transaction_rate = P_INTEREST_RATE
1943 where deal_number = P_DEAL_NO
1944 and amount_type = 'INT'
1945 and date_type = 'COMENCE'
1946 and deal_type = P_DEAL_TYPE
1947 and amount_date >= L_SYSDATE;
1948 end if;
1949 --
1950 if nvl(P_PREM_VALUE,0) <> 0 then
1951 update XTR_DEAL_DATE_AMOUNTS
1952 set amount = abs(P_PREM_VALUE),
1953 hce_amount = abs(round(P_PREM_VALUE/hce_rate,round_fac)),
1954 transaction_date = P_DEAL_DATE,
1955 transaction_rate = P_INTEREST_RATE
1956 where deal_number = P_DEAL_NO
1957 and amount_type IN('DISC','PREMIUM')
1958 and date_type = 'COMENCE'
1959 and deal_type = P_DEAL_TYPE
1960 and amount_date >= L_SYSDATE;
1961 end if;
1962 --
1963 elsif P_DEAL_TYPE = 'IRO' then
1964 update XTR_DEAL_DATE_AMOUNTS
1965 set amount = nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
1966 hce_amount = nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
1967 amount_date = P_EXPIRY_DATE,
1968 transaction_rate = P_INTEREST_RATE,
1969 transaction_date = P_DEAL_DATE,
1970 currency = P_CURRENCY,
1971 company_code = P_COMPANY_CODE,
1972 deal_subtype = P_DEAL_SUBTYPE,
1973 product_type = P_PRODUCT_TYPE,
1974 status_code = P_STATUS_CODE,
1975 dealer_code = P_DEALER_CODE,
1976 client_code = P_CLIENT_CODE,
1977 cparty_code = P_CPARTY_CODE,
1978 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
1979 where deal_number = P_DEAL_NO
1980 and deal_type = 'IRO'
1981 and date_type = 'EXPIRY';
1982 --
1983 update XTR_DEAL_DATE_AMOUNTS
1984 set amount = nvl(P_PREMIUM_AMOUNT,0),
1985 hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
1986 amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
1987 transaction_date = P_DEAL_DATE,
1988 currency = P_CURRENCY,
1989 cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
1990 company_code = P_COMPANY_CODE,
1991 account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
1992 cparty_account_no = decode(P_PREMIUM_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),''),
1993 deal_subtype = P_DEAL_SUBTYPE,
1994 product_type = P_PRODUCT_TYPE,
1995 status_code = P_STATUS_CODE,
1996 dealer_code = P_DEALER_CODE,
1997 client_code = P_CLIENT_CODE,
1998 cparty_code = P_CPARTY_CODE,
1999 action_code = P_PREMIUM_ACTION,
2000 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2001 where deal_number = P_DEAL_NO
2002 and deal_type = 'IRO'
2003 and date_type = 'PREMIUM';
2004 --
2005 update XTR_DEAL_DATE_AMOUNTS
2006 set amount_date = P_DEAL_DATE,
2007 transaction_date = P_DEAL_DATE,
2008 currency = P_CURRENCY,
2009 company_code = P_COMPANY_CODE,
2010 deal_subtype = P_DEAL_SUBTYPE,
2011 product_type = P_PRODUCT_TYPE,
2012 status_code = P_STATUS_CODE,
2013 dealer_code = P_DEALER_CODE,
2014 client_code = P_CLIENT_CODE,
2015 cparty_code = P_CPARTY_CODE,
2016 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2017 where deal_number = P_DEAL_NO
2018 and deal_type = 'IRO'
2019 and date_type = 'DEALT';
2020 --
2021 update XTR_DEAL_DATE_AMOUNTS
2022 set amount_date = P_MATURITY_DATE,
2023 transaction_date = P_DEAL_DATE,
2024 currency = P_CURRENCY,
2025 company_code = P_COMPANY_CODE,
2026 deal_subtype = P_DEAL_SUBTYPE,
2027 product_type = P_PRODUCT_TYPE,
2028 status_code = P_STATUS_CODE,
2029 dealer_code = P_DEALER_CODE,
2030 client_code = P_CLIENT_CODE,
2031 cparty_code = P_CPARTY_CODE,
2032 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2033 where deal_number = P_DEAL_NO
2034 and deal_type = 'IRO'
2035 and date_type = 'MATURE';
2036 --
2037 update XTR_DEAL_DATE_AMOUNTS
2038 set amount_date = P_START_DATE,
2039 transaction_date = P_DEAL_DATE,
2040 currency = P_CURRENCY,
2041 company_code = P_COMPANY_CODE,
2042 deal_subtype = P_DEAL_SUBTYPE,
2043 product_type = P_PRODUCT_TYPE,
2044 status_code = P_STATUS_CODE,
2045 dealer_code = P_DEALER_CODE,
2046 client_code = P_CLIENT_CODE,
2047 cparty_code = P_CPARTY_CODE,
2048 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2049 where deal_number = P_DEAL_NO
2050 and deal_type = 'IRO'
2051 and date_type = 'COMENCE';
2052 --
2053 if P_STATUS_CODE = 'EXERCISED' then
2054
2055 --Bug 3060946 Removed call to xtr_fps2_p.standing_settlement
2056
2057 open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
2058 fetch C_LIMIT_WEIGHTING into v_weighting;
2059 close C_LIMIT_WEIGHTING;
2060 update XTR_DEAL_DATE_AMOUNTS
2061 set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
2062 hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
2063 amount_date = P_SETTLE_DATE,
2064 transaction_date = P_DEAL_DATE,
2065 currency = P_CURRENCY,
2066 company_code = P_COMPANY_CODE,
2067 deal_subtype = P_DEAL_SUBTYPE,
2068 product_type = P_PRODUCT_TYPE,
2069 status_code = P_STATUS_CODE,
2070 dealer_code = P_DEALER_CODE,
2071 client_code = P_CLIENT_CODE,
2072 cparty_code = P_CPARTY_CODE,
2073 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2074 limit_code = nvl(P_LIMIT_CODE,'NILL'),
2075 limit_party = P_CPARTY_CODE
2076 where deal_number = P_DEAL_NO
2077 and deal_type = 'IRO'
2078 and date_type = 'LIMIT';
2079 --
2080 -- AW Bug 894751
2081 delete from XTR_DEAL_DATE_AMOUNTS
2082 where deal_number = P_DEAL_NO
2083 and deal_type = 'IRO'
2084 and date_type = 'EXPIRY';
2085 --
2086 update XTR_DEAL_DATE_AMOUNTS
2087 set amount = nvl(P_SETTLE_AMOUNT,0),
2088 hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
2089 amount_date = P_SETTLE_DATE,
2090 cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
2091 transaction_rate = P_SETTLE_RATE,
2092 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2093 transaction_date = P_DEAL_DATE,
2094 currency = P_CURRENCY,
2095 company_code = P_COMPANY_CODE,
2096 action_code = P_SETTLE_ACTION,
2097 --Bug 3060946 Removed assignment to cparty_account_no
2098 deal_subtype = P_DEAL_SUBTYPE,
2099 product_type = P_PRODUCT_TYPE,
2100 status_code = P_STATUS_CODE,
2101 dealer_code = P_DEALER_CODE,
2102 client_code = P_CLIENT_CODE,
2103 cparty_code = P_CPARTY_CODE,
2104 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2105 where deal_number = P_DEAL_NO
2106 and deal_type = 'IRO'
2107 and date_type = 'SETTLE';
2108 --
2109 --Bug 3060946 Removed 'Insert into XTR_DEAL_DATE_AMOUNTS' statement
2110 --
2111 end if;
2112 --
2113 elsif P_DEAL_TYPE = 'BDO' then
2114 update XTR_DEAL_DATE_AMOUNTS
2115 set amount = nvl(nvl(P_FACE_VALUE_AMOUNT,P_MATURITY_AMOUNT),0),
2116 hce_amount = nvl(nvl(P_FACE_VALUE_HCE_AMOUNT,P_MATURITY_HCE_AMOUNT),0),
2117 amount_date = P_EXPIRY_DATE,
2118 transaction_rate = P_INTEREST_RATE,
2119 transaction_date = P_DEAL_DATE,
2120 currency = P_CURRENCY,
2121 company_code = P_COMPANY_CODE,
2122 deal_subtype = P_DEAL_SUBTYPE,
2123 product_type = P_PRODUCT_TYPE,
2124 status_code = P_STATUS_CODE,
2125 dealer_code = P_DEALER_CODE,
2126 client_code = P_CLIENT_CODE,
2127 cparty_code = P_CPARTY_CODE,
2128 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2129 where deal_number = P_DEAL_NO
2130 and deal_type = 'BDO'
2131 and date_type = 'EXPIRY';
2132 --
2133 update XTR_DEAL_DATE_AMOUNTS
2134 set amount = nvl(P_PREMIUM_AMOUNT,0),
2135 hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2136 amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
2137 transaction_date = P_DEAL_DATE,
2138 currency = P_CURRENCY,
2139 cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1) * nvl(P_PREMIUM_AMOUNT,0),
2140 company_code = P_COMPANY_CODE,
2141 account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
2142 cparty_account_no = decode(P_PREMIUM_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),''),
2143 deal_subtype = P_DEAL_SUBTYPE,
2144 product_type = P_PRODUCT_TYPE,
2145 status_code = P_STATUS_CODE,
2146 dealer_code = P_DEALER_CODE,
2147 client_code = P_CLIENT_CODE,
2148 cparty_code = P_CPARTY_CODE,
2149 action_code = P_PREMIUM_ACTION,
2150 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2151 where deal_number = P_DEAL_NO
2152 and deal_type = 'BDO'
2153 and date_type = 'PREMIUM';
2154 --
2155 update XTR_DEAL_DATE_AMOUNTS
2156 set amount_date = P_DEAL_DATE,
2157 transaction_date = P_DEAL_DATE,
2158 currency = P_CURRENCY,
2159 company_code = P_COMPANY_CODE,
2160 deal_subtype = P_DEAL_SUBTYPE,
2161 product_type = P_PRODUCT_TYPE,
2162 status_code = P_STATUS_CODE,
2163 dealer_code = P_DEALER_CODE,
2164 client_code = P_CLIENT_CODE,
2165 cparty_code = P_CPARTY_CODE,
2166 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2167 where deal_number = P_DEAL_NO
2168 and deal_type = 'BDO'
2169 and date_type = 'DEALT';
2170 --
2171 update XTR_DEAL_DATE_AMOUNTS
2172 set amount_date = P_MATURITY_DATE,
2173 transaction_date = P_DEAL_DATE,
2174 currency = P_CURRENCY,
2175 company_code = P_COMPANY_CODE,
2176 deal_subtype = P_DEAL_SUBTYPE,
2177 product_type = P_PRODUCT_TYPE,
2178 status_code = P_STATUS_CODE,
2179 dealer_code = P_DEALER_CODE,
2180 client_code = P_CLIENT_CODE,
2181 cparty_code = P_CPARTY_CODE,
2182 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2183 where deal_number = P_DEAL_NO
2184 and deal_type = 'BDO'
2185 and date_type = 'MATURE';
2186 --
2187 update XTR_DEAL_DATE_AMOUNTS
2188 set amount_date = P_START_DATE,
2189 transaction_date = P_DEAL_DATE,
2190 currency = P_CURRENCY,
2191 company_code = P_COMPANY_CODE,
2192 deal_subtype = P_DEAL_SUBTYPE,
2193 product_type = P_PRODUCT_TYPE,
2194 status_code = P_STATUS_CODE,
2195 dealer_code = P_DEALER_CODE,
2196 client_code = P_CLIENT_CODE,
2197 cparty_code = P_CPARTY_CODE,
2198 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2199 where deal_number = P_DEAL_NO
2200 and deal_type = 'BDO'
2201 and date_type = 'COMENCE';
2202 --
2203 if P_STATUS_CODE = 'EXERCISED' then
2204 xtr_fps2_p.standing_settlements(P_CPARTY_CODE,P_CURRENCY,'BDO',
2205 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,'SETTLE',
2206 v_settle_ref,v_settle_ac);
2207 -- AW Bug 894751
2208 delete from XTR_DEAL_DATE_AMOUNTS
2209 where deal_number = P_DEAL_NO
2210 and deal_type = 'BDO'
2211 and date_type = 'EXPIRY';
2212 --
2213 if P_EXERCISE_PRICE is not null then
2214 open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
2215 fetch C_LIMIT_WEIGHTING into v_weighting;
2216 close C_LIMIT_WEIGHTING;
2217 update XTR_DEAL_DATE_AMOUNTS
2218 set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
2219 hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
2220 amount_date = P_SETTLE_DATE,
2221 transaction_date = P_DEAL_DATE,
2222 currency = P_CURRENCY,
2223 company_code = P_COMPANY_CODE,
2224 deal_subtype = P_DEAL_SUBTYPE,
2225 product_type = P_PRODUCT_TYPE,
2226 status_code = P_STATUS_CODE,
2227 dealer_code = P_DEALER_CODE,
2228 client_code = P_CLIENT_CODE,
2229 cparty_code = P_CPARTY_CODE,
2230 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2231 limit_code = nvl(P_LIMIT_CODE,'NILL'),
2232 limit_party = P_CPARTY_CODE
2233 where deal_number = P_DEAL_NO
2234 and deal_type = 'BDO'
2235 and date_type = 'LIMIT';
2236 --
2237 update XTR_DEAL_DATE_AMOUNTS
2238 set amount = nvl(P_SETTLE_AMOUNT,0),
2239 hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
2240 amount_date = P_SETTLE_DATE,
2241 cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
2242 transaction_rate = P_SETTLE_RATE,
2243 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2244 transaction_date = P_DEAL_DATE,
2245 currency = P_CURRENCY,
2246 company_code = P_COMPANY_CODE,
2247 action_code = P_SETTLE_ACTION,
2248 cparty_account_no = nvl(v_settle_ac,nvl(v_cparty_account_no, cparty_account_no)), --Bug 2855642
2249 deal_subtype = P_DEAL_SUBTYPE,
2250 product_type = P_PRODUCT_TYPE,
2251 status_code = P_STATUS_CODE,
2252 dealer_code = P_DEALER_CODE,
2253 client_code = P_CLIENT_CODE,
2254 cparty_code = P_CPARTY_CODE,
2255 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2256 where deal_number = P_DEAL_NO
2257 and deal_type = 'BDO'
2258 and date_type = 'SETTLE';
2259 --
2260 if SQL%NOTFOUND and P_SETTLE_DATE is NOT NULL then
2261 insert into XTR_DEAL_DATE_AMOUNTS
2262 (deal_type,amount_type,date_type,
2263 deal_number,transaction_number,transaction_date,currency,
2264 amount,hce_amount,amount_date,transaction_rate,
2265 cashflow_amount,company_code,account_no,action_code,
2266 cparty_account_no,deal_subtype,product_type,status_code,
2267 dealer_code,client_code,cparty_code,settle,portfolio_code)
2268 values('BDO','SETTLE','SETTLE',
2269 P_DEAL_NO,1,P_DEAL_DATE,P_CURRENCY,
2270 nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
2271 nvl(P_SETTLE_DATE,P_START_DATE),P_SETTLE_RATE,
2272 decode(P_SETTLE_ACTION,'PAY',-(1),1) * nvl(P_SETTLE_AMOUNT,0),
2273 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,P_SETTLE_ACTION,
2274 nvl(v_settle_ac,P_CPARTY_ACCOUNT_NO), --Bug 2855642
2275 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
2276 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'));
2277 end if;
2278 else -- AW Bug 894751
2279 -- JT bug 1312363 while validating after exercise the insert was giving
2280 -- duplicate row error a select is added to check whether the row is already
2281 -- existing or not. if no data found then insert the record
2282
2283 open CHK_BDO_SETTLE_ROWS;
2284 fetch CHK_BDO_SETTLE_ROWS into l_dummy;
2285
2286 if CHK_BDO_SETTLE_ROWS%NOTFOUND then
2287 insert into XTR_DEAL_DATE_AMOUNTS
2288 (deal_type,amount_type,date_type,
2289 deal_number,transaction_number,transaction_date,currency,
2290 amount,hce_amount,amount_date,transaction_rate,
2291 cashflow_amount,company_code,deal_subtype,product_type,
2292 status_code,dealer_code,client_code,cparty_code,settle,
2293 portfolio_code,QUICK_INPUT)
2294 values('BDO','N/A','SETTLE',
2295 P_DEAL_NO,1,trunc(sysdate),P_CURRENCY,
2296 0,0,trunc(sysdate),0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
2297 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
2298 P_CLIENT_CODE,P_CPARTY_CODE,'N',
2299 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
2300 end if;
2301 close CHK_BDO_SETTLE_ROWS;
2302 end if;
2303 --
2304 end if;
2305 --
2306 elsif P_DEAL_TYPE = 'FUT' then
2307 update XTR_DEAL_DATE_AMOUNTS
2308 set amount = P_START_AMOUNT,
2309 hce_amount = P_START_HCE_AMOUNT,
2310 transaction_rate = nvl(P_TRANSACTION_RATE,P_CONTRACT_RATE),
2311 amount_date = P_EXPIRY_DATE,
2312 limit_code = nvl(P_LIMIT_CODE,'NILL'),
2313 transaction_date = P_DEAL_DATE,
2314 currency = P_CURRENCY_BUY,
2315 company_code = P_COMPANY_CODE,
2316 deal_subtype = P_DEAL_SUBTYPE,
2317 product_type = P_PRODUCT_TYPE,
2318 status_code = P_STATUS_CODE,
2319 dealer_code = P_DEALER_CODE,
2320 client_code = P_CLIENT_CODE,
2321 cparty_code = P_CPARTY_CODE,
2322 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2323 limit_party = P_CPARTY_CODE,
2324 contract_code = P_BOND_ISSUE
2325 where deal_number = P_DEAL_NO
2326 and deal_type = 'FUT'
2327 and amount_type = 'FACEVAL';
2328 --
2329 update XTR_DEAL_DATE_AMOUNTS
2330 set amount = nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0),
2331 hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2332 cashflow_amount = -(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
2333 amount_date = P_PREMIUM_DATE,
2334 transaction_date = P_DEAL_DATE,
2335 currency = P_PREMIUM_CURRENCY,
2336 company_code = P_COMPANY_CODE,
2337 deal_subtype = P_DEAL_SUBTYPE,
2338 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2339 cparty_account_no = nvl(v_cparty_account_no, cparty_account_no),
2340 product_type = P_PRODUCT_TYPE,
2341 status_code = P_STATUS_CODE,
2342 dealer_code = P_DEALER_CODE,
2343 client_code = P_CLIENT_CODE,
2344 cparty_code = P_CPARTY_CODE,
2345 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2346 contract_code = P_BOND_ISSUE
2347 where deal_number = P_DEAL_NO
2348 and deal_type = 'FUT'
2349 and amount_type = 'PREMIUM';
2350 --
2351 if SQL%NOTFOUND and nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) > 0 then
2352 -- Settlement Amount
2353 insert into XTR_DEAL_DATE_AMOUNTS
2354 (deal_type,amount_type,date_type,
2355 deal_number,transaction_number,transaction_date,currency,
2356 amount,hce_amount,amount_date,transaction_rate,
2357 cashflow_amount,company_code,account_no,action_code,
2358 cparty_account_no,deal_subtype,product_type,status_code,
2359 dealer_code,client_code,cparty_code,settle,
2360 portfolio_code,contract_code)
2361 values('FUT','PREMIUM','SETTLE',
2362 P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
2363 nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0) ,nvl(P_PREMIUM_HCE_AMOUNT,0),
2364 P_PREMIUM_DATE,0,-(1) * (nvl(P_PREMIUM_AMOUNT,0)+nvl(P_CONTRACT_COMMISSION,0)+nvl(P_CONTRACT_FEES,0)),
2365 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,'PAY',P_CPARTY_ACCOUNT_NO,P_DEAL_SUBTYPE,P_PRODUCT_TYPE,
2366 'CURRENT',P_DEALER_CODE,P_CLIENT_CODE,
2367 P_CPARTY_CODE,'N',nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
2368 end if;
2369 --
2370 update XTR_DEAL_DATE_AMOUNTS
2371 set amount_date = P_DEAL_DATE,
2372 transaction_date = P_DEAL_DATE,
2373 currency = P_PREMIUM_CURRENCY,
2374 company_code = P_COMPANY_CODE,
2375 deal_subtype = P_DEAL_SUBTYPE,
2376 product_type = P_PRODUCT_TYPE,
2377 status_code = P_STATUS_CODE,
2378 dealer_code = P_DEALER_CODE,
2379 client_code = P_CLIENT_CODE,
2380 cparty_code = P_CPARTY_CODE,
2381 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2382 where deal_number = P_DEAL_NO
2383 and deal_type = 'FUT'
2384 and amount_type = 'DEALT';
2385 --
2386 if P_SETTLE_ACTION is NOT NULL then
2387 update XTR_DEAL_DATE_AMOUNTS
2388 set amount = P_SETTLE_AMOUNT,
2389 hce_amount = P_SETTLE_HCE_AMOUNT,
2390 amount_date = P_SETTLE_DATE,
2391 cashflow_amount =
2392 decode(P_SETTLE_ACTION,'PAY',(-1),1) * P_SETTLE_AMOUNT,
2393 transaction_rate = nvl(P_BASE_RATE,P_SETTLE_RATE),
2394 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2395 cparty_account_no = decode(P_SETTLE_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),NULL),
2396 action_code = P_SETTLE_ACTION,
2397 currency = P_PREMIUM_CURRENCY,
2398 company_code = P_COMPANY_CODE,
2399 deal_subtype = P_DEAL_SUBTYPE,
2400 product_type = P_PRODUCT_TYPE,
2401 status_code = P_STATUS_CODE,
2402 dealer_code = P_DEALER_CODE,
2403 client_code = P_CLIENT_CODE,
2404 cparty_code = P_CPARTY_CODE,
2405 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2406 contract_code = P_BOND_ISSUE
2407 where deal_number = P_DEAL_NO
2408 and deal_type = 'FUT'
2409 and amount_type = 'SETTLE';
2410 if SQL%NOTFOUND then
2411 insert into XTR_DEAL_DATE_AMOUNTS
2412 (deal_type,amount_type,date_type,
2413 deal_number,transaction_number,transaction_date,currency,
2414 amount,hce_amount,amount_date,transaction_rate,
2415 cashflow_amount,company_code,account_no,action_code,
2416 cparty_account_no,deal_subtype,product_type,status_code,
2417 dealer_code,client_code,cparty_code,settle,
2418 portfolio_code,contract_code)
2419 values('FUT','SETTLE','SETTLE',
2420 P_DEAL_NO,1,P_DEAL_DATE,P_PREMIUM_CURRENCY,
2421 nvl(P_SETTLE_AMOUNT,0),nvl(P_SETTLE_HCE_AMOUNT,0),
2422 P_SETTLE_DATE,
2423 nvl(P_BASE_RATE,P_SETTLE_RATE),decode(P_SETTLE_ACTION,'PAY',-(1),1) * nvl(P_SETTLE_AMOUNT,0),
2424 P_COMPANY_CODE,P_SETTLE_ACCOUNT_NO,P_SETTLE_ACTION,
2425 decode(P_SETTLE_ACTION,'PAY',P_CPARTY_ACCOUNT_NO,NULL),
2426 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,P_STATUS_CODE,
2427 P_DEALER_CODE,P_CLIENT_CODE,P_CPARTY_CODE,'N',
2428 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_BOND_ISSUE);
2429 end if;
2430 --
2431 update XTR_DEAL_DATE_AMOUNTS
2432 set limit_code = NULL
2433 where deal_number = P_DEAL_NO
2434 and limit_code is NOT NULL;
2435 end if;
2436 --
2437 elsif P_DEAL_TYPE = 'SWPTN' then
2438 update XTR_DEAL_DATE_AMOUNTS
2439 set amount_date = decode(date_type,'DEALT',P_DEAL_DATE,
2440 'COMENCE',P_START_DATE,
2441 'MATURE',P_MATURITY_DATE,amount_date),
2442 amount = decode(amount_type,'N/A',0,
2443 'FACEVAL',P_FACE_VALUE_AMOUNT,amount),
2444 hce_amount = decode(amount_type,'N/A',0,
2445 'FACEVAL',P_FACE_VALUE_HCE_AMOUNT,hce_amount),
2446 transaction_date = P_DEAL_DATE,
2447 transaction_rate = P_INTEREST_RATE,
2448 company_code = P_COMPANY_CODE,
2449 deal_subtype = P_DEAL_SUBTYPE,
2450 product_type = P_PRODUCT_TYPE,
2451 status_code = P_STATUS_CODE,
2452 dealer_code = P_DEALER_CODE,
2453 client_code = P_CLIENT_CODE,
2454 cparty_code = P_CPARTY_CODE,
2455 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2456 where deal_number = P_DEAL_NO
2457 and deal_type = 'SWPTN'
2458 and date_type not in ('EXPIRY','PREMIUM','SETTLE');
2459 --
2460 update XTR_DEAL_DATE_AMOUNTS
2461 set amount_date = nvl(P_PREMIUM_DATE,P_START_DATE),
2462 amount = nvl(P_PREMIUM_AMOUNT,0),
2463 hce_amount = nvl(P_PREMIUM_HCE_AMOUNT,0),
2464 cashflow_amount = decode(P_PREMIUM_ACTION,'PAY',-(1),1)*nvl(P_PREMIUM_AMOUNT,0),
2465 currency = P_CURRENCY_BUY,
2466 transaction_date = P_DEAL_DATE,
2467 transaction_rate = P_INTEREST_RATE,
2468 company_code = P_COMPANY_CODE,
2469 deal_subtype = P_DEAL_SUBTYPE,
2470 product_type = P_PRODUCT_TYPE,
2471 status_code = P_STATUS_CODE,
2472 dealer_code = P_DEALER_CODE,
2473 client_code = P_CLIENT_CODE,
2474 cparty_code = P_CPARTY_CODE,
2475 action_code = P_PREMIUM_ACTION,
2476 account_no = nvl(v_PREMIUM_ACCOUNT_NO, account_no),
2477 cparty_account_no= decode(P_PREMIUM_ACTION,'PAY',nvl(v_cparty_account_no, cparty_account_no),''),
2478 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2479 where deal_number = P_DEAL_NO
2480 and deal_type = 'SWPTN'
2481 and date_type = 'PREMIUM';
2482 --
2483 update XTR_DEAL_DATE_AMOUNTS
2484 set amount = P_FACE_VALUE_AMOUNT,
2485 hce_amount = P_FACE_VALUE_HCE_AMOUNT,
2486 amount_date = P_EXPIRY_DATE,
2487 transaction_rate = P_INTEREST_RATE,
2488 status_code = P_STATUS_CODE,
2489 transaction_date = P_DEAL_DATE,
2490 currency = P_CURRENCY,
2491 company_code = P_COMPANY_CODE,
2492 deal_subtype = P_DEAL_SUBTYPE,
2493 product_type = P_PRODUCT_TYPE,
2494 dealer_code = P_DEALER_CODE,
2495 client_code = P_CLIENT_CODE,
2496 cparty_code = P_CPARTY_CODE,
2497 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2498 where deal_number = P_DEAL_NO
2499 and deal_type = 'SWPTN'
2500 and date_type = 'EXPIRY';
2501 --
2502 if P_STATUS_CODE = 'EXERCISED' then
2503 xtr_fps2_p.standing_settlements(P_CPARTY_CODE,P_CURRENCY,'SWPTN',
2504 P_DEAL_SUBTYPE,P_PRODUCT_TYPE,'SETTLE',
2505 v_settle_ref,v_settle_ac);
2506 -- AW Bug 894751
2507 delete from XTR_DEAL_DATE_AMOUNTS
2508 where deal_number = P_DEAL_NO
2509 and deal_type = 'SWPTN'
2510 and date_type = 'EXPIRY';
2511 --
2512 if P_SETTLE_RATE is not null then
2513 open C_LIMIT_WEIGHTING(P_DEAL_TYPE, P_DEAL_SUBTYPE);
2514 fetch C_LIMIT_WEIGHTING into v_weighting;
2515 close C_LIMIT_WEIGHTING;
2516 update XTR_DEAL_DATE_AMOUNTS
2517 set amount = (100/v_weighting*nvl(P_SETTLE_AMOUNT,0)),
2518 hce_amount = (100/v_weighting*nvl(P_SETTLE_HCE_AMOUNT,0)),
2519 amount_date = P_SETTLE_DATE,
2520 status_code = P_STATUS_CODE,
2521 currency = P_CURRENCY,
2522 company_code = P_COMPANY_CODE,
2523 deal_subtype = P_DEAL_SUBTYPE,
2524 product_type = P_PRODUCT_TYPE,
2525 dealer_code = P_DEALER_CODE,
2526 client_code = P_CLIENT_CODE,
2527 cparty_code = P_CPARTY_CODE,
2528 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL'),
2529 limit_code = nvl(P_LIMIT_CODE,'NILL'),
2530 limit_party = P_CPARTY_CODE
2531 where deal_number = P_DEAL_NO
2532 and deal_type = 'SWPTN'
2533 and date_type = 'LIMIT';
2534 --
2535 update XTR_DEAL_DATE_AMOUNTS
2536 set amount = nvl(P_SETTLE_AMOUNT,0),
2537 hce_amount = nvl(P_SETTLE_HCE_AMOUNT,0),
2538 amount_date = P_SETTLE_DATE,
2539 cashflow_amount = decode(P_SETTLE_ACTION,'PAY',(-1),1) * nvl(P_SETTLE_AMOUNT,0),
2540 transaction_rate = P_SETTLE_RATE,
2541 account_no = nvl(v_SETTLE_ACCOUNT_NO, account_no),
2542 --Bug 3060946 Removed assignement to cparty_account_no
2543 status_code = P_STATUS_CODE,
2544 currency = P_CURRENCY,
2545 company_code = P_COMPANY_CODE,
2546 deal_subtype = P_DEAL_SUBTYPE,
2547 product_type = P_PRODUCT_TYPE,
2548 dealer_code = P_DEALER_CODE,
2549 client_code = P_CLIENT_CODE,
2550 cparty_code = P_CPARTY_CODE,
2551 portfolio_code = nvl(P_PORTFOLIO_CODE,'NOTAPPL')
2552 where deal_number = P_DEAL_NO
2553 and deal_type = 'SWPTN'
2554 and date_type = 'SETTLE';
2555 --
2556 --Bug 3060946 Removed 'insert into XTR_DEAL_DATE_AMOUNTS' statement
2557 --
2558 else -- AW Bug 894751
2559 -- This is for creating swap.
2560 -- JT bug 1312363 while validating after exercise the insert was giving
2561 -- duplicate row error a select is added to check whether the row is already
2562 -- existing or not. if no data found then insert the record
2563
2564 open CHK_SWPTN_SETTLE_ROWS;
2565 fetch CHK_SWPTN_SETTLE_ROWS into l_dummy;
2566
2567 if CHK_SWPTN_SETTLE_ROWS%NOTFOUND then
2568 insert into XTR_DEAL_DATE_AMOUNTS
2569 (deal_type,amount_type,date_type,
2570 deal_number,transaction_number,transaction_date,currency,
2571 amount,hce_amount,amount_date,transaction_rate,
2572 cashflow_amount,company_code,deal_subtype,product_type,
2573 status_code,dealer_code,client_code,cparty_code,settle,
2574 portfolio_code,QUICK_INPUT)
2575 values('SWPTN','N/A','SETTLE',
2576 P_DEAL_NO,1,trunc(sysdate),P_CURRENCY,
2577 0,0,trunc(sysdate),0,0,P_COMPANY_CODE,P_DEAL_SUBTYPE,
2578 P_PRODUCT_TYPE,P_STATUS_CODE,P_DEALER_CODE,
2579 P_CLIENT_CODE,P_CPARTY_CODE,'N',
2580 nvl(P_PORTFOLIO_CODE,'NOTAPPL'),P_QUICK_INPUT);
2581 end if;
2582 close CHK_SWPTN_SETTLE_ROWS;
2583 end if;
2584 --
2585 update XTR_DEAL_DATE_AMOUNTS
2586 set limit_code = NULL,
2587 status_code = P_STATUS_CODE
2588 where deal_number = P_DEAL_NO
2589 and limit_code is not null
2590 and date_type <> 'LIMIT';
2591 end if;
2592 end if;
2593 end if;
2594 end if;
2595 end if;
2596 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
2597 xtr_debug_pkg.debug('After MAINTAIN_DDA_PROC on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
2598 END IF;
2599
2600 end MAINTAIN_DDA_PROC;
2601
2602
2603 END XTR_MAINTAIN_DDA_P;