[Home] [Help]
PACKAGE BODY: APPS.XTR_LIMITS_P
Source
1 PACKAGE BODY XTR_LIMITS_P as
2 /* $Header: xtrlmtsb.pls 120.8 2012/03/19 22:57:32 nipant ship $ */
3 --
4 -- Global Variables/Exceptions
5 --
6 ex_no_limit_exists exception;
7 ex_auth_not_y exception;
8 --
9 ------------------------------------------------------------------------------------------------------------------------------------
10 FUNCTION WEIGHTED_USAGE(p_deal_type VARCHAR2,
11 p_deal_subtype VARCHAR2,
12 p_amount_date DATE,
13 p_hce_amount NUMBER)
14 RETURN NUMBER is
15 --
16 -- Calculate amount utilised for this limit code, by this deal type/subtype and date combination.
17 --
18 cursor c_get_weighting (pc_deal_type VARCHAR2,
19 pc_deal_subtype VARCHAR2,
20 pc_amount_date date) is
21 select nvl(c.LIMIT_WEIGHTING / 100,1)
22 from XTR_FX_PERIOD_WEIGHTINGS c
23 where c.DEAL_TYPE = pc_deal_type
24 and c.DEAL_SUBTYPE = pc_deal_subtype
25 and c.NOS_MONTHS =
26 (select max(d.NOS_MONTHS)
27 from XTR_FX_PERIOD_WEIGHTINGS d
28 where d.DEAL_TYPE = c.DEAL_TYPE
32 ,months_between(trunc(pc_amount_date),trunc(sysdate))));
29 and d.DEAL_SUBTYPE = c.DEAL_SUBTYPE
30 and d.NOS_MONTHS <= decode(sign(months_between(trunc(pc_amount_date),trunc(sysdate)))
31 ,-1,0
33 --
34 v_weight NUMBER;
35 --
36 begin
37 open c_get_weighting (p_deal_type,p_deal_subtype,p_amount_date);
38 fetch c_get_weighting into v_weight;
39 if c_get_weighting%NOTFOUND then
40 -- Default to 100%
41 v_weight := 1;
42 end if;
43 close c_get_weighting;
44 --
45 return(nvl(round(nvl(p_hce_amount,0) * v_weight,0),0));
46 end;
47 ------------------------------------------------------------------------------------------------------------------------------------
48 FUNCTION CONVERT_TO_HCE_AMOUNT(p_amount_to_convert NUMBER,
49 p_currency VARCHAR2,
50 p_company_code VARCHAR2)
51 RETURN NUMBER is
52 --
53 -- Calculate home-currency-equivalent for p_amount_to_convert
54 -- (the home country will that stored for p_company_code in PARTIES).
55 --
56 --
57 cursor HC_RATE (pc2_amount number, pc2_currency VARCHAR2) is
58 select round((pc2_amount / nvl(s.HCE_RATE ,1)),nvl(s.ROUNDING_FACTOR,2))
59 from XTR_MASTER_CURRENCIES_V s
60 where s.currency = pc2_currency;
61 --
62 v_home_currency VARCHAR2(15);
63 v_hce_utilised_amt NUMBER;
64 --
65 begin
66 open hc_rate( p_amount_to_convert,p_currency);
67 fetch hc_rate into v_hce_utilised_amt;
68 close hc_rate;
69 return(v_hce_utilised_amt);
70 end;
71 ------------------------------------------------------------------------------------------------------------------------------------
72 FUNCTION GET_HCE_AMOUNT(p_amount_to_convert NUMBER,
73 p_currency VARCHAR2)
74 RETURN NUMBER is
75 --
76 -- Calculate home-currency-equivalent for p_amount_to_convert
77 --
78 cursor HC_RATE (pc2_amount number, pc2_currency VARCHAR2) is
79 select round((pc2_amount / nvl(s.HCE_RATE ,1)),nvl(s.ROUNDING_FACTOR,2))
80 from XTR_MASTER_CURRENCIES_V s
81 where s.currency = pc2_currency;
82 --
83 v_hce_utilised_amt NUMBER;
84 --
85 begin
86 open hc_rate( p_amount_to_convert,p_currency);
87 fetch hc_rate into v_hce_utilised_amt;
88 close hc_rate;
89 return( v_hce_utilised_amt );
90 end;
91 ------------------------------------------------------------------------------------------------------------------------------------
92 --
93 -- Returns the number of limit checks that have been logged
94 -- If the system parameter, 'DISPLAY_LIMIT_WARNING' is set to 'N', then
95 -- it will return 0 even if logs have been made.
96 --
97 FUNCTION LOG_FULL_LIMITS_CHECK (
98 p_DEAL_NUMBER NUMBER,
99 p_TRANSACTION_NUMBER NUMBER,
100 p_COMPANY_CODE VARCHAR2,
101 p_DEAL_TYPE VARCHAR2,
102 p_DEAL_SUBTYPE VARCHAR2,
103 p_CPARTY_CODE VARCHAR2,
104 p_PRODUCT_TYPE VARCHAR2,
105 p_LIMIT_CODE VARCHAR2,
106 p_LIMIT_PARTY VARCHAR2,
107 p_AMOUNT_DATE DATE,
108 p_AMOUNT NUMBER,
109 p_DEALER_CODE VARCHAR2,
110 p_CURRENCY VARCHAR2,
111 p_CURRENCY_SECOND VARCHAR2) return number is
112 -- Second currency and amount added for Limit check in FX deals. bug 1289530
113 --
114 -- Do ALL the "limits" checks and log any errors to the log table.
115 --
116 cursor c_get_country_group (pc_party_code VARCHAR2) is
117 select upper(country_code),upper(nvl(cross_ref_to_other_party,pc_party_code))
118 from XTR_PARTY_INFO
119 where party_code = pc_party_code;
120 --
121 cursor c_get_limit_type (pc_limit_code VARCHAR2,pc_comp_code VARCHAR2) is
122 select upper(limit_type)
123 from XTR_COMPANY_LIMITS
124 where limit_code = pc_limit_code
125 and company_code = pc_comp_code;
126 --
127 cursor get_home_ccy is
128 select param_value
129 from XTR_PRO_PARAM
130 where param_name = 'SYSTEM_FUNCTIONAL_CCY';
131 --
132 -- Old utilised will only be picked up if all the variables have remained the same
133 -- ie only amount has been altered
134 cursor get_old_utilised (pc_deal_number NUMBER) is
135 select sum(nvl(hce_utilised_amount,0))
136 from XTR_MIRROR_DDA_LIMIT_ROW
137 where deal_number = pc_deal_number
138 and limit_code = p_limit_code
139 and limit_party = p_cparty_code
140 and currency = p_currency
141 and company_code = p_company_code;
142 --
143 -- bug 1687715 new cursor for ONC deals
144 cursor get_old_utilised_onc (pc_deal_number NUMBER, pc_trans_number NUMBER) is
145 select sum(nvl(hce_utilised_amount,0))
146 from XTR_MIRROR_DDA_LIMIT_ROW
147 where deal_number = pc_deal_number
148 and transaction_number = pc_trans_number
149 and limit_code = p_limit_code
150 and limit_party = p_cparty_code
151 and currency = p_currency
152 and company_code = p_company_code;
153 -- end bug 1687715
154 --
155 cursor c_get_seq is
156 select XTR_LIMITS_EXCESS_LOG_S.nextval
157 from DUAL;
158 --
162 where param_name = pc3_param_name;
159 cursor c_ok_limit_chk (pc3_param_name VARCHAR2) is
160 select param_value
161 from XTR_PRO_PARAM
163 --
164 cursor c_date_restr is
165 select range
166 from XTR_TIME_RESTRICTIONS
167 where deal_type = p_deal_type
168 and (cparty_code like p_cparty_code or cparty_code is null)
169 and (deal_subtype like p_deal_subtype or deal_subtype is null)
170 and (security_name like p_product_type or security_name is null)
171 order by cparty_code,security_name,deal_subtype;
172 --
173 CURSOR c_limit_control IS
174 SELECT substr(param_name,13) limit_check
175 FROM xtr_pro_param_v
176 WHERE param_name in ('LIMIT_CHECK_CPARTY','LIMIT_CHECK_GROUP','LIMIT_CHECK_SOVRN')
177 AND nvl(param_value,'N') = 'Y';
178 --
179 v_limit_check_type VARCHAR2(8);
180
181 v_limit_type VARCHAR2(2);
182 v_home_ccy VARCHAR2(15);
183 v_company_code VARCHAR2(7);
184 v_limit_amt NUMBER;
185 v_util_amt NUMBER;
186 v_err_code VARCHAR2(8);
187 v_country_code XTR_PARTY_INFO.country_code%TYPE;
188 v_group_party VARCHAR2(7);
189 v_time_limit NUMBER;
190 --
191 v_hce_amount NUMBER;
192 v_new_hce_util NUMBER;
193 --
194 v_logged_yn BOOLEAN := FALSE;
195 v_unique_num NUMBER;
196 v_time_chk_reqd VARCHAR2(1);
197 v_settle_warn VARCHAR2(8);
198 v_old_utilised NUMBER := 0;
199 v_gross_amt NUMBER := 0;
200 v_dummy NUMBER := 0;
201 --
202 begin
203 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
204 xtr_debug_pkg.debug('Before LOG_FULL_LIMITS_CHK on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
205 END IF;
206 -- Get a unique ID
207 open c_get_seq;
208 fetch c_get_seq into v_unique_num;
209 close c_get_seq;
210 --
211 if substr(p_company_code,1,1) = '@' then
212 -- indicates that limit check is being called from 0007 (comp is passed in with
213 -- @ preceeding(saves adding another parameter), therefore settle limit check should
214 -- be for actual settlements not those that may be settled
215 v_company_code := substr(p_company_code,2);
216 v_settle_warn := 'EXCEEDED';
217 -- note we will only check for actual settlement limit when calling from this form
218 else
219 v_company_code := p_company_code;
220 v_settle_warn := 'WARNING';
221 end if;
222 --
223
224 /* Move to concurrent processing
225 -- Check limits have been refreshed for today
226 CALC_ALL_MIRROR_DDA_LIMIT_ROW('N');
227 */
228 --
229 -- Compute usage for this new (as yet uncommitted) record,
230 -- first calculate home-currency-equivalent for p_amount...
231 v_hce_amount := xtr_limits_p.CONVERT_TO_HCE_AMOUNT(abs(p_amount),
232 p_CURRENCY,
233 v_company_code);
234 --
235 v_new_hce_util := xtr_limits_p.WEIGHTED_USAGE(p_deal_type,p_deal_subtype,
236 p_amount_date,v_hce_amount);
237 --
238 --bug 1687715 ONC deals has deal number while insert the check should be done for trans no
239 if p_deal_number is NOT NULL and p_deal_type <> 'ONC' then
240 -- this is being called from an update of a deal therefore we need to
241 -- take out the old utilised (offset) against the new utilised
242 open get_old_utilised(p_deal_number);
243 fetch get_old_utilised into v_old_utilised;
244 close get_old_utilised;
245 elsif p_transaction_number is NOT NULL and p_deal_type = 'ONC' then
246 -- this is being called from an update of a deal therefore we need to
247 -- take out the old utilised (offset) against the new utilised
248 open get_old_utilised_onc(p_deal_number, p_transaction_number);
249 fetch get_old_utilised_onc into v_old_utilised;
250 close get_old_utilised_onc;
251 -- end bug 1687715
252 else
253 v_old_utilised := 0;
254 end if;
255 -- Substract old utilised from new utilised so we don't double up
256 v_new_hce_util := nvl(v_new_hce_util,0) - nvl(v_old_utilised,0);
257 v_gross_amt := v_hce_amount;
258 --
259 --
260 if v_new_hce_util > 0 then
261 open c_get_country_group(p_limit_party);
262 fetch c_get_country_group into v_country_code,v_group_party;
263 if c_get_country_group%NOTFOUND then
264 v_country_code := null;
265 v_group_party := p_limit_party;
266 end if;
267 close c_get_country_group;
268
269
270 ---
271 -- ** Check if limit code is required (Refer to bug 917778)
272 --
273 -- if p_limit_code is NULL then /* RV BUG # 1605612 */
274
275
276 if (p_limit_code is NULL) AND (v_settle_warn = 'WARNING')then
277 open c_limit_control;
278 loop
279 fetch c_limit_control into v_limit_check_type;
280 exit when c_limit_control%NOTFOUND;
281 v_err_code :='NO_LIMIT';
282 INSERT into XTR_limit_excess_log
283 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
284 COMPANY_CODE,LIMIT_CODE,
285 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
286 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
287 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
288 VALUES
289 (v_unique_num,-1,null,
290 v_company_code, p_limit_code,
291 p_limit_party, p_amount_date,nvl(v_hce_amount,0),NULL,
295 end loop;
292 NULL, NULL, p_dealer_code,
293 v_limit_check_type,v_err_code,sysdate);
294 v_logged_yn := TRUE;
296 close c_limit_control;
297 end if;
298
299
300 -- *** NOW DO EACH LIMIT CHECK ***
301 --
302 -- ********** 1. check global limits
303 --
304 if v_company_code is NOT null and p_limit_code is NOT null and v_settle_warn = 'WARNING'
305 then
306 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
307 xtr_debug_pkg.debug('Before Get_lim_global on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
308 END IF;
309 XTR_LIMITS_P.GET_LIM_GLOBAL (p_DEAL_NUMBER,v_company_code,p_limit_code,
310 v_limit_amt,v_util_amt,v_err_code);
311 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
312 xtr_debug_pkg.debug('After Get_lim_global on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
313 END IF;
314 if v_err_code is null then
315 v_util_amt := v_util_amt + v_new_hce_util;
316 if v_util_amt > v_limit_amt then
317 INSERT into XTR_limit_excess_log
318 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
319 COMPANY_CODE,LIMIT_CODE,
320 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
321 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
322 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
323 VALUES
324 (v_unique_num,-1,null,
325 v_company_code, p_limit_code,
326 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
327 v_util_amt - v_limit_amt, NULL, p_dealer_code,
328 'GLOBAL','EXCEEDED',sysdate);
329 v_logged_yn := TRUE;
330 end if;
331 else
332 INSERT into XTR_limit_excess_log
333 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
334 COMPANY_CODE,LIMIT_CODE,
335 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
336 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
337 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
338 VALUES
339 (v_unique_num,-1,null,
340 v_company_code, p_limit_code,
341 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
342 v_util_amt - v_limit_amt, NULL, p_dealer_code,
343 'GLOBAL',v_err_code,sysdate);
344 v_logged_yn := TRUE;
345 end if;
346 end if;
347 --
348 -- ********** 2. check sovereign limits
349 --
350 --
351 if v_company_code is NOT null and v_country_code is NOT null and v_settle_warn = 'WARNING'
352 then
353 XTR_LIMITS_P.GET_LIM_SOVEREIGN(p_DEAL_NUMBER,v_company_code,upper(v_country_code),
354 v_limit_amt,v_util_amt,v_err_code);
355 if v_err_code is null then
356 v_util_amt := v_util_amt + v_new_hce_util;
357 if v_util_amt > v_limit_amt then
358 INSERT into XTR_limit_excess_log
359 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
360 COMPANY_CODE,LIMIT_CODE,
361 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
362 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
363 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
364 VALUES
365 (v_unique_num,-1,null,
366 v_company_code, p_limit_code,
367 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
368 v_util_amt - v_limit_amt, NULL, p_dealer_code,
369 'SOVRN','EXCEEDED',sysdate);
370 v_logged_yn := TRUE;
371 end if;
372 else
373 INSERT into XTR_limit_excess_log
374 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
375 COMPANY_CODE,LIMIT_CODE,
376 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
377 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
378 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
379 VALUES
380 (v_unique_num,-1,null,
381 v_company_code, p_limit_code,
382 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
383 v_util_amt - v_limit_amt, NULL, p_dealer_code,
384 'SOVRN',v_err_code,sysdate);
385 v_logged_yn := TRUE;
386 end if;
387 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
388 xtr_debug_pkg.debug('After sovereign limits on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
389 END IF;
390 end if;
391 --
392 -- ********** 3. check dealer-deal limits
393 --
394 if p_dealer_code is NOT null and p_deal_type is NOT null and p_product_type is NOT null
395 and v_settle_warn = 'WARNING' then
396 XTR_LIMITS_P.GET_LIM_DEALER_DEAL ( p_DEAL_NUMBER,p_dealer_code,p_deal_type,p_product_type,
397 v_limit_amt,v_err_code);
398 if v_err_code is null then
399 v_util_amt := v_hce_amount;
400 if v_gross_amt > v_limit_amt then
401 INSERT into XTR_limit_excess_log
402 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
403 COMPANY_CODE,LIMIT_CODE,
404 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
405 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
406 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
407 VALUES
408 (v_unique_num,-1,null,
409 v_company_code, p_limit_code,
410 p_limit_party, p_amount_date,v_gross_amt,v_limit_amt,
411 v_gross_amt - v_limit_amt, NULL, p_dealer_code,
412 'DLR_DEAL','EXCEEDED',sysdate);
413 v_logged_yn := TRUE;
414 end if;
415 elsif v_err_code = 'NO_LIMIT' then
416 INSERT into XTR_limit_excess_log
417 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
418 COMPANY_CODE,LIMIT_CODE,
419 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
420 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
424 v_company_code, p_limit_code,
421 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
422 VALUES
423 (v_unique_num,-1,null,
425 p_limit_party, p_amount_date,v_gross_amt,NULL,
426 NULL, NULL, p_dealer_code,
427 'DLR_DEAL',v_err_code,sysdate);
428 v_logged_yn := TRUE;
429 else
430 INSERT into XTR_limit_excess_log
431 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
432 COMPANY_CODE,LIMIT_CODE,
433 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
434 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
435 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
436 VALUES
437 (v_unique_num,-1,null,
438 v_company_code, p_limit_code,
439 p_limit_party, p_amount_date,v_gross_amt,v_limit_amt,
440 v_gross_amt - v_limit_amt, NULL, p_dealer_code,
441 'DLR_DEAL',v_err_code,sysdate);
442 v_logged_yn := TRUE;
443 end if;
444 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
445 xtr_debug_pkg.debug('After dealer_deal limits on '||to_char(sysdate,'MM:DD:HH24:MI:SS'));
446 END IF;
447 end if;
448 --
449 -- ********** 4. check cparty limits
450 --
451 if v_company_code is NOT null and p_cparty_code is NOT null and p_limit_code is NOT null
452 and v_settle_warn = 'WARNING' then
453 XTR_LIMITS_P.GET_LIM_CPARTY( p_DEAL_NUMBER,v_company_code,p_cparty_code,p_limit_code,
454 v_limit_amt,v_util_amt,v_err_code);
455 if v_err_code is null then
456 v_util_amt := v_util_amt + v_new_hce_util;
457 if v_util_amt > v_limit_amt then
458 INSERT into XTR_limit_excess_log
459 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
460 COMPANY_CODE,LIMIT_CODE,
461 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
462 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
463 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
464 VALUES
465 (v_unique_num,-1,null,
466 v_company_code, p_limit_code,
467 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
468 v_util_amt - v_limit_amt, NULL, p_dealer_code,
469 'CPARTY','EXCEEDED',sysdate);
470 v_logged_yn := TRUE;
471 end if;
472 else
473 INSERT into XTR_limit_excess_log
474 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
475 COMPANY_CODE,LIMIT_CODE,
476 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
477 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
478 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
479 VALUES
480 (v_unique_num,-1,null,
481 v_company_code, p_limit_code,
482 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
483 v_util_amt - v_limit_amt, NULL, p_dealer_code,
484 'CPARTY',v_err_code,sysdate);
485 v_logged_yn := TRUE;
486 end if;
487 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
488 xtr_debug_pkg.debug('After cparty limits on '||to_char(sysdate,'MM:DD:HH24:MI:SS'));
489 END IF;
490 end if;
491 --
492 -- ********** 5. check settle limits
493 --
494 -- bug 2428516
495 v_dummy:=0;
496 if (p_amount<0) then --payment
497 SELECT COUNT(*)
498 INTO v_dummy
499 FROM XTR_PRO_PARAM
500 WHERE PARAM_NAME='LIMIT_INCLUDE_PAYMENTS'
501 AND PARAM_VALUE='N';
502 end if;
503 if v_company_code is NOT null and p_limit_party is NOT null and p_amount_date is NOT null and v_dummy=0 then
504 -- end bug 2428516
505 if v_settle_warn = 'WARNING' then
506 -- call normal settlement check - ie reflects excess if all settlements occur on amount date (warning only)
507 XTR_LIMITS_P.GET_LIM_SETTLE( p_DEAL_NUMBER,v_company_code,p_limit_party,p_amount_date,
508 v_limit_amt,v_util_amt,v_err_code);
509 else
510 -- excess only for authorised settlements - ie actual excess as settlements are authorised in pro0007
511 -- ie this is an actal excess as the settlements have been authorised
512 XTR_LIMITS_P.GET_ACTUAL_SETTLE_EXCESS( p_DEAL_NUMBER,v_company_code,p_limit_party,p_amount_date,
513 v_limit_amt,v_util_amt,v_err_code);
514 end if;
515 if v_err_code is null then
516 v_util_amt := v_util_amt + v_hce_amount;
517 if v_util_amt > v_limit_amt then
518 INSERT into XTR_limit_excess_log
519 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
520 COMPANY_CODE,LIMIT_CODE,
521 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
522 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
523 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
524 VALUES
525 (v_unique_num,-1,null,
526 v_company_code, p_limit_code,
527 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
528 v_util_amt - v_limit_amt, NULL, p_dealer_code,
529 'SETTLE',v_settle_warn,sysdate);
530 v_logged_yn := TRUE;
531 end if;
532 else
533 INSERT into XTR_limit_excess_log
534 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
535 COMPANY_CODE,LIMIT_CODE,
536 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
537 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
538 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
539 VALUES
540 (v_unique_num,-1,null,
541 v_company_code, p_limit_code,
542 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
543 v_util_amt - v_limit_amt, NULL, p_dealer_code,
544 'SETTLE',v_err_code,sysdate);
545 v_logged_yn := TRUE;
546 end if;
547 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
551 --
548 xtr_debug_pkg.debug('After settle limits on '||to_char(sysdate,'MM:DD:HH24:MI:SS'));
549 END IF;
550 end if;
552 -- ********** 6.1. Check Currency Limits for first currency
553 --
554 /* open get_home_ccy;
555 fetch get_home_ccy into v_home_ccy;
556 close get_home_ccy; */
557 --
558 -- bug 1289530 currency limit check should be done for home ccy
559 -- if p_currency is NOT null and p_currency <> v_home_ccy and v_settle_warn = 'WARNING' then
560 if p_currency is NOT null and v_settle_warn = 'WARNING' then
561 -- end bug 1289530
562 -- Only check currency limits for non domestic currencies
563 XTR_LIMITS_P.GET_LIM_CCY(p_DEAL_NUMBER,p_currency,v_limit_amt,v_util_amt,v_err_code);
564 if v_err_code is null then
565 v_util_amt := v_util_amt + v_hce_amount;
566 if v_util_amt > v_limit_amt then
567 INSERT into XTR_limit_excess_log
568 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
569 COMPANY_CODE,LIMIT_CODE,
570 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
571 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
572 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE, CURRENCY) -- bug 1289530
573 VALUES
574 (v_unique_num,-1,null,
575 v_company_code, p_limit_code,
576 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
577 v_util_amt - v_limit_amt, NULL, p_dealer_code,
578 'CCY','EXCEEDED',sysdate, p_currency); -- bug 1289530
579 v_logged_yn := TRUE;
580 end if;
581 else
582 INSERT into XTR_limit_excess_log
583 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
584 COMPANY_CODE,LIMIT_CODE,
585 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
586 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
587 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE, CURRENCY) -- bug 1289530
588 VALUES
589 (v_unique_num,-1,null,
590 v_company_code, p_limit_code,
591 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
592 v_util_amt - v_limit_amt, NULL, p_dealer_code,
593 'CCY',v_err_code,sysdate, p_currency); -- bug 1289530
594 v_logged_yn := TRUE;
595 end if;
596 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
597 xtr_debug_pkg.debug('After currency limits for first ccy on '||to_char(sysdate,'MM:DD:HH24:MI:SS'));
598 END IF;
599 end if;
600 --
601 -- bug 1289530 currency limit check for second currency
602 -- ********** 6.2. Check Currency Limits for second currency
603 --
604 if p_currency_second is NOT null and v_settle_warn = 'WARNING' then
605 XTR_LIMITS_P.GET_LIM_CCY(p_DEAL_NUMBER,p_currency_second,v_limit_amt,v_util_amt,v_err_code);
606 if v_err_code is null then
607 v_util_amt := v_util_amt + v_hce_amount;
608 if v_util_amt > v_limit_amt then
609 INSERT into XTR_limit_excess_log
610 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
611 COMPANY_CODE,LIMIT_CODE,
612 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
613 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
614 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE, CURRENCY) -- bug 1289530
615 VALUES
616 (v_unique_num,-1,null,
617 v_company_code, p_limit_code,
618 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
619 v_util_amt - v_limit_amt, NULL, p_dealer_code,
620 'CCY','EXCEEDED',sysdate, p_currency_second); -- bug 1289530
621 v_logged_yn := TRUE;
622 end if;
623 else
624 INSERT into XTR_limit_excess_log
625 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
626 COMPANY_CODE,LIMIT_CODE,
627 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
628 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
629 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE, CURRENCY) -- bug 1289530
630 VALUES
631 (v_unique_num,-1,null,
632 v_company_code, p_limit_code,
633 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
634 v_util_amt - v_limit_amt, NULL, p_dealer_code,
635 'CCY',v_err_code,sysdate, p_currency_second); -- bug 1289530
636 v_logged_yn := TRUE;
637 end if;
638 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
639 xtr_debug_pkg.debug('After currency limits for second ccy on '||to_char(sysdate,'MM:DD:HH24:MI:SS'));
640 END IF;
641 end if;
642 -- end bug 1289530
643 --
644 -- ********** 7. check group limits
645 --
646 if v_company_code is NOT null and v_group_party is NOT null and p_limit_code is NOT null
647 and v_settle_warn = 'WARNING' then
648 open c_get_limit_type(p_limit_code,v_company_code);
649 fetch c_get_limit_type into v_limit_type;
650 close c_get_limit_type;
651 --
652 XTR_LIMITS_P.GET_LIM_GROUP(p_DEAL_NUMBER,v_company_code,v_limit_type,v_group_party,
653 v_limit_amt,v_util_amt,v_err_code);
654 if v_err_code is null then
655 v_util_amt := v_util_amt + v_new_hce_util;
656 if v_util_amt > v_limit_amt then
657 INSERT into XTR_limit_excess_log
658 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
659 COMPANY_CODE,LIMIT_CODE,
660 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
661 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
662 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
663 VALUES
664 (v_unique_num,-1,null,
665 v_company_code, p_limit_code,
669 v_logged_yn := TRUE;
666 v_group_party, p_amount_date,v_hce_amount,v_limit_amt,
667 v_util_amt - v_limit_amt, NULL, p_dealer_code,
668 'GROUP','EXCEEDED',sysdate);
670 end if;
671 else
672 INSERT into XTR_limit_excess_log
673 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
674 COMPANY_CODE,LIMIT_CODE,
675 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
676 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
677 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
678 VALUES
679 (v_unique_num,-1,null,
680 v_company_code, p_limit_code,
681 v_group_party, p_amount_date,v_hce_amount,v_limit_amt,
682 v_util_amt - v_limit_amt, NULL, p_dealer_code,
683 'GROUP',v_err_code,sysdate);
684 v_logged_yn := TRUE;
685 end if;
686 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
687 xtr_debug_pkg.debug('After group limits on '||to_char(sysdate,'MM:DD:HH24:MI:SS'));
688 END IF;
689 end if;
690 --
691 -- ********** 8. Time Restrictions
692 --
693 open c_ok_limit_chk('LIMIT_CHECK_TIME');
694 fetch c_ok_limit_chk into v_time_chk_reqd;
695 if c_ok_limit_chk%NOTFOUND then
696 v_time_chk_reqd := 'Y';
697 end if;
698 close c_ok_limit_chk;
699 --
700 if nvl(v_time_chk_reqd,'Y') = 'Y' and v_settle_warn = 'WARNING' then
701 -- bug 1207970 time limit does not depend on counterparty
702 -- if p_deal_type is NOT null and p_cparty_code is NOT null and p_amount_date is NOT null then
703 if p_deal_type is NOT null and p_amount_date is NOT null then
704 -- end bug 1207970
705 open c_date_restr;
706 fetch c_date_restr into v_time_limit;
707 if c_date_restr%FOUND then
708 if (trunc(p_amount_date) - trunc(sysdate)) > v_time_limit then
709 INSERT into XTR_limit_excess_log
710 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
711 COMPANY_CODE,LIMIT_CODE,
712 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
713 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
714 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
715 VALUES
716 (v_unique_num,-1,null,
717 v_company_code,null,p_cparty_code,p_amount_date,0,null,
718 ((trunc(p_amount_date) - trunc(sysdate)) - v_time_limit),
719 null,p_dealer_code,'TIME','EXCEEDED',sysdate);
720 v_logged_yn := TRUE;
721 end if;
722 end if;
723 close c_date_restr;
724 end if;
725 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
726 xtr_debug_pkg.debug('After time limits on '||to_char(sysdate,'MM:DD:HH24:MI:SS'));
727 END IF;
728 end if;
729 --
730 --
731 --
732 /*
733 -- ********** check intra-day limits (NOT REQD AT THIS STAGE)
734 --
735 if p_dealer_code is NOT null and p_deal_type is NOT null then
736 XTR_LIMITS_P.GET_LIM_INTRA_DAY ( p_DEAL_NUMBER,p_dealer_code,p_deal_type,
737 v_limit_amt,v_util_amt,v_err_code);
738 if v_err_code is null then
739 v_util_amt := v_util_amt + v_hce_amount;
740 if v_util_amt > v_limit_amt then
741 INSERT into XTR_limit_excess_log
742 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
743 COMPANY_CODE,LIMIT_CODE,
744 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
745 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
746 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
747 VALUES
748 (v_unique_num,-1,null,
749 v_company_code, p_limit_code,
750 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
751 v_util_amt - v_limit_amt, NULL, p_dealer_code,
752 'INTRA_DY','EXCEEDED',sysdate);
753 v_logged_yn := TRUE;
754 end if;
755 else
756 INSERT into XTR_limit_excess_log
757 (LOG_ID,DEAL_NUMBER,TRANSACTION_NUMBER,
758 COMPANY_CODE,LIMIT_CODE,
759 LIMIT_PARTY,AMOUNT_DATE,HCE_AMOUNT,LIMITING_AMOUNT,
760 EXCEEDED_BY_AMOUNT,AUTHORISED_BY,DEALER_CODE,
761 LIMIT_CHECK_TYPE,EXCEPTION_TYPE,EXCEEDED_ON_DATE)
762 VALUES
763 (v_unique_num,-1,null,
764 v_company_code, p_limit_code,
765 p_limit_party, p_amount_date,v_hce_amount,v_limit_amt,
766 v_util_amt - v_limit_amt, NULL, p_dealer_code,
767 'INTRA_DY',v_err_code,sysdate);
768 v_logged_yn := TRUE;
769 end if;
770 end if;
771 --
772 */
773 --
774 end if;
775 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
776 xtr_debug_pkg.debug('After LOG_FULL_LIMITS_CHK on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
777 END IF;
778 if v_logged_yn then
779 --- commit;
780 return(v_unique_num);
781 else
782 return(0); -- Tell calling form that there were no excesses logged.
783 end if;
784 end;
785 ---------------------------------------------------------------
786
787
788
789
790 PROCEDURE UPDATE_LIMIT_EXCESS_LOG(p_deal_no IN NUMBER,
791 p_trans_no IN NUMBER,
792 p_user IN VARCHAR2,
793 p_log_id IN NUMBER) is
794
795 BEGIN
796
797 update XTR_LIMIT_EXCESS_LOG_V
798 set deal_number = p_deal_no,
799 transaction_number = p_trans_no,
800 authorised_by = p_user
801 where log_id = p_log_id;
802
803 END UPDATE_LIMIT_EXCESS_LOG;
804
805 ---------------------------------------------------------------
809 -- if not alreay done once for today
806 PROCEDURE CALC_ALL_MIRROR_DDA_LIMIT_ROW(p_auto_recalc VARCHAR2) is
807 --
808 -- Completely repopulate the dda mirror table, also recalculating HCE amounts.
810 --
811 cursor CHK_UPDATE is
812 select 1
813 from XTR_PRO_PARAM
814 where PARAM_NAME = 'LAST_LIMIT_RECALC'
815 and to_date(nvl(PARAM_VALUE,'01/01/1990'),'DD/MM/YYYY') < trunc(sysdate);
816 --
817 cursor c_dda_row is
818 select COMPANY_CODE,CPARTY_CODE,LIMIT_CODE,LIMIT_PARTY,AMOUNT_DATE,AMOUNT,
819 HCE_AMOUNT,DEALER_CODE,DEAL_NUMBER,DEAL_TYPE,
820 TRANSACTION_NUMBER,DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,
821 PRODUCT_TYPE,CURRENCY
822 from XTR_DEAL_DATE_AMOUNTS
823 where LIMIT_CODE is NOT NULL
824 and STATUS_CODE = 'CURRENT'
825 and ((AMOUNT_DATE >= trunc(sysdate) and DEAL_TYPE NOT IN('ONC','CMF')) or
826 (AMOUNT_DATE >= trunc(sysdate) and DEAL_TYPE IN('ONC','CMF')
827 and PRODUCT_TYPE = 'FIXED') or
828 (DEAL_TYPE IN('ONC','CMF') and PRODUCT_TYPE <> 'FIXED'));
829 --
830 cursor c_get_country (pc_party_code VARCHAR2) is
831 select country_code
832 from XTR_PARTY_INFO
833 where party_code = pc_party_code;
834 --
835 cursor HCE is
836 select amount,rowid row_id,DEAL_TYPE,DEAL_SUBTYPE,AMOUNT_DATE,COMPANY_CODE,CURRENCY
837 from XTR_MIRROR_DDA_LIMIT_ROW
838 for update of amount;
839 --
840 -- ER 6449996 Start
841 cursor GET_LIMIT_RELEASE_TYPE IS
842 select param_value from xtr_pro_param where
843 param_name = 'RELEASE_LIMIT_UTIL';
844 -- ER 6449996 End
845 --
846 l_new_utilamt NUMBER;
847 l_new_hce_utilamt NUMBER;
848 l_new_hce_amt NUMBER;
849 l_dummy NUMBER;
850 l_dummy_char VARCHAR2(100);
851 v_country_code VARCHAR2(50);
852 v_utilised_amt NUMBER;
853 v_hce_utilised_amt NUMBER;
854 v_limit_party VARCHAR2(7);
855 -- ER 6449996 Start
856 L_RELEASE_TYPE XTR_PRO_PARAM.PARAM_VALUE%TYPE;
857 -- ER 6449996 End
858 L_SYS_DATE DATE;
859 --
860 begin
861 -- ER 6449996 Start
862 open GET_LIMIT_RELEASE_TYPE ;
863 fetch GET_LIMIT_RELEASE_TYPE into L_RELEASE_TYPE ;
864 close GET_LIMIT_RELEASE_TYPE;
865 if L_RELEASE_TYPE = 'ON_MATURITY' then
866 L_SYS_DATE :=trunc(sysdate)+1;
867 else
868 L_SYS_DATE :=trunc(sysdate);
869 end if;
870 -- ER 6449996 End
871 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
872 xtr_debug_pkg.debug('Before CALC_ALL_MIRROR_DDA_LIMIT_ROW on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
873 END IF;
874 open CHK_UPDATE;
875 fetch CHK_UPDATE INTO l_dummy;
876 if CHK_UPDATE%FOUND or p_auto_recalc = 'Y' then
877 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
878 xtr_debug_pkg.debug('Before delete xtr_mirror_dda_limit_row on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
879 END IF;
880 delete from XTR_mirror_dda_limit_row
881 where amount_date < L_SYS_DATE
882 and deal_type not in('ONC','CA','IG', 'STOCK');
883 ---
884 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
885 xtr_debug_pkg.debug('After delete xtr_mirror_dda_limit_row('||to_char(sql%rowcount)||') on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
886 END IF;
887 FOR c in HCE LOOP
888 -- Calculate weighted usage for this data.
889 l_new_utilamt := XTR_LIMITS_P.WEIGHTED_USAGE(c.deal_type,c.deal_subtype,
890 c.amount_date,c.amount);
891
892 l_new_hce_utilamt := XTR_LIMITS_P.CONVERT_TO_HCE_AMOUNT( l_new_utilamt,
893 c.currency,
894 c.company_code);
895 l_new_hce_amt := XTR_LIMITS_P.CONVERT_TO_HCE_AMOUNT(c.amount,
896 c.currency,
897 c.company_code);
898 update XTR_mirror_dda_limit_row
899 set hce_amount = l_new_hce_amt,
900 hce_utilised_amount = l_new_hce_utilamt,
901 utilised_amount = l_new_utilamt
902 where rowid = c.row_id;
903 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
904 xtr_debug_pkg.debug('After update xtr_mirror_dda_limit_row('||c.row_id||') on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
905 END IF;
906 END LOOP;
907 --
908 update XTR_PRO_PARAM
909 set PARAM_VALUE = to_char(sysdate,'DD/MM/YYYY')
910 where PARAM_NAME = 'LAST_LIMIT_RECALC';
911 --- commit;
912 end if;
913 --
914 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
915 xtr_debug_pkg.debug('After CALC_ALL_MIRROR_DDA_LIMIT_ROW on:'||to_char(sysdate,'MM:DD:HH24:MI:SS'));
916 END IF;
917 close CHK_UPDATE;
918 end;
919 ---------------------------------------------------------------
920 --
921 -- Cover routine so CALC_ALL_MIRROR_DDA_LIMIT_ROW can be called as a concurrent
922 -- program.
923 --
924 PROCEDURE update_weightings(
925 errbuf OUT NOCOPY VARCHAR2,
926 retcode OUT NOCOPY NUMBER) IS
927 BEGIN
928 -- Pass as 'Y' to force the update to occur
929 calc_all_mirror_dda_limit_row('Y');
930 END update_weightings;
931 ---------------------------------------------------------------
932 -- Note: this procedure assumes there will be only one record in DDA for each deal_no/
933 -- transaction_number combination WHICH HAS a non-null limit_code.
934 --
935 procedure MIRROR_DDA_LIMIT_ROW_PROC (
936 p_action VARCHAR2,
937 p_old_LIMIT_CODE VARCHAR2,
941 p_new_COMPANY_CODE VARCHAR2,
938 p_old_DEAL_NUMBER NUMBER,
939 p_old_TRANSACTION_NUMBER NUMBER,
940 p_new_product_type VARCHAR2,
942 p_new_LIMIT_PARTY VARCHAR2,
943 p_new_LIMIT_CODE VARCHAR2,
944 p_new_AMOUNT_DATE DATE,
945 p_new_AMOUNT NUMBER,
946 p_new_HCE_AMOUNT NUMBER,
947 p_new_DEALER_CODE VARCHAR2,
948 p_new_DEAL_NUMBER NUMBER,
949 p_new_DEAL_TYPE VARCHAR2,
950 p_new_TRANSACTION_NUMBER NUMBER,
951 p_new_DEAL_SUBTYPE VARCHAR2,
952 p_new_PORTFOLIO_CODE VARCHAR2,
953 p_new_STATUS_CODE VARCHAR2,
954 p_new_currency VARCHAR2,
955 p_amount_type VARCHAR2,
956 p_transaction_rate NUMBER,
957 p_currency_combination VARCHAR2,
958 p_account_no VARCHAR2,
959 p_commence_date DATE ) is
960 --
961 -- This procedure is called by a DB trigger on table DDA whenever a DDA record is
962 -- UPDATED/DELETED/INSERTED. This procedure has two purposes:
963 -- 1) maintain a mirror of the non-null-limit-code DDA records in table mirror_dda_limit_row.
964 -- 2) calculate and store the current limit usage amount for each mirror record whenever a
965 -- mirror row is inserted/updated.
966 --
967 cursor c_get_type is
968 select limit_type
969 from XTR_company_limits
970 where company_code = p_new_company_code
971 and limit_code = p_new_limit_code;
972 --
973 cursor c_get_country (pc_party_code VARCHAR2) is
974 select country_code,nvl(cross_ref_to_other_party,pc_party_code)
975 from XTR_PARTY_INFO
976 where party_code = pc_party_code;
977 --
978 v_country_code XTR_PARTY_INFO.country_code%TYPE;
979 v_utilised_amount NUMBER;
980 v_hce_utilised_amt NUMBER;
981 v_hce_amt NUMBER;
982 v_limit_party VARCHAR2(7);
983 v_limit_type VARCHAR2(7);
984 --
985 v_amount_indic NUMBER := 1;
986 v_contra_ccy VARCHAR2(15) :=NULL;
987 v_group_party VARCHAR2(20);
988 --
989 l_commence_date date;
990
991 begin
992 -- Note: this procedure MUST only be called (from a DB trigger) when at least
993 -- one of :old.limit_code and :new.limit_code is NOT null. Otherwise the
994 -- following if p_old_limit_code ... then ... elsif ... will work incorrectly.
995 --
996 open c_get_country( p_new_LIMIT_PARTY );
997 fetch c_get_country into v_country_code,v_group_party;
998 close c_get_country;
999 --
1000 open c_get_type;
1001 fetch c_get_type into v_limit_type;
1002 close c_get_type;
1003 --
1004 if p_new_deal_type IN ('FX','FXO') then
1005 if p_new_currency =substr(p_currency_combination,1,3) then
1006 v_contra_ccy :=substr(p_currency_combination,5,7);
1007 else
1008 v_contra_ccy :=substr(p_currency_combination,1,3);
1009 end if;
1010 if p_amount_type IN('SELL','FXOSELL') then
1011 v_amount_indic :=-1;
1012 else
1013 v_amount_indic :=1;
1014 end if;
1015 end if;
1016 ----
1017 l_commence_date :=nvl(p_commence_date,p_new_AMOUNT_DATE);
1018
1019 if p_action <> 'DELETE' and (p_new_status_code = 'CURRENT'
1020 OR (p_old_limit_code is not null and p_new_limit_code is not null and p_new_status_code IN ('EXERCISED','SETTLED','CANCELLED'))) then
1021 -- Calculate weighted usage for this data.
1022 v_utilised_amount := XTR_LIMITS_P.WEIGHTED_USAGE(p_new_deal_type,
1023 p_new_deal_subtype,
1024 p_new_amount_date,
1025 p_new_amount);
1026 -- Now calculate home-currency-equivalent for v_utilised_amt...
1027 v_hce_utilised_amt := XTR_LIMITS_P.CONVERT_TO_HCE_AMOUNT(v_utilised_amount,
1028 p_new_currency,
1029 p_new_company_code);
1030 v_hce_amt := XTR_LIMITS_P.CONVERT_TO_HCE_AMOUNT(p_new_amount,
1031 p_new_currency,
1032 p_new_company_code);
1033 else
1034 v_utilised_amount := 0;
1035 v_hce_utilised_amt := 0;
1036 v_hce_amt := 0;
1037 end if;
1038 ----------------------------------------------
1039 if p_action = 'UPDATE' then
1040 -- old lim code | new lim code | action
1041 -- ++++++++++++++++++++++++++++++++++++++
1042 -- null | null | does not reach here
1043 -- null | X | INSERT
1044 -- X | null | DELETE (status = 'CLOSED') (no reverse cof)
1045 -- X | Y | UPDATE
1046 --
1047 -- Remember: to get this far, at least one of old/new limit code is NOT null.
1048 if p_old_limit_code is null then
1049 update XTR_mirror_dda_limit_row
1050 set PRODUCT_TYPE =p_new_product_type,
1051 COMPANY_CODE = p_new_COMPANY_CODE,
1052 LIMIT_CODE =p_new_LIMIT_CODE,
1053 LIMIT_PARTY =p_new_LIMIT_PARTY,
1054 AMOUNT_DATE =p_new_AMOUNT_DATE,
1055 AMOUNT =p_new_AMOUNT,
1056 HCE_AMOUNT =v_hce_amt,
1057 DEALER_CODE =p_new_DEALER_CODE,
1061 PORTFOLIO_CODE =p_new_PORTFOLIO_CODE,
1058 COUNTRY_CODE =v_country_code,
1059 DEAL_TYPE =p_new_DEAL_TYPE,
1060 DEAL_SUBTYPE =p_new_DEAL_SUBTYPE,
1062 STATUS_CODE =p_new_STATUS_CODE,
1063 UTILISED_AMOUNT = v_utilised_amount,
1064 DATE_LAST_SET = sysdate,
1065 HCE_UTILISED_AMOUNT = v_hce_utilised_amt,
1066 CURRENCY = p_new_currency,
1067 AMOUNT_INDIC = v_amount_indic,
1068 TRANSACTION_RATE = p_transaction_rate,
1069 CONTRA_CCY = v_contra_ccy,
1070 CURRENCY_COMBINATION = p_currency_combination,
1071 COMMENCE_DATE = l_commence_date,
1072 ACCOUNT_NO = p_account_no,
1073 CROSS_REF_TO_OTHER_PARTY = v_group_party,
1074 LIMIT_TYPE = v_limit_type
1075 where deal_number = p_new_deal_number
1076 and transaction_number = p_new_transaction_number ;
1077 --
1078 if SQL%NOTFOUND then -- No row was updated.
1079 insert into XTR_mirror_dda_limit_row
1080 (COMPANY_CODE,LIMIT_CODE,LIMIT_PARTY,AMOUNT_DATE,AMOUNT,
1081 HCE_AMOUNT,DEALER_CODE,COUNTRY_CODE,DEAL_NUMBER,DEAL_TYPE,
1082 TRANSACTION_NUMBER,DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,
1083 UTILISED_AMOUNT,PRODUCT_TYPE,DATE_LAST_SET,HCE_UTILISED_AMOUNT,CURRENCY,
1084 AMOUNT_INDIC,TRANSACTION_RATE,CONTRA_CCY,CURRENCY_COMBINATION,ACCOUNT_NO,
1085 COMMENCE_DATE,CROSS_REF_TO_OTHER_PARTY,LIMIT_TYPE)
1086 values
1087 (p_new_COMPANY_CODE,p_new_LIMIT_CODE,p_new_LIMIT_PARTY,p_new_AMOUNT_DATE,p_new_AMOUNT,
1088 v_hce_amt,p_new_DEALER_CODE,v_country_code,p_new_DEAL_NUMBER,p_new_DEAL_TYPE,
1089 p_new_TRANSACTION_NUMBER,p_new_DEAL_SUBTYPE,p_new_PORTFOLIO_CODE,p_new_STATUS_CODE,
1090 v_utilised_amount,p_new_product_type,sysdate,v_hce_utilised_amt,p_new_currency,
1091 v_amount_indic,p_transaction_rate,v_contra_ccy,p_currency_combination,p_account_no,
1092 l_commence_date,v_group_party,v_limit_type);
1093 end if;
1094 elsif p_new_limit_code is null then
1095 delete from XTR_mirror_dda_limit_row
1096 where deal_number = p_old_deal_number
1097 and transaction_number = p_old_transaction_NUMBER;
1098 else -- same as: elsif p_old_limit_code is NOT null and p_new_limit_code is NOT null then
1099 update XTR_mirror_dda_limit_row
1100 set PRODUCT_TYPE =p_new_product_type,
1101 COMPANY_CODE = p_new_COMPANY_CODE,
1102 LIMIT_CODE =p_new_LIMIT_CODE,
1103 LIMIT_PARTY =p_new_LIMIT_PARTY,
1104 AMOUNT_DATE =p_new_AMOUNT_DATE,
1105 AMOUNT =p_new_AMOUNT,
1106 HCE_AMOUNT =v_hce_amt,
1107 DEALER_CODE =p_new_DEALER_CODE,
1108 COUNTRY_CODE =v_country_code,
1109 DEAL_TYPE =p_new_DEAL_TYPE,
1110 DEAL_SUBTYPE =p_new_DEAL_SUBTYPE,
1111 PORTFOLIO_CODE =p_new_PORTFOLIO_CODE,
1112 STATUS_CODE =p_new_STATUS_CODE,
1113 UTILISED_AMOUNT = v_utilised_amount,
1114 DATE_LAST_SET = sysdate,
1115 HCE_UTILISED_AMOUNT = v_hce_utilised_amt,
1116 CURRENCY = p_new_currency,
1117 AMOUNT_INDIC = v_amount_indic,
1118 TRANSACTION_RATE = p_transaction_rate,
1119 CONTRA_CCY = v_contra_ccy,
1120 CURRENCY_COMBINATION = p_currency_combination,
1121 COMMENCE_DATE = l_commence_date,
1122 ACCOUNT_NO = p_account_no,
1123 CROSS_REF_TO_OTHER_PARTY = v_group_party,
1124 LIMIT_TYPE = v_limit_type
1125 where deal_number = p_new_deal_number
1126 and transaction_number = p_new_transaction_number ;
1127 --
1128 if SQL%NOTFOUND then -- No row was updated.
1129 insert into XTR_mirror_dda_limit_row
1130 (COMPANY_CODE,LIMIT_CODE,LIMIT_PARTY,AMOUNT_DATE,AMOUNT,
1131 HCE_AMOUNT,DEALER_CODE,COUNTRY_CODE,DEAL_NUMBER,DEAL_TYPE,
1132 TRANSACTION_NUMBER,DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,
1133 UTILISED_AMOUNT,PRODUCT_TYPE,DATE_LAST_SET,HCE_UTILISED_AMOUNT,CURRENCY,
1134 AMOUNT_INDIC,TRANSACTION_RATE,CONTRA_CCY,CURRENCY_COMBINATION,ACCOUNT_NO,
1135 COMMENCE_DATE,CROSS_REF_TO_OTHER_PARTY,LIMIT_TYPE)
1136 values
1137 (p_new_COMPANY_CODE,p_new_LIMIT_CODE,p_new_LIMIT_PARTY,p_new_AMOUNT_DATE,p_new_AMOUNT,
1138 v_hce_amt,p_new_DEALER_CODE,v_country_code,p_new_DEAL_NUMBER,p_new_DEAL_TYPE,
1139 p_new_TRANSACTION_NUMBER,p_new_DEAL_SUBTYPE,p_new_PORTFOLIO_CODE,p_new_STATUS_CODE,
1140 v_utilised_amount,p_new_product_type,sysdate,v_hce_utilised_amt,p_new_currency,
1141 v_amount_indic,p_transaction_rate,v_contra_ccy,p_currency_combination,p_account_no,
1142 l_commence_date,v_group_party,v_limit_type);
1143 end if;
1144 end if;
1145 ----------------------------------------------
1146 elsif p_action = 'INSERT' then
1147
1148 insert into XTR_mirror_dda_limit_row
1149 (COMPANY_CODE,LIMIT_CODE,LIMIT_PARTY,AMOUNT_DATE,AMOUNT,
1150 HCE_AMOUNT,DEALER_CODE,COUNTRY_CODE,DEAL_NUMBER,DEAL_TYPE,
1151 TRANSACTION_NUMBER,DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,
1152 UTILISED_AMOUNT,PRODUCT_TYPE,DATE_LAST_SET,HCE_UTILISED_AMOUNT,CURRENCY,
1153 AMOUNT_INDIC,TRANSACTION_RATE,CONTRA_CCY,CURRENCY_COMBINATION,ACCOUNT_NO,
1154 COMMENCE_DATE,CROSS_REF_TO_OTHER_PARTY,LIMIT_TYPE)
1155 values
1156 (p_new_COMPANY_CODE,p_new_LIMIT_CODE,p_new_LIMIT_PARTY,p_new_AMOUNT_DATE,p_new_AMOUNT,
1157 v_hce_amt,p_new_DEALER_CODE,v_country_code,p_new_DEAL_NUMBER,p_new_DEAL_TYPE,
1158 p_new_TRANSACTION_NUMBER,p_new_DEAL_SUBTYPE,p_new_PORTFOLIO_CODE,p_new_STATUS_CODE,
1159 v_utilised_amount,p_new_product_type,sysdate,v_hce_utilised_amt,p_new_currency,
1160 v_amount_indic,p_transaction_rate,v_contra_ccy,p_currency_combination,p_account_no,
1164 delete from XTR_mirror_dda_limit_row
1161 l_commence_date,v_group_party,v_limit_type);
1162 -----------------------------------------------------------------------------------------
1163 elsif p_action = 'DELETE' then
1165 where deal_number = p_old_deal_number
1166 and transaction_number = p_old_transaction_number ;
1167 end if;
1168 end;
1169 ------------------------------------------------------------------------------------------------------------------------------
1170 PROCEDURE GET_LIM_GLOBAL ( p_deal_no NUMBER,
1171 p_company_code VARCHAR2,
1172 p_limit_code VARCHAR2,
1173 p_limit_amt OUT NOCOPY number,
1174 p_util_amt OUT NOCOPY number,
1175 p_err_code OUT NOCOPY VARCHAR2) is
1176 --
1177 cursor c_get_util(pc1_company_code VARCHAR2,pc1_limit_code VARCHAR2) is
1178 SELECT nvl(limit_amount,0), nvl(utilised_amount,0)
1179 FROM xtr_company_limits
1180 WHERE company_code = pc1_company_code
1181 AND limit_code = pc1_limit_code
1182 AND (limit_amount <> 0 OR utilised_amount <> 0);
1183 --
1184 cursor c_ok_to_do (pc3_param_name VARCHAR2) is
1185 select nvl(param_value,'Y')
1186 from XTR_PRO_PARAM
1187 where param_name = pc3_param_name;
1188 --
1189 v_ok_to_do VARCHAR2(2);
1190 v_auth VARCHAR2(1);
1191 v_used_amt NUMBER;
1192 v_limit_amt NUMBER;
1193 --
1194 begin
1195
1196 p_err_code := null;
1197 --
1198 open c_ok_to_do('LIMIT_CHECK_GLOBAL');
1199 fetch c_ok_to_do into v_ok_to_do;
1200 if c_ok_to_do%NOTFOUND then
1201 v_ok_to_do := 'Y';
1202 end if;
1203 close c_ok_to_do;
1204 --
1205 if v_ok_to_do = 'Y' then
1206 open c_get_util(p_company_code,p_limit_code);
1207 fetch c_get_util into v_limit_amt,v_used_amt;
1208 if c_get_util%NOTFOUND then
1209 close c_get_util;
1210 p_limit_amt := 0;
1211 p_util_amt := 0;
1212 raise ex_no_limit_exists;
1213 end if;
1214 close c_get_util;
1215 p_limit_amt := v_limit_amt;
1216 p_util_amt := v_used_amt;
1217 end if;
1218 EXCEPTION
1219 WHEN ex_no_limit_exists then p_err_code := 'NO_LIMIT';
1220 WHEN ex_auth_not_y then p_err_code := 'NO_AUTHO';
1221 end;
1222 ---------------------------------------------------------------
1223 PROCEDURE GET_LIM_GROUP ( p_deal_no NUMBER,
1224 p_company_code VARCHAR2,
1225 p_limit_type VARCHAR2,
1226 p_group_party VARCHAR2,
1227 p_limit_amt OUT NOCOPY number,
1228 p_util_amt OUT NOCOPY number,
1229 p_err_code OUT NOCOPY VARCHAR2) is
1230 --
1231 cursor c_get_util(pc1_company_code VARCHAR2,pc1_limit_type VARCHAR2,
1232 pc1_limit_party VARCHAR2) is
1233 SELECT nvl(limit_amount,0), nvl(utilised_amount,0)
1234 FROM xtr_group_limits
1235 WHERE company_code = pc1_company_code
1236 AND cparty_code = pc1_limit_party
1237 AND ((limit_type = pc1_limit_type and limit_type <>'XI')
1238 or (limit_type='XI' and pc1_limit_type in('X','I')))
1239 AND (limit_amount <> 0 OR utilised_amount <> 0);
1240 --
1241 cursor c_ok_to_do (pc3_param_name VARCHAR2) is
1242 select nvl(param_value,'Y')
1243 from XTR_PRO_PARAM
1244 where param_name = pc3_param_name;
1245 --
1246 v_ok_to_do VARCHAR2(2);
1247 v_auth VARCHAR2(1);
1248 v_used_amt NUMBER;
1249 v_limit_amt NUMBER;
1250 --
1251 cursor GET_FX_INVEST_FUND_TYPE is
1252 select fx_invest_fund_type
1253 from XTR_LIMIT_TYPES
1254 where limit_type=p_limit_type;
1255
1256 l_limit_type varchar2(2);
1257
1258 begin
1259 p_err_code := null;
1260 --
1261 open c_ok_to_do('LIMIT_CHECK_GROUP');
1262 fetch c_ok_to_do into v_ok_to_do;
1263 if c_ok_to_do%NOTFOUND then
1264 v_ok_to_do := 'Y';
1265 end if;
1266 close c_ok_to_do;
1267 --
1268 if v_ok_to_do = 'Y' then
1269
1270 -- bug 2990074
1271
1272 /*
1273 open GET_FX_INVEST_FUND_TYPE;
1274 fetch GET_FX_INVEST_FUND_TYPE into l_limit_type;
1275 close GET_FX_INVEST_FUND_TYPE;
1276 */
1277
1278 open c_get_util(p_company_code,p_limit_type,p_group_party);
1279 fetch c_get_util into v_limit_amt,v_used_amt;
1280 if c_get_util%NOTFOUND then
1281 close c_get_util;
1282 p_limit_amt := 0;
1283 p_util_amt := 0;
1284 raise ex_no_limit_exists;
1285 end if;
1286 close c_get_util;
1287 p_limit_amt := v_limit_amt;
1288 p_util_amt := v_used_amt;
1289 end if;
1290 EXCEPTION
1291 WHEN ex_no_limit_exists then p_err_code := 'NO_LIMIT';
1292 WHEN ex_auth_not_y then p_err_code := 'NO_AUTHO';
1293 end;
1294 ---------------------------------------------------------------
1295 PROCEDURE GET_LIM_SOVEREIGN ( p_deal_no NUMBER,
1296 p_company_code VARCHAR2,
1297 p_country_code VARCHAR2,
1298 p_limit_amt OUT NOCOPY number,
1299 p_util_amt OUT NOCOPY number,
1300 p_err_code OUT NOCOPY VARCHAR2) is
1301 --
1302 cursor c_get_util (pc1_company_code VARCHAR2,pc1_country_code VARCHAR2) is
1303 SELECT nvl(limit_amount,0), nvl(utilised_amount,0)
1304 FROM xtr_country_company_limits
1305 WHERE company_code = pc1_company_code
1306 AND country_code = pc1_country_code
1307 AND (limit_amount <> 0 OR utilised_amount <> 0);
1311 from XTR_PRO_PARAM
1308 --
1309 cursor c_ok_to_do (pc3_param_name VARCHAR2) is
1310 select nvl(param_value,'Y')
1312 where param_name = pc3_param_name;
1313 --
1314 v_ok_to_do VARCHAR2(2);
1315 v_auth VARCHAR2(1);
1316 v_used_amt NUMBER;
1317 v_limit_amt NUMBER;
1318 --
1319 begin
1320
1321 p_err_code := null;
1322 --
1323 open c_ok_to_do('LIMIT_CHECK_SOVRN');
1324 fetch c_ok_to_do into v_ok_to_do;
1325 if c_ok_to_do%NOTFOUND then
1326 v_ok_to_do := 'Y';
1327 end if;
1328 close c_ok_to_do;
1329 --
1330 if v_ok_to_do = 'Y' then
1331 open c_get_util(p_company_code,p_country_code );
1332 fetch c_get_util into v_limit_amt,v_used_amt;
1333 if c_get_util%NOTFOUND then
1334 close c_get_util;
1335 p_util_amt := 0;
1336 p_limit_amt := 0;
1337 raise ex_no_limit_exists;
1338 end if;
1339 p_limit_amt := v_limit_amt;
1340 p_util_amt := v_used_amt;
1341 close c_get_util;
1342 end if;
1343 EXCEPTION
1344 WHEN ex_no_limit_exists then p_err_code := 'NO_LIMIT';
1345 WHEN ex_auth_not_y then p_err_code := 'NO_AUTHO';
1346 end;
1347 ---------------------------------------------------------------
1348 PROCEDURE GET_LIM_DEALER_DEAL ( p_deal_no NUMBER,
1349 -- deal_no not actually used at present.
1350 p_dealer_code VARCHAR2,
1351 p_deal_type VARCHAR2,
1352 p_product_type VARCHAR2,
1353 p_limit_amt OUT NOCOPY number,
1354 p_err_code OUT NOCOPY VARCHAR2) is
1355 --
1356 cursor c_get_limit (pc2_dealer_code VARCHAR2,pc2_deal_type VARCHAR2,
1357 pc2_product_type VARCHAR2) is
1358 select single_deal_limit_amount,authorised
1359 from XTR_DEALER_LIMITS
1360 where dealer_code = pc2_dealer_code
1361 and deal_type = pc2_deal_type
1362 and (product_type = pc2_product_type or product_type is NULL)
1363 order by product_type;
1364 --
1365 cursor c_ok_to_do (pc3_param_name VARCHAR2) is
1366 select nvl(param_value,'Y')
1367 from XTR_PRO_PARAM
1368 where param_name = pc3_param_name;
1369 --
1370 v_limit_amt NUMBER;
1371 v_ok_to_do VARCHAR2(2);
1372 v_auth VARCHAR2(1);
1373 --
1374 begin
1375
1376 p_err_code := null;
1377 --
1378 open c_ok_to_do('LIMIT_CHECK_DLR_DEAL');
1379 fetch c_ok_to_do into v_ok_to_do;
1380 if c_ok_to_do%NOTFOUND then
1381 v_ok_to_do := 'Y';
1382 end if;
1383 close c_ok_to_do;
1384 --
1385 if v_ok_to_do = 'Y' then
1386 open c_get_limit( p_dealer_code, p_deal_type, p_product_type );
1387 fetch c_get_limit into v_limit_amt,v_auth;
1388 if c_get_limit%NOTFOUND then
1389 close c_get_limit;
1390 p_limit_amt := 0;
1391 raise ex_no_limit_exists;
1392 end if;
1393 if nvl(v_auth,'N') <> 'Y' then
1394 close c_get_limit;
1395 p_limit_amt := 0;
1396 raise ex_auth_not_y;
1397 end if;
1398 close c_get_limit;
1399 p_limit_amt := v_limit_amt;
1400 end if;
1401 EXCEPTION
1402 WHEN ex_no_limit_exists then p_err_code := 'NO_LIMIT';
1403 WHEN ex_auth_not_y then p_err_code := 'NO_AUTHO';
1404 end;
1405 ---------------------------------------------------------------
1406 PROCEDURE GET_LIM_INTRA_DAY ( p_deal_no NUMBER,
1407 p_dealer_code VARCHAR2,
1408 p_deal_type VARCHAR2,
1409 p_limit_amt OUT NOCOPY number,
1410 p_util_amt OUT NOCOPY number,
1411 p_err_code OUT NOCOPY VARCHAR2) is
1412 --
1413 cursor c_get_util (pc1_dealer_code VARCHAR2, pc1_deal_type VARCHAR2) is
1414 select nvl(sum(decode(deal_subtype,'BUY',HCE_AMOUNT,'SELL',-HCE_AMOUNT,0)),0)
1415 from XTR_mirror_dda_limit_row
1416 where dealer_code = pc1_dealer_code
1417 and deal_type = pc1_deal_type;
1418 --
1419 cursor c_get_limit (pc2_dealer_code VARCHAR2, pc2_deal_type VARCHAR2) is
1420 select limit_amount, authorised
1421 from XTR_intra_day_limits
1422 where dealer_code = pc2_dealer_code
1423 and deal_type = pc2_deal_type;
1424 --
1425 cursor c_ok_to_do (pc3_param_name VARCHAR2) is
1426 select nvl(param_value,'Y')
1427 from XTR_pro_param
1428 where param_name = pc3_param_name;
1429 --
1430 v_ok_to_do VARCHAR2(2);
1431 v_auth XTR_DEALER_LIMITS.authorised%TYPE;
1432 v_used_amt XTR_MIRROR_DDA_LIMIT_ROW.utilised_amount%TYPE;
1433 begin
1434 -- this is not called at this stage
1435 null;
1436 /*
1437 p_err_code := null;
1438 open c_ok_to_do( 'LIMIT_CHECK_INTRA_DY');
1439 fetch c_ok_to_do into v_ok_to_do;
1440 if c_ok_to_do%NOTFOUND then v_ok_to_do := 'Y'; end if;
1441 close c_ok_to_do;
1442 if v_ok_to_do = 'N' then return; end if;
1443 --
1444 open c_get_util(p_dealer_code,p_deal_type );
1445 fetch c_get_util into v_used_amt;
1446 if c_get_util%NOTFOUND then p_util_amt := 0; end if;
1447 close c_get_util;
1448 p_util_amt := v_used_amt;
1449 --
1450 open c_get_limit( p_dealer_code,p_deal_type );
1451 fetch c_get_limit into p_limit_amt, v_auth;
1452 if c_get_limit%NOTFOUND then
1453 close c_get_limit;
1454 raise ex_no_limit_exists;
1455 end if;
1456 if nvl(v_auth,'N') <> 'Y' then
1457 close c_get_limit;
1458 raise ex_auth_not_y;
1459 end if;
1463 WHEN ex_auth_not_y then p_err_code := 'NO_AUTHO';
1460 close c_get_limit;
1461 EXCEPTION
1462 WHEN ex_no_limit_exists then p_err_code := 'NO_LIMIT';
1464 */
1465 end;
1466 ---------------------------------------------------------------
1467 PROCEDURE GET_LIM_CPARTY ( p_deal_no NUMBER,
1468 p_company_code VARCHAR2,
1469 p_cparty_code VARCHAR2,
1470 p_limit_code VARCHAR2,
1471 p_limit_amt OUT NOCOPY number,
1472 p_util_amt OUT NOCOPY number,
1473 p_err_code OUT NOCOPY VARCHAR2) is
1474 --
1475 cursor c_get_util (pc1_company_code VARCHAR2,pc1_cparty_code VARCHAR2,
1476 pc1_limit_code VARCHAR2) is
1477 SELECT nvl(cl.limit_amount,0), nvl(cl.utilised_amount,0)
1478 FROM xtr_counterparty_limits cl, xtr_parties_v p
1479 WHERE cl.company_code = pc1_company_code
1480 AND cl.cparty_code = pc1_cparty_code
1481 AND cl.limit_code = pc1_limit_code
1482 AND cl.limit_code <> 'SETTLE'
1483 AND cl.cparty_code = p.party_code||''
1484 AND (cl.limit_amount <> 0 OR cl.utilised_amount <> 0);
1485 --
1486 cursor c_ok_to_do (pc3_param_name VARCHAR2) is
1487 select nvl(param_value,'Y')
1488 from XTR_pro_param
1489 where param_name = pc3_param_name;
1490 --
1491 v_ok_to_do VARCHAR2(2);
1492 v_auth VARCHAR2(1);
1493 v_used_amt NUMBER;
1494 v_limit_amt NUMBER;
1495 --
1496 begin
1497
1498 p_err_code := null;
1499 --
1500 open c_ok_to_do('LIMIT_CHECK_CPARTY');
1501 fetch c_ok_to_do into v_ok_to_do;
1502 if c_ok_to_do%NOTFOUND then
1503 v_ok_to_do := 'Y';
1504 end if;
1505 close c_ok_to_do;
1506 --
1507 if v_ok_to_do = 'Y' then
1508 open c_get_util(p_company_code,p_cparty_code,p_limit_code );
1509 fetch c_get_util into v_limit_amt,v_used_amt;
1510 if c_get_util%NOTFOUND then
1511 close c_get_util;
1512 p_util_amt := 0;
1513 p_limit_amt := 0;
1514 raise ex_no_limit_exists;
1515 end if;
1516 p_limit_amt := v_limit_amt;
1517 p_util_amt := v_used_amt;
1518 close c_get_util;
1519 end if;
1520 EXCEPTION
1521 WHEN ex_no_limit_exists then p_err_code := 'NO_LIMIT';
1522 WHEN ex_auth_not_y then p_err_code := 'NO_AUTHO';
1523 end;
1524 ---------------------------------------------------------------
1525 PROCEDURE GET_ACTUAL_SETTLE_EXCESS ( p_deal_no NUMBER,
1526 p_company_code VARCHAR2,
1527 p_limit_party VARCHAR2,
1528 p_amount_date DATE,
1529 p_limit_amt OUT NOCOPY number,
1530 p_util_amt OUT NOCOPY number,
1531 p_err_code OUT NOCOPY VARCHAR2) is
1532 v_ok_to_do VARCHAR2(2);
1533 v_auth VARCHAR2(1);
1534 v_used_amt NUMBER;
1535 v_limit_amt NUMBER;
1536 v_include_payments VARCHAR2(1);
1537 --
1538 cursor c_get_util(pc1_company_code VARCHAR2,pc1_limit_party VARCHAR2,
1539 pc1_amount_date date) is
1540 select nvl(max(B.LIMIT_AMOUNT),0) LIMIT_AMT,
1541 nvl(sum(round(abs(A.CASHFLOW_AMOUNT) / M.HCE_RATE,0)),0) UTILISED
1542 from XTR_DEAL_DATE_AMOUNTS A,
1543 XTR_COUNTERPARTY_LIMITS B,
1544 XTR_MASTER_CURRENCIES M
1545 where A.ACTUAL_SETTLEMENT_DATE = pc1_amount_date
1546 and A.SETTLE = 'Y'
1547 and A.COMPANY_CODE = pc1_company_code
1548 and A.AMOUNT_TYPE NOT IN ('FXOBUY','FXOSELL')
1549 and A.EXP_SETTLE_REQD = 'Y'
1550 and NVL(A.MULTIPLE_SETTLEMENTS,'N') = 'N'
1551 and A.DEAL_SUBTYPE <> 'INDIC'
1552 and nvl(A.BENEFICIARY_PARTY,A.CPARTY_CODE) = pc1_limit_party
1553 and B.CPARTY_CODE = A.CPARTY_CODE
1554 and B.COMPANY_CODE = A.COMPANY_CODE
1555 and B.LIMIT_CODE = 'SETTLE'
1556 and M.CURRENCY = A.CURRENCY
1557 and (
1558 (v_include_payments='N' and A.CASHFLOW_AMOUNT>0)
1559 or
1560 (v_include_payments='Y')
1561 );
1562 --
1563 cursor c_ok_to_do(pc3_param_name VARCHAR2) is
1564 select nvl(param_value,'Y')
1565 from XTR_PRO_PARAM
1566 where param_name = pc3_param_name;
1567 --
1568 cursor c_get_limit_amt(pc1_company_code VARCHAR2,pc1_limit_party VARCHAR2) is
1569 select nvl(limit_amount,0)
1570 from XTR_COUNTERPARTY_LIMITS
1571 where cparty_code = pc1_limit_party
1572 and company_code = pc1_company_code
1573 and limit_code = 'SETTLE';
1574 --
1575 --
1576 begin
1577 p_err_code := null;
1578 --
1579 open c_ok_to_do('LIMIT_CHECK_SETTLE');
1580 fetch c_ok_to_do into v_ok_to_do;
1581 if c_ok_to_do%NOTFOUND then
1582 v_ok_to_do := 'Y';
1583 end if;
1584 close c_ok_to_do;
1585 --
1586 -- bug 2428516
1587 open c_ok_to_do('LIMIT_INCLUDE_PAYMENTS');
1588 fetch c_ok_to_do into v_include_payments;
1589 if c_ok_to_do%NOTFOUND then
1590 v_include_payments := 'Y';
1591 end if;
1592 close c_ok_to_do;
1593 --
1594 if v_ok_to_do = 'Y' then
1595 open c_get_util(p_company_code,p_limit_party,p_amount_date);
1596 fetch c_get_util into v_limit_amt,v_used_amt;
1597 if c_get_util%NOTFOUND or nvl(v_limit_amt,0) = 0 then
1598 v_used_amt := 0;
1599 v_limit_amt := 0;
1600 -- need to get limit amount separately as no settle rows
1601 -- exist therefore the join won't pick up the limit amount
1602 open c_get_limit_amt(p_company_code,p_limit_party);
1606 p_limit_amt := 0;
1603 fetch c_get_limit_amt into v_limit_amt;
1604 if c_get_limit_amt%NOTFOUND then
1605 close c_get_limit_amt;
1607 raise ex_no_limit_exists;
1608 end if;
1609 close c_get_limit_amt;
1610 end if;
1611 close c_get_util;
1612 p_limit_amt := v_limit_amt;
1613 p_util_amt := v_used_amt;
1614 end if;
1615 EXCEPTION
1616 WHEN ex_no_limit_exists then p_err_code := 'NO_LIMIT';
1617 WHEN ex_auth_not_y then p_err_code := 'NO_AUTHO';
1618 end;
1619 --------------------------------------------------------------------------------
1620 PROCEDURE GET_LIM_SETTLE ( p_deal_no NUMBER,
1621 p_company_code VARCHAR2,
1622 p_limit_party VARCHAR2,
1623 p_amount_date DATE,
1624 p_limit_amt OUT NOCOPY number,
1625 p_util_amt OUT NOCOPY number,
1626 p_err_code OUT NOCOPY VARCHAR2) is
1627 v_ok_to_do VARCHAR2(2);
1628 v_auth VARCHAR2(1);
1629 v_used_amt NUMBER;
1630 v_limit_amt NUMBER;
1631 v_include_payments VARCHAR2(1);
1632 -- This cursor only applies when p_amount_date is greater than sysdate
1633 cursor c_get_util_future(pc1_company_code VARCHAR2,pc1_limit_party VARCHAR2,
1634 pc1_amount_date date) is
1635 SELECT nvl(max(cl.limit_amount),0),
1636 nvl(sum(round(abs(s.cashflow_amount)/mc.hce_rate,0)),0)
1637 FROM xtr_settlements_v s,
1638 xtr_counterparty_limits cl,
1639 xtr_master_currencies mc
1640 WHERE s.amount_date > trunc(sysdate)
1641 AND cl.cparty_code = s.cparty
1642 AND cl.company_code = s.company
1643 AND cl.limit_code = 'SETTLE'
1644 AND mc.currency = s.currency
1645 AND s.amount_date = pc1_amount_date
1646 AND s.cparty = pc1_limit_party
1647 AND s.company = pc1_company_code
1648 AND (
1649 (v_include_payments='N' and S.CASHFLOW_AMOUNT>0)
1650 OR
1651 (v_include_payments='Y')
1652 )
1653 GROUP BY s.cparty, s.company, s.amount_date;
1654 -- This cursor only applies when p_amount_date is sysdate
1655 cursor c_get_util_today(pc1_company_code VARCHAR2,pc1_limit_party VARCHAR2,
1656 pc1_amount_date date) is
1657 SELECT nvl(max(cl.limit_amount),0),
1658 nvl(sum(sru.utilised_amount),0)
1659 FROM (
1660 SELECT nvl(SUM(ROUND(ABS(a.CASHFLOW_AMOUNT) / m.HCE_RATE,0)),0) UTILISED_AMOUNT,
1661 a.CPARTY CPARTY_CODE,
1662 a.COMPANY COMPANY_CODE,
1663 nvl(a.AMOUNT_DATE,TRUNC(SYSDATE)) EFFECTIVE_DATE
1664 FROM XTR_SETTLEMENTS_V a, XTR_MASTER_CURRENCIES m
1665 WHERE a.AMOUNT_DATE = trunc(SYSDATE)
1666 AND a.CASHFLOW_AMOUNT <> 0
1667 AND m.CURRENCY = a.CURRENCY
1668 AND (
1669 (v_include_payments='N' and A.CASHFLOW_AMOUNT>0)
1670 OR
1671 (v_include_payments='Y')
1672 )
1673 GROUP by a.CPARTY,a.COMPANY,nvl(a.AMOUNT_DATE,trunc(SYSDATE))
1674 ) sru,
1675 xtr_counterparty_limits cl
1676 WHERE cl.cparty_code = sru.cparty_code(+)
1677 AND cl.company_code = sru.company_code(+)
1678 AND cl.limit_code = 'SETTLE'
1679 AND nvl(sru.effective_date,trunc(sysdate)) = pc1_amount_date
1680 AND cl.cparty_code = pc1_limit_party
1681 AND cl.company_code = pc1_company_code
1682 GROUP BY cl.cparty_code, cl.company_code, sru.effective_date;
1683 --
1684 cursor c_param_val(pc3_param_name VARCHAR2) is
1685 select param_value
1686 from XTR_PRO_PARAM
1687 where param_name = pc3_param_name;
1688 --
1689 cursor c_get_limit_amt(pc1_company_code VARCHAR2,pc1_limit_party VARCHAR2) is
1690 select nvl(limit_amount,0)
1691 from XTR_COUNTERPARTY_LIMITS
1692 where cparty_code = pc1_limit_party
1693 and company_code = pc1_company_code
1694 and limit_code = 'SETTLE'
1695 and limit_amount <> 0;
1696 --
1697 --
1698 begin
1699 p_err_code := null;
1700 --
1701 open c_param_val('LIMIT_CHECK_SETTLE');
1702 fetch c_param_val into v_ok_to_do;
1703 if c_param_val%NOTFOUND then
1704 v_ok_to_do := 'Y';
1705 end if;
1706 close c_param_val;
1707 --
1708 -- bug 2428516
1709 open c_param_val('LIMIT_INCLUDE_PAYMENTS');
1710 fetch c_param_val into v_include_payments;
1711 if c_param_val%NOTFOUND then
1712 v_include_payments := 'Y';
1713 end if;
1714 close c_param_val;
1715 --
1716 if nvl(v_ok_to_do,'Y') = 'Y' then
1717 v_limit_amt := Null;
1718 IF trunc(p_amount_date) = trunc(sysdate) THEN
1719 open c_get_util_today(p_company_code,p_limit_party,p_amount_date);
1720 fetch c_get_util_today into v_limit_amt,v_used_amt;
1721 ELSE
1722 open c_get_util_future(p_company_code,p_limit_party,p_amount_date);
1723 fetch c_get_util_future into v_limit_amt,v_used_amt;
1724 END IF;
1725 if nvl(v_limit_amt,0) = 0 then
1726 v_used_amt := 0;
1727 v_limit_amt := 0;
1728 open c_get_limit_amt(p_company_code,p_limit_party);
1729 fetch c_get_limit_amt into v_limit_amt;
1730 if c_get_limit_amt%NOTFOUND then
1731 close c_get_limit_amt;
1732 p_limit_amt := 0;
1733 raise ex_no_limit_exists;
1734 end if;
1735 close c_get_limit_amt;
1736 end if;
1737 IF trunc(p_amount_date) = trunc(sysdate) THEN
1738 close c_get_util_today;
1739 ELSE
1740 close c_get_util_future;
1741 END IF;
1742 p_limit_amt := v_limit_amt;
1743 p_util_amt := v_used_amt;
1744 end if;
1745 EXCEPTION
1746 WHEN ex_no_limit_exists then p_err_code := 'NO_LIMIT';
1747 WHEN ex_auth_not_y then p_err_code := 'NO_AUTHO';
1748 end;
1749 --------------------------------------------------------------------------------
1750 PROCEDURE GET_LIM_CCY ( p_deal_no NUMBER,
1751 p_currency VARCHAR2,
1752 p_limit_amt OUT NOCOPY number,
1753 p_util_amt OUT NOCOPY number,
1754 p_err_code OUT NOCOPY VARCHAR2) is
1755 --
1756 cursor c_get_util(pc1_currency VARCHAR2) is
1757 SELECT nvl(mc.net_fx_exposure,0), nvl(mc.utilised_amount,0)
1758 FROM xtr_master_currencies mc
1759 WHERE mc.authorised = 'Y'
1760 AND (mc.net_fx_exposure <> 0 OR mc.utilised_amount <> 0)
1761 /* bug 1289530 limit check should be done for home ccy
1762 AND mc.currency <> (SELECT max(param_value)
1763 FROM xtr_pro_param p
1764 WHERE p.param_name='SYSTEM_FUNCTIONAL_CCY')
1765 bug 1289530 */
1766 AND mc.currency = p_currency;
1767 --
1768 cursor c_ok_to_do (pc3_param_name VARCHAR2) is
1769 select nvl(param_value,'Y')
1770 from XTR_PRO_PARAM
1771 where param_name = pc3_param_name;
1772 --
1773 v_ok_to_do VARCHAR2(2);
1774 v_auth VARCHAR2(1);
1775 v_used_amt NUMBER;
1776 --
1777 begin
1778 p_err_code := null;
1779 open c_ok_to_do('LIMIT_CHECK_CCY');
1780 fetch c_ok_to_do into v_ok_to_do;
1781 if c_ok_to_do%NOTFOUND then
1782 v_ok_to_do := 'Y';
1783 end if;
1784 close c_ok_to_do;
1785 --
1786 if v_ok_to_do = 'Y' then
1787 open c_get_util(p_currency);
1788 fetch c_get_util into p_limit_amt,v_used_amt;
1789 if c_get_util%NOTFOUND then
1790 close c_get_util;
1791 p_util_amt := 0;
1792 raise ex_auth_not_y;
1793 end if;
1794 close c_get_util;
1795 p_util_amt := v_used_amt;
1796 end if;
1797 EXCEPTION
1798 WHEN ex_no_limit_exists then p_err_code := 'NO_LIMIT';
1799 WHEN ex_auth_not_y then p_err_code := 'NO_AUTHO';
1800 end;
1801 ---------------------------------------------------------------
1802 PROCEDURE MAINTAIN_EXCESS_LOG( p_log_id NUMBER,
1803 p_action VARCHAR2,
1804 p_user VARCHAR2 ) is
1805 --
1806 begin
1807 if p_action = 'D' then
1808 delete from XTR_LIMIT_EXCESS_LOG
1809 where LOG_ID = p_log_id;
1810 elsif p_action = 'A' then
1811 update XTR_LIMIT_EXCESS_LOG
1812 set AUTHORISED_BY = p_user
1813 where LOG_ID = p_log_id;
1814 end if;
1815 ---commit;
1816 end;
1817 ---------------------------------------------------------------
1818 -- Procedure to update all limits table with the most up-to-date information
1819 -- from XTR_MIRROR_DDA_LIMIT_ROW_V
1820 PROCEDURE reinitialize_limits (
1821 errbuf OUT NOCOPY VARCHAR2,
1822 retcode OUT NOCOPY NUMBER) IS
1823 BEGIN
1824 delete from xtr_MIRROR_DDA_LIM_ROW_TMP_V;
1825 --
1826 update xtr_COMPANY_LIMITS_V
1827 set UTILISED_AMOUNT = 0;
1828 update xtr_COUNTERPARTY_LIMITS_V
1829 set UTILISED_AMOUNT = 0;
1830 update xtr_MASTER_CURRENCIES
1831 set UTILISED_AMOUNT = 0;
1832 update xtr_COUNTRY_COMPANY_LIMITS_V
1833 set UTILISED_AMOUNT = 0;
1834 update xtr_GROUP_LIMITS_V
1835 set UTILISED_AMOUNT = 0;
1836 --
1837 -- Insert Trigger on MIRROR_DDA_LIMIT_ROW_TEMP will initialize all
1838 -- limits in above tables
1839 insert into xtr_MIRROR_DDA_LIM_ROW_TMP_V (
1840 DEAL_NUMBER,DEAL_TYPE,TRANSACTION_NUMBER,LIMIT_CODE,
1841 AMOUNT,HCE_AMOUNT,DATE_LAST_SET,PRODUCT_TYPE,COMPANY_CODE,
1842 LIMIT_PARTY,AMOUNT_DATE,DEALER_CODE,COUNTRY_CODE,
1843 DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,CURRENCY,
1844 UTILISED_AMOUNT,HCE_UTILISED_AMOUNT,CROSS_REF_TO_OTHER_PARTY,
1845 LIMIT_TYPE,COMMENCE_DATE,CURRENCY_COMBINATION,CONTRA_CCY,
1846 TRANSACTION_RATE,AMOUNT_INDIC,ACCOUNT_NO)
1847 select
1848 DEAL_NUMBER,DEAL_TYPE,TRANSACTION_NUMBER,LIMIT_CODE,
1849 AMOUNT,HCE_AMOUNT,DATE_LAST_SET,PRODUCT_TYPE,COMPANY_CODE,
1850 LIMIT_PARTY,AMOUNT_DATE,DEALER_CODE,COUNTRY_CODE,
1851 DEAL_SUBTYPE,PORTFOLIO_CODE,STATUS_CODE,CURRENCY,
1852 UTILISED_AMOUNT,HCE_UTILISED_AMOUNT,CROSS_REF_TO_OTHER_PARTY,
1853 LIMIT_TYPE,COMMENCE_DATE,CURRENCY_COMBINATION,CONTRA_CCY,
1854 TRANSACTION_RATE,AMOUNT_INDIC,ACCOUNT_NO
1855 from xtr_MIRROR_DDA_LIMIT_ROW_V;
1856 --
1857 delete from xtr_MIRROR_DDA_LIM_ROW_TMP_V;
1858 END reinitialize_limits;
1859 ---------------------------------------------------------------
1860 end XTR_LIMITS_P;