1 PACKAGE BODY XTR_EXP_TRANSFERS_PKG AS
2 /* $Header: xtrimexb.pls 120.14.12020000.2 2012/10/05 18:22:03 nipant ship $ */
3
4 /*------------------------------------------------------------------------
5 This procedure is used to log errors.
6 ------------------------------------------------------------------------*/
7 procedure LOG_ERRORS(p_Ext_Deal_Id In Varchar2,
8 p_Deal_Type In Varchar2,
9 p_Error_Column In Varchar2,
10 p_Error_Code In Varchar2,
11 p_Field_Name In Varchar2) is
12 cursor c_text is
13 select text
14 from xtr_sys_languages_vl
15 where item_name = p_Field_Name;
16
17 p_text xtr_sys_languages_vl.text%TYPE;
18
19 BEGIN
20 IF xtr_risk_debug_pkg.g_Debug THEN
21 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.LOG_ERRORS');
22 END IF;
23
24 if G_Source is null then
25 xtr_import_deal_data.log_interface_errors(p_ext_deal_id,
26 p_deal_type,
27 p_error_column,
28 p_error_code);
29 else
30 if p_Error_Code in ('XTR_MANDATORY','XTR_INV_LIMIT_CODE',
31 'XTR_IMP_DEAL_REVAL_EXIST', 'XTR_IMP_DEAL_ACCRUAL_EXIST',
32 'XTR_LIMIT_EXCEEDED','XTR_INV_DESC_FLEX_API',
33 'XTR_INV_DESC_FLEX_CONTEXT','XTR_INV_DESC_FLEX') then
34 -------------------------------
35 -- Get the dynamic prompt text.
36 -------------------------------
37 open c_text;
38 fetch c_text into p_text;
39 close c_text;
40
41 if p_Error_code = 'XTR_MANDATORY' then
42 FND_MESSAGE.Set_Name('XTR','XTR_MANDATORY_FIELD');
43 FND_MESSAGE.Set_Token('FIELD', p_text);
44
45 elsif p_Error_code = 'XTR_INV_LIMIT_CODE' then
46 FND_MESSAGE.Set_Name('XTR','XTR_INV_LIMIT_CODE_FIELD');
47 FND_MESSAGE.Set_Token('LIMIT_CODE', p_text);
48
49 elsif p_Error_code = 'XTR_IMP_DEAL_REVAL_EXIST' then
50 FND_MESSAGE.Set_Name ('XTR', 'XTR_DEAL_REVAL_DONE');
51 FND_MESSAGE.Set_Token ('DATE',p_field_name);
52
53 elsif p_Error_code = 'XTR_IMP_DEAL_ACCRUAL_EXIST' then
54 FND_MESSAGE.Set_Name ('XTR', 'XTR_DEAL_ACCRLS_EXIST');
55 FND_MESSAGE.Set_Token ('DATE',p_field_name);
56
57 elsif p_Error_code in ('XTR_INV_DESC_FLEX_API',
58 'XTR_INV_DESC_FLEX_CONTEXT','XTR_INV_DESC_FLEX') then
59 FND_MESSAGE.Set_Name ('XTR', 'XTR_INV_DESC_FLEX_API');
60
61 elsif p_Error_code = 'XTR_LIMIT_EXCEEDED' then
62 null;
63 -- do nothing, return error to calling form to handle limits checks.
64 end if;
65 else
66 FND_MESSAGE.Set_Name('XTR', p_Error_Code);
67 end if;
68
69 APP_EXCEPTION.raise_exception;
70 end if;
71
72 IF xtr_risk_debug_pkg.g_Debug THEN
73 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.LOG_ERRORS');
74 END IF;
75
76 END LOG_ERRORS;
77
78
79
80 /*------------------------------------------------------------------------
81 This procedure get the actual Action Code.
82 ------------------------------------------------------------------------*/
83 FUNCTION get_actual_action_code(p_user_action_code VARCHAR2,
84 p_deal_type VARCHAR2)
85 RETURN VARCHAR2 IS
86
87 cursor Get_Type is
88 select ACTION_CODE
89 from XTR_AMOUNT_ACTIONS_V
90 where DEAL_TYPE = p_deal_type
91 and AMOUNT_TYPE = 'AMOUNT'
92 and USER_ACTION_CODE = p_user_action_code;
93
94 v_type VARCHAR2(7);
95
96 BEGIN
97 IF xtr_risk_debug_pkg.g_Debug THEN
98 xtr_risk_debug_pkg.dpush('get_actual_action_code: ' || 'XTR_EXP_TRANSFERS.GET_ACT_ACTION_CODE');
99 END IF;
100
101 open get_type;
102 fetch get_type into v_type;
103 close get_type;
104
105 IF xtr_risk_debug_pkg.g_Debug THEN
106 xtr_risk_debug_pkg.dpop('get_actual_action_code: ' || 'XTR_EXP_TRANSFERS.GET_ACT_ACTION_CODE');
107 END IF;
108 return v_type;
109
110 END get_actual_action_code;
111
112
113
114 /*------------------------------------------------------------------------
115 This procedure get the Actual Deal Type code.
116 ------------------------------------------------------------------------*/
117 FUNCTION get_actual_deal_type(p_user_deal_type VARCHAR2)
118 RETURN VARCHAR2 IS
119
120 cursor deal_type is
121 select deal_type from xtr_deal_types
122 where user_deal_type = p_user_deal_type;
123
124 v_deal_type VARCHAR2(7);
125
126 BEGIN
127 IF xtr_risk_debug_pkg.g_Debug THEN
128 xtr_risk_debug_pkg.dpush('get_actual_deal_type: ' || 'XTR_EXP_TRANSFERS.GET_ACT_DEAL_TYPE');
129 END IF;
130
131 open deal_type;
132 fetch deal_type into v_deal_type;
133 close deal_type;
134
135 IF xtr_risk_debug_pkg.g_Debug THEN
136 xtr_risk_debug_pkg.dpop('get_actual_deal_type: ' || 'XTR_EXP_TRANSFERS.GET_ACT_DEAL_TYPE');
137 END IF;
138 return v_deal_type;
139
140 END get_actual_deal_type;
141
142
143
144
145 /*------------------------------------------------------------------------
146 This procedure get the FX Spot Rate.
147 ------------------------------------------------------------------------*/
148 FUNCTION get_actual_deal_subtype(p_deal_type VARCHAR2,
149 p_user_deal_subtype VARCHAR2)
150 RETURN VARCHAR2 IS
151
152 cursor deal_subtype is
153 select deal_subtype from xtr_deal_subtypes
154 where user_deal_subtype = p_user_deal_subtype
155 and deal_type = 'EXP'; --p_deal_type; -- fails with different user deal types
156
157 v_deal_subtype VARCHAR2(7);
158
159 BEGIN
160 IF xtr_risk_debug_pkg.g_Debug THEN
161 xtr_risk_debug_pkg.dpush('get_actual_deal_subtype: ' || 'XTR_EXP_TRANSFERS.GET_ACT_DEAL_SUBT');
162 END IF;
163
164 open deal_subtype;
165 fetch deal_subtype into v_deal_subtype;
166 close deal_subtype;
167
168 IF xtr_risk_debug_pkg.g_Debug THEN
169 xtr_risk_debug_pkg.dpop('get_actual_deal_subtype: ' || 'XTR_EXP_TRANSFERS.GET_ACT_DEAL_SUBT');
170 END IF;
171 return v_deal_subtype;
172
173 END get_actual_deal_subtype;
174
175
176
177
178 /*------------------------------------------------------------------------
179 This procedure is checks whether the company_code is valid.
180 ------------------------------------------------------------------------*/
181 function VALID_COMPANY_CODE(p_comp IN VARCHAR2) return boolean IS
182 CURSOR company_code IS
183 SELECT COUNT(*) FROM xtr_parties_v
184 WHERE party_type='C' AND party_code=p_comp;
185
186 v_count NUMBER;
187
188 BEGIN
189 IF xtr_risk_debug_pkg.g_Debug THEN
190 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_COMPANY_CODE');
191 END IF;
192
193 OPEN company_code;
194 FETCH company_code INTO v_count;
195 CLOSE company_code;
196
197 IF xtr_risk_debug_pkg.g_Debug THEN
198 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_COMPANY_CODE');
199 END IF;
200
201 IF v_count=0 THEN
202 RETURN FALSE;
203 ELSE
204 RETURN TRUE;
205 END IF;
206 END VALID_COMPANY_CODE;
207
208
209
210
211 /*------------------------------------------------------------------------
212 This procedure is used to log errors.
213 ------------------------------------------------------------------------*/
214 function VALID_STATUS_CODE(p_status_code IN VARCHAR2) return boolean IS
215
216 BEGIN
217 IF xtr_risk_debug_pkg.g_Debug THEN
218 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_STATUS_CODE');
219 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_STATUS_CODE');
220 END IF;
221
222 IF p_status_code='CURRENT' THEN
223 RETURN TRUE;
224 ELSE
225 RETURN FALSE;
226 END IF;
227 END VALID_STATUS_CODE;
228
229
230
231 /*------------------------------------------------------------------------
232 This procedure validates the Exposure Type.
233 ------------------------------------------------------------------------*/
234 function VALID_EXPOSURE_TYPE(p_comp IN VARCHAR2,
235 p_exposure_type IN VARCHAR2) return boolean IS
236 CURSOR exposure_type IS
237 SELECT COUNT(*) FROM XTR_EXPOSURE_TYPES_V
238 WHERE company_code=p_comp
239 AND exposure_type=p_exposure_type
240 AND tax_brokerage_type IS NULL;
241
242 v_count NUMBER;
243
244 BEGIN
245 IF xtr_risk_debug_pkg.g_Debug THEN
246 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_EXPOSURE_TYPE');
247 END IF;
248
249 OPEN exposure_type;
250 FETCH exposure_type INTO v_count;
251 CLOSE exposure_type;
252
253 IF xtr_risk_debug_pkg.g_Debug THEN
254 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_EXPOSURE_TYPE');
255 END IF;
256
257 IF v_count=0 THEN
258 RETURN FALSE;
259 ELSE
260 RETURN TRUE;
261 END IF;
262
263 END VALID_EXPOSURE_TYPE;
264
265
266
267 /*------------------------------------------------------------------------
268 This procedure is used to log errors.
269 ------------------------------------------------------------------------*/
270 function VALID_DEAL_SUBTYPE(p_deal_type IN VARCHAR2,
271 p_deal_subtype IN VARCHAR2) return boolean IS
272
273 -- CURSOR deal_subtype IS
274 -- select COUNT(*)
275 -- from xtr_auth_deal_subtypes_v
276 -- where deal_type = p_deal_type
277 -- and deal_subtype = p_deal_subtype;
278 --The deal_subtype in the view is referring to the user_deal_subtype
279 --in the table
280
281 CURSOR deal_subtype IS
282 select COUNT(*)
283 from xtr_deal_subtypes_v
284 where deal_type='EXP' and authorised='Y'
285 and user_deal_subtype = p_deal_subtype;
286
287 v_count NUMBER;
288
289 BEGIN
290 IF xtr_risk_debug_pkg.g_Debug THEN
291 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_DEAL_SUBTYPE');
292 xtr_risk_debug_pkg.dlog('VALID_DEAL_SUBTYPE: ' || 'p_deal_type',p_deal_type);
293 xtr_risk_debug_pkg.dlog('VALID_DEAL_SUBTYPE: ' || 'p_deal_subtype',p_deal_subtype);
294 END IF;
295 OPEN deal_subtype;
296 FETCH deal_subtype INTO v_count;
297 CLOSE deal_subtype;
298 IF xtr_risk_debug_pkg.g_Debug THEN
299 xtr_risk_debug_pkg.dlog('VALID_DEAL_SUBTYPE: ' || 'v_count',v_count);
300 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_DEAL_SUBTYPE');
301 END IF;
302
303 IF v_count=0 THEN
304 RETURN FALSE;
305 ELSE
306 RETURN TRUE;
307 END IF;
308 END VALID_DEAL_SUBTYPE;
309
310
311
312 /*------------------------------------------------------------------------
313 This procedure checks whether the portfolio code is valid.
314 ------------------------------------------------------------------------*/
315 function VALID_PORTFOLIO(p_comp IN VARCHAR2,
316 p_portfolio IN VARCHAR2) return boolean IS
317 CURSOR portfolio IS
318 select COUNT(*)
319 from xtr_portfolios_v
320 where company_code = p_comp
321 and portfolio = p_portfolio
322 and nvl(cmf_yn,'N') = 'N'
323 and nvl(external_portfolio,'N') = 'N';
324
325 v_count NUMBER;
326
327 BEGIN
328 IF xtr_risk_debug_pkg.g_Debug THEN
329 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_PORTFOLIO');
330 END IF;
331
332 OPEN portfolio;
333 FETCH portfolio INTO v_count;
334 CLOSE portfolio;
335
336 IF xtr_risk_debug_pkg.g_Debug THEN
337 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_PORTFOLIO');
338 END IF;
339
340 IF v_count=0 THEN
341 RETURN FALSE;
342 ELSE
343 RETURN TRUE;
344 END IF;
345 END VALID_PORTFOLIO;
346
347
348
349
350 /*------------------------------------------------------------------------
351 This procedure is used to log errors.
352 ------------------------------------------------------------------------*/
353 function VALID_ACTION(p_action IN VARCHAR2,
354 p_deal_type IN VARCHAR2) return boolean IS
355 CURSOR action IS
356 select COUNT(*)
357 from xtr_amount_actions_v
358 where amount_type = 'AMOUNT'
359 and deal_type = 'EXP'
360 and user_action_code = p_action;
361
362 v_count NUMBER;
363
364 BEGIN
365 IF xtr_risk_debug_pkg.g_Debug THEN
366 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_ACTION');
367 END IF;
368
369 OPEN action;
370 FETCH action INTO v_count;
371 CLOSE action;
372
373 IF xtr_risk_debug_pkg.g_Debug THEN
374 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_ACTION');
375 END IF;
376
377 IF v_count=0 THEN
378 RETURN FALSE;
379 ELSE
380 RETURN TRUE;
381 END IF;
382 END VALID_ACTION;
383
384
385
386
387 /*------------------------------------------------------------------------
388 This procedure checks the validity of the currency.
389 ------------------------------------------------------------------------*/
390 function VALID_CURRENCY(p_curr IN VARCHAR2) return boolean IS
391 CURSOR currency IS
392 select COUNT(*)
393 from xtr_master_currencies_v
397 v_count NUMBER;
394 where currency = p_curr
395 and NVL(authorised,'N')='Y';
396
398
399 BEGIN
400 IF xtr_risk_debug_pkg.g_Debug THEN
401 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_CURRENCY');
402 END IF;
403
404 OPEN currency;
405 FETCH currency INTO v_count;
406 CLOSE currency;
407
408 IF xtr_risk_debug_pkg.g_Debug THEN
409 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_CURRENCY');
410 END IF;
411
412 IF v_count=0 THEN
413 RETURN FALSE;
414 ELSE
415 RETURN TRUE;
416 END IF;
417 END VALID_CURRENCY;
418
419
420
421
422 /*------------------------------------------------------------------------
423 This procedure is checks the validity of the company account no.
424 ------------------------------------------------------------------------*/
425 function VALID_COMP_ACCT(p_comp IN VARCHAR2,
426 p_comp_acct IN VARCHAR2,
427 p_curr IN VARCHAR2) return boolean IS
428 CURSOR comp_acct IS
429 select COUNT(*)
430 from xtr_bank_accounts_v
431 where party_code = p_comp
432 and currency = p_curr
433 and account_number = p_comp_acct
434 and NVL(authorised,'N') = 'Y';
435
436 v_count NUMBER;
437
438 BEGIN
439 IF xtr_risk_debug_pkg.g_Debug THEN
440 xtr_risk_debug_pkg.dpush('VALID_COMP_ACCT: ' || 'XTR_EXP_TRANSFERS.VALID_COMP_ACT');
441 END IF;
442
443 OPEN comp_acct;
444 FETCH comp_acct INTO v_count;
445 CLOSE comp_acct;
446
447 IF xtr_risk_debug_pkg.g_Debug THEN
448 xtr_risk_debug_pkg.dpop('VALID_COMP_ACCT: ' || 'XTR_EXP_TRANSFERS.VALID_COMP_ACT');
449 END IF;
450
451 IF v_count=0 THEN
452 RETURN FALSE;
453 ELSE
454 RETURN TRUE;
455 END IF;
456 END VALID_COMP_ACCT;
457
458 /*------------------------------------------------------------------------
459 This procedure is used to log errors.
460 ------------------------------------------------------------------------*/
461 function VALID_SETTLE_ACTION(p_settle_action IN VARCHAR2,
462 p_deal_subtype IN VARCHAR2,
463 p_act_amount IN NUMBER,
464 p_act_date IN DATE,
465 p_cparty_code IN VARCHAR2) return boolean IS
466
467 p_error BOOLEAN := TRUE;
468 v_deal_subtype VARCHAR2(7);
469
470 BEGIN
471 IF xtr_risk_debug_pkg.g_Debug THEN
472 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_SETTLE_ACTION');
473 END IF;
474
475 v_deal_subtype := get_actual_deal_subtype('EXP',p_deal_subtype);
476 --xtr_risk_debug_pkg.dlog('p_settle_action',p_settle_action);
477 --xtr_risk_debug_pkg.dlog('p_deal_subtype',p_deal_subtype||'-');
478 --xtr_risk_debug_pkg.dlog('p_act_amount',p_act_amount);
479 --xtr_risk_debug_pkg.dlog('p_act_date',p_act_date);
480 --xtr_risk_debug_pkg.dlog('p_cparty_code',p_cparty_code);
481 if p_settle_action = 'Y' THEN
482 if p_deal_subtype <> 'FIRM' THEN
483 p_error := FALSE;
484 end if;
485 --xtr_risk_debug_pkg.dlog('After checking DST p_error',p_error);
486 if p_act_amount is null or p_act_date is null
487 or p_cparty_code is null then
488 p_error := FALSE;
489 end if;
490 end if;
491
492 IF xtr_risk_debug_pkg.g_Debug THEN
493 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_SETTLE_ACTION');
494 END IF;
495 return p_error;
496 END VALID_SETTLE_ACTION;
497
498 /*------------------------------------------------------------------------
499 This procedure is used to log errors.
500 ------------------------------------------------------------------------*/
501 function VALID_CPARTY_CODE(p_comp IN VARCHAR2,
502 p_cparty IN VARCHAR2) return boolean IS
503 CURSOR cparty_code IS
504 select COUNT(*)
505 from xtr_party_info_v
506 where party_code <> p_comp
507 and party_code = p_cparty
508 and NVL(authorised,'N') = 'Y';
509
510 -- select party_code,short_name
511 --from xtr_party_info_v
512 --where nvl(authorised,'N') = 'Y'
513 --and party_code <> :ET.company_code
514 --order by party_code
515
516 v_count NUMBER;
517
518 BEGIN
519 IF xtr_risk_debug_pkg.g_Debug THEN
520 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_CPARTY_CODE');
521 xtr_risk_debug_pkg.dlog('VALID_CPARTY_CODE: ' || 'p_cparty',p_cparty);
522 xtr_risk_debug_pkg.dlog('VALID_CPARTY_CODE: ' || 'p_comp',p_comp);
523 END IF;
524 OPEN cparty_code;
525 FETCH cparty_code INTO v_count;
526 CLOSE cparty_code;
527
528 IF xtr_risk_debug_pkg.g_Debug THEN
529 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_CPARTY_CODE');
530 END IF;
531
532 IF v_count=0 THEN
533 RETURN FALSE;
534 ELSE
535 RETURN TRUE;
536 END IF;
537 END VALID_CPARTY_CODE;
538
539
540
541 /*------------------------------------------------------------------------
542 This procedure is used to log errors.
543 ------------------------------------------------------------------------*/
544 function VALID_CPARTY_REF( p_cparty_account_no IN VARCHAR2,
545 p_cparty_ref IN VARCHAR2,
546 p_cparty IN VARCHAR2,
547 p_curr IN VARCHAR2) return boolean IS
548 CURSOR cparty_ref IS
549 select COUNT(*)
550 from xtr_bank_accounts_v
551 where party_code = p_cparty
552 and account_number = p_cparty_account_no
553 and currency = p_curr
554 and NVL(authorised,'N') = 'Y';
555
556 v_count NUMBER;
557
558 BEGIN
559 IF xtr_risk_debug_pkg.g_Debug THEN
560 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_CPARTY_REF');
561 END IF;
562
563 OPEN cparty_ref;
564 FETCH cparty_ref INTO v_count;
565 CLOSE cparty_ref;
566
567 IF xtr_risk_debug_pkg.g_Debug THEN
568 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_CPARTY_REF');
569 END IF;
570
571 IF v_count=0 THEN
572 RETURN FALSE;
573 ELSE
574 RETURN TRUE;
575 END IF;
576 END VALID_CPARTY_REF;
577
578 /*------------------------------------------------------------------------
579 This procedure is used to log errors.
580 ------------------------------------------------------------------------*/
581 function VALID_DEALER_CODE(p_dealer_code IN VARCHAR2) return BOOLEAN is
582
583 CURSOR dealer_code IS
584 select COUNT(*)
585 from xtr_dealer_codes_v
586 where dealer_code = p_dealer_code;
587
588 v_count NUMBER;
589 BEGIN
590 OPEN dealer_code;
591 FETCH dealer_code INTO v_count;
592 CLOSE dealer_code;
593
594 IF v_count = 0 THEN
595 RETURN FALSE;
596 ELSE
597 RETURN TRUE;
598 END IF;
599 END VALID_DEALER_CODE;
600
601
602 /*------------------------------------------------------------------------
603 This procedure is used to log errors.
604 ------------------------------------------------------------------------*/
605 function VALID_DEAL_LINK_CODE(p_deal_link_code IN VARCHAR2)
606 return boolean IS
607
608 CURSOR deal_link_code IS
609 select COUNT(*)
610 from xtr_deal_linking_v
611 where deal_linking_code = p_deal_link_code;
612
613 v_count NUMBER;
614
615 BEGIN
616 IF xtr_risk_debug_pkg.g_Debug THEN
617 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALID_DEAL_LINK_CODE');
618 END IF;
619
620 OPEN deal_link_code;
621 FETCH deal_link_code INTO v_count;
622 CLOSE deal_link_code;
623
624 IF xtr_risk_debug_pkg.g_Debug THEN
625 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALID_DEAL_LINK_CODE');
626 END IF;
627
628 IF v_count=0 THEN
629 RETURN FALSE;
630 ELSE
631 RETURN TRUE;
632 END IF;
633 END VALID_DEAL_LINK_CODE;
634
635
636
637
638 /*------------------------------------------------------------------------
639 Local Procedure to find the home currency for this company and use
640 the latest bid rate for chosen currency from Spot rates to calculate
641 HCE amount
642 ------------------------------------------------------------------------*/
643 FUNCTION CALC_HCE_AMOUNT(p_hce_rate IN NUMBER,
644 p_actual_amount IN NUMBER,
645 p_estimate_amount IN NUMBER) RETURN NUMBER is
646
647 -- Example from Exposure Transactions FORMS
648 -- cursor HCE is
649 -- select nvl(round(nvl(:ET.AMOUNT,:ET.ESTIMATE_AMOUNT)/p_hce_rate,
650 -- rounding_factor),0)
651 -- from XTR_MASTER_CURRENCIES_V
652 -- where CURRENCY = :ET.CURRENCY;
653 --
654 v_hce_amount NUMBER;
655
656 begin
657 IF xtr_risk_debug_pkg.g_Debug THEN
658 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.CALC_HCE_AMOUNT');
659 END IF;
660
661 if p_actual_amount is not null then
662 v_hce_amount := p_actual_amount/p_hce_rate;
663 elsif p_estimate_amount is not null then
664 v_hce_amount := p_estimate_amount/p_hce_rate;
665 else
666 v_hce_amount := 0;
667 end if;
668
669 IF xtr_risk_debug_pkg.g_Debug THEN
670 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.CALC_HCE_AMOUNT');
671 END IF;
672 RETURN v_hce_amount;
673
674 end CALC_HCE_AMOUNT;
675
676
677 /*------------------------------------------------------------------------
678 This procedure get the FX Spot Rate.
679 ------------------------------------------------------------------------*/
680 FUNCTION get_fx_rate(p_company_code VARCHAR2,
681 p_curr VARCHAR2)
682 RETURN NUMBER IS
683
684 cursor get_home_currency is
685 select home_currency
686 from XTR_parties_V
687 where party_code = p_company_code;
688
689 cursor get_rate_hce is
690 select round(hce_rate,5)
691 from XTR_master_currencies_V
692 where currency = p_curr;
693
694 v_home_curr VARCHAR2(20);
695 v_fx_rate NUMBER;
696
697 BEGIN
698 IF xtr_risk_debug_pkg.g_Debug THEN
699 xtr_risk_debug_pkg.dpush('QRM_PA_AGGREGATION_P.GET_FX_RATE');
700 END IF;
701
702 open get_home_currency;
703 fetch get_home_currency into v_home_curr;
704 close get_home_currency;
705
706 open get_rate_hce;
707 fetch get_rate_hce into v_fx_rate;
708 close get_rate_hce;
709 --
710 IF v_home_curr = p_curr THEN
711 v_fx_rate := 1;
712 END IF;
713
714 IF xtr_risk_debug_pkg.g_Debug THEN
715 xtr_risk_debug_pkg.dpop('QRM_PA_AGGREGATION_P.GET_FX_RATE');
716 END IF;
717
718 RETURN v_fx_rate;
719
720 END get_fx_rate;
721
722
723
724
725 /*------------------------------------------------------------------------
726 This procedure maps the XTR_DEALS_INTERFACE to XTR_EXPOSURE_TRANSACTIONS
727 table.
728 ------------------------------------------------------------------------*/
729 procedure COPY_FROM_INTERFACE_TO_EXP
730 (ARec_Interface IN XTR_DEALS_INTERFACE%rowtype,
731 p_error OUT NOCOPY BOOLEAN) IS
732
733 v_fx_rate NUMBER;
734
735 BEGIN
736 IF xtr_risk_debug_pkg.g_Debug THEN
737 xtr_risk_debug_pkg.dpush('COPY_FROM_INTERFACE_TO_EXP: ' || 'XTR_EXP_TRANSFERS.COPY_FROM_INT_TO_EXP');
738 END IF;
739
740 p_error := FALSE;
741 v_fx_rate := get_fx_rate(ARec_Interface.company_code,
742 ARec_Interface.currency_a);
743 if v_fx_rate is null then
744 Log_Errors(ARec_Interface.external_deal_id,
745 ARec_Interface.deal_type,
746 'CurrencyA','XTR_886');
747 p_error := TRUE;
748 end if;
749
750 if NOT p_error then
751 g_main_rec.ACCOUNT_NO := ARec_Interface.account_no_a;
752 /* Bug 4092067. The deal_type is still the user entered value.
753 In case the user_deal_type has been changed from the seeded value
754 the procedure get_actual_action_code will return a null value.
755 DEAL_TYPE has to be called earlier so that the actual deal_type
756 will be passed to the get_actual_action_code procedure. */
757 g_main_rec.DEAL_TYPE := get_actual_deal_type(ARec_Interface.deal_type);
758 g_main_rec.ACTION_CODE := get_actual_action_code(
759 ARec_Interface.action_code,
760 g_main_rec.deal_type);
761 g_main_rec.AMOUNT := ARec_Interface.amount_b;
762 --AVG_RATE has to be called earlier than AMOUNT_HCE,
763 --because AMOUNT_HCE requires AVG_RATE.
764 g_main_rec.AVG_RATE := v_fx_rate;
765 g_main_rec.AMOUNT_HCE := calc_hce_amount(v_fx_rate,
766 ARec_Interface.amount_b,
767 ARec_Interface.amount_a);
768 g_main_rec.AMOUNT_TYPE := 'AMOUNT'; --refer to hidden item in ET block
769 g_main_rec.ARCHIVE_BY := null;
770 g_main_rec.ARCHIVE_DATE := null;
771 g_main_rec.AUDIT_INDICATOR := NULL;
772 g_main_rec.BALANCE := NULL;
773 g_main_rec.BENEFICIARY_CODE := NULL;
774 g_main_rec.COMMENTS := ARec_Interface.comments;
775 g_main_rec.COMPANY_CODE := ARec_Interface.company_code;
776 g_main_rec.CONTRA_NZD_AMOUNT := NULL;
777 g_main_rec.COVERED_BY_FX_CONTRACT := NULL;
778 g_main_rec.CPARTY_CODE := ARec_Interface.cparty_code;
779 g_main_rec.CPARTY_REF := null; -- bug 3034164
780 g_main_rec.CPARTY_ACCOUNT_NO := Arec_Interface.cparty_account_no; -- CE BANK MIGRATION
781 g_main_rec.CREATED_BY := g_user;
782 g_main_rec.CREATED_ON := g_curr_date;
783 g_main_rec.CURRENCY := ARec_Interface.currency_a;
784 g_main_rec.DEAL_STATUS := NULL; --refer to STATUS_CODE
785 g_main_rec.DEAL_SUBTYPE := get_actual_deal_subtype(
786 ARec_Interface.deal_type,
787 ARec_Interface.deal_subtype);
788 g_main_rec.ESTIMATE_AMOUNT := ARec_Interface.amount_a;
789 g_main_rec.ESTIMATE_DATE := ARec_Interface.date_a;
790 g_main_rec.EXPOSURE_TYPE := ARec_Interface.exposure_type;
791 g_main_rec.FIS_FOB := NULL;
792 g_main_rec.INTERMEDIARY_BANK_DETAILS := NULL;
793 g_main_rec.NZD_AMOUNT := NULL;
794 g_main_rec.PAYMENT_AMOUNT := NULL;
795 g_main_rec.PAYMENT_STATUS := NULL;
796 g_main_rec.PORTFOLIO_CODE := ARec_Interface.portfolio_code;
797 g_main_rec.PROFIT_LOSS := NULL;
798 g_main_rec.PURCHASING_MODULE := 'N'; --refer to PRE-INSERT trigger
799 g_main_rec.SELECT_ACTION := NULL;
800 g_main_rec.SELECT_REFERENCE := NULL;
801 g_main_rec.SETTLE_ACTION_REQD := ARec_Interface.settle_action_reqd;
802 --there is no formal status code in EXP deal, deal can be deleted, but
803 --to be consistent put 'CURRENT'
804 g_main_rec.STATUS_CODE := nvl(ARec_Interface.status_code,'CURRENT');
805 g_main_rec.SUBSIDIARY_REF := NULL;
806 g_main_rec.TAX_BROKERAGE_TYPE := NULL;
807 g_main_rec.THIRDPARTY_CODE := ARec_Interface.cparty_code;
808 g_main_rec.TRANSACTION_NUMBER := get_transaction_number;
809 g_main_rec.UPDATED_BY := null;
810 g_main_rec.UPDATED_ON := null;
811 g_main_rec.VALUE_DATE := ARec_Interface.date_b;
812 g_main_rec.WHOLESALE_REFERENCE := NULL;
813 g_main_rec.ATTRIBUTE_CATEGORY := ARec_Interface.attribute_category;
814 g_main_rec.ATTRIBUTE1 := ARec_Interface.attribute1;
815 g_main_rec.ATTRIBUTE2 := ARec_Interface.attribute2;
816 g_main_rec.ATTRIBUTE3 := ARec_Interface.attribute3;
817 g_main_rec.ATTRIBUTE4 := ARec_Interface.attribute4;
818 g_main_rec.ATTRIBUTE5 := ARec_Interface.attribute5;
819 g_main_rec.ATTRIBUTE6 := ARec_Interface.attribute6;
820 g_main_rec.ATTRIBUTE7 := ARec_Interface.attribute7;
821 g_main_rec.ATTRIBUTE8 := ARec_Interface.attribute8;
822 g_main_rec.ATTRIBUTE9 := ARec_Interface.attribute9;
823 g_main_rec.ATTRIBUTE10 := ARec_Interface.attribute10;
824 g_main_rec.ATTRIBUTE11 := ARec_Interface.attribute11;
825 g_main_rec.ATTRIBUTE12 := ARec_Interface.attribute12;
826 g_main_rec.ATTRIBUTE13 := ARec_Interface.attribute13;
827 g_main_rec.ATTRIBUTE14 := ARec_Interface.attribute14;
828 g_main_rec.ATTRIBUTE15 := ARec_Interface.attribute15;
829 g_main_rec.EXTERNAL_DEAL_ID := ARec_Interface.external_deal_id;
830 g_main_rec.REQUEST_ID := fnd_global.conc_request_id;
831 g_main_rec.PROGRAM_APPLICATION_ID := fnd_global.prog_appl_id;
832 g_main_rec.PROGRAM_ID := fnd_global.conc_program_id;
833 g_main_rec.PROGRAM_UPDATE_DATE := g_curr_date;
834 g_main_rec.INTERNAL_COMMENTS := NULL;
835 g_main_rec.EXTERNAL_COMMENTS := ARec_Interface.external_comments;
836 g_main_rec.DEAL_LINK_CODE := ARec_Interface.deal_linking_code;
837
838 --Bug 2254853
839 if nvl(g_main_rec.SETTLE_ACTION_REQD, 'N') = 'Y' then
840 g_main_rec.DUAL_AUTHORISATION_BY := ARec_Interface.dual_authorization_by;
841 g_main_rec.DUAL_AUTHORISATION_ON := ARec_Interface.dual_authorization_on;
842 else
843 g_main_rec.DUAL_AUTHORISATION_BY := NULL;
844 g_main_rec.DUAL_AUTHORISATION_ON := NULL;
845 end if;
846 --Bug 2254853
847
848 end if;
849
850 IF xtr_risk_debug_pkg.g_Debug THEN
851 xtr_risk_debug_pkg.dpop('COPY_FROM_INTERFACE_TO_EXP: ' || 'XTR_EXP_TRANSFERS.COPY_FROM_INT_TO_EXP');
852 END IF;
853 END COPY_FROM_INTERFACE_TO_EXP;
854
855
856
857
858 /*------------------------------------------------------------------------
859 This procedure assigns the values to the global record that will be used
860 to insert the deal later on.
861 ------------------------------------------------------------------------*/
862 procedure COPY_TO_EXP
863 (ARec IN OUT NOCOPY XTR_EXPOSURE_TRANSACTIONS%rowtype) IS
864
865 BEGIN
866 IF xtr_risk_debug_pkg.g_Debug THEN
867 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.COPY_TO_EXP');
868 END IF;
869
870 --per request from One-Step API by Venil
871 ARec.transaction_number := get_transaction_number;
872
873 g_main_rec.ACCOUNT_NO := ARec.account_no;
874 g_main_rec.ACTION_CODE := ARec.action_code;
875 g_main_rec.AMOUNT := ARec.amount;
876 --AVG_RATE has to be called earlier than AMOUNT_HCE,
877 --because AMOUNT_HCE requires AVG_RATE.
878 g_main_rec.AVG_RATE := ARec.avg_rate;
879 g_main_rec.AMOUNT_HCE := ARec.amount_hce;
880 g_main_rec.AMOUNT_TYPE := 'AMOUNT'; --refer to hidden item in ET block
881 g_main_rec.ARCHIVE_BY := null;
882 g_main_rec.ARCHIVE_DATE := null;
883 g_main_rec.AUDIT_INDICATOR := NULL;
884 g_main_rec.BALANCE := ARec.balance;
885 g_main_rec.BENEFICIARY_CODE := ARec.beneficiary_code;
886 g_main_rec.COMMENTS := ARec.comments;
887 g_main_rec.COMPANY_CODE := ARec.company_code;
888 g_main_rec.CONTRA_NZD_AMOUNT := ARec.contra_nzd_amount;
889 g_main_rec.COVERED_BY_FX_CONTRACT := ARec.covered_by_fx_contract;
890 g_main_rec.CPARTY_CODE := ARec.cparty_code;
891 g_main_rec.CPARTY_ACCOUNT_NO := ARec.cparty_account_no; -- CE BANK MIGRATION
892 g_main_rec.CPARTY_REF := null; --bug 3034164
893 g_main_rec.CREATED_BY := g_user;
894 g_main_rec.CREATED_ON := g_curr_date;
895 g_main_rec.CURRENCY := ARec.currency;
896 g_main_rec.DEAL_STATUS := ARec.deal_status; --refer to STATUS_CODE
897 --DEAL_TYPE has to be called earlier than DEAL_SUBTYPE,
898 --because to get actual DEAL_SUBTYPE requires the actual DEAL_TYPE.
899 g_main_rec.DEAL_TYPE := ARec.deal_type;
900 g_main_rec.DEAL_SUBTYPE := ARec.deal_subtype;
901 g_main_rec.ESTIMATE_AMOUNT := ARec.estimate_amount;
902 g_main_rec.ESTIMATE_DATE := ARec.estimate_date;
903 g_main_rec.EXPOSURE_TYPE := ARec.exposure_type;
904 g_main_rec.FIS_FOB := ARec.fis_fob;
905 g_main_rec.INTERMEDIARY_BANK_DETAILS := ARec.intermediary_bank_details;
906 g_main_rec.NZD_AMOUNT := ARec.nzd_amount;
907 g_main_rec.PAYMENT_AMOUNT := ARec.payment_amount;
908 g_main_rec.PAYMENT_STATUS := ARec.payment_status;
909 g_main_rec.PORTFOLIO_CODE := ARec.portfolio_code;
910 g_main_rec.PROFIT_LOSS := ARec.profit_loss;
911 g_main_rec.PURCHASING_MODULE := ARec.purchasing_module;
912 g_main_rec.SELECT_ACTION := ARec.select_action;
913 g_main_rec.SELECT_REFERENCE := ARec.select_reference;
914 g_main_rec.SETTLE_ACTION_REQD := ARec.settle_action_reqd;
915 --there is no formal status code in EXP deal, deal can be deleted, but
916 --to be consistent put 'CURRENT'
917 g_main_rec.STATUS_CODE := nvl(ARec.status_code,'CURRENT');
918 g_main_rec.SUBSIDIARY_REF := ARec.subsidiary_ref;
919 g_main_rec.TAX_BROKERAGE_TYPE := ARec.tax_brokerage_type;
920 g_main_rec.THIRDPARTY_CODE := ARec.thirdparty_code;
921 g_main_rec.TRANSACTION_NUMBER := ARec.transaction_number;
922 g_main_rec.UPDATED_BY := null;
923 g_main_rec.UPDATED_ON := null;
924 g_main_rec.VALUE_DATE := ARec.value_date;
925 g_main_rec.WHOLESALE_REFERENCE := ARec.wholesale_reference;
926 g_main_rec.ATTRIBUTE_CATEGORY := ARec.attribute_category;
927 g_main_rec.ATTRIBUTE1 := ARec.attribute1;
928 g_main_rec.ATTRIBUTE2 := ARec.attribute2;
929 g_main_rec.ATTRIBUTE3 := ARec.attribute3;
930 g_main_rec.ATTRIBUTE4 := ARec.attribute4;
931 g_main_rec.ATTRIBUTE5 := ARec.attribute5;
932 g_main_rec.ATTRIBUTE6 := ARec.attribute6;
933 g_main_rec.ATTRIBUTE7 := ARec.attribute7;
934 g_main_rec.ATTRIBUTE8 := ARec.attribute8;
935 g_main_rec.ATTRIBUTE9 := ARec.attribute9;
936 g_main_rec.ATTRIBUTE10 := ARec.attribute10;
937 g_main_rec.ATTRIBUTE11 := ARec.attribute11;
938 g_main_rec.ATTRIBUTE12 := ARec.attribute12;
939 g_main_rec.ATTRIBUTE13 := ARec.attribute13;
940 g_main_rec.ATTRIBUTE14 := ARec.attribute14;
941 g_main_rec.ATTRIBUTE15 := ARec.attribute15;
942 g_main_rec.EXTERNAL_DEAL_ID := ARec.external_deal_id;
943 g_main_rec.REQUEST_ID := fnd_global.conc_request_id;
944 g_main_rec.PROGRAM_APPLICATION_ID := fnd_global.prog_appl_id;
945 g_main_rec.PROGRAM_ID := fnd_global.conc_program_id;
946 g_main_rec.PROGRAM_UPDATE_DATE := g_curr_date;
947 g_main_rec.INTERNAL_COMMENTS := ARec.internal_comments;
948 g_main_rec.EXTERNAL_COMMENTS := ARec.external_comments;
949 g_main_rec.DEAL_LINK_CODE := ARec.deal_link_code;
950 g_main_rec.DUAL_AUTHORISATION_BY := g_user;
951 g_main_rec.DUAL_AUTHORISATION_ON := trunc(g_curr_date);
952 g_main_rec.CASH_POSITION_EXPOSURE := ARec.cash_position_exposure;
953
954 IF xtr_risk_debug_pkg.g_Debug THEN
955 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.COPY_TO_EXP');
956 END IF;
957 END COPY_TO_EXP;
958
959
960
961
962 /*------------------------------------------------------------------------
963 This procedure is used to check whether all the mandatory fields are
964 NOT NULL.
965 If there is error then log the error.
966 ------------------------------------------------------------------------*/
967 procedure CHECK_MANDATORY_FIELDS(ARec_Interface IN XTR_DEALS_INTERFACE%rowtype
968 ,p_error OUT NOCOPY BOOLEAN) IS
969
970 BEGIN
971 IF xtr_risk_debug_pkg.g_Debug THEN
972 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.CHECK_MANDATORY_FIELDS');
973 END IF;
974
975 p_error := FALSE;
976
977 if ARec_Interface.company_code is null then
978 log_errors(ARec_Interface.external_deal_id,
979 ARec_Interface.deal_type,
980 'CompanyCode','XTR_MANDATORY','ET.COMPANY_CODE');
981 p_error := TRUE;
982 end if;
983 IF xtr_risk_debug_pkg.g_Debug THEN
984 xtr_risk_debug_pkg.dlog('CHECK_MANDATORY_FIELDS: ' || 'company_code',p_error);
985 END IF;
986 if ARec_Interface.exposure_type is null then
987 log_errors(ARec_Interface.external_deal_id,
988 ARec_Interface.deal_type,
989 'ExposureType','XTR_MANDATORY','ET.EXPOSURE_TYPE');
990 p_error := TRUE;
991 end if;
992 IF xtr_risk_debug_pkg.g_Debug THEN
993 xtr_risk_debug_pkg.dlog('CHECK_MANDATORY_FIELDS: ' || 'exposure_type',p_error);
994 END IF;
995 if ARec_Interface.deal_subtype is null then
996 log_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
997 'DealSubtype','XTR_MANDATORY','ET.USER_DEAL_SUBTYPE');
998 p_error := TRUE;
999 end if;
1000 IF xtr_risk_debug_pkg.g_Debug THEN
1001 xtr_risk_debug_pkg.dlog('CHECK_MANDATORY_FIELDS: ' || 'deal_subtype',p_error);
1002 END IF;
1003 if ARec_Interface.portfolio_code is null then
1004 log_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1005 'PortfolioCode','XTR_MANDATORY','ET.PORTFOLIO_CODE');
1006 p_error := TRUE;
1007 end if;
1008 IF xtr_risk_debug_pkg.g_Debug THEN
1009 xtr_risk_debug_pkg.dlog('CHECK_MANDATORY_FIELDS: ' || 'portfolio_code',p_error);
1010 END IF;
1011 if ARec_Interface.action_code is null then
1012 log_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1013 'ActionCode','XTR_MANDATORY','ET.USER_ACTION_CODE');
1014 p_error := TRUE;
1015 end if;
1016 IF xtr_risk_debug_pkg.g_Debug THEN
1017 xtr_risk_debug_pkg.dlog('CHECK_MANDATORY_FIELDS: ' || 'action_code',p_error);
1018 END IF;
1019 if ARec_Interface.currency_a is null then
1020 log_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1021 'CurrencyA','XTR_MANDATORY','ET.CURRENCY');
1022 p_error := TRUE;
1023 end if;
1024 IF xtr_risk_debug_pkg.g_Debug THEN
1025 xtr_risk_debug_pkg.dlog('CHECK_MANDATORY_FIELDS: ' || 'currency_a',p_error);
1026 END IF;
1027 if ARec_Interface.settle_action_reqd is null then
1028 log_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1029 'SettleActionReqd','XTR_MANDATORY','ET.SETTLE_ACTION_REQD');
1030 p_error := TRUE;
1031 end if;
1032 IF xtr_risk_debug_pkg.g_Debug THEN
1033 xtr_risk_debug_pkg.dlog('CHECK_MANDATORY_FIELDS: ' || 'settle_action_reqd',p_error);
1034 END IF;
1035 if ARec_Interface.account_no_a is null then
1036 log_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1037 'AccountNoA','XTR_MANDATORY','ET.ACCOUNT_NO');
1038 p_error := TRUE;
1039 end if;
1040 IF xtr_risk_debug_pkg.g_Debug THEN
1041 xtr_risk_debug_pkg.dlog('CHECK_MANDATORY_FIELDS: ' || 'account_no_a',p_error);
1042 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.CHECK_MANDATORY_FIELDS');
1043 END IF;
1044 END check_mandatory_fields;
1045
1046
1047
1048 /*------------------------------------------------------------------------
1049 This procedure validates the business logic for the deal items.
1050 ------------------------------------------------------------------------*/
1051 procedure VALIDATE_DEALS(ARec_Interface IN XTR_DEALS_INTERFACE%rowtype,
1052 p_error OUT NOCOPY BOOLEAN) IS
1053
1054 -- v_holiday_level VARCHAR2(1);
1055 -- v_holiday_error NUMBER;
1056
1057 v_err_segment VARCHAR2(30);
1058 p_cparty_error BOOLEAN := FALSE;
1059
1060 BEGIN
1061 IF xtr_risk_debug_pkg.g_Debug THEN
1062 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.VALIDATE_DEALS');
1063 END IF;
1064
1065 p_error := FALSE;
1066
1067 if not valid_company_code(ARec_Interface.Company_Code) then
1068 Log_Errors(ARec_Interface.external_deal_id,
1069 ARec_Interface.deal_type,
1070 'CompanyCode','XTR_INV_COMP_CODE');
1071 p_error := TRUE;
1072 end if;
1073
1074 --DEAL_TYPE has to be called earlier than DEAL_SUBTYPE,
1075 --because to get actual DEAL_SUBTYPE requires the actual DEAL_TYPE.
1076 -- if not valid_status_code(ARec_Interface.status_code) then
1077 -- Log_Errors(ARec_Interface.external_deal_id,
1078 -- ARec_Interface.deal_type,
1079 -- 'StatusCode','XTR_INV_STATUS_CODE');
1080 -- p_error := TRUE;
1081 -- end if;
1082
1083 if not valid_exposure_type(ARec_Interface.company_code,
1084 ARec_Interface.exposure_type) then
1085 Log_Errors(ARec_Interface.external_deal_id,
1086 ARec_Interface.deal_type,
1087 'ExposureType','XTR_INV_EXPOSURE_TYPE');
1088 p_error := TRUE;
1089 end if;
1090
1091 if not valid_deal_subtype('EXP',
1092 ARec_Interface.deal_subtype) then
1093 Log_Errors(ARec_Interface.external_deal_id,
1094 ARec_Interface.deal_type,
1095 'DealSubtype','XTR_INV_DEAL_SUBTYPE');
1096 p_error := TRUE;
1097 end if;
1098
1099 if not valid_portfolio(ARec_Interface.company_code,
1100 ARec_Interface.portfolio_code) then
1101 Log_Errors(ARec_Interface.external_deal_id,
1102 ARec_Interface.deal_type,
1103 'PortfolioCode','XTR_INV_PORT_CODE');
1104 p_error := TRUE;
1105 end if;
1106
1107 if not valid_action(ARec_Interface.action_code,
1108 ARec_Interface.deal_type) then
1109 Log_Errors(ARec_Interface.external_deal_id,
1110 ARec_Interface.deal_type,
1111 'ActionCode','XTR_INV_ACTION');
1112 p_error := TRUE;
1113 end if;
1114
1115 if not valid_currency(ARec_Interface.Currency_A) then
1116 Log_Errors(ARec_Interface.external_deal_id,
1117 ARec_Interface.deal_type,
1118 'CurrencyA','XTR_INV_CURR');
1119 p_error := TRUE;
1120 end if;
1121
1122 if not valid_comp_acct(ARec_Interface.company_code,
1123 ARec_Interface.account_no_a,ARec_Interface.currency_a) then
1124 Log_Errors(ARec_Interface.external_deal_id,
1125 ARec_Interface.deal_type,
1126 'AccountNoA','XTR_INV_COMP_ACCT_NO');
1127 p_error := TRUE;
1128 end if;
1129
1130 --
1131 --All amounts cannot be negative numbers.
1132 --
1133 if NVL(ARec_Interface.amount_a,0)<0 then
1134 Log_Errors(ARec_Interface.external_deal_id,
1135 ARec_Interface.deal_type,
1136 'AmountA','XTR_VALUE_GE_ZERO');
1137 p_error := TRUE;
1138 end if;
1139
1140 if NVL(ARec_Interface.amount_b,0)<0 then
1141 Log_Errors(ARec_Interface.external_deal_id,
1142 ARec_Interface.deal_type,
1143 'AmountB','XTR_56');
1144 p_error := TRUE;
1145 end if;
1146
1147 --
1148 --Error if both estimate and actual amounts are zero
1149 --
1150 -- if ((ARec_Interface.amount_a IS NULL)
1151 -- and (ARec_Interface.amount_b IS NULL)) then
1152 -- Log_Errors(ARec_Interface.external_deal_id,
1153 -- ARec_Interface.deal_type,
1154 -- 'AmountB','XTR_NEED_AMOUNT');
1155 -- p_error := TRUE;
1156 -- end if;
1157
1158 --
1159 --Warn if dates fall into holidays.
1160 --
1161 -- xtr_fps3_p.CHK_HOLIDAY (ARec_Interface.date_a,
1162 -- ARec_Interface.currency_a,
1163 -- v_holiday_error,
1164 -- v_holiday_level);
1165 -- if v_holiday_error is not null then
1166 -- Log_Errors(ARec_Interface.external_deal_id,
1167 -- ARec_Interface.deal_type,
1168 -- 'DateA','XTR_INV_ESTIMATE_DATE');
1169 -- p_error := TRUE;
1170 -- end if;
1171
1172 -- xtr_fps3_p.CHK_HOLIDAY (ARec_Interface.date_b,
1173 -- ARec_Interface.currency_a,
1174 -- v_holiday_error,
1175 -- v_holiday_level);
1176 -- if v_holiday_error is not null then
1177 -- Log_Errors(ARec_Interface.external_deal_id,
1178 -- ARec_Interface.deal_type,
1179 -- 'DateB','XTR_INV_ACTUAL_DATE');
1180 -- p_error := TRUE;
1181 -- end if;
1182
1183 if not valid_settle_action(ARec_Interface.settle_action_reqd,
1184 ARec_Interface.deal_subtype, ARec_Interface.amount_b,
1185 ARec_Interface.date_b, ARec_Interface.cparty_code) then
1186 Log_Errors(ARec_Interface.external_deal_id,
1187 ARec_Interface.deal_type,
1188 'SettleActionReqd','XTR_INV_SETTLE_ACTION_REQD');
1189 p_error := TRUE;
1190 end if;
1191
1192 if ARec_Interface.cparty_code is not null and
1193 not valid_cparty_code(ARec_Interface.company_code,
1194 ARec_Interface.cparty_code) then
1195 Log_Errors(ARec_Interface.external_deal_id,
1196 ARec_Interface.deal_type,
1197 'CpartyCode','XTR_INV_CPARTY_CODE');
1198 p_error := TRUE;
1199 end if;
1200
1201 if (ARec_Interface.account_no_b is not null or ARec_Interface.cparty_account_no is not null) and
1202 not valid_cparty_ref(ARec_interface.cparty_account_no,ARec_Interface.account_no_b,
1203 ARec_Interface.cparty_code, ARec_Interface.currency_a) then
1204 Log_Errors(ARec_Interface.external_deal_id,
1205 ARec_Interface.deal_type,
1206 'CpartyAccountNo','XTR_INV_CPARTY_ACCOUNT');
1207 p_error := TRUE;
1208 p_cparty_error := TRUE;
1209 end if;
1210
1211 if (ARec_Interface.dual_authorization_by is not null and
1212 not valid_dealer_code(ARec_Interface.dual_authorization_by)) then
1213 Log_Errors(ARec_Interface.external_deal_id,
1214 ARec_Interface.deal_type,
1215 'DualAuthorizationBy','XTR_INV_DUAL_AUTH_BY'); -- Bug 2254853
1216 p_error := TRUE;
1217 end if;
1218
1219 if p_cparty_error <> TRUE and ARec_Interface.cparty_code is NOT NULL AND
1220 ARec_Interface.account_no_b is NOT NULL then
1221 G_cparty_account := get_cparty_account(ARec_Interface.cparty_code,
1222 ARec_Interface.currency_a,
1223 ARec_Interface.cparty_ref);--Bug 14515777
1224 if G_cparty_account is null then
1225 Log_Errors(ARec_Interface.external_deal_id,
1226 ARec_Interface.deal_type,
1227 'CpartyAccountNo','XTR_CPARTY_ACCT_REQD'); -- CE BANK MIGRATION
1228 p_error := TRUE;
1229 end if;
1230 end if;
1231
1232 if ARec_Interface.deal_linking_code is not null and
1233 not valid_deal_link_code(ARec_Interface.deal_linking_code) then
1234 Log_Errors(ARec_Interface.external_deal_id,
1235 ARec_Interface.deal_type,
1236 'DealLinkingCode','XTR_INV_LINKING_CODE');
1237 p_error := TRUE;
1238 end if;
1239
1240 --
1241 --validate Descriptive Flexfields
1242 --
1243 if not (xtr_import_deal_data.val_desc_flex(
1244 ARec_Interface,'XTR_EXP_DESC',v_err_segment)) then
1245 p_error := TRUE;
1246 if v_err_segment is not null and v_err_segment = 'Attribute16' then
1247 Log_Errors( ARec_Interface.external_deal_id,
1248 ARec_Interface.deal_type,
1249 v_err_segment,
1250 'XTR_INV_DESC_FLEX_API');
1251 elsif v_err_segment is not null and
1252 v_err_segment='AttributeCategory' then
1253 Log_Errors( ARec_Interface.external_deal_id,
1254 ARec_Interface.deal_type,
1255 v_err_segment,
1256 'XTR_INV_DESC_FLEX_CONTEXT');
1257 else
1258 Log_Errors( ARec_Interface.external_deal_id,
1259 ARec_Interface.deal_type,
1260 v_err_segment,
1261 'XTR_INV_DESC_FLEX');
1262 end if;
1263 end if;
1264
1265 IF xtr_risk_debug_pkg.g_Debug THEN
1266 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.VALIDATE_DEALS');
1267 END IF;
1268 END VALIDATE_DEALS;
1269
1270
1271 /*------------------------------------------------------------------------
1272 This procedure is used to transfer EXP deals for the open API.
1273 ------------------------------------------------------------------------*/
1274 function GET_TRANSACTION_NUMBER return number IS
1275 cursor trans_no is
1276 select XTR_EXPOSURE_TRANS_S.NEXTVAL
1277 from DUAL;
1278
1279 v_trans_no NUMBER;
1280
1281 BEGIN
1282 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.GET_TRANSACTION_NUMBER');
1283
1284 open trans_no;
1285 fetch trans_no into v_trans_no;
1286 close trans_no;
1287
1288 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.GET_TRANSACTION_NUMBER');
1289 return v_trans_no;
1290 END get_transaction_number;
1291
1292
1293
1294 /*------------------------------------------------------------------------
1295 This procedure is used to get thirdparty account.
1296 ------------------------------------------------------------------------*/
1297 function GET_CPARTY_ACCOUNT(p_cparty_code IN VARCHAR2,
1298 p_curr IN VARCHAR2,
1299 p_cparty_ref IN VARCHAR2) return varchar2 IS
1300
1301 cursor REF_ACC is
1302 select ACCOUNT_NUMBER
1303 from XTR_BANK_ACCOUNTS_V
1304 where PARTY_CODE = p_cparty_code
1305 and CURRENCY = p_curr
1306 and BANK_SHORT_CODE = p_cparty_ref;
1307
1308 v_cparty_account VARCHAR2(20);
1309
1310 BEGIN
1311 IF xtr_risk_debug_pkg.g_Debug THEN
1312 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.GET_CPARTY_ACCOUNT');
1313 END IF;
1314
1315 open REF_ACC;
1316 fetch REF_ACC into v_cparty_account;
1317 close REF_ACC;
1318
1319 IF xtr_risk_debug_pkg.g_Debug THEN
1320 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.GET_CPARTY_ACCOUNT');
1321 END IF;
1322 return v_cparty_account;
1323 END get_cparty_account;
1324
1325
1326
1327 /*------------------------------------------------------------------------
1328 This procedure is the table handler for XTR_EXPOSURE_TRANSACTIONS
1329 ------------------------------------------------------------------------*/
1330 procedure CREATE_EXP_DEAL(ARec_Exp IN XTR_EXPOSURE_TRANSACTIONS%rowtype) IS
1331
1332 cursor FIND_USER (p_fnd_user in number) is
1333 select dealer_code
1334 from xtr_dealer_codes_v
1335 where user_id = p_fnd_user;
1336
1337 l_user xtr_dealer_codes.dealer_code%TYPE;
1338 l_dual_user xtr_dealer_codes.dealer_code%TYPE;
1339 l_dual_date DATE;
1340
1341 BEGIN
1342
1343 IF xtr_risk_debug_pkg.g_Debug THEN
1344 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.CREATE_EXP_DEAL');
1345 END IF;
1346
1347 open FIND_USER(G_User_Id);
1348 fetch FIND_USER into l_user;
1349 close FIND_USER;
1350
1351 l_dual_user := ARec_Exp.DUAL_AUTHORISATION_BY;
1352 l_dual_date := ARec_Exp.DUAL_AUTHORISATION_ON;
1353 if ((l_dual_user is not null and l_dual_date is null) or
1354 (l_dual_user is null and l_dual_date is not null)) then
1355 if l_dual_date is null then
1356 l_dual_date := trunc(sysdate);
1357 elsif l_dual_user is null then
1358 l_dual_user := l_user;
1359 end if;
1360 end if;
1361
1362 INSERT INTO xtr_exposure_transactions (
1363 ACCOUNT_NO,
1364 ACTION_CODE,
1365 AMOUNT ,
1366 AMOUNT_HCE,
1367 AMOUNT_TYPE,
1368 ARCHIVE_BY,
1369 ARCHIVE_DATE,
1370 AUDIT_INDICATOR,
1371 AVG_RATE,
1372 BALANCE,
1373 BENEFICIARY_CODE,
1374 COMMENTS,
1375 COMPANY_CODE,
1376 CONTRA_NZD_AMOUNT,
1377 COVERED_BY_FX_CONTRACT ,
1378 CPARTY_CODE,
1379 CPARTY_ACCOUNT_NO, -- CE BANK MIGRATION
1380 CPARTY_REF,
1381 CREATED_BY,
1382 CREATED_ON,
1383 CURRENCY,
1384 DEAL_STATUS,
1385 DEAL_SUBTYPE,
1386 DEAL_TYPE,
1387 ESTIMATE_AMOUNT,
1388 ESTIMATE_DATE,
1389 EXPOSURE_TYPE,
1390 FIS_FOB,
1391 INTERMEDIARY_BANK_DETAILS,
1392 NZD_AMOUNT,
1393 PAYMENT_AMOUNT ,
1394 PAYMENT_STATUS ,
1395 PORTFOLIO_CODE ,
1396 PROFIT_LOSS,
1397 PURCHASING_MODULE,
1398 SELECT_ACTION,
1399 SELECT_REFERENCE,
1400 SETTLE_ACTION_REQD,
1401 STATUS_CODE,
1402 SUBSIDIARY_REF ,
1403 TAX_BROKERAGE_TYPE,
1404 THIRDPARTY_CODE,
1405 TRANSACTION_NUMBER,
1406 UPDATED_BY,
1407 UPDATED_ON,
1408 VALUE_DATE,
1409 WHOLESALE_REFERENCE,
1410 ATTRIBUTE_CATEGORY,
1411 ATTRIBUTE1,
1412 ATTRIBUTE2,
1413 ATTRIBUTE3,
1414 ATTRIBUTE4,
1415 ATTRIBUTE5,
1416 ATTRIBUTE6,
1417 ATTRIBUTE7,
1418 ATTRIBUTE8,
1419 ATTRIBUTE9,
1420 ATTRIBUTE10,
1421 ATTRIBUTE11,
1422 ATTRIBUTE12,
1423 ATTRIBUTE13,
1424 ATTRIBUTE14,
1425 ATTRIBUTE15,
1426 EXTERNAL_DEAL_ID,
1427 REQUEST_ID,
1428 PROGRAM_APPLICATION_ID ,
1429 PROGRAM_ID,
1430 PROGRAM_UPDATE_DATE,
1431 INTERNAL_COMMENTS,
1432 EXTERNAL_COMMENTS,
1433 DEAL_LINK_CODE,
1434 DUAL_AUTHORISATION_BY,
1435 DUAL_AUTHORISATION_ON,
1436 CASH_POSITION_EXPOSURE
1437 )
1438 VALUES (
1439 Arec_Exp.ACCOUNT_NO,
1440 Arec_Exp.ACTION_CODE,
1441 Arec_Exp.AMOUNT ,
1442 Arec_Exp.AMOUNT_HCE,
1443 Arec_Exp.AMOUNT_TYPE,
1444 Arec_Exp.ARCHIVE_BY,
1445 Arec_Exp.ARCHIVE_DATE,
1446 Arec_Exp.AUDIT_INDICATOR,
1447 Arec_Exp.AVG_RATE,
1448 Arec_Exp.BALANCE,
1449 Arec_Exp.BENEFICIARY_CODE,
1450 Arec_Exp.COMMENTS,
1451 Arec_Exp.COMPANY_CODE,
1452 Arec_Exp.CONTRA_NZD_AMOUNT,
1453 Arec_Exp.COVERED_BY_FX_CONTRACT ,
1454 Arec_Exp.CPARTY_CODE,
1455 Arec_Exp.CPARTY_ACCOUNT_NO,
1456 Arec_Exp.CPARTY_REF,
1457 Arec_Exp.CREATED_BY,
1458 Arec_Exp.CREATED_ON,
1459 Arec_Exp.CURRENCY,
1460 Arec_Exp.DEAL_STATUS,
1461 Arec_Exp.DEAL_SUBTYPE,
1462 Arec_Exp.DEAL_TYPE,
1463 Arec_Exp.ESTIMATE_AMOUNT,
1464 Arec_Exp.ESTIMATE_DATE,
1465 Arec_Exp.EXPOSURE_TYPE,
1466 Arec_Exp.FIS_FOB,
1467 Arec_Exp.INTERMEDIARY_BANK_DETAILS,
1468 Arec_Exp.NZD_AMOUNT,
1469 Arec_Exp.PAYMENT_AMOUNT ,
1470 Arec_Exp.PAYMENT_STATUS ,
1471 Arec_Exp.PORTFOLIO_CODE ,
1472 Arec_Exp.PROFIT_LOSS,
1473 Arec_Exp.PURCHASING_MODULE,
1474 Arec_Exp.SELECT_ACTION,
1475 Arec_Exp.SELECT_REFERENCE,
1476 Arec_Exp.SETTLE_ACTION_REQD,
1477 Arec_Exp.STATUS_CODE,
1478 Arec_Exp.SUBSIDIARY_REF ,
1479 Arec_Exp.TAX_BROKERAGE_TYPE,
1480 Arec_Exp.THIRDPARTY_CODE,
1481 Arec_Exp.TRANSACTION_NUMBER,
1482 Arec_Exp.UPDATED_BY,
1483 Arec_Exp.UPDATED_ON,
1484 Arec_Exp.VALUE_DATE,
1485 Arec_Exp.WHOLESALE_REFERENCE,
1486 Arec_Exp.ATTRIBUTE_CATEGORY,
1487 Arec_Exp.ATTRIBUTE1,
1488 Arec_Exp.ATTRIBUTE2,
1489 Arec_Exp.ATTRIBUTE3,
1490 Arec_Exp.ATTRIBUTE4,
1491 Arec_Exp.ATTRIBUTE5,
1492 Arec_Exp.ATTRIBUTE6,
1493 Arec_Exp.ATTRIBUTE7,
1494 Arec_Exp.ATTRIBUTE8,
1495 Arec_Exp.ATTRIBUTE9,
1496 Arec_Exp.ATTRIBUTE10,
1497 Arec_Exp.ATTRIBUTE11,
1498 Arec_Exp.ATTRIBUTE12,
1499 Arec_Exp.ATTRIBUTE13,
1500 Arec_Exp.ATTRIBUTE14,
1501 Arec_Exp.ATTRIBUTE15,
1502 Arec_Exp.EXTERNAL_DEAL_ID,
1503 Arec_Exp.REQUEST_ID,
1504 Arec_Exp.PROGRAM_APPLICATION_ID ,
1505 Arec_Exp.PROGRAM_ID,
1506 Arec_Exp.PROGRAM_UPDATE_DATE,
1507 Arec_Exp.INTERNAL_COMMENTS,
1508 Arec_Exp.EXTERNAL_COMMENTS,
1509 Arec_Exp.DEAL_LINK_CODE,
1510 l_dual_user, --Bug 2254853
1511 l_dual_date, --Bug 2254853
1512 Arec_Exp.CASH_POSITION_EXPOSURE
1513 );
1514
1515 if l_dual_user is not null then
1516 UPDATE xtr_confirmation_details
1517 SET confirmation_validated_by = l_dual_user,
1518 confirmation_validated_on = l_dual_date
1519 WHERE deal_type = 'EXP'
1520 AND transaction_no = Arec_Exp.TRANSACTION_NUMBER;
1521 end if;
1522
1523 IF xtr_risk_debug_pkg.g_Debug THEN
1524 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.CREATE_EXP_DEAL');
1525 END IF;
1526
1527 --The following exception is handled in Transfer_Deals_Protected
1528 --and should not be handled here because the potential deal would
1529 --otherwise be deleted from the list
1530
1531 --exception
1532 -- when OTHERS then
1533 -- UPDATE Xtr_Deals_Interface
1534 -- SET Load_Status_Code='ERROR'
1535 -- WHERE External_Deal_Id=Arec_Exp.External_Deal_Id;
1536
1537 END CREATE_EXP_DEAL;
1538
1539
1540
1541 /*------------------------------------------------------------------------
1542 This procedure is the table handler for XTR_DEAL_DATE_AMOUNTS_V
1543 ------------------------------------------------------------------------*/
1544 PROCEDURE INS_DEAL_DATE_AMOUNTS (ARec_Exp IN XTR_EXPOSURE_TRANSACTIONS%rowtype)
1545 IS
1546
1547 -- bug 1849281 proper dealer id should be inserted into dda
1548 cursor DEALER is
1549 select DEALER_CODE
1550 from XTR_DEALER_CODES_V
1551 where user_id = g_user_id;
1552 --
1553 v_dealer xtr_dealer_codes.dealer_code%TYPE;
1554 -- end bug 1849281
1555
1556 v_dual_user xtr_dealer_codes.dealer_code%TYPE;
1557 v_dual_date DATE;
1558
1559 v_comments VARCHAR2(255);
1560 v_portfolio_code VARCHAR2(7);
1561 v_balance_sheet_exposure VARCHAR2(1);
1562 v_cashflow_amount NUMBER;
1563 v_cparty_account_no VARCHAR2(20);
1564
1565 BEGIN
1566 IF xtr_risk_debug_pkg.g_Debug THEN
1567 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.INS_DEAL_DATE_AMOUNTS');
1568 END IF;
1569
1570 -- bug 1849281 select the dealer id
1571 Open DEALER;
1572 Fetch DEALER into v_dealer;
1573 Close DEALER;
1574 -- end bug 1849281
1575
1576 --The following logic differentiates some parameter inserted into DDA
1577 --depending on the purpose/caller indicated by g_source.
1578 IF g_source='TAX' THEN
1579 v_comments := AREC_EXP.COMMENTS;
1580 v_portfolio_code := 'NOTAPPL' ; -- bug 4910602
1581 v_balance_sheet_exposure := NULL;
1582 v_cashflow_amount := nvl(AREC_EXP.AMOUNT,AREC_EXP.ESTIMATE_AMOUNT);
1583 v_cparty_account_no := G_cparty_account;
1584 --Gross Compounded Interest Action requires cashflow=0, so that it won't be
1585 --shown in Settlement Form.
1586 ELSIF g_source= 'TAX_CP_G' THEN
1587 v_comments := AREC_EXP.COMMENTS;
1588 v_portfolio_code := 'NOTAPPL'; -- bug 4910602
1589 v_balance_sheet_exposure := NULL;
1590 v_cashflow_amount := 0;
1591 v_cparty_account_no := G_cparty_account;
1592 ELSE
1593 v_comments := NULL;
1594 v_portfolio_code := nvl(AREC_EXP.PORTFOLIO_CODE,'NOTAPPL');
1595 v_balance_sheet_exposure := 'N';
1596 v_cashflow_amount := nvl(AREC_EXP.AMOUNT,AREC_EXP.ESTIMATE_AMOUNT);
1597 IF AREC_EXP.ACTION_CODE='PAY' THEN
1598 v_cparty_account_no := G_cparty_account;
1599 ELSE
1600 v_cparty_account_no := '';
1601 END IF;
1602 END IF;
1603
1604 --Bug 2254853
1605 v_dual_user := ARec_Exp.DUAL_AUTHORISATION_BY;
1606 v_dual_date := ARec_Exp.DUAL_AUTHORISATION_ON;
1607 if ((v_dual_user is not null and v_dual_date is null) or
1608 (v_dual_user is null and v_dual_date is not null)) then
1609 if v_dual_date is null then
1610 v_dual_date := trunc(sysdate);
1611 elsif v_dual_user is null then
1612 v_dual_user := v_dealer;
1613 end if;
1614 end if;
1615 --Bug 2254853
1616
1617 --CGC$USER_1=v_dealer and CGC$SYSDATE_1=trunc(sysdate)
1618 --from KEY_STARTUP procedure.
1619 insert into XTR_DEAL_DATE_AMOUNTS_V
1620 (deal_type,amount_type,date_type,product_type,
1621 deal_number,transaction_number,transaction_date,
1622 currency,amount,hce_amount,amount_date,
1623 cashflow_amount,company_code,account_no,action_code,
1624 cparty_account_no,cparty_code,status_code,settle,
1625 exp_settle_reqd,deal_subtype,portfolio_code,balance_sheet_exposure,
1626 dual_authorisation_by, dual_authorisation_on,
1627 dealer_code, comments)
1628 -- bug 1849281
1629 values ('EXP','AMOUNT','VALUE',ARec_Exp.EXPOSURE_TYPE,
1630 0,ARec_Exp.TRANSACTION_NUMBER,
1631 nvl(ARec_Exp.VALUE_DATE,AREC_EXP.ESTIMATE_DATE),AREC_EXP.CURRENCY,
1632 nvl(nvl(AREC_EXP.AMOUNT,AREC_EXP.ESTIMATE_AMOUNT),0),
1633 nvl(AREC_EXP.AMOUNT_HCE,nvl(nvl(AREC_EXP.AMOUNT,
1634 AREC_EXP.ESTIMATE_AMOUNT),0)),
1635 nvl(AREC_EXP.VALUE_DATE,AREC_EXP.ESTIMATE_DATE),
1636 decode(AREC_EXP.ACTION_CODE,'PAY',(-1),1)*v_cashflow_amount,
1637 AREC_EXP.COMPANY_CODE,AREC_EXP.ACCOUNT_NO,AREC_EXP.ACTION_CODE,
1638 v_cparty_account_no,
1639 AREC_EXP.THIRDPARTY_CODE,AREC_EXP.STATUS_CODE,'N',
1640 nvl(AREC_EXP.SETTLE_ACTION_REQD,'N'),AREC_EXP.DEAL_SUBTYPE,
1641 v_portfolio_code,v_balance_sheet_exposure,
1642 v_dual_user, v_dual_date, --Bug 2254853
1643 v_dealer, v_comments);
1644 -- bug 1849281
1645 --
1646 --No need for 2nd row insertion for TAX.
1647 IF g_source IS NULL OR g_source IN ('CONC','FORM') THEN
1648 /*====================*/
1649 /* Enhancement to DDA */
1650 /*====================*/
1651 insert into XTR_DEAL_DATE_AMOUNTS_V
1652 (deal_type,amount_type,date_type,product_type,
1653 deal_number,transaction_number,transaction_date,
1654 currency,amount,hce_amount,amount_date,
1655 cashflow_amount,company_code,account_no,action_code,
1656 cparty_account_no,cparty_code,status_code,settle,
1657 exp_settle_reqd,deal_subtype,portfolio_code,balance_sheet_exposure,
1658 dual_authorisation_by, dual_authorisation_on, dealer_code, comments)
1659 -- bug 1849281
1660 values ('EXP','N/A','DEALT',ARec_Exp.EXPOSURE_TYPE,
1661 0,AREC_EXP.TRANSACTION_NUMBER,
1662 trunc(SYSDATE),AREC_EXP.CURRENCY,0,
1663 0,trunc(SYSDATE),
1664 0,AREC_EXP.COMPANY_CODE,NULL,NULL,NULL,
1665 AREC_EXP.THIRDPARTY_CODE,AREC_EXP.STATUS_CODE,'N',
1666 nvl(AREC_EXP.SETTLE_ACTION_REQD,'N'),AREC_EXP.DEAL_SUBTYPE,
1667 v_portfolio_code,v_balance_sheet_exposure,
1668 v_dual_user, v_dual_date, --Bug 2254853
1669 v_dealer, v_comments);
1670 -- bug 1849281
1671 END IF;
1672
1673 IF xtr_risk_debug_pkg.g_Debug THEN
1674 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.INS_DEAL_DATE_AMOUNTS');
1675 END IF;
1676 END INS_DEAL_DATE_AMOUNTS;
1677
1678
1679 /*------------------------------------------------------------------------
1680 This procedure is used to transfer EXP deals for the open API from
1681 the concurrent program.
1682 p_source = CONC (if called from CONC Program for Deal Import)
1683 Stub for backwards compatibility
1684 ------------------------------------------------------------------------*/
1685 procedure TRANSFER_EXP_DEALS( ARec_Interface IN XTR_DEALS_INTERFACE%rowtype,
1686 p_source IN VARCHAR2,
1687 user_error OUT NOCOPY BOOLEAN,
1688 mandatory_error OUT NOCOPY BOOLEAN,
1689 validation_error OUT NOCOPY BOOLEAN,
1690 limit_error OUT NOCOPY BOOLEAN) IS
1691 v_dummy NUMBER;
1692 BEGIN
1693 TRANSFER_EXP_DEALS(ARec_Interface,p_source,user_error,mandatory_error,validation_error,limit_error,v_dummy);
1694 END TRANSFER_EXP_DEALS;
1695
1696
1697
1698 /*------------------------------------------------------------------------
1699 This procedure is used to transfer EXP deals for the open API from
1700 the concurrent program.
1701 p_source = CONC (if called from CONC Program for Deal Import)
1702 ------------------------------------------------------------------------*/
1703 procedure TRANSFER_EXP_DEALS( ARec_Interface IN XTR_DEALS_INTERFACE%rowtype,
1704 p_source IN VARCHAR2,
1705 user_error OUT NOCOPY BOOLEAN,
1706 mandatory_error OUT NOCOPY BOOLEAN,
1707 validation_error OUT NOCOPY BOOLEAN,
1708 limit_error OUT NOCOPY BOOLEAN,
1709 deal_num OUT NOCOPY NUMBER) IS
1710
1711 CURSOR FIND_USER (p_fnd_user in number) is
1712 select dealer_code
1713 from xtr_dealer_codes_v
1714 where user_id = p_fnd_user;
1715
1716 BEGIN
1717 IF xtr_risk_debug_pkg.g_Debug THEN
1718 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.TRANSFER_EXP_DEALS');
1719 END IF;
1720 --
1721 --Initialize variables
1722 --
1723 user_error := FALSE;
1724 mandatory_error := FALSE;
1725 validation_error := FALSE;
1726 limit_error := FALSE; --no limit for EXPOSURE
1727
1728 g_source := p_source;
1729 g_curr_date := SYSDATE;
1730 g_user_id := FND_GLOBAL.USER_ID;
1731 g_cparty_account := null;
1732 OPEN find_user(g_user_id);
1733 FETCH find_user INTO g_user;
1734 CLOSE find_user;
1735
1736 --
1737 --Purge the related data from the error table
1738 --
1739 if g_source is null then
1740 delete from xtr_interface_errors
1741 where external_deal_id = ARec_Interface.external_deal_id
1742 and deal_type = ARec_Interface.deal_type;
1743 end if;
1744
1745 --
1746 --Check if the user has permissions to transfer the deal
1747 --
1748 xtr_import_deal_data.CHECK_USER_AUTH(ARec_Interface.external_deal_id,
1749 ARec_Interface.deal_type,
1750 ARec_Interface.company_code,
1751 user_error);
1752
1753 IF xtr_risk_debug_pkg.g_Debug THEN
1754 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'user_error',user_error);
1755 END IF;
1756
1757 if (user_error <> TRUE) then
1758 --
1762 IF xtr_risk_debug_pkg.g_Debug THEN
1759 --The following code does mandatory field validation specific to the deal
1760 --
1761 CHECK_MANDATORY_FIELDS(ARec_Interface,mandatory_error);
1763 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'mandatory_error',mandatory_error);
1764 END IF;
1765
1766 if (mandatory_error <> TRUE) then
1767 --
1768 -- The following code performs the business logic validation
1769 --
1770 VALIDATE_DEALS(ARec_Interface, validation_error);
1771 IF xtr_risk_debug_pkg.g_Debug THEN
1772 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'validation_error',validation_error);
1773 END IF;
1774
1775 if (validation_error <> TRUE) then
1776 --
1777 -- Copy to the temp. storage that will be used for inserting
1778 -- into the XTR_EXPOSURE_TRANSACTIONS table
1779 --
1780 COPY_FROM_INTERFACE_TO_EXP(ARec_Interface, validation_error);
1781 IF xtr_risk_debug_pkg.g_Debug THEN
1782 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'validation_error from COPY',validation_error);
1783 END IF;
1784
1785 end if; --validation error
1786 end if; --mandatory_error
1787 end if; --user_error
1788
1789 --
1790 --If the process passed all the previous validation, it would be
1791 --considered a valid deal entry.
1792 --
1793 if user_error <> TRUE and mandatory_error <> TRUE and
1794 limit_error <> TRUE and validation_error <> TRUE then
1795 --
1796 --Insert deal
1797 --
1798 CREATE_EXP_DEAL(g_main_rec);
1799 IF xtr_risk_debug_pkg.g_Debug THEN
1800 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'After inserting to XTR_EXPOSURE_TRANSACTIONS');
1801 END IF;
1802 --
1803 --Also insert to XTR_DEAL_DATE_AMOUNTS_V
1804 --
1805 INS_DEAL_DATE_AMOUNTS(g_main_rec);
1806
1807 deal_num:=g_main_rec.transaction_number;
1808 IF xtr_risk_debug_pkg.g_Debug THEN
1809 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'After inserting to XTR_DEAL_DATE_AMOUNTS');
1810 END IF;
1811 COMMIT;
1812 --
1813 --Since the insert is done, we can now delete the rows from the
1814 --interface table.
1815 --
1816 if G_Source is null then
1817 delete from xtr_deals_interface
1818 where external_deal_id = ARec_Interface.external_deal_id
1819 and deal_type = ARec_Interface.deal_type;
1820 end if;
1821
1822 else /* if any other errors */
1823 /*---------------------------------------------*/
1824 /* Deal interface has error. Do not import. */
1825 /*---------------------------------------------*/
1826 if G_Source is null then
1827 update xtr_deals_interface
1828 set load_status_code = 'ERROR',
1829 last_update_date = G_curr_date,
1830 last_Updated_by = G_user_id
1831 where external_deal_id = ARec_Interface.external_deal_id
1832 and deal_type = ARec_Interface.deal_type;
1833 end if;
1834 end if;
1835
1836 COMMIT;
1837 IF xtr_risk_debug_pkg.g_Debug THEN
1838 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.TRANSFER_EXP_DEALS');
1839 END IF;
1840
1841 END TRANSFER_EXP_DEALS;
1842
1843
1844
1845 /*------------------------------------------------------------------------
1846 This procedure is used to transfer EXP deals for the open API from
1847 the FORM. There are no business logic validations and no mandatory fields
1848 validations performed.
1849
1850 p_source = TAX (if called from TAX API)
1851 , FORM (if called from FORM for general purpose)
1852 , TAX_CP_G (if called from TAX API with interest action Compounded
1853 gross)
1854 ------------------------------------------------------------------------*/
1855 procedure TRANSFER_EXP_DEALS(
1856 ARec IN OUT NOCOPY XTR_EXPOSURE_TRANSACTIONS%rowtype,
1857 p_source IN VARCHAR2,
1858 user_error OUT NOCOPY BOOLEAN,
1859 mandatory_error OUT NOCOPY BOOLEAN,
1860 validation_error OUT NOCOPY BOOLEAN,
1861 limit_error OUT NOCOPY BOOLEAN) IS
1862
1863 CURSOR FIND_USER (p_fnd_user in number) is
1864 select dealer_code
1865 from xtr_dealer_codes_v
1866 where user_id = p_fnd_user;
1867
1868 BEGIN
1869 IF xtr_risk_debug_pkg.g_Debug THEN
1870 xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.TRANSFER_EXP_DEALS');
1871 END IF;
1872
1873 user_error := FALSE;
1874 mandatory_error := FALSE;
1875 validation_error := FALSE;
1876 limit_error := FALSE; --no limit for EXPOSURE
1877
1878 g_source := p_source;
1879 --Cash Positioning form pass their own created_on
1880 IF Arec.created_on IS NOT NULL THEN
1881 g_curr_date := ARec.created_on;
1882 ELSE
1883 g_curr_date := SYSDATE;
1884 END IF;
1885 g_user_id := FND_GLOBAL.USER_ID;
1886 g_cparty_account := null;
1887 --Cash Positioning form pass their own created_by
1888 IF xtr_risk_debug_pkg.g_Debug THEN
1889 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'Arec.created_by', Arec.created_by);
1890 END IF;
1891 IF Arec.created_by IS NOT NULL THEN
1892 g_user := ARec.created_by;
1893 ELSE
1894 OPEN find_user(g_user_id);
1895 FETCH find_user INTO g_user;
1896 CLOSE find_user;
1897 END IF;
1898 IF xtr_risk_debug_pkg.g_Debug THEN
1899 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'g_user', g_user);
1900 END IF;
1901
1902 COPY_TO_EXP(ARec);
1903 --
1904 --Insert deal
1905 --
1906 CREATE_EXP_DEAL(g_main_rec);
1907 IF xtr_risk_debug_pkg.g_Debug THEN
1908 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'After inserting to XTR_EXPOSURE_TRANSACTIONS');
1909 END IF;
1910 --
1911 --Also insert to XTR_DEAL_DATE_AMOUNTS_V
1912 --
1913 INS_DEAL_DATE_AMOUNTS(g_main_rec);
1914 IF xtr_risk_debug_pkg.g_Debug THEN
1915 xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'After inserting to XTR_DEAL_DATE_AMOUNTS');
1916 xtr_risk_debug_pkg.dpop('XTR_EXP_TRANSFERS.TRANSFER_EXP_DEALS');
1917 END IF;
1918 END TRANSFER_EXP_DEALS;
1919
1920
1921
1922 END;
[Home] [Help]