1 PACKAGE BODY XTR_MISC_P AS
2 /* $Header: xtrprc3b.pls 120.9 2006/02/03 10:28:10 eaggarwa 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
396 and deal_number = 0 -- bug 4957910
393 select dual_authorisation_by, dual_authorisation_on
394 from xtr_deal_date_amounts
395 where transaction_number=p_transaction_number
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 --
410 begin
411 /* List of Confirmation Types that will be passed to this Procedure
412 if p_deal_type = 'FX' then
413 FX_CONTRACT_SWAP
414 PREDELIVERY_OF_FX_CONTRACT
415 ROLLOVER_OF_FX_CONTRACT
416 NEW_FX_CONTRACT
417 elsif p_deal_type = 'FXO' then
418 NEW_FXO_CONTRACT
419 EXERCISE_OF_FX_OPTION_CONTRACT
420 elsif p_deal_type = 'FRA' then
421 NEW_FRA_CONTRACT
422 SETTLEMENT_OF_FRA_CONTRACT
423 elsif p_deal_type = 'FUT' then
424 New Futures Contract
425 Closeout of Futures Contract
426 elsif p_deal_type = 'IRO' then
427 NEW_IRO_CONTRACT
428 EXERCISE_OF_IRO_CONTRACT
429 NEW_BOND_OPTION_CONTRACT
430 EXERCISE_OF_BOND_OPTION_CONTRACT
431 elsif p_deal_type = 'NI' then
432 NEW_NI_CONTRACT
433 elsif p_deal_type = 'BOND' then
434 NEW_BOND_CONTRACT
435 elsif p_deal_type = 'SWPTN' then
436 NEW_SWAPTION_CONTRACT
437 EXERCISE_OF_SWAPTION_CONTRACT
438 elsif p_deal_type = 'DEB' then
439 New Debenture Contract
440 elsif p_deal_type = 'IRS' then
441 NEW_INT_RATE_SWAP_CONTRACT
442 elsif p_deal_type = 'TMM' then
443 -- Retail
444 NEW_RETAIL_TERM_CONTRACT
445 Change Schedule Type for Retail Term
446 PRINCIPAL_ADJUSTMENT_OF_RETAIL_TERM_CONTRA
447 RETAIL_TERM_INTEREST_RESET
448 -- Wholesale
449 NEW_WHOLESALE_TERM_CONTRACT
450 end if;
451 */
452 --
453 -- Fetch the break above level for validation purposes
454 -- ie only validate transactions above this amount
455 open VALIDATE_ABOVE_AMT;
456 fetch VALIDATE_ABOVE_AMT INTO l_above_amt;
457 if VALIDATE_ABOVE_AMT%NOTFOUND then
458 l_above_amt := 0;
459 end if;
460 close VALIDATE_ABOVE_AMT;
461 --
462 -- Convert amount to hce amount for comparison to above break limit
463 open HCE;
464 fetch HCE into l_hce_amount;
465 close HCE;
466 --
467 if ABS(l_hce_amount) < l_above_amt then -- bug 4135644
468 -- Auto confirm inserted row
469 confirmed_by := fnd_global.user_id;
470 confirmed_on := sysdate;
471 else
472 confirmed_by := NULL;
473 confirmed_on := NULL;
474 end if;
475 --
476
477
478 -- bug 3800146 IAC -Redesign Project Added lines
479
480 if p_deal_type = 'IAC' and confirmed_by is null then
481 open iac_validated;
482 fetch iac_validated into confirmed_by, confirmed_on;
483 close iac_validated;
484 end if;
485
486 -- bug 3800146 IAC -Redesign Project Ended lines
487
488 /************************
489
490 if (nvl(p_cparty_advice,'N') = 'N' and nvl(p_client_advice,'N')= 'N') or (nvl(p_org_flag,'~') = 'U') then
491 insert into xtr_confirmation_details(
492 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
493 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,AMOUNT_TYPE,CLIENT_CODE,
494 CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
495 values(p_action_type,p_company_code,NULL,'V',p_cparty_code,trunc(p_date_created),
496 p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
497 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
498 elsif nvl(p_cparty_advice,'N') = 'Y' and nvl(p_client_advice,'N') = 'N' then
499 --
500 open chk_confo_reqd(p_cparty_code );
501 fetch chk_confo_reqd into l_dumy_num;
502 if chk_confo_reqd%NOTFOUND then
503 insert into xtr_confirmation_details(
504 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
505 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,
506 AMOUNT,AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
507 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, p_amount,NULL,p_client_code,
510 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
511 else
512 insert into xtr_confirmation_details(
513 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
514 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
515 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
516 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
517 values(p_action_type,p_company_code,p_cparty_code,'B',p_cparty_code,trunc(p_date_created),
518 p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
519 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
520 end if;
521 close chk_confo_reqd;
522 --
523 elsif nvl(p_cparty_advice,'N') = 'N' and nvl(p_client_advice ,'N') = 'Y' then
524 --
525 open chk_confo_reqd(p_client_code);
526 fetch chk_confo_reqd into l_dumy_num;
527 if chk_confo_reqd%NOTFOUND then
528 insert into xtr_confirmation_details(
532 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
529 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
530 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
531 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
533 values(p_action_type,p_company_code, NULL,'V',p_cparty_code,trunc(p_date_created),
534 p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
535 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
536 else
537 insert into xtr_confirmation_details(
538 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
539 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
540 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
541 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
542 values(p_action_type,p_company_code,p_client_code,'B',p_cparty_code,trunc(p_date_created),
543 p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
544 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
545 end if;
546 close chk_confo_reqd;
547 --
548 elsif nvl(p_cparty_advice,'N')='Y' and nvl(p_client_advice ,'N')='Y' then
549 --
550 *****************************/
551
552 open chk_confo_reqd(p_cparty_code);
553 fetch chk_confo_reqd into l_dumy_num;
554 if chk_confo_reqd%NOTFOUND then
555 insert into xtr_confirmation_details(
556 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
557 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
558 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
559 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
560 values(p_action_type,p_company_code,NULL,'V',p_cparty_code,trunc(p_date_created),
561 p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
562 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
563 else
564 insert into xtr_confirmation_details(
565 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
566 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
567 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE,
568 CONFIRMATION_VALIDATED_BY,CONFIRMATION_VALIDATED_ON)
569 values(p_action_type,p_company_code,p_cparty_code,'B',p_cparty_code,trunc(p_date_created),
570 p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
571 p_currency,p_deal_subtype,p_deal_type,confirmed_by,confirmed_on);
572 end if;
573 close chk_confo_reqd;
574 --
575 open chk_confo_reqd(p_client_code );
576 fetch chk_confo_reqd into l_dumy_num;
577 if chk_confo_reqd%FOUND then
578 insert into xtr_confirmation_details(
579 ACTION_TYPE,COMPANY_CODE,CONFO_PARTY_CODE,CONFO_VALIDATION_BOTH,
580 CPARTY_CODE,DATE_ACTION_INITIATED,DEAL_NO,STATUS_CODE,TRANSACTION_NO,AMOUNT,
581 AMOUNT_TYPE,CLIENT_CODE,CURRENCY,DEAL_SUBTYPE,DEAL_TYPE)
582 values(p_action_type,p_company_code,p_client_code,'C',p_cparty_code,trunc(p_date_created),
583 p_deal_number,p_status_code,p_transaction_number,p_amount,NULL,p_client_code,
584 p_currency,p_deal_subtype,p_deal_type);
585 end if;
586 close chk_confo_reqd;
587 --
588 -- end if;
589 end DEAL_ACTIONS;
590
591
592
593 -- Procedure to Calculate Fixed V's Floating Details (PRO1108) + Int Rate Bands
594 procedure INS_ACTUALS(
595 p_company_code IN varchar2,
596 p_currency IN varchar2,
597 p_portfolio_code IN varchar2,
598 p_from_date IN date,
599 p_to_date IN date,
600 p_fund_invest IN varchar2,
601 p_amount_unit IN number,
602 p_inc_ig IN varchar2,
603 p_unique_ref_number IN number,
604 p_company_name IN varchar2,
605 p_port_name IN varchar2,
606 p_floating_less IN varchar2) is
607 --
608 cursor get_ca is
609 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,
610 'FLOAT' FIXED_OR_FLOAT,nvl(a.amount,0) gross_amount,1 no_of_days
611 from XTR_DEAL_DATE_AMOUNTS_V a
612 where a.amount_date >=p_from_date
613 and a.company_code like p_company_code and a.currency like p_currency
614 and a.portfolio_code like p_portfolio_code
615 and a.status_code='CURRENT'
616 and ((a.DEAL_TYPE ='CA' and a.AMOUNT_TYPE='BAL')
617 or (a.DEAL_TYPE ='IG' and a.AMOUNT_TYPE='BAL' and p_inc_ig='Y'))
618 and ( (a.DEAL_SUBTYPE='INVEST' and p_fund_invest='INVEST')
619 or (a.DEAL_SUBTYPE='FUND' and p_fund_invest='FUND')
620 or (p_fund_invest='NONE'));
621 --
622 cursor get_data1 is
623 select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,
624 a.deal_subtype,a.currency,a.transaction_rate,
625 decode(a.deal_type,'IRO',nvl(b.start_date,b.expiry_date),b.start_date) start_date,
626 b.maturity_date,p_to_date to_date,p_from_date from_date,
627 decode(a.deal_type||a.deal_subtype,
628 'IROBCAP','FIXED',
629 'IROBFLOOR','FIXED',
630 'IROSFLOOR','FLOAT',
631 'IROSCAP','FLOAT',
632 'BDOBCAP','FIXED',
633 'BDOBFLOOR','FIXED',
634 'BDOSFLOOR','FLOAT',
635 'BDOSCAP','FLOAT',
636 'SWPTNSELL','FLOAT',
637 'SWPTNBUY','FIXED',
638 'FRAFUND','FIXED',
639 'FRAINVEST','FIXED',
643 '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),
640 decode(sign(p_to_date-b.maturity_date+1),1,'FLOAT','FIXED')) FIXED_OR_FLOAT,
641 decode(a.deal_type,
642 '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),
644 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,
645 nvl(sum(decode(a.deal_type||a.amount_type,
646 'NIBAL_FV',a.amount,
647 'BONDINTL_FV',a.amount,
648 'IROFACEVAL',a.amount,
649 'BDOFACEVAL',a.amount,
650 'SWPTNFACEVAL',a.amount,
651 'FRAFACEVAL',a.amount,0)),0) gross_amount
652 from XTR_DEAL_DATE_AMOUNTS_V a,
653 XTR_DEALS_V b
654 where a.amount_date >= p_from_date
655 and a.currency like p_currency
656 and a.company_code like p_company_code
657 and a.portfolio_code like p_portfolio_code
658 and a.status_code='CURRENT'
659 and a.deal_number = b.deal_no
660 and a.DEAL_TYPE in('NI','SWPTN','FRA','IRO','BOND','BDO')
661 and ((a.DEAL_TYPE in('FRA') AND a.DEAL_SUBTYPE = 'INVEST' and p_fund_invest='INVEST')
662 or (a.DEAL_TYPE in('FRA') AND a.DEAL_SUBTYPE = 'FUND' and p_fund_invest='FUND')
663 or (p_fund_invest = 'NONE')
664 or (a.DEAL_TYPE = 'NI' AND a.DEAL_SUBTYPE in('BUY','COVER') and p_fund_invest='INVEST')
665 or (a.DEAL_TYPE = 'NI' AND a.DEAL_SUBTYPE in('SELL','SHORT','ISSUE') and p_fund_invest = 'FUND')
666 or (a.DEAL_TYPE in('BOND') AND a.DEAL_SUBTYPE='BUY' and p_fund_invest = 'INVEST')
667 or (a.DEAL_TYPE in('BOND') AND a.DEAL_SUBTYPE='SHORT' and p_fund_invest = 'FUND')
668 or (a.DEAL_TYPE in('BOND') AND a.DEAL_SUBTYPE='ISSUE' and p_fund_invest = 'FUND')
669 or (a.DEAL_TYPE in('IRO') AND a.DEAL_SUBTYPE in('BFLOOR','SFLOOR') and p_fund_invest = 'INVEST')
670 or (a.DEAL_TYPE in('IRO') AND a.DEAL_SUBTYPE in('SCAP','BCAP') and p_fund_invest = 'FUND')
671 or (a.DEAL_TYPE in('BDO') AND a.DEAL_SUBTYPE in('BFLOOR','SFLOOR') and p_fund_invest = 'INVEST')
672 or (a.DEAL_TYPE in('BDO') AND a.DEAL_SUBTYPE in('SCAP','BCAP') and p_fund_invest = 'FUND')
673 or (a.DEAL_TYPE in('SWPTN') and b.COUPON_ACTION='PAY' and p_fund_invest='FUND')
674 or (a.DEAL_TYPE in('SWPTN') and b.COUPON_ACTION='REC' and p_fund_invest='INVEST'))
675 having nvl(sum(decode(a.deal_type||a.amount_type,
676 'NIBAL_FV',a.amount,
677 'BONDINTL_FV',a.amount,
678 'IROFACEVAL',a.amount,
679 'BDOFACEVAL',a.amount,
680 'SWPTNFACEVAL',a.amount,
681 'FRAFACEVAL',a.amount,0)),0) >0
682 group by a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,
683 a.deal_subtype,a.currency,a.transaction_rate,
684 decode(a.deal_type,'IRO',nvl(b.start_date,b.expiry_date),b.start_date),b.maturity_date,p_to_date,p_from_date,
685 decode(a.deal_type,'BDO',nvl(b.start_date,b.expiry_date),b.start_date),b.maturity_date,p_to_date,p_from_date,
686 decode(a.deal_type||a.deal_subtype,
687 'IROBCAP','FIXED',
688 'IROBFLOOR','FIXED',
689 'IROSFLOOR','FLOAT',
690 'IROSCAP','FLOAT',
691 'BDOBCAP','FIXED',
692 'BDOBFLOOR','FIXED',
693 'BDOSFLOOR','FLOAT',
694 'BDOSCAP','FLOAT',
695 'SWPTNSELL','FLOAT',
696 'SWPTNBUY','FIXED',
697 'FRAFUND','FIXED',
698 'FRAINVEST','FIXED',
699 decode(sign(p_to_date-b.maturity_date+1),1,'FLOAT','FIXED')),
700 decode(a.deal_type,
701 '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),
702 '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),
703 nvl(b.maturity_date-b.start_date+decode(sign(b.start_date-p_from_date),-1,b.start_date-p_from_date,0),0));
704 --
705 cursor get_fut is
706 select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,a.deal_type,
707 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,
708 'FIXED' FIXED_OR_FLOAT,nvl(a.amount,0) gross_amount,1 no_of_days
709 from XTR_DEAL_DATE_AMOUNTS_V a,
710 XTR_FUTURES b
711 where a.amount_date >=p_from_date
712 and a.company_code like p_company_code
713 and a.currency like p_currency
714 and a.portfolio_code like p_portfolio_code
715 and a.amount_type='FACEVAL'
716 and a.status_code='CURRENT'
717 and a.deal_type='FUT' and ((a.deal_subtype='BUY' and p_fund_invest='INVEST') or
718 (a.deal_subtype = 'SELL' and p_fund_invest = 'FUND') or p_fund_invest='NONE')
719 and a.contract_code = b.contract_code
720 and b.financial_contract = 'F';
721 --
722
723 cursor get_data2 is
724 select a.company_code,a.portfolio_code,a.deal_number,a.transaction_number,
725 a.deal_type,a.deal_subtype,a.currency,a.INTEREST_RATE transaction_rate,
726 a.start_date,a.maturity_date,p_to_date to_date,p_from_date from_date,
727 decode(a.deal_type,
728 'TMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),
729 nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
730 decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
731 'RTMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),
735 decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,'FLOAT','FIXED')) FIXED_OR_FLOAT,
732 nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
733 decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
734 'IRS',decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,b.fixed_or_floating_rate,'FLOAT'),
736 nvl(decode(a.deal_type,
737 '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')),
738 'FLOAT',
739 decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
740 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),
741 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)-
742 (nvl(b.settle_date,b.start_date)-p_to_date)),
743 'FIXED',
744 nvl(b.settle_date,b.start_date)-p_to_date),
745 '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')),
746 'FLOAT',
747 decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
748 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),
749 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)-
750 (nvl(b.settle_date,b.start_date)-p_to_date)),
751 'FIXED',
752 nvl(b.settle_date,b.start_date)-p_to_date),
753 '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),
754 '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),
755 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,
756 nvl(sum(decode(a.deal_type,
757 'TMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
758 'RTMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
759 'DEB',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
760 a.balance_out)),0) gross_amount
761 from XTR_ROLLOVER_TRANSACTIONS_V a,
762 XTR_DEALS_V b
763 where a.company_code like p_company_code and a.currency like p_currency
764 and a.status_code='CURRENT'
765 and a.portfolio_code like p_portfolio_code
766 and nvl(a.maturity_date,p_from_date+1) >p_from_date
767 and a.start_date <=p_from_date
768 and a.deal_type in('ONC','CMF','IRS','DEB','FX','TMM','RTMM')
769 and ((a.deal_type in('ONC','RTMM','TMM','IRS','CMF','FX') and (a.deal_subtype='INVEST' and p_fund_invest='INVEST'))
770 or (a.deal_type in('ONC','TMM','RTMM','IRS','CMF','FX') and (a.deal_subtype='FUND' and p_fund_invest='FUND'))
771 or (p_fund_invest='NONE')
772 or (a.DEAL_TYPE in('DEB') AND a.DEAL_SUBTYPE='BUY' and p_fund_invest='INVEST')
773 or (a.DEAL_TYPE in('DEB') AND a.DEAL_SUBTYPE='ISSUE' and p_fund_invest='FUND')
774 )
775 and a.deal_number=b.deal_no
776 having nvl(sum(decode(a.deal_type,
777 'TMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
778 'RTMM',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
779 'DEB',decode(nvl(a.balance_out,0),0,a.balance_out_bf,a.balance_out),
780 a.balance_out)),0) >0
781 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,
782 decode(a.deal_type,
783 'TMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
784 decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
785 'RTMM',decode(sign(p_to_date-greatest(nvl(a.maturity_date,p_from_date),nvl(b.settle_date,b.start_date))+1),1,'FLOAT',
786 decode(sign(nvl(b.settle_date,b.start_date)-a.maturity_date+1),1,'FIXED','FLOAT')),
787 'IRS',decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,b.fixed_or_floating_rate,'FLOAT'),
788 decode(sign(p_to_date-nvl(a.maturity_date,p_from_date)+1),1,'FLOAT','FIXED')),
789 nvl(decode(a.deal_type,
790 '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')),
791 'FLOAT',
792 decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
793 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),
794 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)),
795 'FIXED',
796 nvl(b.settle_date,b.start_date)-p_to_date),
797 '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')),
798 'FLOAT',
799 decode(sign(p_to_date-nvl(b.settle_date,b.start_date)+1),1,
800 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),
804 '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),
801 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)),
802 'FIXED',
803 nvl(b.settle_date,b.start_date)-p_to_date),
805 '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),
806 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);
807
808 l_sysdate date :=sysdate;
809 l_fixed_or_float varchar2(5);
810 l_syn_phy varchar2(1);
811 begin
812 delete from XTR_INTEREST_RATE_EXPOSURE where created_on <sysdate-1;
813 commit;
814 for c in get_ca loop
815 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
816 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
817 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
818 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
819 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
820 c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
821 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N','P');
822 end loop;
823 for c in get_data1 loop
824 if c.deal_type in('ONC','CA','IG','NI','TMM','BOND','DEB','CMF','RTMM') then
825 l_syn_phy :='P';
826 else
827 l_syn_phy :='S';
828 end if;
829 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
830 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
831 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
832 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
833 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
834 c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
835 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N',l_syn_phy);
836 if c.deal_type in('IRS','FRA','SWPTN','IRO','BDO') and nvl(c.gross_amount,0) <>0 then
837 if c.fixed_or_float='FIXED' then
838 l_fixed_or_float :='FLOAT';
839 else
840 l_fixed_or_float :='FIXED';
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 l_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,'Y',l_syn_phy);
849 end if;
850 end loop;
851 for c in get_fut loop
852 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
853 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
854 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
855 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
856 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
857 c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
858 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N','S');
859 if nvl(c.gross_amount,0) <>0 then
860 if c.fixed_or_float='FIXED' then
861 l_fixed_or_float :='FLOAT';
862 else
863 l_fixed_or_float :='FIXED';
864 end if;
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 l_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,'Y','S');
872 end if;
873 end loop;
874
875 for c in get_data2 loop
876 if c.deal_type in('ONC','CA','IG','NI','TMM','BOND','DEB','CMF','RTMM') then
877 l_syn_phy :='P';
878 else
879 l_syn_phy :='S';
880 end if;
881 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
882 COMPANY_CODE,CREATED_ON,CURRENCY,DEAL_NUMBER,DEAL_SUBTYPE,DEAL_TYPE,
883 FIXED_OR_FLOAT,FROM_DATE,GROSS_AMOUNT,MATURITY_DATE,NO_OF_DAYS,
884 PORTFOLIO_CODE,REF_NUMBER,START_DATE,TO_DATE,TRANSACTION_NUMBER,TRANSACTION_RATE,SYN_FLAG,SYN_PHY)
885 values(p_fund_invest,fnd_global.user_id,c.COMPANY_CODE,l_sysdate,c.CURRENCY,c.DEAL_NUMBER,c.DEAL_SUBTYPE,c.DEAL_TYPE,
886 c.FIXED_OR_FLOAT,c.FROM_DATE,c.GROSS_AMOUNT,c.MATURITY_DATE,c.NO_OF_DAYS,
887 c.PORTFOLIO_CODE,p_unique_ref_number,c.START_DATE,c.TO_DATE,c.TRANSACTION_NUMBER,c.TRANSACTION_RATE,'N',l_syn_phy);
888 if c.deal_type in('IRS','FRA','SWPTN','IRO') and nvl(c.gross_amount,0) <>0 then
889 if c.fixed_or_float='FIXED' then
890 l_fixed_or_float :='FLOAT';
891 else
892 l_fixed_or_float :='FIXED';
893 end if;
894 insert into XTR_INTEREST_RATE_EXPOSURE(FUND_INVEST,CREATED_BY,
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,
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)
899 l_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,'Y',l_syn_phy);
901 end if;
902 end loop;
903 commit;
904 END INS_ACTUALS;
905
906
907
908 PROCEDURE VALIDATE_DEALS (p_deal_no IN NUMBER,
909 p_trans_no IN NUMBER,
910 p_deal_type IN VARCHAR2,
911 p_action_type IN VARCHAR2,
912 p_validated_by IN VARCHAR2) is
913 --
914 cursor GET_DATE is
915 select INCREASE_EFFECTIVE_FROM_DATE,FROM_START_DATE,EFFECTIVE_FROM_DATE
916 from XTR_TERM_ACTIONS
917 where DEAL_NO = p_deal_no;
918
919 -- bug #1295341 jhung
920 cursor IRS_OTHER_DEAL is
921 select b.deal_no
922 from xtr_deals_v a, xtr_deals_v b
923 where a.int_swap_ref = b.int_swap_ref
924 and a.deal_type = b.deal_type
925 and a.deal_type = 'IRS'
926 and a.deal_subtype <> b.deal_subtype
927 and a.deal_no = p_deal_no;
928 --
929 c get_date%ROWTYPE;
930 v_cnt NUMBER := 0;
931 u_cnt NUMBER := 0;
932 l_date DATE;
933 receive_deal_no NUMBER;
934 --
935 begin
936 if p_deal_type IN ('FX','FXO') then
937 -- Actions are as follows (and the form they originated from)
938 -- PRO0170 - NEW_FX_CONTRACT
939 -- PRO0190 - FX_CONTRACT_SWAP (FX Contract re Exercise of FX Option)
940 -- PRO0200 - ROLLOVER_OF_FX_CONTRACT
941 -- PRO0200 - PREDELIVERY_OF_FX_CONTRACT
942 -- PRO0190 - NEW_FXO_CONTRACT
943 -- PRO0190 - EXERCISE_OF_FX_OPTION_CONTRACT
944 if p_action_type in('NEW_FX_CONTRACT',
945 'NEW_FXO_CONTRACT',
946 'FX_CONTRACT_SWAP',
947 'ROLLOVER_OF_FX_CONTRACT',
948 'PREDELIVERY_OF_FX_CONTRACT') then
949 update XTR_DEALS
950 set DUAL_AUTHORISATION_BY = p_validated_by,
951 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
952 where DEAL_NO = p_deal_no
953 and DEAL_TYPE = p_deal_type;
954 elsif p_action_type='EXERCISE_OF_FX_OPTION_CONTRACT' then
955 update XTR_DEALS
956 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
957 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
958 where DEAL_NO = p_deal_no
959 and DEAL_TYPE = p_deal_type;
960 end if;
961 elsif p_deal_type ='FRA' then
962 -- Actions are as follows (and the form they originated from)
963 -- PRO0770 - NEW_FRA_CONTRACT
964 -- PRO0770 - SETTLEMENT_OF_FRA_CONTRACT
965 if p_action_type ='NEW_FRA_CONTRACT' then
966 update XTR_DEALS
967 set DUAL_AUTHORISATION_BY = p_validated_by,
968 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
969 where DEAL_NO = p_deal_no
970 and DEAL_TYPE = p_deal_type;
971 elsif p_action_type='SETTLEMENT_OF_FRA_CONTRACT' then
972 update XTR_DEALS
973 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
974 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
975 where DEAL_NO = p_deal_no
976 and DEAL_TYPE = p_deal_type;
977 end if;
978 elsif p_deal_type ='IRO' then
979 -- Actions are as follows (and the form they originated from)
980 -- PRO0270 - NEW_IRO_CONTRACT
981 -- PRO0270 - Settlement of IRO Contract
982 if p_action_type = 'NEW_IRO_CONTRACT' then
983 update XTR_DEALS
984 set DUAL_AUTHORISATION_BY = p_validated_by,
985 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
986 where DEAL_NO = p_deal_no
987 and DEAL_TYPE = p_deal_type;
988 elsif p_action_type = 'EXERCISE_OF_IRO_CONTRACT' then
989 update XTR_DEALS
990 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
991 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
992 where DEAL_NO = p_deal_no
993 and DEAL_TYPE = p_deal_type;
994 end if;
995 elsif p_deal_type ='BDO' then
996 -- Actions are as follows (and the form they originated from)
997 if p_action_type = 'NEW_BOND_OPTION_CONTRACT' then
998 update XTR_DEALS
999 set DUAL_AUTHORISATION_BY = p_validated_by,
1000 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1001 where DEAL_NO = p_deal_no
1002 and DEAL_TYPE = p_deal_type;
1003 elsif p_action_type = 'EXERCISE_OF_BOND_OPTION_CONTRACT' then
1004 update XTR_DEALS
1005 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
1006 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1007 where DEAL_NO = p_deal_no
1008 and DEAL_TYPE = p_deal_type;
1009 end if;
1010 elsif p_deal_type ='NI' then
1011 -- Actions are as follows (and the form they originated from)
1012 -- PRO0240 - NEW_NI_CONTRACT
1013 if p_action_type ='NEW_NI_CONTRACT' then
1014 update XTR_DEALS
1015 set DUAL_AUTHORISATION_BY = p_validated_by,
1019 end if;
1016 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1017 where DEAL_NO = p_deal_no
1018 and DEAL_TYPE = p_deal_type;
1020 elsif p_deal_type ='BOND' then
1021 -- Actions are as follows (and the form they originated from)
1022 -- PRO0280 - NEW_BOND_CONTRACT
1023 if p_action_type ='NEW_BOND_CONTRACT' then
1024 update XTR_DEALS
1025 set DUAL_AUTHORISATION_BY = p_validated_by,
1026 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1027 where DEAL_NO = p_deal_no
1028 and DEAL_TYPE = p_deal_type;
1029 end if;
1030 elsif p_deal_type ='SWPTN' then
1031 -- Actions are as follows (and the form they originated from)
1032 -- PRO0290 - New SWPTN Contract
1033 if p_action_type ='NEW_SWAPTION_CONTRACT' then
1034 update XTR_DEALS
1035 set DUAL_AUTHORISATION_BY = p_validated_by,
1036 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1037 where DEAL_NO = p_deal_no
1038 and DEAL_TYPE = p_deal_type;
1039 elsif p_action_type = 'EXERCISE_OF_SWAPTION_CONTRACT' then
1040 update XTR_DEALS
1041 set SETTLE_DUAL_AUTHORISATION_BY = p_validated_by,
1042 SETTLE_DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1043 where DEAL_NO = p_deal_no
1044 and DEAL_TYPE = p_deal_type;
1045 end if;
1046 elsif p_deal_type ='IRS' then
1047 -- Actions are as follows (and the form they originated from)
1048 -- bug #1295341 jhung
1049 if p_action_type ='NEW_INT_RATE_SWAP_CONTRACT' then
1050 open IRS_OTHER_DEAL;
1051 fetch IRS_OTHER_DEAL into receive_deal_no;
1052 close IRS_OTHER_DEAL;
1053
1054 update XTR_DEALS -- Update paying side deal
1055 set DUAL_AUTHORISATION_BY = p_validated_by,
1056 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1057 where DEAL_NO = p_deal_no
1058 and DEAL_TYPE = p_deal_type;
1059 update XTR_DEALS -- Update receiving side deal
1060 set DUAL_AUTHORISATION_BY = p_validated_by,
1061 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1062 where DEAL_NO = receive_deal_no
1063 and DEAL_TYPE = p_deal_type;
1064 end if;
1065 elsif p_deal_type ='TMM' then
1066 ---- Only for NEW_WHOLESALE_TERM_CONTRACT at this stage. Should use TERM_ACTIONS like Retail Term
1067 update XTR_DEALS
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
1073 update XTR_ROLLOVER_TRANSACTIONS
1074 set DUAL_AUTHORISATION_BY = p_validated_by,
1075 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1076 where DEAL_NUMBER = p_deal_no
1077 and DEAL_TYPE = p_deal_type
1078 and settle_date is null;
1079
1080 elsif p_deal_type = 'STOCK' then
1081 if p_action_type = 'NEW_STOCK_BUY_CONTRACT' then -- BUY stock deal
1082 update XTR_DEALS
1083 set DUAL_AUTHORISATION_BY = p_validated_by,
1084 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1085 where DEAL_NO = p_deal_no
1086 and DEAL_TYPE = p_deal_type;
1087
1088 update XTR_DEAL_DATE_AMOUNTS
1089 set DUAL_AUTHORISATION_BY = p_validated_by,
1090 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1091 where DEAL_NUMBER = p_deal_no
1092 and TRANSACTION_NUMBER = p_trans_no
1093 and DEAL_TYPE = p_deal_type;
1094
1095 elsif p_action_type = 'NEW_STOCK_CASH_DIVIDEND' then -- stock cash dividend
1096 update XTR_ROLLOVER_TRANSACTIONS
1097 set DUAL_AUTHORISATION_BY = p_validated_by,
1098 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1099 where DEAL_NUMBER = p_deal_no
1100 and TRANSACTION_NUMBER = p_trans_no
1101 and DEAL_TYPE = p_deal_type;
1102
1103 update XTR_DEAL_DATE_AMOUNTS
1104 set DUAL_AUTHORISATION_BY = p_validated_by,
1105 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1106 where DEAL_NUMBER = p_deal_no
1107 and TRANSACTION_NUMBER = p_trans_no
1108 and DEAL_TYPE = p_deal_type;
1109
1110 elsif p_action_type = 'NEW_STOCK_SELL_CONTRACT' then -- SELL stock deal
1111 update XTR_DEALS
1112 set DUAL_AUTHORISATION_BY = p_validated_by,
1113 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1114 where DEAL_NO = p_deal_no
1115 and DEAL_TYPE = p_deal_type;
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 DEAL_TYPE = p_deal_type;
1121 end if;
1122
1123 elsif p_deal_type ='RTMM' then
1124 -- Actions are as follows (and the form they originated from)
1125 -- PRO0235 - NEW_RETAIL_TERM_CONTRACT
1126 --- PRINCIPAL_ADJUSTMENT_OF_RETAIL_TERM_CONTRA
1127 --- RETAIL_TERM_INTEREST_RESET
1128 --- Ammend Schedule Type for Retail Term
1129 if p_action_type ='NEW_RETAIL_TERM_CONTRACT' then
1130 update XTR_DEALS
1131 set DUAL_AUTHORISATION_BY = p_validated_by,
1135
1132 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1133 where DEAL_NO = p_deal_no
1134 and DEAL_TYPE = p_deal_type;
1136 update XTR_ROLLOVER_TRANSACTIONS
1137 set DUAL_AUTHORISATION_BY = p_validated_by,
1138 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1139 where DEAL_NUMBER = p_deal_no
1140 and DEAL_TYPE = p_deal_type;
1141
1142 else
1143 update XTR_TERM_ACTIONS
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 open get_date;
1148 fetch get_date into c;
1149 close get_date;
1150 if c.INCREASE_EFFECTIVE_FROM_DATE is not null then
1151 l_date :=c.INCREASE_EFFECTIVE_FROM_DATE;
1152 elsif c.FROM_START_DATE is not null then
1153 l_date :=c.FROM_START_DATE;
1154 elsif c.EFFECTIVE_FROM_DATE is not null then
1155 l_date :=c.EFFECTIVE_FROM_DATE;
1156 end if;
1157
1158 update XTR_ROLLOVER_TRANSACTIONS
1159 set DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate),
1160 DUAL_AUTHORISATION_BY = p_validated_by
1161 where DEAL_NUMBER = p_deal_no
1162 and START_DATE >= l_date
1163 and DEAL_TYPE = p_deal_type;
1164
1165 end if;
1166 -- add on 01 July 98
1167 elsif p_deal_type ='ONC' then
1168 update XTR_ROLLOVER_TRANSACTIONS
1169 set DUAL_AUTHORISATION_BY = p_validated_by,
1170 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1171 where DEAL_NUMBER = p_deal_no
1172 and TRANSACTION_NUMBER = p_trans_no
1173 and DEAL_TYPE = p_deal_type;
1174
1175 -- bug 2254835
1176 elsif p_deal_type = 'EXP' then
1177 if p_action_type = 'NEW_EXPOSURE_TRANSACTION' then
1178 update XTR_EXPOSURE_TRANSACTIONS
1179 set DUAL_AUTHORISATION_BY = p_validated_by,
1180 DUAL_AUTHORISATION_ON = decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1181 where TRANSACTION_NUMBER = p_trans_no;
1182 end if;
1183
1184
1185 -- IAC Redesign Project bug 3800146
1186 elsif p_deal_type IN ('IAC') then
1187 if p_action_type in('INTERACCOUNT_TRANSFER') then
1188 update XTR_INTERACCT_TRANSFERS
1189 set DUAL_AUTHORISATION_BY = p_validated_by,
1190 DUAL_AUTHORISATION_ON= decode(nvl(p_validated_by,'@@'),'@@',NULL,sysdate)
1191 where TRANSACTION_NUMBER = p_trans_no;
1192 end if;
1193
1194 end if;
1195
1196 end VALIDATE_DEALS;
1197
1198
1199
1200 PROCEDURE MAINT_PROJECTED_BALANCES IS
1201 --
1202 -- This stored procedure will maintain the balances
1203 -- to reflect the actual bals + cflows from the actual bal date
1204 -- until prior to today. We can then use this balance in instances
1205 -- where the actual balance is fo an old date.
1206 --
1207 l_ccy VARCHAR2(15);
1208 l_acct_ccy VARCHAR2(15);
1209 l_acct VARCHAR2(20);
1210 l_acct_party VARCHAR2(20);
1211 l_state_date DATE;
1212 l_op_balance NUMBER;
1213 l_cflow NUMBER;
1214 l_settle_acct VARCHAR2(20);
1215 --
1216 cursor GET_OLD_ACCT_BALS is
1217 select a.ACCOUNT_NUMBER,a.OPENING_BALANCE,a.STATEMENT_DATE,a.CURRENCY,a.PARTY_CODE
1218 from XTR_BANK_ACCOUNTS a,
1219 XTR_PARTIES_V b
1220 where nvl(a.PROJECTED_BALANCE_UPDATED_ON,to_date('01/01/1980','DD/MM/YYYY')) < trunc(SYSDATE)
1221 and nvl(a.SETOFF_ACCOUNT_YN,'N') <> 'Y'
1222 and b.PARTY_CODE = a.PARTY_CODE
1223 and (b.PARTY_TYPE = 'C' or b.INTERNAL_PTY = 'Y');
1224 --
1225 cursor CFLOWS is
1226 select sum(CASHFLOW_AMOUNT),COMPANY_ACCOUNT,CURRENCY
1227 from XTR_SETTLEMENTS_V
1228 where AMOUNT_DATE > nvl(l_state_date,to_date('01/01/1980','DD/MM/YYYY'))
1229 and AMOUNT_DATE < trunc(SYSDATE)
1230 and COMPANY = l_acct_party
1231 and COMPANY_ACCOUNT = l_acct
1232 and CURRENCY = l_ccy
1233 group by COMPANY_ACCOUNT,CURRENCY;
1234 --
1235 begin
1236 open GET_OLD_ACCT_BALS;
1237 LOOP
1238 fetch GET_OLD_ACCT_BALS INTO l_acct,l_op_balance,l_state_date,l_ccy,l_acct_party;
1239 EXIT WHEN GET_OLD_ACCT_BALS%NOTFOUND;
1240 if l_state_date = (trunc(sysdate) - 1) then
1241 -- Balances are up to date therefore set projected balance = actual balance
1242 update XTR_BANK_ACCOUNTS
1243 set PROJECTED_BALANCE = OPENING_BALANCE,
1244 PROJECTED_BALANCE_UPDATED_ON = trunc(SYSDATE),
1245 PROJECTED_BALANCE_DATE = STATEMENT_DATE
1246 where ACCOUNT_NUMBER = l_acct
1247 and PARTY_CODE = l_acct_party
1248 and CURRENCY = l_ccy;
1249 else
1250 open CFLOWS;
1251 fetch CFLOWS INTO l_cflow,l_settle_acct,l_acct_ccy;
1252 if CFLOWS%FOUND then
1253 update XTR_BANK_ACCOUNTS
1254 set PROJECTED_BALANCE = nvl(l_op_balance,0) + nvl(l_cflow,0),
1255 PROJECTED_BALANCE_UPDATED_ON = trunc(SYSDATE),
1256 PROJECTED_BALANCE_DATE = (trunc(SYSDATE) -1)
1257 where ACCOUNT_NUMBER = l_settle_acct
1258 and PARTY_CODE = l_acct_party
1259 and CURRENCY = l_acct_ccy;
1260 else
1261 update XTR_BANK_ACCOUNTS
1262 set PROJECTED_BALANCE = nvl(l_op_balance,0),
1263 PROJECTED_BALANCE_UPDATED_ON = trunc(SYSDATE),
1267 and CURRENCY = l_ccy;
1264 PROJECTED_BALANCE_DATE = (trunc(SYSDATE) -1)
1265 where ACCOUNT_NUMBER = l_acct
1266 and PARTY_CODE = l_acct_party
1268 end if;
1269 close CFLOWS;
1270 end if;
1271 END LOOP;
1272 close GET_OLD_ACCT_BALS;
1273 commit;
1274 --
1275 end MAINT_PROJECTED_BALANCES;
1276
1277
1278
1279 PROCEDURE CHK_PRO_AUTH
1280 (p_event IN VARCHAR2,
1281 p_company_code IN VARCHAR2,
1282 p_user IN VARCHAR2,
1283 p_deal_type IN VARCHAR2,
1284 p_action IN VARCHAR2) is
1285
1286 l_form_nos VARCHAR2(10);
1287
1288 ex_error_auth exception;
1289 ex_error_insert exception;
1290 ex_error_delete exception;
1291 ex_error_update exception;
1292 ex_error_expiry exception;
1293 ex_error_company exception;
1294
1295 /*
1296 cursor get_user_auth is
1297 SELECT AUTHORISED,AUTH_TO_CREATE,AUTH_TO_DELETE,
1298 AUTH_TO_UPDATE,PASSWORD_EXPIRY
1299 FROM XTR_USER_AUTHORITIES
1300 where USER_NAME=p_user
1301 and FORM_NOS=l_form_nos;
1302 l_auth varchar2(1);
1303 l_insert varchar2(1);
1304 l_delete varchar2(1);
1305 l_update varchar2(1);
1306 l_expiry_date date;
1307 */
1308
1309 cursor get_user_company_auth is
1310 SELECT 'Y'
1311 FROM XTR_COMPANY_AUTHORITIES
1312 where DEALER_CODE=p_user
1313 and COMPANY_AUTHORISED_FOR_INPUT='Y'
1314 and PARTY_CODE =p_company_code;
1315 l_company_auth varchar2(1);
1316
1317 cursor get_dealtype is
1318 select name
1319 from xtr_deal_types
1320 where deal_type=p_deal_type;
1321
1322 l_mesg varchar2(200);
1323
1324 begin
1325 if p_company_code is not null then
1326 l_company_auth :='N';
1327 open get_user_company_auth;
1328 fetch get_user_company_auth into l_company_auth;
1329 close get_user_company_auth;
1330 if l_company_auth <>'Y' then
1331 raise ex_error_company;
1332 end if;
1333 end if;
1334
1335 if p_event='DEALS' then
1336 if p_deal_type='ONC' then
1337 l_form_nos :='PRO0210';
1338 elsif p_deal_type='CA' then
1339 l_form_nos :='PRO1080';
1340 elsif p_deal_type='IG' then
1341 l_form_nos :='PRO1075';
1342 elsif p_deal_type='NI' then
1343 l_form_nos :='PRO0240';
1344 elsif p_deal_type='TMM' then
1345 l_form_nos :='PRO0239';
1346 elsif p_deal_type='BOND' then
1347 l_form_nos :='PRO0280';
1348 elsif p_deal_type='DEB' then
1349 l_form_nos :='PRO0310';
1350 elsif p_deal_type='IRS' then
1351 l_form_nos :='PRO0290';
1352 elsif p_deal_type='IRO' then
1353 l_form_nos :='PRO0230';
1354 elsif p_deal_type='FRA' then
1355 l_form_nos :='PRO0770';
1356 elsif p_deal_type='SWPTN' then
1357 l_form_nos :='PRO0320';
1358 elsif p_deal_type='FUT' then
1359 l_form_nos :='PRO0330';
1360 elsif p_deal_type='FX' then
1361 l_form_nos :='PRO0170';
1362 elsif p_deal_type='FXO' then
1363 l_form_nos :='PRO0190';
1364 end if;
1365 end if;
1366 if l_form_nos is not null then
1367 l_mesg :=null;
1368 open get_dealtype;
1369 fetch get_dealtype into l_mesg;
1370 close get_dealtype;
1371 /*
1372 l_auth :='N';
1373 open get_user_auth;
1374 fetch get_user_auth into l_auth,l_insert,l_delete,l_update,l_expiry_date;
1375 close get_user_auth;
1376 if l_auth <>'Y' then
1377 raise ex_error_auth;
1378 elsif nvl(l_expiry_date,sysdate+1)<trunc(sysdate) then
1379 raise ex_error_expiry;
1380 elsif p_action='INSERT' and l_insert <>'Y' then
1381 raise ex_error_insert;
1382 elsif p_action='DELETE' and l_delete <>'Y' then
1383 raise ex_error_delete;
1384 elsif p_action='UPDATE' and l_update <>'Y' then
1385 raise ex_error_update;
1386 end if;
1387 */
1388 end if;
1389
1390 exception
1391 when ex_error_company then
1392 FND_MESSAGE.Set_Name('XTR', 'XTR_2036');
1393 FND_MESSAGE.Set_Token('P_COMPANY_CODE', p_company_code);
1394 APP_EXCEPTION.Raise_exception;
1395 /*
1396 when ex_error_auth then
1397 FND_MESSAGE.Set_Name('XTR', 'XTR_2037');
1398 FND_MESSAGE.Set_Token('L_MESG', l_mesg);
1399 APP_EXCEPTION.Raise_exception;
1400 when ex_error_expiry then
1401 FND_MESSAGE.Set_Name('XTR', 'XTR_2038');
1402 FND_MESSAGE.Set_Token('L_FORM_NOS', l_form_nos);
1403 APP_EXCEPTION.Raise_exception;
1404 when ex_error_insert then
1405 FND_MESSAGE.Set_Name('XTR', 'XTR_1003');
1406 FND_MESSAGE.Set_Token('L_MESG', l_mesg);
1407 APP_EXCEPTION.Raise_exception;
1408 when ex_error_delete then
1409 FND_MESSAGE.Set_Name('XTR', 'XTR_1004');
1410 FND_MESSAGE.Set_Token('L_MESG', l_mesg);
1411 APP_EXCEPTION.Raise_exception;
1412 when ex_error_update then
1413 FND_MESSAGE.Set_Name('XTR', 'XTR_1005');
1414 FND_MESSAGE.Set_Token('L_MESG', l_mesg);
1415 APP_EXCEPTION.Raise_exception;
1416 */
1417 END CHK_PRO_AUTH;
1418
1419
1420 PROCEDURE SETOFF is
1421 -- Called form 1080 - banak a/c maintenance
1422 l_calc_date DATE;
1423 roundfac NUMBER;
1424 yr_basis NUMBER;
1425 l_ccy VARCHAR(15);
1426 l_setoff VARCHAR(5);
1427 l_setoff_company VARCHAR(7);
1428 l_bank_code VARCHAR(7);
1429 l_no_days NUMBER;
1430 l_prv_date DATE;
1431 l_this_rate NUMBER;
1432 l_prv_rate NUMBER;
1433 l_rate NUMBER;
1434 l_prv_bal NUMBER;
1435 l_int_bf NUMBER;
1436 l_int_cf NUMBER;
1437 l_int_set NUMBER;
1438 l_interest NUMBER;
1439 l_this_bal NUMBER;
1440 --
1441 cursor SEL_SETOFF_ACCT is
1442 select distinct rtrim(SETOFF),rtrim(BANK_CODE),
1443 SETOFF_COMPANY,CURRENCY
1444 from XTR_BANK_ACCOUNTS;
1445
1446 cursor FIND_SETOFF_RATE is
1447 select INTEREST_RATE
1448 from XTR_INTEREST_RATE_RANGES
1449 where REF_CODE = l_setoff||'-'||l_bank_code
1450 and MIN_AMT <= l_this_bal
1451 and MAX_AMT >= l_this_bal;
1452 --
1453 cursor RNDING is
1454 select ROUNDING_FACTOR,YEAR_BASIS
1455 from XTR_MASTER_CURRENCIES_V
1456 where CURRENCY = l_ccy;
1457 --
1458 cursor SETOFF_CAL_DATE is
1459 select distinct BALANCE_DATE
1460 from XTR_BANK_BALANCES
1461 where BALANCE_DATE >= (select max(BALANCE_DATE)
1462 from XTR_BANK_BAL_INTERFACE)
1463 and SETOFF = l_setoff
1464 order by BALANCE_DATE asc;
1465 --
1466 cursor SETOFF_PRV_RATE is
1467 select INTEREST_RATE
1468 from XTR_BANK_BALANCES
1469 where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
1470 and BALANCE_DATE = l_prv_date;
1471 --
1472 cursor SETOFF_PREV_DETAILS is
1473 select a.BALANCE_DATE,nvl(sum(a.BALANCE_CFLOW),0),
1474 nvl(sum(a.ACCUM_INT_CFWD),0)
1475 from XTR_BANK_BALANCES a
1476 where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
1477 and a.BALANCE_DATE = (select max(b.BALANCE_DATE)
1478 from XTR_BANK_BALANCES b
1479 where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
1480 and b.BALANCE_DATE < l_calc_date)
1481 group by a.BALANCE_DATE,a.ACCOUNT_NUMBER;
1482 --
1483 cursor SETOFF_THIS_DETAILS is
1484 select nvl(sum(a.BALANCE_CFLOW),0)
1485 from XTR_BANK_BALANCES a
1486 where SETOFF = l_setoff
1487 and a.BALANCE_DATE = l_calc_date;
1488 --
1489 begin
1490 -- Calculate Setoff details
1491 open SEL_SETOFF_ACCT;
1492 LOOP
1493 fetch SEL_SETOFF_ACCT INTO l_setoff,l_bank_code,
1494 l_setoff_company,l_ccy;
1495 EXIT WHEN SEL_SETOFF_ACCT%NOTFOUND;
1496 delete XTR_BANK_BALANCES
1497 where ACCOUNT_NUMBER = l_setoff||'-'||l_bank_code
1498 and BALANCE_DATE >= (select max(BALANCE_DATE)
1499 from XTR_BANK_BAL_INTERFACE);
1500 open SETOFF_CAL_DATE;
1501 LOOP
1502 fetch SETOFF_CAL_DATE INTO l_calc_date;
1503 EXIT WHEN SETOFF_CAL_DATE%NOTFOUND;
1504 open SETOFF_PREV_DETAILS;
1505 fetch SETOFF_PREV_DETAILS INTO l_prv_date,l_prv_bal,l_int_bf;
1506 if SETOFF_PREV_DETAILS%NOTFOUND then
1507 l_prv_date := l_calc_date;
1508 l_prv_bal := 0;
1509 l_int_bf := 0;
1510 l_no_days := 0;
1511 end if;
1512 open SETOFF_THIS_DETAILS;
1513 fetch SETOFF_THIS_DETAILS INTO l_this_bal;
1514 close SETOFF_THIS_DETAILS;
1515 open FIND_SETOFF_RATE;
1516 fetch FIND_SETOFF_RATE INTO l_rate;
1517 if FIND_SETOFF_RATE%NOTFOUND then
1518 l_rate := 0;
1519 end if;
1520 close FIND_SETOFF_RATE;
1521 close SETOFF_PREV_DETAILS;
1522 open RNDING;
1523 fetch RNDING INTO roundfac,yr_basis;
1524 close RNDING;
1525 open SETOFF_PRV_RATE;
1526 fetch SETOFF_PRV_RATE INTO l_prv_rate;
1527 if SETOFF_PRV_RATE%NOTFOUND then
1528 l_prv_rate := 0;
1529 end if;
1530 close SETOFF_PRV_RATE;
1531 l_no_days := (trunc(l_calc_date) - trunc(l_prv_date));
1532 l_interest := round(l_prv_bal * l_prv_rate / 100 * l_no_days
1533 / yr_basis,roundfac);
1534 l_int_cf := l_int_bf + l_interest;
1535 l_rate := nvl(l_rate,0);
1536 insert into XTR_BANK_BALANCES
1537 (COMPANY_CODE,ACCOUNT_NUMBER,BALANCE_DATE,NO_OF_DAYS,
1538 STATEMENT_BALANCE,BALANCE_ADJUSTMENT,BALANCE_CFLOW,
1539 ACCUM_INT_BFWD,INTEREST,INTEREST_RATE,INTEREST_SETTLED,
1540 INTEREST_SETTLED_HCE,ACCUM_INT_CFWD,
1541 created_on, created_by)
1542 values
1543 (l_setoff_company,l_setoff||'-'||l_bank_code,
1544 l_calc_date,l_no_days,l_this_bal,0,l_this_bal,l_int_bf,
1545 l_interest,l_rate,0,0,l_int_cf,
1546 sysdate, fnd_global.user_id);
1547 END LOOP;
1548 close SETOFF_CAL_DATE;
1549 END LOOP;
1550 close SEL_SETOFF_ACCT;
1551 commit;
1552 end SETOFF;
1553
1554
1555 END XTR_MISC_P;
[Home] [Help]