1 PACKAGE BODY XTR_MISC_P AS
2 /* $Header: xtrprc3b.pls 120.11 2010/08/27 23:56:54 nipant ship $ */
3
4
5 PROCEDURE FPS_AUDIT(p_audit_requested_by IN VARCHAR2,
6 p_event_name IN VARCHAR2,
7 p_date_from IN VARCHAR2,
8 p_date_to IN VARCHAR2) is
9 --
10 -- Purpose: Populate AUDIT_SUMMARY table with audit
11 -- information according to parameters.
12 -- This Procedure is called from form PRO1006 (Audit Summary)
13 -- The user specifies in this form the tables they want to see audit history on (1 or more)
14 -- This Procedure is then called for each event name they want to look at
15 -- any output(ie changed values) are inserted int the table AUDIT SUMMARY for the user requested by
16 -- and the date/time - the form then retireves all records for that user and date/time
17
18
19 -- KEY PROCESS POINTS
20 -- The process below will
21 -- 1. Build a cursor (using dbms_sql.parse,dbms_sql.define_column packages etc) comprising the following
22 -- - table name for the event passed in (refer cursor c_get_table_name below)
23 -- - all columns for the event passed in (refer cursor c_get_columns below)
24 -- Therefore we end up building a cursor for the required table and columns as setup in PRO0095
25 --
26 -- 2. The records from that table and columns will be fetched by the constructed cursor
27 -- - firstly from the AUDIT table for the above table in date asc order
28 -- - lastly from the actual table (ie fetch the current record)
29
30 -- - the fetch order is important as we need to fetch the records in date order as they were changed
31 -- - so as to see changes in data as they occurred from one record to the next
32
33 -- 3. For each record fetched we compare the old with the new values and if they are different
34 -- for that column we insert the old and new values into the table AUDIT_SUMMARY
35
36
37 v6 constant integer := 0;
38 native constant integer := 1;
39 v7 constant integer := 2;
40 V_MAX_COL constant integer := 50; -- Maximum number of columns which can be audited.
41 v_counter binary_integer;
42 v_table_column xtr_audit_columns_v.table_column%TYPE;
43 v_select varchar2(1000);
44 v_num_col integer; -- The number of columns for this audit.
45 v_sql varchar2(2000);
46 v_rec_num binary_integer := 1;
47 v_key_the_same varchar2(1);
48 v_reference_code varchar2(50);
49 v_table_name varchar2(50);
50 v_audit_table_name varchar2(50);
51 ex_error exception;
52 v_cursor binary_integer;
53 v_rows_processed binary_integer;
54 v_old_letter varchar2(1);
55 v_old_updated_on date;
56 v_old_updated_by varchar2(30);
57 v_new_letter varchar2(1);
58 v_new_updated_on date;
59 v_new_updated_by varchar2(30);
60 v_var1 varchar2(100); -- This holds the audited columns (one by one).
61 --
62 TYPE t_col_title IS TABLE OF VARCHAR2(50)
63 INDEX BY BINARY_INTEGER;
64 TYPE t_col_type IS TABLE OF VARCHAR2(15)
65 INDEX BY BINARY_INTEGER;
66 TYPE t_col_pkey IS TABLE OF VARCHAR2(1)
67 INDEX BY BINARY_INTEGER;
68 --
69 TYPE t_old IS TABLE OF VARCHAR2(100)
70 INDEX BY BINARY_INTEGER;
71 --
72 TYPE t_new IS TABLE OF VARCHAR2(100)
73 INDEX BY BINARY_INTEGER;
74 --
75 v_col_title t_col_title;
76 v_col_type t_col_type;
77 v_col_pkey t_col_pkey;
78 v_old t_old; -- Holds old fetched columns
79 v_new t_new; -- Holds newly fetched records columns
80 --
81 cursor c_get_table_name (pc_event varchar2) is
82 select table_name,'XTR_A_'||substr(table_name,5)
83 from XTR_SETUP_AUDIT_REQMTS
84 where event = pc_event;
85 --
86 cursor c_get_columns ( pc_event varchar2 )is
87 select table_column, column_title, upper(nvl(p_key_yn,'N')), upper(column_type)
88 from XTR_AUDIT_COLUMNS
89 where event = pc_event
90 and ( nvl(audit_yn, 'N') = 'Y' or
91 nvl(P_KEY_YN, 'N') = 'Y') ;
92 --
93 begin
94 --
95 -- Get the table name for audit
96 open c_get_table_name(p_event_name);
97 fetch c_get_table_name into v_table_name,v_audit_table_name;
98 IF c_get_table_name%NOTFOUND THEN
99 close c_get_table_name;
100 raise ex_error;
101 ELSE
102 close c_get_table_name;
103 END IF;
104 --
105 -- Make select string ...
106 --
107 open c_get_columns ( p_event_name );
108 --
109 v_select := 'nvl(UPDATED_ON,to_date(''01/01/1900'',''DD/MM/YYYY'')),UPDATED_BY';
110 v_counter := 1;
111 --
112 LOOP
113 EXIT WHEN v_counter > V_MAX_COL;
114 fetch c_get_columns into v_table_column, v_col_title( v_counter),
115 v_col_pkey( v_counter), v_col_type( v_counter);
116 EXIT WHEN c_get_columns%NOTFOUND;
117 IF substr(v_col_type(v_counter),1,4) = 'DATE' THEN
118 v_select := v_select || ', to_char('||v_table_column||',''DD/MM/YYYY HH24:MI:SS'')';
119 ELSIF substr(v_col_type(v_counter),1,4) in ('CHAR','VARC') THEN
120 v_select := v_select || ',' || v_table_column;
121 ELSE
122 v_select := v_select || ',to_char(' || v_table_column || ')';
123 END IF;
124 --
125 v_counter := v_counter + 1;
126 END LOOP;
127 close c_get_columns;
128 v_num_col := v_counter -1; -- The number of AUDITED columns. *****
129 --
130 -- Put all of SQL statement together (ie select + where clause)
131 --
132 v_sql := 'select ''B'',' || v_select || ' FROM '||v_table_name||' '||
133 'WHERE (updated_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
134 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS''))';
135 v_sql := v_sql || ' union ' || 'select ''A'',' || v_select ||' from '||v_audit_table_name||' '||
136 'WHERE (updated_on between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
137 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) OR '||
138 '(audit_date_stored between to_date('''|| p_date_from ||''',''DD/MM/YYYY HH24:MI:SS'') '||
139 'and to_date('''|| p_date_to ||''',''DD/MM/YYYY HH24:MI:SS'')) '||'order by ';
140 -- Add the primary key column/s to the SORT BY clause
141 FOR v_counter IN 1..v_num_col LOOP
142 IF v_col_pkey(v_counter) = 'Y' THEN
143 v_sql := v_sql || to_char(v_counter+3)||',';
144 END IF;
145 END LOOP;
146 v_sql := v_sql||'1,2,3'; -- Add B/A, "updated_on, updated_by" to SORT BY clause
147 --
148 -- Now set up dbms_sql cursor
149 --
150 v_cursor := dbms_sql.open_cursor;
151 dbms_sql.parse(v_cursor,v_sql,native);
152 --
153 dbms_sql.define_column(v_cursor,1,v_new_letter, 1);
154 dbms_sql.define_column(v_cursor,2,v_new_updated_on);
155 dbms_sql.define_column(v_cursor,3,v_new_updated_by,30);
156 -- Its weird how come this next bit works !!??
157 FOR v_counter IN 1..v_num_col LOOP
158 dbms_sql.define_column( v_cursor, v_counter+3, v_var1, 100);
159 END LOOP;
160 --
161 v_rows_processed := dbms_sql.execute( v_cursor );
162 --
163 -- Now loop through records in cursor. This is the **** MAIN LOOP ******
164 --
165 v_rec_num := 1;
166 LOOP
167 EXIT WHEN dbms_sql.fetch_rows(v_cursor) < 1; -- Exit when no more rows
168 dbms_sql.column_value(v_cursor,1,v_new_letter);
169 dbms_sql.column_value(v_cursor,2,v_new_updated_on);
170 dbms_sql.column_value(v_cursor,3,v_new_updated_by);
171 FOR v_counter IN 1..v_num_col LOOP
172 -- Place fetched column into v_new array
173 dbms_sql.column_value(v_cursor,v_counter + 3,v_var1);
174 v_new(v_counter) := v_var1;
175 END LOOP;
176 --
177 -- Analyse differences (if this is not the first record fetched) ...
178 --
179 IF v_rec_num > 1 THEN
180 --
181 -- See if primary keys MATCH
182 --
183 v_key_the_same := 'Y';
184 v_reference_code := null;
185 FOR v_counter IN 1..v_num_col LOOP
186 IF v_col_pkey(v_counter) = 'Y' THEN
187 IF nvl(v_old(v_counter),'JJ') = nvl(v_new(v_counter),'JJ') THEN
188 IF v_reference_code is not null THEN
189 v_reference_code := v_reference_code||'|';
190 END IF;
191 v_reference_code := v_reference_code||rtrim(v_new( v_counter ));
192 ELSE
193 v_key_the_same := 'N';
194 END IF;
195 END IF;
196 END LOOP;
197 --
198 if v_key_the_same = 'Y' then
199 --
200 -- Insert any differences between individual columns
201 --
202 FOR v_counter IN 1..v_num_col LOOP
203 IF nvl(v_old(v_counter),'JJ') <> nvl(v_new(v_counter),'JJ') THEN
204 insert into XTR_AUDIT_SUMMARY
205 (AUDIT_REQUESTED_BY,AUDIT_REQUESTED_ON,
206 AUDIT_RECORDS_FROM,AUDIT_RECORDS_TO,
207 NAME_OF_COLUMN_CHANGED,TABLE_NAME,
208 REFERENCE_CODE,UPDATED_ON_DATE,
209 UPDATED_BY_USER,OLD_VALUE,NEW_VALUE,TRANSACTION_REF,
210 NON_TRANSACTION_REF)
211 values
212 (p_audit_requested_by,sysdate,
213 to_date(p_date_from,'DD/MM/YYYY HH24:MI:SS'),
214 to_date(p_date_to,'DD/MM/YYYY HH24:MI:SS'),
215 rtrim(v_col_title(v_counter)),upper(v_table_name),
216 rtrim(substr(v_reference_code,1,20)),
217 to_char(v_new_updated_on,'DD/MM/YYYY HH24:MI:SS'),
218 substr(v_new_updated_by,1,10),rtrim(substr(v_old(v_counter),1,255)),
219 rtrim(substr(v_new(v_counter),1,255)),null,v_new_letter);
220 END IF;
221 END LOOP;
222 end if;
223 END IF;
224 --
225 -- Store all "new" column values into "old"
226 --
227 v_old_letter := v_new_letter;
228 v_old_updated_on := v_new_updated_on;
229 v_old_updated_by := v_new_updated_by;
230 --
231 FOR v_counter in 1..v_num_col LOOP
232 v_old(v_counter) := v_new(v_counter);
233 END LOOP;
234 v_rec_num := v_rec_num + 1;
235 END LOOP;
236 dbms_sql.close_cursor(v_cursor);
237 --
238 exception
239 when ex_error then
240 null;
241 end FPS_AUDIT;
242
243
244
245 -- Procedure to Maintain Language (Called from each form)
246 PROCEDURE MAINTAIN_LANGUAGE(l_form IN VARCHAR2,
247 l_item IN VARCHAR2,
248 l_old_val IN VARCHAR2,
249 l_new_val IN VARCHAR2,
250 l_option IN VARCHAR2,
251 l_language IN VARCHAR2,
252 l_original_text IN VARCHAR2) is -- 3424625 Added new parameter
253 --
254 l_module VARCHAR2(20);
255 v_rowid VARCHAR2(100);
256 v_module_name XTR_SYS_LANGUAGES.MODULE_NAME%type;
257 v_canvas_type XTR_SYS_LANGUAGES.CANVAS_TYPE%type :='TEXT';
258 v_item_name XTR_SYS_LANGUAGES.ITEM_NAME%type;
259
260 --
261 -- Create cursor to fetch all the rows that satisfies the given criteria
262 -- bug 3424625 modified cursor lang_cursor
263
264 cursor lang_cursor
265 (v_module_name VARCHAR2,
266 v_canvas_type VARCHAR2,
267 v_language VARCHAR2,
268 v_text VARCHAR2,
269 v_original_text VARCHAR2) IS
270 select tl.MODULE_NAME, tl.CANVAS_TYPE, tl.ITEM_NAME
271 from XTR_SYS_LANGUAGES_TL tl , xtr_sys_languages sl
272 where tl.MODULE_NAME like v_module_name
273 and tl.CANVAS_TYPE = v_canvas_type
274 and tl.LANGUAGE = v_language
275 and tl.TEXT = v_text
276 and tl.canvas_type = sl.canvas_type
277 and tl.module_name = sl.module_name
278 and sl.ORIGINAL_TEXT = v_original_text
279 and tl.item_name =sl.item_name;
280
281
282 /* select tl.MODULE_NAME, tl.CANVAS_TYPE, tl.ITEM_NAME
283 from XTR_SYS_LANGUAGES_TL tl
284 where tl.MODULE_NAME like v_module_name
285 and tl.CANVAS_TYPE = v_canvas_type
286 and tl.LANGUAGE = v_language
287 and tl.TEXT = v_text; */
288
289 --
290 l_cnt number :=0;
291 l_rowid varchar2(30);
292 l_orginal_text varchar2(100);
293 begin
294 if l_option = 'O' then
295 l_module := l_form;
296 else
297 l_module := '%';
298 end if;
299
300 --
301 -- Only rows of canvas_type = 'TEXT' are ever updated
302 --
303 open lang_cursor(l_module, 'TEXT', l_language, l_old_val, l_original_text);
304 loop
305 fetch lang_cursor into v_module_name, v_canvas_type, v_item_name;
306 exit when lang_cursor%notfound or lang_cursor%notfound is null;
307 --
308 -- Update row based on rowid
309 --
310 XTR_SYS_LANGUAGES_PKG.Update_Row
311 (X_MODULE_NAME => v_module_name,
312 X_CANVAS_TYPE => v_canvas_type,
313 X_ITEM_NAME => v_item_name,
314 X_ORIGINAL_TEXT => null,
315 X_TEXT => l_new_val,
316 X_LAST_UPDATE_DATE => sysdate,
317 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
318 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
319 l_cnt :=1;
320
321 end loop; -- lang_cursor
322 if l_cnt =0 then
323 l_orginal_text :=initcap(replace(l_item,'_',' '));
324 XTR_SYS_LANGUAGES_PKG.Insert_Row
325 (X_ROWID => l_rowid,
326 X_MODULE_NAME => l_form,
327 X_CANVAS_TYPE => 'TEXT',
328 X_ITEM_NAME => l_item,
329 X_ORIGINAL_TEXT => l_orginal_text,
330 X_TEXT => l_new_val,
331 X_CREATION_DATE => sysdate,
332 X_CREATED_BY => FND_GLOBAL.USER_ID,
333 X_LAST_UPDATE_DATE => sysdate,
334 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
335 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
336 end if;
337
338
339 --
340 -- Commit work
341 --
342 commit;
343
344 end MAINTAIN_LANGUAGE;
345
346
347
348 /* deleted Treasury_Archive to fix bug # 1855372
349 */
350
351
352 PROCEDURE DEAL_ACTIONS(p_deal_type IN VARCHAR2,
353 p_deal_number IN NUMBER,
354 p_transaction_number IN NUMBER,
355 p_action_type IN VARCHAR2,
356 p_cparty_code IN VARCHAR2,
357 p_client_code IN VARCHAR2,
358 p_date_created IN DATE,
359 p_company_code IN VARCHAR2,
360 p_status_code IN VARCHAR2,
361 p_file_name IN VARCHAR2,
362 p_deal_subtype IN VARCHAR2,
363 p_currency IN VARCHAR2,
364 p_cparty_advice IN VARCHAR2,
365 p_client_advice IN VARCHAR2,
366 p_amount IN NUMBER,
367 p_org_flag IN VARCHAR2) is
368 --
369 -- Purpose: Populate CONFIRMATION_DETAILS table with action codes
370 -- (used in Forms PRO1011 - Confirmations and PRO1012 - Dual Validation).
371 --
372 cursor CHK_CONFO_REQD(l_party_code varchar2) is
373 select 1
374 from XTR_PARTIES_V a,
375 XTR_CONFIRMATION_ACTIONS b
376 where a.party_code= l_party_code
377 and a.confo_group_code = b. confo_action_group
378 and b.action_type = p_action_type
379 and b.confo_reqd = 'Y';
380 --
381 cursor VALIDATE_ABOVE_AMT is
382 select nvl(to_number(PARAM_VALUE),0)
383 from XTR_PRO_PARAM
384 where upper(PARAM_NAME) = 'VALIDATE ABOVE AMNT';
385 --
386 cursor HCE is
387 select p_amount / hce_rate
388 from XTR_MASTER_CURRENCIES
389 where CURRENCY = p_currency;
390
391 -- bug 3800146 IAC -Redesign Project Added lines
392 cursor iac_validated is
393 select dual_authorisation_by, dual_authorisation_on
394 from xtr_deal_date_amounts
395 where transaction_number=p_transaction_number
396 and deal_number = 0 -- bug 4957910
397 and dual_authorisation_by is not null
398 and deal_type='IAC';
399 -- bug 3800146 IAC -Redesign Project Added lines
400
401
402
403 --
404 l_hce_amount NUMBER;
405 l_above_amt NUMBER := 0;
406 l_dumy_num NUMBER;
407 confirmed_by VARCHAR2(10);
408 confirmed_on DATE;
409 l_amount NUMBER;
410 --
411 begin
412 /* List of Confirmation Types that will be passed to this Procedure
413 if p_deal_type = 'FX' then
414 FX_CONTRACT_SWAP
415 PREDELIVERY_OF_FX_CONTRACT
416 ROLLOVER_OF_FX_CONTRACT
417 NEW_FX_CONTRACT
418 elsif p_deal_type = 'FXO' then
419 NEW_FXO_CONTRACT
420 EXERCISE_OF_FX_OPTION_CONTRACT
421 elsif p_deal_type = 'FRA' then
422 NEW_FRA_CONTRACT
423 SETTLEMENT_OF_FRA_CONTRACT
424 elsif p_deal_type = 'FUT' then
425 New Futures Contract
426 Closeout of Futures Contract
427 elsif p_deal_type = 'IRO' then
428 NEW_IRO_CONTRACT
429 EXERCISE_OF_IRO_CONTRACT
430 NEW_BOND_OPTION_CONTRACT
431 EXERCISE_OF_BOND_OPTION_CONTRACT
432 elsif p_deal_type = 'NI' then
433 NEW_NI_CONTRACT
434 elsif p_deal_type = 'BOND' then
435 NEW_BOND_CONTRACT
436 elsif p_deal_type = 'SWPTN' then
437 NEW_SWAPTION_CONTRACT
438 EXERCISE_OF_SWAPTION_CONTRACT
439 elsif p_deal_type = 'DEB' then
440 New Debenture Contract
441 elsif p_deal_type = 'IRS' then
442 NEW_INT_RATE_SWAP_CONTRACT
443 elsif p_deal_type = 'TMM' then
444 -- Retail
445 NEW_RETAIL_TERM_CONTRACT
446 Change Schedule Type for Retail Term
447 PRINCIPAL_ADJUSTMENT_OF_RETAIL_TERM_CONTRA
448 RETAIL_TERM_INTEREST_RESET
449 -- Wholesale
450 NEW_WHOLESALE_TERM_CONTRACT
451 end if;
452 */
453
454 l_amount:= p_amount;
455
456 --bug 6161318 starts
457 if p_deal_subtype = 'INVEST' and p_action_type = 'NEW_CALL_CONTRACT' then
458 l_amount := l_amount * (-1);
459 end if;
460
461 if p_deal_subtype = 'FUND' and p_action_type = 'CALL_PRINCIPAL_REPAYMENT_RENEG' then
462 l_amount := l_amount * (-1);
463 end if;
464 -- Bug 6161318 ends
465 --
466 -- Fetch the break above level for validation purposes
467 -- ie only validate transactions above this amount
468 open VALIDATE_ABOVE_AMT;
469 fetch VALIDATE_ABOVE_AMT INTO l_above_amt;
470 if VALIDATE_ABOVE_AMT%NOTFOUND then
471 l_above_amt := 0;
472 end if;
473 close VALIDATE_ABOVE_AMT;
474 --
475 -- Convert amount to hce amount for comparison to above break limit
476 open HCE;
477 fetch HCE into l_hce_amount;
478 close HCE;
479 --
480 if ABS(l_hce_amount) < l_above_amt then -- bug 4135644
481 -- Auto confirm inserted row
482 confirmed_by := fnd_global.user_id;
483 confirmed_on := sysdate;
484 else
485 confirmed_by := NULL;
486 confirmed_on := NULL;
487 end if;
488 --
489
490
491 -- bug 3800146 IAC -Redesign Project Added lines
492
493 if p_deal_type = 'IAC' and confirmed_by is null then
494 open iac_validated;
495 fetch iac_validated into confirmed_by, confirmed_on;
496 close iac_validated;
497 end if;
498
499 -- bug 3800146 IAC -Redesign Project Ended lines
500
501 /************************
502
503 if (nvl(p_cparty_advice,'N') = 'N' and nvl(p_client_advice,'N')= 'N') or (nvl(p_org_flag,'~') = 'U') then
504 insert into xtr_confirmation_details(
505 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
506 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,AMOUNT_TYPE,CLIENT_CODE,
507 CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
508 values(p_action_type,p_company_code,NULL,'V',p_cparty_code,trunc(p_date_created),
509 p_deal_number,p_status_code,p_transaction_number,l_amount,NULL,p_client_code,
510 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
511 elsif nvl(p_cparty_advice,'N') = 'Y' and nvl(p_client_advice,'N') = 'N' then
512 --
513 open chk_confo_reqd(p_cparty_code );
514 fetch chk_confo_reqd into l_dumy_num;
515 if chk_confo_reqd%NOTFOUND then
516 insert into xtr_confirmation_details(
517 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
518 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,
519 AMOUNT,AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
520 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
521 values( p_action_type,p_company_code, NULL,'V',p_cparty_code,trunc(p_date_created),
522 p_deal_number,p_status_code, p_transaction_number, l_amount,NULL,p_client_code,
523 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
524 else
525 insert into xtr_confirmation_details(
526 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
527 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
528 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
529 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
530 values(p_action_type,p_company_code,p_cparty_code,'B',p_cparty_code,trunc(p_date_created),
531 p_deal_number,p_status_code,p_transaction_number,l_amount,NULL,p_client_code,
532 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
533 end if;
534 close chk_confo_reqd;
535 --
536 elsif nvl(p_cparty_advice,'N') = 'N' and nvl(p_client_advice ,'N') = 'Y' then
537 --
538 open chk_confo_reqd(p_client_code);
539 fetch chk_confo_reqd into l_dumy_num;
540 if chk_confo_reqd%NOTFOUND then
541 insert into xtr_confirmation_details(
542 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
543 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
544 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
545 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
546 values(p_action_type,p_company_code, NULL,'V',p_cparty_code,trunc(p_date_created),
547 p_deal_number,p_status_code,p_transaction_number,l_amount,NULL,p_client_code,
548 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
549 else
550 insert into xtr_confirmation_details(
551 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
552 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
553 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
554 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
555 values(p_action_type,p_company_code,p_client_code,'B',p_cparty_code,trunc(p_date_created),
556 p_deal_number,p_status_code,p_transaction_number,l_amount,NULL,p_client_code,
557 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
558 end if;
559 close chk_confo_reqd;
560 --
561 elsif nvl(p_cparty_advice,'N')='Y' and nvl(p_client_advice ,'N')='Y' then
562 --
563 *****************************/
564
565 open chk_confo_reqd(p_cparty_code);
566 fetch chk_confo_reqd into l_dumy_num;
567 if chk_confo_reqd%NOTFOUND then
568 insert into xtr_confirmation_details(
569 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
570 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
571 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
572 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
573 values(p_action_type,p_company_code,NULL,'V',p_cparty_code,trunc(p_date_created),
574 p_deal_number,p_status_code,p_transaction_number,l_amount,NULL,p_client_code,
575 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
576 else
577 insert into xtr_confirmation_details(
578 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
579 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
580 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
581 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
582 values(p_action_type,p_company_code,p_cparty_code,'B',p_cparty_code,trunc(p_date_created),
583 p_deal_number,p_status_code,p_transaction_number,l_amount,NULL,p_client_code,
584 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
585 end if;
586 close chk_confo_reqd;
587 --
588 open chk_confo_reqd(p_client_code );
589 fetch chk_confo_reqd into l_dumy_num;
590 if chk_confo_reqd%FOUND then
591 insert into xtr_confirmation_details(
592 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
593 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
594 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE)
595 values(p_action_type,p_company_code,p_client_code,'C',p_cparty_code,trunc(p_date_created),
596 p_deal_number,p_status_code,p_transaction_number,l_amount,NULL,p_client_code,
597 p_currency,p_deal_subtype,p_deal_type);
598 end if;
599 close chk_confo_reqd;
600 --
601 -- end if;
602 end DEAL_ACTIONS;
603
604
605
606 -- Procedure to Calculate Fixed V's Floating Details (PRO1108) + Int Rate Bands
607 procedure INS_ACTUALS(
608 p_company_code IN varchar2,
609 p_currency IN varchar2,
610 p_portfolio_code IN varchar2,
611 p_from_date IN date,
612 p_to_date IN date,
613 p_fund_invest IN varchar2,
614 p_amount_unit IN number,
615 p_inc_ig IN varchar2,
616 p_unique_ref_number IN number,
617 p_company_name IN varchar2,
618 p_port_name IN varchar2,
619 p_floating_less IN varchar2) is
620 --
621 cursor get_ca is
622 select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,a.deal_subtype,a.currency,a.transaction_rate,a.amount_date start_date,a.amount_date maturity_date,p_to_date to_date,p_from_date from_date,
623 'FLOAT' FIXED_OR_FLOAT,nvl(a.amount,0) gross_amount,1 no_of_days
624 from XTR_DEAL_DATE_AMOUNTS_V a
625 where a.amount_date >=p_from_date
626 and a.company_code like p_company_code and a.currency like p_currency
627 and a.portfolio_code like p_portfolio_code
628 and a.status_code='CURRENT'
629 and ((a.DEAL_TYPE ='CA' and a.AMOUNT_TYPE='BAL')
630 or (a.DEAL_TYPE ='IG' and a.AMOUNT_TYPE='BAL' and p_inc_ig='Y'))
631 and ( (a.DEAL_SUBTYPE='INVEST' and p_fund_invest='INVEST')
632 or (a.DEAL_SUBTYPE='FUND' and p_fund_invest='FUND')
633 or (p_fund_invest='NONE'));
634 --
635 cursor get_data1 is
636 select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,
637 a.deal_subtype,a.currency,a.transaction_rate,
638 decode(a.deal_type,'IRO',nvl(b.start_date,b.expiry_date),b.start_date) start_date,
639 b.maturity_date,p_to_date to_date,p_from_date from_date,
640 decode(a.deal_type||a.deal_subtype,
641 'IROBCAP','FIXED',
642 'IROBFLOOR','FIXED',
643 'IROSFLOOR','FLOAT',
644 'IROSCAP','FLOAT',
645 'BDOBCAP','FIXED',
646 'BDOBFLOOR','FIXED',
647 'BDOSFLOOR','FLOAT',
648 'BDOSCAP','FLOAT',
649 'SWPTNSELL','FLOAT',
650 'SWPTNBUY','FIXED',
651 'FRAFUND','FIXED',
652 'FRAINVEST','FIXED',
653 decode(sign(p_to_date-b.maturity_date+1),1,'FLOAT','FIXED')) FIXED_OR_FLOAT,
654 decode(a.deal_type,
655 'IRO',nvl(b.maturity_date-nvl(b.start_date,b.expiry_date)+decode(sign(nvl(b.start_date,b.expiry_date)-p_from_date),-1,nvl(b.start_date,b.expiry_date)-p_from_date,0),0),
656 'BDO',nvl(b.maturity_date-nvl(b.start_date,b.expiry_date)+decode(sign(nvl(b.start_date,b.expiry_date)-p_from_date),-1,nvl(b.start_date,b.expiry_date)-p_from_date,0),0),
657 nvl(b.maturity_date-b.start_date+decode(sign(b.start_date-p_from_date),-1,b.start_date-p_from_date,0),0)) no_of_days,
658 nvl(sum(decode(a.deal_type||a.amount_type,
659 'NIBAL_FV',a.amount,
660 'BONDINTL_FV',a.amount,
661 'IROFACEVAL',a.amount,
662 'BDOFACEVAL',a.amount,
663 'SWPTNFACEVAL',a.amount,
664 'FRAFACEVAL',a.amount,0)),0) gross_amount
665 from XTR_DEAL_DATE_AMOUNTS_V a,
666 XTR_DEALS_V b
667 where a.amount_date >= p_from_date
668 and a.currency like p_currency
669 and a.company_code like p_company_code
670 and a.portfolio_code like p_portfolio_code
671 and a.status_code='CURRENT'
672 and a.deal_number = b.deal_no
673 and a.DEAL_TYPE in('NI','SWPTN','FRA','IRO','BOND','BDO')
674 and ((a.DEAL_TYPE in('FRA') AND a.DEAL_SUBTYPE = 'INVEST' and p_fund_invest='INVEST')
675 or (a.DEAL_TYPE in('FRA') AND a.DEAL_SUBTYPE = 'FUND' and p_fund_invest='FUND')
676 or (p_fund_invest = 'NONE')
677 or (a.DEAL_TYPE = 'NI' AND a.DEAL_SUBTYPE in('BUY','COVER') and p_fund_invest='INVEST')
678 or (a.DEAL_TYPE = 'NI' AND a.DEAL_SUBTYPE in('SELL','SHORT','ISSUE') and p_fund_invest = 'FUND')
679 or (a.DEAL_TYPE in('BOND') AND a.DEAL_SUBTYPE='BUY' and p_fund_invest = 'INVEST')
680 or (a.DEAL_TYPE in('BOND') AND a.DEAL_SUBTYPE='SHORT' and p_fund_invest = 'FUND')
681 or (a.DEAL_TYPE in('BOND') AND a.DEAL_SUBTYPE='ISSUE' and p_fund_invest = 'FUND')
682 or (a.DEAL_TYPE in('IRO') AND a.DEAL_SUBTYPE in('BFLOOR','SFLOOR') and p_fund_invest = 'INVEST')
683 or (a.DEAL_TYPE in('IRO') AND a.DEAL_SUBTYPE in('SCAP','BCAP') and p_fund_invest = 'FUND')
684 or (a.DEAL_TYPE in('BDO') AND a.DEAL_SUBTYPE in('BFLOOR','SFLOOR') and p_fund_invest = 'INVEST')
685 or (a.DEAL_TYPE in('BDO') AND a.DEAL_SUBTYPE in('SCAP','BCAP') and p_fund_invest = 'FUND')
686 or (a.DEAL_TYPE in('SWPTN') and b.COUPON_ACTION='PAY' and p_fund_invest='FUND')
687 or (a.DEAL_TYPE in('SWPTN') and b.COUPON_ACTION='REC' and p_fund_invest='INVEST'))
688 having nvl(sum(decode(a.deal_type||a.amount_type,
689 'NIBAL_FV',a.amount,
690 'BONDINTL_FV',a.amount,
691 'IROFACEVAL',a.amount,
692 'BDOFACEVAL',a.amount,
693 'SWPTNFACEVAL',a.amount,
694 'FRAFACEVAL',a.amount,0)),0) >0
695 group by a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,
696 a.deal_subtype,a.currency,a.transaction_rate,
697 decode(a.deal_type,'IRO',nvl(b.start_date,b.expiry_date),b.start_date),b.maturity_date,p_to_date,p_from_date,
698 decode(a.deal_type,'BDO',nvl(b.start_date,b.expiry_date),b.start_date),b.maturity_date,p_to_date,p_from_date,
699 decode(a.deal_type||a.deal_subtype,
700 'IROBCAP','FIXED',
701 'IROBFLOOR','FIXED',
702 'IROSFLOOR','FLOAT',
703 'IROSCAP','FLOAT',
704 'BDOBCAP','FIXED',
705 'BDOBFLOOR','FIXED',
706 'BDOSFLOOR','FLOAT',
707 'BDOSCAP','FLOAT',
708 'SWPTNSELL','FLOAT',
709 'SWPTNBUY','FIXED',
710 'FRAFUND','FIXED',
711 'FRAINVEST','FIXED',
712 decode(sign(p_to_date-b.maturity_date+1),1,'FLOAT','FIXED')),
713 decode(a.deal_type,
714 'IRO',nvl(b.maturity_date-nvl(b.start_date,b.expiry_date)+decode(sign(nvl(b.start_date,b.expiry_date)-p_from_date),-1,nvl(b.start_date,b.expiry_date)-p_from_date,0),0),
715 'BDO',nvl(b.maturity_date-nvl(b.start_date,b.expiry_date)+decode(sign(nvl(b.start_date,b.expiry_date)-p_from_date),-1,nvl(b.start_date,b.expiry_date)-p_from_date,0),0),
716 nvl(b.maturity_date-b.start_date+decode(sign(b.start_date-p_from_date),-1,b.start_date-p_from_date,0),0));
717 --
718 cursor get_fut is
719 select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,
720 a.deal_subtype,a.currency,a.transaction_rate,a.amount_date start_date,a.amount_date maturity_date,p_to_date to_date,p_from_date from_date,
721 'FIXED' FIXED_OR_FLOAT,nvl(a.amount,0) gross_amount,1 no_of_days
722 from XTR_DEAL_DATE_AMOUNTS_V a,
723 XTR_FUTURES b
724 where a.amount_date >=p_from_date
725 and a.company_code like p_company_code
726 and a.currency like p_currency
727 and a.portfolio_code like p_portfolio_code
728 and a.amount_type='FACEVAL'
729 and a.status_code='CURRENT'
730 and a.deal_type='FUT' and ((a.deal_subtype='BUY' and p_fund_invest='INVEST') or
731 (a.deal_subtype = 'SELL' and p_fund_invest = 'FUND') or p_fund_invest='NONE')
732 and a.contract_code = b.contract_code
733 and b.financial_contract = 'F';
734 --
735
736 cursor get_data2 is
737 select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,
738 a.deal_type,a.deal_subtype,a.currency,a.INTEREST_RATE transaction_rate,
739 a.start_date,a.maturity_date,p_to_date to_date,p_from_date from_date,
740 decode(a.deal_type,
741 'TMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),
742 nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
743 decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
744 'RTMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),
745 nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
746 decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
747 'IRS',decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,b.fixed_or_floating_rate,'FLOAT'),
748 decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,'FLOAT','FIXED')) FIXED_OR_FLOAT,
749 nvl(decode(a.deal_type,
750 'TMM',decode(decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
751 'FLOAT',
752 decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
753 nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
754 nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)-
755 (nvl(b.settle_date,b.start_date)-p_to_date)),
756 'FIXED',
757 nvl(b.settle_date,b.start_date)-p_to_date),
758 'RTMM',decode(decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
759 'FLOAT',
760 decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
761 nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
762 nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)-
763 (nvl(b.settle_date,b.start_date)-p_to_date)),
764 'FIXED',
765 nvl(b.settle_date,b.start_date)-p_to_date),
766 'IRS',nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
767 'DEB',nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
768 nvl(a.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)),0) no_of_days,
769 nvl(sum(decode(a.deal_type,
770 'TMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
771 'RTMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
772 'DEB',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
773 a.balance_out)),0) gross_amount
774 from XTR_ROLLOVER_TRANSACTIONS_V a,
775 XTR_DEALS_V b
776 where a.company_code like p_company_code and a.currency like p_currency
777 and a.status_code='CURRENT'
778 and a.portfolio_code like p_portfolio_code
779 and nvl(a.maturity_date,p_from_date+1) >p_from_date
780 and a.start_date <=p_from_date
781 and a.deal_type in('ONC','CMF','IRS','DEB','FX','TMM','RTMM')
782 and ((a.deal_type in('ONC','RTMM','TMM','IRS','CMF','FX') and (a.deal_subtype='INVEST' and p_fund_invest='INVEST'))
783 or (a.deal_type in('ONC','TMM','RTMM','IRS','CMF','FX') and (a.deal_subtype='FUND' and p_fund_invest='FUND'))
784 or (p_fund_invest='NONE')
785 or (a.DEAL_TYPE in('DEB') AND a.DEAL_SUBTYPE='BUY' and p_fund_invest='INVEST')
786 or (a.DEAL_TYPE in('DEB') AND a.DEAL_SUBTYPE='ISSUE' and p_fund_invest='FUND')
787 )
788 and a.deal_number=b.deal_no
789 having nvl(sum(decode(a.deal_type,
790 'TMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
791 'RTMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
792 'DEB',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
793 a.balance_out)),0) >0
794 group by a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,a.deal_subtype,a.currency,a.INTEREST_RATE,a.start_date,a.maturity_date,p_to_date,p_from_date,
795 decode(a.deal_type,
796 'TMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
797 decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
798 'RTMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
799 decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
800 'IRS',decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,b.fixed_or_floating_rate,'FLOAT'),
801 decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,'FLOAT','FIXED')),
802 nvl(decode(a.deal_type,
803 'TMM',decode(decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
804 'FLOAT',
805 decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
806 nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
807 nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)- (nvl(b.settle_date,b.start_date)-p_to_date)),
808 'FIXED',
809 nvl(b.settle_date,b.start_date)-p_to_date),
810 'RTMM',decode(decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
811 'FLOAT',
812 decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
813 nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
814 nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)- (nvl(b.settle_date,b.start_date)-p_to_date)),
815 'FIXED',
816 nvl(b.settle_date,b.start_date)-p_to_date),
817 'IRS',nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
818 'DEB',nvl(b.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0),
819 nvl(a.maturity_date,p_from_date+1)-a.start_date+decode(sign(a.start_date-p_from_date),-1,a.start_date-p_from_date,0)),0);
820
821 l_sysdate date :=sysdate;
822 l_fixed_or_float varchar2(5);
823 l_syn_phy varchar2(1);
824 begin
825 delete from XTR_INTEREST_RATE_EXPOSURE where created_on <sysdate-1;
826 commit;
827 for c in get_ca loop
828 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
829 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
830 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
831 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
832 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
833 c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
834 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N','P');
835 end loop;
836 for c in get_data1 loop
837 if c.deal_type in('ONC','CA','IG','NI','TMM','BOND','DEB','CMF','RTMM') then
838 l_syn_phy :='P';
839 else
840 l_syn_phy :='S';
841 end if;
842 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
843 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
844 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
845 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
846 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
847 c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
848 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N',l_syn_phy);
849 if c.deal_type in('IRS','FRA','SWPTN','IRO','BDO') and nvl(c.gross_amount,0) <>0 then
850 if c.fixed_or_float='FIXED' then
851 l_fixed_or_float :='FLOAT';
852 else
853 l_fixed_or_float :='FIXED';
854 end if;
855 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
856 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
857 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
858 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
859 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
860 l_fixed_or_float,c.FROM_DATE,-c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
861 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'Y',l_syn_phy);
862 end if;
863 end loop;
864 for c in get_fut loop
865 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
866 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
867 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
868 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
869 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
870 c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
871 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N','S');
872 if nvl(c.gross_amount,0) <>0 then
873 if c.fixed_or_float='FIXED' then
874 l_fixed_or_float :='FLOAT';
875 else
876 l_fixed_or_float :='FIXED';
877 end if;
878 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
879 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
880 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
881 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
882 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
883 l_fixed_or_float,c.FROM_DATE,-c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
884 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'Y','S');
885 end if;
886 end loop;
887
888 for c in get_data2 loop
889 if c.deal_type in('ONC','CA','IG','NI','TMM','BOND','DEB','CMF','RTMM') then
890 l_syn_phy :='P';
891 else
892 l_syn_phy :='S';
893 end if;
894 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
895 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
896 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
897 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
898 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
899 c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
900 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N',l_syn_phy);
901 if c.deal_type in('IRS','FRA','SWPTN','IRO') and nvl(c.gross_amount,0) <>0 then
902 if c.fixed_or_float='FIXED' then
903 l_fixed_or_float :='FLOAT';
904 else
905 l_fixed_or_float :='FIXED';
906 end if;
907 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
908 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
909 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
910 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
911 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
912 l_fixed_or_float,c.FROM_DATE,-c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
913 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'Y',l_syn_phy);
914 end if;
915 end loop;
916 commit;
917 END INS_ACTUALS;
918
919
920
921 PROCEDURE VALIDATE_DEALS (p_deal_no IN NUMBER,
922 p_trans_no IN NUMBER,
923 p_deal_type IN VARCHAR2,
924 p_action_type IN VARCHAR2,
925 p_validated_by IN VARCHAR2) is
926 --
927 cursor GET_DATE is
928 select INCREASE_EFFECTIVE_FROM_DATE,FROM_START_DATE,EFFECTIVE_FROM_DATE
929 from XTR_TERM_ACTIONS
930 where DEAL_NO = p_deal_no;
931
932 -- bug #1295341 jhung
933 cursor IRS_OTHER_DEAL is
934 select b.deal_no
935 from xtr_deals_v a, xtr_deals_v b
936 where a.int_swap_ref = b.int_swap_ref
937 and a.deal_type = b.deal_type
938 and a.deal_type = 'IRS'
939 and a.deal_subtype <> b.deal_subtype
940 and a.deal_no = p_deal_no;
941 --
942 c get_date%ROWTYPE;
943 v_cnt NUMBER := 0;
944 u_cnt NUMBER := 0;
945 l_date DATE;
946 receive_deal_no NUMBER;
947 --
948 begin
949 if p_deal_type IN ('FX','FXO') then
950 -- Actions are as follows (and the form they originated from)
951 -- PRO0170 - NEW_FX_CONTRACT
952 -- PRO0190 - FX_CONTRACT_SWAP (FX Contract re Exercise of FX Option)
953 -- PRO0200 - ROLLOVER_OF_FX_CONTRACT
954 -- PRO0200 - PREDELIVERY_OF_FX_CONTRACT
955 -- PRO0190 - NEW_FXO_CONTRACT
956 -- PRO0190 - EXERCISE_OF_FX_OPTION_CONTRACT
957 if p_action_type in('NEW_FX_CONTRACT',
958 'NEW_FXO_CONTRACT',
959 'FX_CONTRACT_SWAP',
960 'ROLLOVER_OF_FX_CONTRACT',
961 'PREDELIVERY_OF_FX_CONTRACT') then
962 update XTR_DEALS
963 set DUAL_AUTHORISATION_BY = p_validated_by,
964 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
965 where DEAL_NO = p_deal_no
966 and DEAL_TYPE = p_deal_type;
967 elsif p_action_type='EXERCISE_OF_FX_OPTION_CONTRACT' then
968 update XTR_DEALS
969 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
970 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
971 where DEAL_NO = p_deal_no
972 and DEAL_TYPE = p_deal_type;
973 end if;
974 elsif p_deal_type ='FRA' then
975 -- Actions are as follows (and the form they originated from)
976 -- PRO0770 - NEW_FRA_CONTRACT
977 -- PRO0770 - SETTLEMENT_OF_FRA_CONTRACT
978 if p_action_type ='NEW_FRA_CONTRACT' then
979 update XTR_DEALS
980 set DUAL_AUTHORISATION_BY = p_validated_by,
981 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
982 where DEAL_NO = p_deal_no
983 and DEAL_TYPE = p_deal_type;
984 elsif p_action_type='SETTLEMENT_OF_FRA_CONTRACT' then
985 update XTR_DEALS
986 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
987 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
988 where DEAL_NO = p_deal_no
989 and DEAL_TYPE = p_deal_type;
990 end if;
991 elsif p_deal_type ='IRO' then
992 -- Actions are as follows (and the form they originated from)
993 -- PRO0270 - NEW_IRO_CONTRACT
994 -- PRO0270 - Settlement of IRO Contract
995 if p_action_type = 'NEW_IRO_CONTRACT' then
996 update XTR_DEALS
997 set DUAL_AUTHORISATION_BY = p_validated_by,
998 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
999 where DEAL_NO = p_deal_no
1000 and DEAL_TYPE = p_deal_type;
1001 elsif p_action_type = 'EXERCISE_OF_IRO_CONTRACT' then
1002 update XTR_DEALS
1003 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
1004 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1005 where DEAL_NO = p_deal_no
1006 and DEAL_TYPE = p_deal_type;
1007 end if;
1008 elsif p_deal_type ='BDO' then
1009 -- Actions are as follows (and the form they originated from)
1010 if p_action_type = 'NEW_BOND_OPTION_CONTRACT' then
1011 update XTR_DEALS
1012 set DUAL_AUTHORISATION_BY = p_validated_by,
1013 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1014 where DEAL_NO = p_deal_no
1015 and DEAL_TYPE = p_deal_type;
1016 elsif p_action_type = 'EXERCISE_OF_BOND_OPTION_CONTRACT' then
1017 update XTR_DEALS
1018 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
1019 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1020 where DEAL_NO = p_deal_no
1021 and DEAL_TYPE = p_deal_type;
1022 end if;
1023 elsif p_deal_type ='NI' then
1024 -- Actions are as follows (and the form they originated from)
1025 -- PRO0240 - NEW_NI_CONTRACT
1026 if p_action_type ='NEW_NI_CONTRACT' then
1027 update XTR_DEALS
1028 set DUAL_AUTHORISATION_BY = p_validated_by,
1029 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1030 where DEAL_NO = p_deal_no
1031 and DEAL_TYPE = p_deal_type;
1032 end if;
1033 elsif p_deal_type ='BOND' then
1034 -- Actions are as follows (and the form they originated from)
1035 -- PRO0280 - NEW_BOND_CONTRACT
1036 if p_action_type ='NEW_BOND_CONTRACT' then
1037 update XTR_DEALS
1038 set DUAL_AUTHORISATION_BY = p_validated_by,
1039 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1040 where DEAL_NO = p_deal_no
1041 and DEAL_TYPE = p_deal_type;
1042 end if;
1043 elsif p_deal_type ='SWPTN' then
1044 -- Actions are as follows (and the form they originated from)
1045 -- PRO0290 - New SWPTN Contract
1046 if p_action_type ='NEW_SWAPTION_CONTRACT' then
1047 update XTR_DEALS
1048 set DUAL_AUTHORISATION_BY = p_validated_by,
1049 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1050 where DEAL_NO = p_deal_no
1051 and DEAL_TYPE = p_deal_type;
1052 elsif p_action_type = 'EXERCISE_OF_SWAPTION_CONTRACT' then
1053 update XTR_DEALS
1054 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
1055 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1056 where DEAL_NO = p_deal_no
1057 and DEAL_TYPE = p_deal_type;
1058 end if;
1059 elsif p_deal_type ='IRS' then
1060 -- Actions are as follows (and the form they originated from)
1061 -- bug #1295341 jhung
1062 if p_action_type ='NEW_INT_RATE_SWAP_CONTRACT' then
1063 open IRS_OTHER_DEAL;
1064 fetch IRS_OTHER_DEAL into receive_deal_no;
1065 close IRS_OTHER_DEAL;
1066
1067 update XTR_DEALS -- Update paying side deal
1068 set DUAL_AUTHORISATION_BY = p_validated_by,
1069 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1070 where DEAL_NO = p_deal_no
1071 and DEAL_TYPE = p_deal_type;
1072 update XTR_DEALS -- Update receiving side deal
1073 set DUAL_AUTHORISATION_BY = p_validated_by,
1074 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1075 where DEAL_NO = receive_deal_no
1076 and DEAL_TYPE = p_deal_type;
1077 end if;
1078 elsif p_deal_type ='TMM' then
1079 ---- Only for NEW_WHOLESALE_TERM_CONTRACT at this stage. Should use TERM_ACTIONS like Retail Term
1080 update XTR_DEALS
1081 set DUAL_AUTHORISATION_BY = p_validated_by,
1082 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1083 where DEAL_NO = p_deal_no
1084 and DEAL_TYPE = p_deal_type;
1085
1086 update XTR_ROLLOVER_TRANSACTIONS
1087 set DUAL_AUTHORISATION_BY = p_validated_by,
1088 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1089 where DEAL_NUMBER = p_deal_no
1090 and DEAL_TYPE = p_deal_type
1091 and settle_date is null;
1092
1093 elsif p_deal_type = 'STOCK' then
1094 if p_action_type = 'NEW_STOCK_BUY_CONTRACT' then -- BUY stock deal
1095 update XTR_DEALS
1096 set DUAL_AUTHORISATION_BY = p_validated_by,
1097 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1098 where DEAL_NO = p_deal_no
1099 and DEAL_TYPE = p_deal_type;
1100
1101 update XTR_DEAL_DATE_AMOUNTS
1102 set DUAL_AUTHORISATION_BY = p_validated_by,
1103 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1104 where DEAL_NUMBER = p_deal_no
1105 and TRANSACTION_NUMBER = p_trans_no
1106 and DEAL_TYPE = p_deal_type;
1107
1108 elsif p_action_type = 'NEW_STOCK_CASH_DIVIDEND' then -- stock cash dividend
1109 update XTR_ROLLOVER_TRANSACTIONS
1110 set DUAL_AUTHORISATION_BY = p_validated_by,
1111 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1112 where DEAL_NUMBER = p_deal_no
1113 and TRANSACTION_NUMBER = p_trans_no
1114 and DEAL_TYPE = p_deal_type;
1115
1116 update XTR_DEAL_DATE_AMOUNTS
1117 set DUAL_AUTHORISATION_BY = p_validated_by,
1118 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1119 where DEAL_NUMBER = p_deal_no
1120 and TRANSACTION_NUMBER = p_trans_no
1121 and DEAL_TYPE = p_deal_type;
1122
1123 elsif p_action_type = 'NEW_STOCK_SELL_CONTRACT' then -- SELL stock deal
1124 update XTR_DEALS
1125 set DUAL_AUTHORISATION_BY = p_validated_by,
1126 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1127 where DEAL_NO = p_deal_no
1128 and DEAL_TYPE = p_deal_type;
1129 update XTR_DEAL_DATE_AMOUNTS
1130 set DUAL_AUTHORISATION_BY = p_validated_by,
1131 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1132 where DEAL_NUMBER = p_deal_no
1133 and DEAL_TYPE = p_deal_type;
1134 end if;
1135
1136 elsif p_deal_type ='RTMM' then
1137 -- Actions are as follows (and the form they originated from)
1138 -- PRO0235 - NEW_RETAIL_TERM_CONTRACT
1139 --- PRINCIPAL_ADJUSTMENT_OF_RETAIL_TERM_CONTRA
1140 --- RETAIL_TERM_INTEREST_RESET
1141 --- Ammend Schedule Type for Retail Term
1142 if p_action_type ='NEW_RETAIL_TERM_CONTRACT' then
1143 update XTR_DEALS
1144 set DUAL_AUTHORISATION_BY = p_validated_by,
1145 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1146 where DEAL_NO = p_deal_no
1147 and DEAL_TYPE = p_deal_type;
1148
1149 update XTR_ROLLOVER_TRANSACTIONS
1150 set DUAL_AUTHORISATION_BY = p_validated_by,
1151 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1152 where DEAL_NUMBER = p_deal_no
1153 and DEAL_TYPE = p_deal_type;
1154
1155 else
1156 update XTR_TERM_ACTIONS
1157 set DUAL_AUTHORISATION_BY = p_validated_by,
1158 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1159 where DEAL_NO = p_deal_no;
1160 open get_date;
1161 fetch get_date into c;
1162 close get_date;
1163 if c.INCREASE_EFFECTIVE_FROM_DATE is not null then
1164 l_date :=c.INCREASE_EFFECTIVE_FROM_DATE;
1165 elsif c.FROM_START_DATE is not null then
1166 l_date :=c.FROM_START_DATE;
1167 elsif c.EFFECTIVE_FROM_DATE is not null then
1168 l_date :=c.EFFECTIVE_FROM_DATE;
1169 end if;
1170
1171 update XTR_ROLLOVER_TRANSACTIONS
1172 set DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate),
1173 DUAL_AUTHORISATION_BY = p_validated_by
1174 where DEAL_NUMBER = p_deal_no
1175 and START_DATE >= l_date
1176 and DEAL_TYPE = p_deal_type;
1177
1178 end if;
1179 -- add on 01 July 98
1180 elsif p_deal_type ='ONC' then
1181 update XTR_ROLLOVER_TRANSACTIONS
1182 set DUAL_AUTHORISATION_BY = p_validated_by,
1183 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1184 where DEAL_NUMBER = p_deal_no
1185 and TRANSACTION_NUMBER = p_trans_no
1186 and DEAL_TYPE = p_deal_type;
1187
1188 -- bug 2254835
1189 elsif p_deal_type = 'EXP' then
1190 if p_action_type = 'NEW_EXPOSURE_TRANSACTION' then
1191 update XTR_EXPOSURE_TRANSACTIONS
1192 set DUAL_AUTHORISATION_BY = p_validated_by,
1193 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1194 where TRANSACTION_NUMBER = p_trans_no;
1195 end if;
1196
1197
1198 -- IAC Redesign Project bug 3800146
1199 elsif p_deal_type IN ('IAC') then
1200 if p_action_type in('INTERACCOUNT_TRANSFER') then
1201 update XTR_INTERACCT_TRANSFERS
1202 set DUAL_AUTHORISATION_BY = p_validated_by,
1203 DUAL_AUTHORISATION_ON= decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1204 where TRANSACTION_NUMBER = p_trans_no;
1205 end if;
1206
1207 end if;
1208
1209 end VALIDATE_DEALS;
1210
1211
1212
1213 PROCEDURE MAINT_PROJECTED_BALANCES IS
1214 --
1215 -- This stored procedure will maintain the balances
1216 -- to reflect the actual bals + cflows from the actual bal date
1217 -- until prior to today. We can then use this balance in instances
1218 -- where the actual balance is fo an old date.
1219 --
1220 l_ccy VARCHAR2(15);
1221 l_acct_ccy VARCHAR2(15);
1222 l_acct VARCHAR2(20);
1223 l_acct_party VARCHAR2(20);
1224 l_state_date DATE;
1225 l_op_balance NUMBER;
1226 l_cflow NUMBER;
1227 l_settle_acct VARCHAR2(20);
1228 --
1229 cursor GET_OLD_ACCT_BALS is
1230 select a.ACCOUNT_NUMBER,a.OPENING_BALANCE,a.STATEMENT_DATE,a.CURRENCY,a.PARTY_CODE
1231 from XTR_BANK_ACCOUNTS a,
1232 XTR_PARTIES_V b
1233 where nvl(a.PROJECTED_BALANCE_UPDATED_ON,to_date('01/01/1980','DD/MM/YYYY')) < trunc(SYSDATE)
1234 and nvl(a.SETOFF_ACCOUNT_YN,'N') <> 'Y'
1235 and b.PARTY_CODE = a.PARTY_CODE
1236 and (b.PARTY_TYPE = 'C' or b.INTERNAL_PTY = 'Y');
1237 --
1238 cursor CFLOWS is
1239 select sum(CASHFLOW_AMOUNT),COMPANY_ACCOUNT,CURRENCY
1240 from XTR_SETTLEMENTS_V
1241 where AMOUNT_DATE > nvl(l_state_date,to_date('01/01/1980','DD/MM/YYYY'))
1242 and AMOUNT_DATE < trunc(SYSDATE)
1243 and COMPANY = l_acct_party
1244 and COMPANY_ACCOUNT = l_acct
1245 and CURRENCY = l_ccy
1246 group by COMPANY_ACCOUNT,CURRENCY;
1247 --
1248 begin
1249 open GET_OLD_ACCT_BALS;
1250 LOOP
1251 fetch GET_OLD_ACCT_BALS INTO l_acct,l_op_balance,l_state_date,l_ccy,l_acct_party;
1252 EXIT WHEN GET_OLD_ACCT_BALS%NOTFOUND;
1253 if l_state_date = (trunc(sysdate) - 1) then
1254 -- Balances are up to date therefore set projected balance = actual balance
1255 update XTR_BANK_ACCOUNTS
1256 set PROJECTED_BALANCE = OPENING_BALANCE,
1257 PROJECTED_BALANCE_UPDATED_ON = trunc(SYSDATE),
1258 PROJECTED_BALANCE_DATE = STATEMENT_DATE
1259 where ACCOUNT_NUMBER = l_acct
1260 and PARTY_CODE = l_acct_party
1261 and CURRENCY = l_ccy;
1262 else
1263 open CFLOWS;
1264 fetch CFLOWS INTO l_cflow,l_settle_acct,l_acct_ccy;
1265 if CFLOWS%FOUND then
1266 update XTR_BANK_ACCOUNTS
1267 set PROJECTED_BALANCE = nvl(l_op_balance,0) + nvl(l_cflow,0),
1268 PROJECTED_BALANCE_UPDATED_ON = trunc(SYSDATE),
1269 PROJECTED_BALANCE_DATE = (trunc(SYSDATE) -1)
1270 where ACCOUNT_NUMBER = l_settle_acct
1271 and PARTY_CODE = l_acct_party
1272 and CURRENCY = l_acct_ccy;
1273 else
1274 update XTR_BANK_ACCOUNTS
1275 set PROJECTED_BALANCE = nvl(l_op_balance,0),
1276 PROJECTED_BALANCE_UPDATED_ON = trunc(SYSDATE),
1277 PROJECTED_BALANCE_DATE = (trunc(SYSDATE) -1)
1278 where ACCOUNT_NUMBER = l_acct
1279 and PARTY_CODE = l_acct_party
1280 and CURRENCY = l_ccy;
1281 end if;
1282 close CFLOWS;
1283 end if;
1284 END LOOP;
1285 close GET_OLD_ACCT_BALS;
1286 commit;
1287 --
1288 end MAINT_PROJECTED_BALANCES;
1289
1290
1291
1292 PROCEDURE CHK_PRO_AUTH
1293 (p_event IN VARCHAR2,
1294 p_company_code IN VARCHAR2,
1295 p_user IN VARCHAR2,
1296 p_deal_type IN VARCHAR2,
1297 p_action IN VARCHAR2) is
1298
1299 l_form_nos VARCHAR2(10);
1300
1301 ex_error_auth exception;
1302 ex_error_insert exception;
1303 ex_error_delete exception;
1304 ex_error_update exception;
1305 ex_error_expiry exception;
1306 ex_error_company exception;
1307
1308 /*
1309 cursor get_user_auth is
1310 SELECT AUTHORISED,AUTH_TO_CREATE,AUTH_TO_DELETE,
1311 AUTH_TO_UPDATE,PASSWORD_EXPIRY
1312 FROM XTR_USER_AUTHORITIES
1313 where USER_NAME=p_user
1314 and FORM_NOS=l_form_nos;
1315 l_auth varchar2(1);
1316 l_insert varchar2(1);
1317 l_delete varchar2(1);
1318 l_update varchar2(1);
1319 l_expiry_date date;
1320 */
1321
1322 cursor get_user_company_auth is
1323 SELECT 'Y'
1324 FROM XTR_COMPANY_AUTHORITIES
1325 where DEALER_CODE=p_user
1326 and COMPANY_AUTHORISED_FOR_INPUT='Y'
1327 and PARTY_CODE =p_company_code;
1328 l_company_auth varchar2(1);
1329
1330 cursor get_dealtype is
1331 select name
1332 from xtr_deal_types
1333 where deal_type=p_deal_type;
1334
1335 l_mesg varchar2(200);
1336
1337 begin
1338 if p_company_code is not null then
1339 l_company_auth :='N';
1340 open get_user_company_auth;
1341 fetch get_user_company_auth into l_company_auth;
1342 close get_user_company_auth;
1343 if l_company_auth <>'Y' then
1344 raise ex_error_company;
1345 end if;
1346 end if;
1347
1348 if p_event='DEALS' then
1349 if p_deal_type='ONC' then
1350 l_form_nos :='PRO0210';
1351 elsif p_deal_type='CA' then
1352 l_form_nos :='PRO1080';
1353 elsif p_deal_type='IG' then
1354 l_form_nos :='PRO1075';
1355 elsif p_deal_type='NI' then
1356 l_form_nos :='PRO0240';
1357 elsif p_deal_type='TMM' then
1358 l_form_nos :='PRO0239';
1359 elsif p_deal_type='BOND' then
1360 l_form_nos :='PRO0280';
1361 elsif p_deal_type='DEB' then
1362 l_form_nos :='PRO0310';
1363 elsif p_deal_type='IRS' then
1364 l_form_nos :='PRO0290';
1365 elsif p_deal_type='IRO' then
1366 l_form_nos :='PRO0230';
1367 elsif p_deal_type='FRA' then
1368 l_form_nos :='PRO0770';
1369 elsif p_deal_type='SWPTN' then
1370 l_form_nos :='PRO0320';
1371 elsif p_deal_type='FUT' then
1372 l_form_nos :='PRO0330';
1373 elsif p_deal_type='FX' then
1374 l_form_nos :='PRO0170';
1375 elsif p_deal_type='FXO' then
1376 l_form_nos :='PRO0190';
1377 end if;
1378 end if;
1379 if l_form_nos is not null then
1380 l_mesg :=null;
1381 open get_dealtype;
1382 fetch get_dealtype into l_mesg;
1383 close get_dealtype;
1384 /*
1385 l_auth :='N';
1386 open get_user_auth;
1387 fetch get_user_auth into l_auth,l_insert,l_delete,l_update,l_expiry_date;
1388 close get_user_auth;
1389 if l_auth <>'Y' then
1390 raise ex_error_auth;
1391 elsif nvl(l_expiry_date,sysdate+1)<trunc(sysdate) then
1392 raise ex_error_expiry;
1393 elsif p_action='INSERT' and l_insert <>'Y' then
1394 raise ex_error_insert;
1395 elsif p_action='DELETE' and l_delete <>'Y' then
1396 raise ex_error_delete;
1397 elsif p_action='UPDATE' and l_update <>'Y' then
1398 raise ex_error_update;
1399 end if;
1400 */
1401 end if;
1402
1403 exception
1404 when ex_error_company then
1405 FND_MESSAGE.Set_Name('XTR', 'XTR_2036');
1406 FND_MESSAGE.Set_Token('P_COMPANY_CODE', p_company_code);
1407 APP_EXCEPTION.Raise_exception;
1408 /*
1409 when ex_error_auth then
1410 FND_MESSAGE.Set_Name('XTR', 'XTR_2037');
1411 FND_MESSAGE.Set_Token('L_MESG', l_mesg);
1412 APP_EXCEPTION.Raise_exception;
1413 when ex_error_expiry then
1414 FND_MESSAGE.Set_Name('XTR', 'XTR_2038');
1415 FND_MESSAGE.Set_Token('L_FORM_NOS', l_form_nos);
1416 APP_EXCEPTION.Raise_exception;
1417 when ex_error_insert then
1418 FND_MESSAGE.Set_Name('XTR', 'XTR_1003');
1419 FND_MESSAGE.Set_Token('L_MESG', l_mesg);
1420 APP_EXCEPTION.Raise_exception;
1421 when ex_error_delete then
1422 FND_MESSAGE.Set_Name('XTR', 'XTR_1004');
1423 FND_MESSAGE.Set_Token('L_MESG', l_mesg);
1424 APP_EXCEPTION.Raise_exception;
1425 when ex_error_update then
1426 FND_MESSAGE.Set_Name('XTR', 'XTR_1005');
1427 FND_MESSAGE.Set_Token('L_MESG', l_mesg);
1428 APP_EXCEPTION.Raise_exception;
1429 */
1430 END CHK_PRO_AUTH;
1431
1432
1433 PROCEDURE SETOFF is
1434 -- Called form 1080 - banak a/c maintenance
1435 l_calc_date DATE;
1436 roundfac NUMBER;
1437 yr_basis NUMBER;
1438 l_ccy VARCHAR(15);
1439 l_setoff VARCHAR(5);
1440 l_setoff_company VARCHAR(7);
1441 l_bank_code VARCHAR(7);
1442 l_no_days NUMBER;
1443 l_prv_date DATE;
1444 l_this_rate NUMBER;
1445 l_prv_rate NUMBER;
1446 l_rate NUMBER;
1447 l_prv_bal NUMBER;
1448 l_int_bf NUMBER;
1449 l_int_cf NUMBER;
1450 l_int_set NUMBER;
1451 l_interest NUMBER;
1452 l_this_bal NUMBER;
1453 --
1454 cursor SEL_SETOFF_ACCT is
1455 select distinct rtrim(SETOFF),rtrim(BANK_CODE),
1456 SETOFF_COMPANY,CURRENCY
1457 from XTR_BANK_ACCOUNTS;
1458
1459 cursor FIND_SETOFF_RATE is
1460 select INTEREST_RATE
1461 from XTR_INTEREST_RATE_RANGES
1462 where REF_CODE = l_setoff||'-'||l_bank_code
1463 and MIN_AMT <= l_this_bal
1464 and MAX_AMT >= l_this_bal;
1465 --
1466 cursor RNDING is
1467 select ROUNDING_FACTOR,YEAR_BASIS
1468 from XTR_MASTER_CURRENCIES_V
1469 where CURRENCY = l_ccy;
1470 --
1471 cursor SETOFF_CAL_DATE is
1472 select distinct BALANCE_DATE
1473 from XTR_BANK_BALANCES
1474 where BALANCE_DATE >= (select max(BALANCE_DATE)
1475 from XTR_BANK_BAL_INTERFACE)
1476 and SETOFF = l_setoff
1477 order by BALANCE_DATE asc;
1478 --
1479 cursor SETOFF_PRV_RATE is
1480 select INTEREST_RATE
1481 from XTR_BANK_BALANCES
1482 where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
1483 and BALANCE_DATE = l_prv_date;
1484 --
1485 cursor SETOFF_PREV_DETAILS is
1486 select a.BALANCE_DATE,nvl(sum(a.BALANCE_CFLOW),0),
1487 nvl(sum(a.ACCUM_INT_CFWD),0)
1488 from XTR_BANK_BALANCES a
1489 where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
1490 and a.BALANCE_DATE = (select max(b.BALANCE_DATE)
1491 from XTR_BANK_BALANCES b
1492 where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
1493 and b.BALANCE_DATE < l_calc_date)
1494 group by a.BALANCE_DATE,a.ACCOUNT_NUMBER;
1495 --
1496 cursor SETOFF_THIS_DETAILS is
1497 select nvl(sum(a.BALANCE_CFLOW),0)
1498 from XTR_BANK_BALANCES a
1499 where SETOFF = l_setoff
1500 and a.BALANCE_DATE = l_calc_date;
1501 --
1502 begin
1503 -- Calculate Setoff details
1504 open SEL_SETOFF_ACCT;
1505 LOOP
1506 fetch SEL_SETOFF_ACCT INTO l_setoff,l_bank_code,
1507 l_setoff_company,l_ccy;
1508 EXIT WHEN SEL_SETOFF_ACCT%NOTFOUND;
1509 delete XTR_BANK_BALANCES
1510 where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
1511 and BALANCE_DATE >= (select max(BALANCE_DATE)
1512 from XTR_BANK_BAL_INTERFACE);
1513 open SETOFF_CAL_DATE;
1514 LOOP
1515 fetch SETOFF_CAL_DATE INTO l_calc_date;
1516 EXIT WHEN SETOFF_CAL_DATE%NOTFOUND;
1517 open SETOFF_PREV_DETAILS;
1518 fetch SETOFF_PREV_DETAILS INTO l_prv_date,l_prv_bal,l_int_bf;
1519 if SETOFF_PREV_DETAILS%NOTFOUND then
1520 l_prv_date := l_calc_date;
1521 l_prv_bal := 0;
1522 l_int_bf := 0;
1523 l_no_days := 0;
1524 end if;
1525 open SETOFF_THIS_DETAILS;
1526 fetch SETOFF_THIS_DETAILS INTO l_this_bal;
1527 close SETOFF_THIS_DETAILS;
1528 open FIND_SETOFF_RATE;
1529 fetch FIND_SETOFF_RATE INTO l_rate;
1530 if FIND_SETOFF_RATE%NOTFOUND then
1531 l_rate := 0;
1532 end if;
1533 close FIND_SETOFF_RATE;
1534 close SETOFF_PREV_DETAILS;
1535 open RNDING;
1536 fetch RNDING INTO roundfac,yr_basis;
1537 close RNDING;
1538 open SETOFF_PRV_RATE;
1539 fetch SETOFF_PRV_RATE INTO l_prv_rate;
1540 if SETOFF_PRV_RATE%NOTFOUND then
1541 l_prv_rate := 0;
1542 end if;
1543 close SETOFF_PRV_RATE;
1544 l_no_days := (trunc(l_calc_date) - trunc(l_prv_date));
1545 l_interest := round(l_prv_bal * l_prv_rate / 100 * l_no_days
1546 / yr_basis,roundfac);
1547 l_int_cf := l_int_bf + l_interest;
1548 l_rate := nvl(l_rate,0);
1549 insert into XTR_BANK_BALANCES
1550 (COMPANY_CODE,ACCOUNT_NUMBER,BALANCE_DATE,NO_OF_DAYS,
1551 STATEMENT_BALANCE,BALANCE_ADJUSTMENT,BALANCE_CFLOW,
1552 ACCUM_INT_BFWD,INTEREST,INTEREST_RATE,INTEREST_SETTLED,
1553 INTEREST_SETTLED_HCE,ACCUM_INT_CFWD,
1554 created_on, created_by)
1555 values
1556 (l_setoff_company,l_setoff||'-'||l_bank_code,
1557 l_calc_date,l_no_days,l_this_bal,0,l_this_bal,l_int_bf,
1558 l_interest,l_rate,0,0,l_int_cf,
1559 sysdate, fnd_global.user_id);
1560 END LOOP;
1561 close SETOFF_CAL_DATE;
1562 END LOOP;
1563 close SEL_SETOFF_ACCT;
1564 commit;
1565 end SETOFF;
1566
1567
1568 END XTR_MISC_P;
[Home] [Help]