[Home] [Help]
PACKAGE BODY: APPS.XTR_FX_TRANSFERS_PKG
Source
1 PACKAGE BODY XTR_FX_TRANSFERS_PKG AS
2 /* $Header: xtrimfxb.pls 120.5 2005/06/29 09:22:27 badiredd ship $*/
3
4 /* Stub for backwards compatability */
5 /*-------------------------------------------------------------------------------------*/
6 PROCEDURE TRANSFER_FX_DEALS(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
7 user_error OUT NOCOPY BOOLEAN,
8 mandatory_error OUT NOCOPY BOOLEAN,
9 validation_error OUT NOCOPY BOOLEAN,
10 limit_error OUT NOCOPY BOOLEAN) is
11 /*-------------------------------------------------------------------------------------*/
12 v_dummy NUMBER;
13 BEGIN
14 TRANSFER_FX_DEALS(ARec_Interface,user_error,mandatory_error,validation_error,limit_error,v_dummy);
15 END TRANSFER_FX_DEALS;
16
17 /*-------------------------------------------------------------------------------------*/
18 PROCEDURE TRANSFER_FX_DEALS(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
19 user_error OUT NOCOPY BOOLEAN,
20 mandatory_error OUT NOCOPY BOOLEAN,
21 validation_error OUT NOCOPY BOOLEAN,
22 limit_error OUT NOCOPY BOOLEAN,
23 deal_num OUT NOCOPY NUMBER) is
24 /*-------------------------------------------------------------------------------------*/
25
26 l_limit_amount NUMBER;
27 v_limit_log_return NUMBER;
28
29 cursor c_deal_no is
30 select xtr_deals_s.nextval
31 from dual;
32
33 BEGIN
34
35 -------------------------
36 -- Initialise Variables
37 -------------------------
38 limit_error := FALSE;
39 G_User_Id := fnd_global.user_id;
40 g_currency_first := null;
41 g_currency_second := null;
42 G_Pricing_Model := null;
43 Select Trunc(sysdate) Into G_Curr_Date From Dual;
44
45 --------------------------------------------------------------------------------------------------------
46 --* Perform the following to purge all the related data in the error table before processing the record
47 --------------------------------------------------------------------------------------------------------
48 delete from xtr_interface_errors
49 where external_deal_id = ARec_Interface.external_deal_id
50 and deal_type = ARec_Interface.deal_type;
51
52 ----------------------------------------------------------------------------------------------------
53 --* The following code checks if user has permissions to transfer the deal (company authorization)
54 ----------------------------------------------------------------------------------------------------
55 XTR_IMPORT_DEAL_DATA.CHECK_USER_AUTH(ARec_Interface.external_deal_id,
56 ARec_Interface.deal_type,
57 ARec_Interface.company_code,
58 user_error);
59
60
61 if (user_error <> TRUE) then
62
63 --------------------------------------------------------------------------------
64 --* The following code does mandatory field validation specific to the FX deals
65 --------------------------------------------------------------------------------
66 CHECK_MANDATORY_FIELDS(ARec_Interface, mandatory_error);
67
68
69 if (mandatory_error <> TRUE) then
70
71 --------------------------------------------------------------------------------------
72 --* The following code performs the business logic validation specific to the FX deals
73 --------------------------------------------------------------------------------------
74 VALIDATE_DEALS(ARec_Interface, validation_error);
75
76 if (validation_error <> TRUE) then
77
78 ------------------------------------------------------------------
79 --* Perform limit checks
80 ------------------------------------------------------------------
81
82 if g_currency_first = G_Fx_Main_Rec.currency_buy then
83 l_limit_amount := G_Fx_Main_Rec.buy_amount;
84 else
85 l_limit_amount := G_Fx_Main_Rec.sell_amount;
86 end if;
87
88
89 v_limit_log_return := XTR_LIMITS_P.LOG_FULL_LIMITS_CHECK (
90 G_Fx_Main_Rec.deal_no,
91 1,
92 G_Fx_Main_Rec.company_code,
93 G_Fx_Main_Rec.deal_type,
94 G_Fx_Main_Rec.deal_subtype,
95 G_Fx_Main_Rec.cparty_code,
96 G_Fx_Main_Rec.product_type,
97 G_Fx_Main_Rec.limit_code,
98 G_Fx_Main_Rec.cparty_code, -- limit_party
99 G_Fx_Main_Rec.value_date, -- amount_date
100 l_limit_amount,
101 G_Fx_Main_Rec.dealer_code,
102 g_currency_first,
103 g_currency_second );
104
105 If Nvl(ARec_Interface.override_limit,'N') = 'N' and v_limit_log_return <> 0 then
106 xtr_import_deal_data.log_interface_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
107 'OverrideLimit','XTR_LIMIT_EXCEEDED');
108 limit_error := TRUE;
109 else
110 limit_error := FALSE;
111
112 end if; /* If Limit needs to be checked */
113
114 end if; /* Validating various fields */
115
116 end if; /* Checking Mandatory values */
117
118 end if; /* Checking User Auth */
119
120 /*----------------------------------------------------------------------------------------------*/
121 /* If the process passed all the previous validation, it would be considered a valid deal entry */
122 /*----------------------------------------------------------------------------------------------*/
123 if user_error <> TRUE and mandatory_error <> TRUE and
124 limit_error <> TRUE and validation_error <> TRUE then
125
126 open c_deal_no;
127 fetch c_deal_no into deal_num;
128 close c_deal_no;
129
130 XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(deal_num,
131 1,
132 Fnd_Global.User_Id,
133 v_limit_log_return);
134
135 /*----------------------------------------------------*/
136 /* Call the insert procedure to insert into xtr_deals */
137 /*----------------------------------------------------*/
138 CREATE_FX_DEAL(G_Fx_Main_Rec, deal_num);
139
140 /*---------------------------------------------------------------------------------*/
141 /* Since the insert is done, we can now delete the rows from the interface table. */
142 /*---------------------------------------------------------------------------------*/
143 delete from xtr_deals_interface
144 where external_deal_id = ARec_Interface.external_deal_id
145 and deal_type = ARec_Interface.deal_type;
146
147 else
148
149 update xtr_deals_interface
150 set load_status_code = 'ERROR',
151 last_update_date = G_Curr_Date,
152 Last_Updated_by = g_user_id
153 where external_deal_id = ARec_Interface.external_deal_id
154 and deal_type = ARec_Interface.deal_type;
155
156 end if;
157
158 end TRANSFER_FX_DEALS;
159
160
161 /*------------------------------------------------------------*/
162 /* The following code implements the CHECK_USER_AUTH process */
163 /*------------------------------------------------------------*/
164 /* Moved to xtrimddb.pls
165 PROCEDURE CHECK_USER_AUTH(p_external_deal_id IN VARCHAR2,
166 p_deal_type IN VARCHAR2,
167 p_company_code IN VARCHAR2,
168 error OUT NOCOPY BOOLEAN) is
169 l_dummy varchar2(1);
170
171 BEGIN
172
173 error := FALSE;
174
175 BEGIN
176 select 'Y'
177 into l_dummy
178 from xtr_parties_v
179 where party_type = 'C'
180 and party_code = p_company_code
181 and rownum = 1;
182
183
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 error := TRUE;
187 xtr_import_deal_data.log_interface_errors( p_external_deal_id ,p_deal_type,'CompanyCode','XTR_INV_COMP_CODE');
188 END;
189
190 END CHECK_USER_AUTH;
191 */
192
193
194 /*------------------------------------------------------------------------------*/
195 /* The following code implements the CHECK_MANDATORY_FIELDS process */
196 /*------------------------------------------------------------------------------*/
197 PROCEDURE CHECK_MANDATORY_FIELDS(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
198 error OUT NOCOPY BOOLEAN) is
199 /*------------------------------------------------------------------------------*/
200 BEGIN
201
202 error := FALSE; /* Defaulting it to No errors */
203
204 if ARec_Interface.dealer_code is null then
205 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
206 'DealerCode','XTR_MANDATORY');
207 error := TRUE;
208 end if;
209
210 if ARec_Interface.date_a is null then
211 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
212 'DateA','XTR_MANDATORY');
213 error := TRUE;
214 end if;
215
216 if ARec_Interface.cparty_code is null then
217 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
218 'CpartyCode','XTR_MANDATORY');
219 error := TRUE;
220 end if;
221
222 if ARec_Interface.deal_subtype is null then
223 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
224 'DealSubtype','XTR_MANDATORY');
225 error := TRUE;
226 end if;
227
228 if ARec_Interface.product_type is null then
229 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
230 'ProductType','XTR_MANDATORY');
231 error := TRUE;
232 end if;
233
234 if ARec_Interface.date_b is null then
235 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
236 'DateB','XTR_MANDATORY');
237 error := TRUE;
238 end if;
239
240 if ARec_Interface.currency_a is null then
241 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
242 'CurrencyA','XTR_MANDATORY');
243 error := TRUE;
244 end if;
245
246 if ARec_Interface.currency_b is null then
247 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
248 'CurrencyB','XTR_MANDATORY');
249 error := TRUE;
250 end if;
251
252 if ARec_Interface.amount_a is null then
253 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
254 'AmountA','XTR_MANDATORY');
255 error := TRUE;
256 end if;
257
258 if ARec_Interface.amount_b is null then
259 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
260 'AmountB','XTR_MANDATORY');
261 error := TRUE;
262 end if;
263
264 if ARec_Interface.rate_a is null then
265 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
266 'RateA','XTR_MANDATORY');
267 error := TRUE;
268 end if;
269
270 if ARec_Interface.rate_b is null then
271 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
272 'RateB','XTR_MANDATORY');
273 error := TRUE;
274 end if;
275
276 if ARec_Interface.account_no_a is null then
277 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
278 'AccountNoA','XTR_MANDATORY');
279 error := TRUE;
280 end if;
281
282 if ARec_Interface.account_no_b is null then
283 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
284 'AccountNoB','XTR_MANDATORY');
285 error := TRUE;
286 end if;
287
288 if ARec_Interface.pricing_model is null then
289 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
290 'PricingModel','XTR_MANDATORY');
291 error := TRUE;
292 end if;
293
294 if ARec_Interface.brokerage_code is null and
295 (ARec_Interface.rate_c is not null or ARec_Interface.amount_c is not null) then
296 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
297 'BrokerageCode','XTR_MANDATORY_BROKERAGE');
298 error := TRUE;
299 end if;
300
301
302 END CHECK_MANDATORY_FIELDS;
303
304
305
306 /*------------------------------------------------------------------------*/
307 /* The following code implements the VALIDATE_DEALS process */
308 /*------------------------------------------------------------------------*/
309 PROCEDURE VALIDATE_DEALS(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
310 error OUT NOCOPY BOOLEAN) is
311 /*------------------------------------------------------------------------*/
312 rate_error boolean := FALSE;
313 validity_error boolean := FALSE;
314 BEGIN
315
316 CHECK_VALIDITY(ARec_Interface, validity_error);
317
318 IF validity_error <> TRUE then
319
320 COPY_FROM_INTERFACE_TO_FX(ARec_Interface);
321
322 CALC_RATES(ARec_Interface, rate_error);
323
324 if (rate_error <> TRUE) then
325 error := FALSE;
326 else
327 error := TRUE;
328 end if;
329 ELSE
330 error := TRUE;
331 END IF;
332
333 END VALIDATE_DEALS;
334
335
336
337 /*-------------------------------------------------------------------------*/
338 PROCEDURE CHECK_VALIDITY(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
339 error OUT NOCOPY BOOLEAN) is
340 /*-------------------------------------------------------------------------*/
341
342 l_error number := 0;
343 l_err_segment varchar2(30);
344 l_err_cparty boolean := FALSE;
345 l_err_deal_subtype boolean := FALSE;
346 l_err_deal_date boolean := FALSE;
347 l_err_currency_b boolean := FALSE;
348 l_err_currency_a boolean := FALSE;
349 l_err_brokerage_code boolean := FALSE;
350
351 BEGIN
352
353 /* This procedure will include all the column validations */
354
355 if not ( val_cparty_code(ARec_Interface.company_code, ARec_Interface.cparty_code)) then
356 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
357 ARec_Interface.Deal_Type,
358 'CpartyCode',
359 'XTR_INV_CPARTY_CODE');
360 l_error := l_error +1;
361 l_err_cparty := TRUE;
362 end if;
363
364 if l_err_cparty <> TRUE then
365
366 if not ( val_portfolio_code(ARec_Interface.company_code,
367 ARec_Interface.cparty_code,
368 ARec_Interface.portfolio_code)) then
369 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
370 ARec_Interface.Deal_Type,
371 'PortfolioCode',
372 'XTR_INV_PORT_CODE');
373 l_error := l_error +1;
374 end if;
375
376 if not ( val_limit_code(ARec_Interface.company_code,
377 ARec_Interface.cparty_code,
378 ARec_Interface.limit_code)) then
379 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
380 ARec_Interface.Deal_Type,
381 'LimitCode',
382 'XTR_INV_LIMIT_CODE');
383 l_error := l_error +1;
384 end if;
385 end if;
386
387 if not (val_currencies(ARec_Interface.currency_a)) then
388 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
389 ARec_Interface.Deal_Type,
390 'CurrencyA',
391 'XTR_INV_BUY_CURR');
392 l_error := l_error +1;
393 l_err_currency_a := TRUE;
394 end if;
395
396 if not (val_currencies(ARec_Interface.currency_b)) then
397 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
398 ARec_Interface.Deal_Type,
399 'CurrencyB',
400 'XTR_INV_SELL_CURR');
401 l_error := l_error +1;
402 l_err_currency_b := TRUE;
403 end if;
404
405 if not ( val_deal_subtype(ARec_Interface.deal_subtype,G_Fx_Deal_Type)) then
406 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
407 ARec_Interface.Deal_Type,
408 'DealSubtype',
409 'XTR_INV_DEAL_SUBTYPE');
410 l_error := l_error +1;
411 l_err_deal_subtype := TRUE;
412 end if;
413
414 if l_err_deal_subtype <> TRUE then
415 if not ( val_product_type(ARec_Interface.product_type,ARec_Interface.deal_subtype,G_Fx_Deal_type)) then
416 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
417 ARec_Interface.Deal_Type,
418 'ProductType',
419 'XTR_INV_PRODUCT_TYPE');
420 l_error := l_error +1;
421 end if;
422 end if;
423
424 if l_err_currency_a <> TRUE and l_err_currency_b <> TRUE then
425 if not (val_buy_sell_curr_comb(ARec_Interface.currency_a, ARec_Interface.currency_b)) then
426 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
427 ARec_Interface.Deal_Type,
428 'CurrencyA',
429 'XTR_INV_BUY_SELL_CURR_COMB');
430
431 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
432 ARec_Interface.Deal_Type,
433 'CurrencyB',
434 'XTR_INV_BUY_SELL_CURR_COMB');
435 l_error := l_error +1;
436 end if;
437 end if;
438
439 if not ( val_brokerage_code(ARec_Interface.brokerage_code)) then
440 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
441 ARec_Interface.Deal_Type,
442 'BrokerageCode',
443 'XTR_INV_BROKERAGE_CODE');
444 l_error := l_error +1;
445 l_err_brokerage_code := TRUE;
446 end if;
447
448 if l_err_currency_a <> TRUE then
449 if not ( val_comp_acct_no(ARec_Interface.company_code,
450 ARec_Interface.currency_a,
451 ARec_Interface.account_no_a)) then
452 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
453 ARec_Interface.Deal_Type,
454 'AccountNoA',
455 'XTR_INV_BUY_ACCT_NO');
456 l_error := l_error +1;
457 end if;
458 end if;
459
460 if l_err_currency_b <> TRUE then
461 if not ( val_comp_acct_no(ARec_Interface.company_code,
462 ARec_Interface.currency_b,
463 ARec_Interface.account_no_b)) then
464 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
465 ARec_Interface.Deal_Type,
466 'AccountNoB',
467 'XTR_INV_SELL_ACCT_NO');
468 l_error := l_error +1;
469 end if;
470 end if;
471
472 if l_err_cparty <> TRUE and l_err_currency_b <> TRUE then
473 if not ( val_cparty_ref(ARec_Interface.cparty_account_no,
474 ARec_Interface.cparty_ref,
475 ARec_Interface.cparty_code,
476 ARec_Interface.currency_b)) then
477 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
478 ARec_Interface.Deal_Type,
479 'CpartyAccountNo', -- CE BANK MIGRATION
480 'XTR_INV_CPARTY_REF');
481 l_error := l_error +1;
482 end if;
483 end if;
484
485 if not ( val_dealer_code(ARec_Interface.dealer_code)) then
486 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
487 ARec_Interface.Deal_Type,
488 'DealerCode',
489 'XTR_INV_DEALER_CODE');
490 l_error := l_error +1;
491 end if;
492
493 if ARec_Interface.dual_authorization_by is not null and
494 not ( val_dealer_code(ARec_Interface.dual_authorization_by)) then
495 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
496 ARec_Interface.Deal_Type,
497 'DualAuthorizationBy',
498 'XTR_INV_DUAL_AUTH_BY');
499 l_error := l_error +1;
500 end if;
501
502 if not ( val_deal_date(ARec_Interface.date_a)) then
503 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
504 ARec_Interface.Deal_Type,
505 'DateA',
506 'XTR_INV_DEAL_DATE');
507 l_error := l_error +1;
508 l_err_deal_date := TRUE;
509 end if;
510
511 if not ( val_client_code(ARec_Interface.client_code)) then
512 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
513 ARec_Interface.Deal_Type,
514 'ClientCode',
515 'XTR_INV_CLIENT_CODE');
516 l_error := l_error +1;
517 end if;
518
519 if l_err_deal_date <> TRUE then
520 if not ( val_value_date(ARec_Interface.date_a,
521 ARec_Interface.date_b)) then
522 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
523 ARec_Interface.Deal_Type,
524 'DateB',
525 'XTR_INV_VALUE_DATE');
526 l_error := l_error +1;
527 end if;
528 end if;
529
530 if not ( val_deal_linking_code(ARec_Interface.deal_linking_code)) then
531 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
532 ARec_Interface.Deal_Type,
533 'DealLinkingCode',
534 'XTR_INV_LINKING_CODE');
535 l_error := l_error +1;
536 end if;
537
538
539 if not ( val_pricing_model(ARec_Interface.pricing_model)) then
540 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
541 ARec_Interface.Deal_Type,
542 'PricingModel',
543 'XTR_INV_PRICING_MODEL');
544 l_error := l_error +1;
545 end if;
546
547 if not ( val_market_data_set(ARec_Interface.market_data_set)) then
548 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
549 ARec_Interface.Deal_Type,
550 'MarketDataSet',
551 'XTR_INV_MKT_DATA_SET');
552 l_error := l_error +1;
553 end if;
554
555 if l_err_brokerage_code <> TRUE and l_err_currency_a <> TRUE and l_err_currency_b <> TRUE then
556
557 if not ( val_brokerage_currency(ARec_Interface.brokerage_currency,
558 G_Fx_Deal_Type,
559 ARec_Interface.currency_a,
560 ARec_Interface.currency_b,
561 ARec_Interface.brokerage_code)) then
562 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
563 ARec_Interface.Deal_Type,
564 'BrokerageCurrency',
565 'XTR_INV_BROK_CURRENCY');
566 l_error := l_error +1;
567 end if;
568 end if;
569
570
571 /*-------------------------------------------------------------------------------*/
572 /* Flexfields Validation */
573 /*-------------------------------------------------------------------------------*/
574
575 if not ( xtr_import_deal_data.val_desc_flex(ARec_Interface,'XTR_DEALS_DESC_FLEX',l_err_segment)) then
576 l_error := l_error +1;
577 if l_err_segment is not null and l_err_segment = 'Attribute16' then
578 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
579 ARec_Interface.Deal_Type,
580 l_err_segment,
581 'XTR_INV_DESC_FLEX_API');
582 elsif l_err_segment is not null and l_err_segment = 'AttributeCategory' then
583 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
584 ARec_Interface.Deal_Type,
585 l_err_segment,
586 'XTR_INV_DESC_FLEX_CONTEXT');
587 else
588 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
589 ARec_Interface.Deal_Type,
590 l_err_segment,
591 'XTR_INV_DESC_FLEX');
592 end if;
593 end if;
594
595 --rvallams begin
596
597
598 if (ARec_Interface.rate_a <= 0) then
599 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
600 ARec_Interface.Deal_Type,
601 'RateA',
602 'XTR_2180');
603 l_error := l_error +1;
604 end if;
605
606 if (ARec_Interface.rate_b <= 0) then
607 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
608 ARec_Interface.Deal_Type,
609 'RateB',
610 'XTR_2180');
611 l_error := l_error +1;
612 end if;
613
614 --rvallams end
615
616 if l_error > 0 then
617 error := TRUE;
618 else
619 error := FALSE;
620 end if;
621
622 END CHECK_VALIDITY;
623
624 /*--------------------------------------------------------------------*/
625 FUNCTION val_deal_date (p_date_a IN date) return BOOLEAN is
626 /*--------------------------------------------------------------------*/
627 l_date date;
628 BEGIN
629
630 IF ( p_date_a > g_curr_date ) THEN
631 return(FALSE);
632 END IF;
633 return TRUE;
634 END val_deal_date;
635
636 /*--------------------------------------------------------------------*/
637 FUNCTION val_value_date (p_date_a IN date,
638 p_date_b IN date) return BOOLEAN is
639 /*--------------------------------------------------------------------*/
640 BEGIN
641
642 IF ( p_date_a > p_date_b ) THEN
643 return(FALSE);
644 END IF;
645 return TRUE;
646 END val_value_date;
647
648 /*--------------------------------------------------------------------*/
649 FUNCTION val_client_code(p_client_code IN varchar2) return BOOLEAN is
650 /*--------------------------------------------------------------------*/
651 l_temp varchar2(1);
652 BEGIN
653 IF p_client_code is not null then
654 BEGIN
655 select 'Y'
656 into l_temp
657 from xtr_parties_v
658 where party_type = 'CP'
659 and party_category = 'CL'
660 and party_code = p_client_code
661 and rownum = 1;
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664 return(FALSE);
665 END;
666 END IF;
667 return TRUE;
668 END val_client_code;
669
670 /*----------------------------------------------------------------------------*/
671 FUNCTION val_portfolio_code(p_company_code IN varchar2,
672 p_cparty_code IN varchar2,
673 p_portfolio_code IN varchar2) return BOOLEAN is
674 /*----------------------------------------------------------------------------*/
675 l_temp varchar2(1);
676 BEGIN
677 BEGIN
678 select 'Y'
679 into l_temp
680 from xtr_portfolios_v
681 where company_code = p_company_code
682 and (external_party is null or external_party = p_cparty_code)
683 and portfolio = p_portfolio_code
684 and rownum = 1;
685 EXCEPTION
686 WHEN NO_DATA_FOUND THEN
687 return(FALSE);
688 END;
689
690 return TRUE;
691
692 END val_portfolio_code;
693
694 /*----------------------------------------------------------------------------*/
695 FUNCTION val_limit_code( p_company_code IN varchar2,
696 p_cparty_code IN varchar2,
697 p_limit_code IN varchar2) return BOOLEAN is
698 /*----------------------------------------------------------------------------*/
699 l_temp varchar2(1);
700 BEGIN
701 IF p_limit_code is not null then
702 BEGIN
703 select 'Y'
704 into l_temp
705 from xtr_counterparty_limits_v a, xtr_limit_types_v b
706 where a.company_code = p_company_code
707 and a.cparty_code = p_cparty_code
708 and a.limit_code <> 'SETTLE'
709 and a.limit_code = p_limit_code
710 and a.limit_type = b.limit_type
711 and b.fx_invest_fund_type='X'
712 and nvl(a.authorised,'N') = 'Y'
713 and nvl(a.expiry_date,sysdate+1) > sysdate
714 and rownum = 1;
715 EXCEPTION
716 WHEN NO_DATA_FOUND THEN
717 return(FALSE);
718 END;
719 END IF;
720 return TRUE;
721 END val_limit_code;
722
723 /*----------------------------------------------------------------------------*/
724 FUNCTION val_currencies ( p_currency IN varchar2) return BOOLEAN is
725 /*----------------------------------------------------------------------------*/
726 l_temp varchar2(1);
727 BEGIN
728 BEGIN
729 Select 'Y'
730 Into l_temp
731 from xtr_master_currencies_v
732 where nvl(authorised,'N') = 'Y'
733 And currency = p_currency
734 and rownum = 1;
735
736 EXCEPTION
737 WHEN NO_DATA_FOUND THEN
738 return(FALSE);
739 END;
740 return TRUE;
741 END val_currencies;
742
743 /*----------------------------------------------------------------------------*/
744 FUNCTION val_buy_sell_curr_comb ( p_buy_currency IN varchar2,
745 p_sell_currency IN varchar2) return BOOLEAN is
746 /*----------------------------------------------------------------------------*/
747 l_temp varchar2(1);
748 BEGIN
749 BEGIN
750 select currency_first, currency_second
751 into g_currency_first, g_currency_second
752 from xtr_buy_sell_combinations_v
753 where nvl(authorised,'N') = 'Y'
754 and currency_buy = p_buy_currency
755 and currency_sell = p_sell_currency
756 and rownum = 1;
757
758 EXCEPTION
759 WHEN NO_DATA_FOUND THEN
760 return(FALSE);
761 END;
762 return TRUE;
763 END val_buy_sell_curr_comb;
764
765 /*----------------------------------------------------------------------------*/
766 FUNCTION val_comp_acct_no(p_company_code IN varchar2,
767 p_currency IN varchar2,
768 p_account_no IN varchar2) return BOOLEAN is
769 /*----------------------------------------------------------------------------*/
770 l_temp varchar2(1);
771 BEGIN
772 BEGIN
773 select 'Y'
774 into l_temp
775 from xtr_company_acct_lov_v
776 where company_code = p_company_code
777 and currency = p_currency
778 and account_number = p_account_no
779 and rownum = 1;
780 EXCEPTION
781 WHEN NO_DATA_FOUND THEN
782 return(FALSE);
783 END;
784 return TRUE;
785 END val_comp_acct_no;
786
787
788 /*----------------------------------------------------------------------------*/
789 FUNCTION val_cparty_ref( p_cparty_account_no IN varchar2,
790 p_cparty_ref IN varchar2,
791 p_cparty_code IN varchar2,
792 p_currency_b IN varchar2) return BOOLEAN is
793 /*----------------------------------------------------------------------------*/
794 l_temp varchar2(1);
795 BEGIN
796 IF (p_cparty_ref is null and p_cparty_account_no is null) then
797 return TRUE;
798 END IF;
799
800 BEGIN
801 select 'Y'
802 into l_temp
803 from xtr_bank_accounts_v
804 where bank_short_code = p_cparty_ref
805 and party_code = p_cparty_code
806 and account_number = p_cparty_account_no
807 and nvl(authorised,'N') = 'Y'
808 and currency = p_currency_b
809 and rownum = 1;
810 EXCEPTION
811 WHEN NO_DATA_FOUND THEN
812 return(FALSE);
813 END;
814
815 return TRUE;
816 END val_cparty_ref;
817
818
819 /*--------------------------------------------------------------------------------*/
820 FUNCTION val_deal_linking_code( p_deal_linking_code IN varchar2) return BOOLEAN is
821 /*--------------------------------------------------------------------------------*/
822 l_temp varchar2(1);
823 BEGIN
824 IF p_deal_linking_code is not null then
825 BEGIN
826 select 'Y'
827 into l_temp
828 from xtr_deal_linking_v
829 where deal_linking_code = p_deal_linking_code
830 and rownum = 1;
831 EXCEPTION
832 WHEN NO_DATA_FOUND THEN
833 return(FALSE);
834 END;
835 END IF;
836 return TRUE;
837 END val_deal_linking_code;
838
839 /*--------------------------------------------------------------------------------*/
840 FUNCTION val_brokerage_code ( p_brokerage_code IN varchar2) return BOOLEAN is
841 /*--------------------------------------------------------------------------------*/
842 l_temp varchar2(1);
843 BEGIN
844 IF p_brokerage_code is not null then
845 BEGIN
846 select 'Y'
847 into l_temp
848 from xtr_tax_brokerage_setup_v a, xtr_deduction_calcs_v b
849 where a.reference_code = p_brokerage_code
850 and a.deal_type = G_Fx_Deal_Type
851 and a.deduction_type = 'B'
852 and a.deal_type = b.deal_type
853 and a.calc_type = b.calc_type
854 and nvl(a.authorised,'N') = 'Y'
855 and rownum = 1;
856 EXCEPTION
857 WHEN NO_DATA_FOUND THEN
858 return(FALSE);
859 END;
860 END IF;
861 return TRUE;
862 END val_brokerage_code;
863
864
865 /*--------------------------------------------------------------------------------*/
866 FUNCTION val_dealer_code(p_dealer_code IN VARCHAR2) return BOOLEAN is
867 /*--------------------------------------------------------------------------------*/
868 l_temp varchar2(1);
869 BEGIN
870 BEGIN
871 select 'Y'
872 into l_temp
873 from xtr_dealer_codes_v
874 where dealer_code = p_dealer_code
875 and rownum = 1;
876 EXCEPTION
877 WHEN NO_DATA_FOUND THEN
878 return(FALSE);
879 END;
880 return(TRUE);
881 END val_dealer_code;
882
883 /*--------------------------------------------------------------------------------*/
884 FUNCTION val_cparty_code(p_company_code IN VARCHAR2,
885 p_cparty_code IN VARCHAR2) return BOOLEAN is
886 /*--------------------------------------------------------------------------------*/
887 l_temp varchar2(1);
888 BEGIN
889 BEGIN
890 select 'Y'
891 into l_temp
892 from xtr_party_info_v
893 where ((party_type = 'CP' and fx_cparty='Y')
894 or party_type = 'C')
895 and party_code = p_cparty_code
896 and party_code <> p_company_code
897 and nvl(authorised,'N') = 'Y'
898 and rownum = 1;
899 EXCEPTION
900 WHEN NO_DATA_FOUND THEN
901 return(FALSE);
902 END;
903 return(TRUE);
904 END val_cparty_code;
905
906
907 /*--------------------------------------------------------------------------------*/
908 FUNCTION val_deal_subtype(p_deal_subtype IN VARCHAR2,
909 p_deal_type IN VARCHAR2) return BOOLEAN is
910 /*--------------------------------------------------------------------------------*/
911 l_temp varchar2(1);
912 BEGIN
913 ------------------------------------------------------------------------------------------------------
914 --* Note : Deal_subtype column in the view is actually referring to the user_deal_subtype of the table.
915 ------------------------------------------------------------------------------------------------------
916 BEGIN
917 select 'Y'
918 into l_temp
919 from xtr_auth_deal_subtypes_v
920 where deal_type = p_deal_type
921 and deal_subtype = p_deal_subtype
922 and rownum = 1;
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 return(FALSE);
926 END;
927 return(TRUE);
928 END val_deal_subtype;
929
930 /*--------------------------------------------------------------------------------*/
931 FUNCTION val_product_type(p_product_type IN VARCHAR2,
932 p_deal_subtype IN VARCHAR2,
933 p_deal_type IN VARCHAR2) return BOOLEAN is
934 /*--------------------------------------------------------------------------------*/
935 l_temp varchar2(1);
936 l_deal_subtype varchar2(10);
937 BEGIN
938 BEGIN
939 select deal_subtype
940 into l_deal_subtype
941 from xtr_deal_subtypes
942 where deal_type = p_deal_type
943 and user_deal_subtype = p_deal_subtype
944 and nvl(authorised,'N') = 'Y'
945 and rownum = 1;
946 EXCEPTION
947 WHEN NO_DATA_FOUND THEN
948 return(FALSE);
949 END;
950
951 BEGIN
952 select 'Y'
953 into l_temp
954 from xtr_auth_product_types_v
955 where product_type = p_product_type
956 and deal_type = p_deal_type
957 and deal_subtype = l_deal_subtype
958 and rownum = 1;
959 EXCEPTION
960 WHEN NO_DATA_FOUND THEN
961 return(FALSE);
962 END;
963 return(TRUE);
964
965 END val_product_type;
966
967
968 /*--------------------------------------------------------------------------------*/
969 FUNCTION val_pricing_model(p_pricing_model IN VARCHAR2) return BOOLEAN is
970 /*--------------------------------------------------------------------------------*/
971 cursor cur_pricing is
972 select code
973 from xtr_price_models
974 where code = p_pricing_model
975 and deal_type = 'FX'
976 and nvl(authorized,'N') = 'Y';
977
978 BEGIN
979 open cur_pricing;
980 fetch cur_pricing into G_Pricing_model;
981 if cur_pricing%NOTFOUND then
982 close cur_pricing;
983 return(FALSE);
984 end if;
985 close cur_pricing;
986 return(TRUE);
987
988 END val_pricing_model;
989
990 /*--------------------------------------------------------------------------------*/
991 FUNCTION val_market_data_set(p_market_data_set IN VARCHAR2) return BOOLEAN is
992 /*--------------------------------------------------------------------------------*/
993 l_temp varchar2(1);
994
995
996 BEGIN
997 IF p_market_data_set is not null then
998 BEGIN
999 select 'Y'
1000 into l_temp
1001 from xtr_rm_md_sets
1002 where set_code = p_market_data_set
1003 and nvl(authorized_yn,'N') = 'Y'
1004 and rownum = 1;
1005 EXCEPTION
1006 WHEN NO_DATA_FOUND THEN
1007 return(FALSE);
1008 END;
1009 END IF;
1010 return(TRUE);
1011 END val_market_data_set;
1012
1013 /*--------------------------------------------------------------------------------*/
1014 FUNCTION val_brokerage_currency(p_brokerage_currency IN VARCHAR2,
1015 p_deal_type IN VARCHAR2,
1016 p_currency_a IN VARCHAR2,
1017 p_currency_b IN VARCHAR2,
1018 p_brokerage_code IN VARCHAR2) return BOOLEAN is
1019 /*--------------------------------------------------------------------------------*/
1020 l_temp varchar2(1);
1021 l_amount_type varchar2(10);
1022 BEGIN
1023 IF p_brokerage_currency is not null and p_brokerage_code is not null then
1024 BEGIN
1025 select d.amount_type
1026 into l_amount_type
1027 from xtr_tax_brokerage_setup a,
1028 xtr_deduction_calcs_v d
1029 where a.deal_type = p_deal_type
1030 and a.reference_code = p_brokerage_code
1031 and nvl(a.authorised,'N')= 'Y'
1032 and d.deal_type = a.deal_type
1033 and d.calc_type = a.calc_type
1034 and rownum =1;
1035 EXCEPTION
1036 WHEN NO_DATA_FOUND THEN
1037 return(FALSE);
1038 END;
1039
1040 IF (l_amount_type = 'BUY' and p_brokerage_currency <> p_currency_a ) then
1041 return(FALSE);
1042 ELSIF (l_amount_type = 'SELL' and p_brokerage_currency <> p_currency_b ) then
1043 return(FALSE);
1044 END IF;
1045 END IF;
1046 return(TRUE);
1047 END val_brokerage_currency;
1048
1049 /*--------------------------------------------------------------------------------*/
1050 --FUNCTION val_desc_flex( p_Interface_Rec IN XTR_DEALS_INTERFACE%ROWTYPE,
1051 -- p_error_segment IN OUT NOCOPY VARCHAR2) return BOOLEAN is
1052 /*--------------------------------------------------------------------------------*/
1053 /*
1054 l_segment number(3);
1055 BEGIN
1056
1057
1058 fnd_flex_descval.set_column_value('ATTRIBUTE1',p_Interface_Rec.ATTRIBUTE1);
1059 fnd_flex_descval.set_column_value('ATTRIBUTE2',p_Interface_Rec.ATTRIBUTE2);
1060 fnd_flex_descval.set_column_value('ATTRIBUTE3',p_Interface_Rec.ATTRIBUTE3);
1061 fnd_flex_descval.set_column_value('ATTRIBUTE4',p_Interface_Rec.ATTRIBUTE4);
1062 fnd_flex_descval.set_column_value('ATTRIBUTE5',p_Interface_Rec.ATTRIBUTE5);
1063 fnd_flex_descval.set_column_value('ATTRIBUTE6',p_Interface_Rec.ATTRIBUTE6);
1064 fnd_flex_descval.set_column_value('ATTRIBUTE7',p_Interface_Rec.ATTRIBUTE7);
1065 fnd_flex_descval.set_column_value('ATTRIBUTE8',p_Interface_Rec.ATTRIBUTE8);
1066 fnd_flex_descval.set_column_value('ATTRIBUTE9',p_Interface_Rec.ATTRIBUTE9);
1067 fnd_flex_descval.set_column_value('ATTRIBUTE10',p_Interface_Rec.ATTRIBUTE10);
1068 fnd_flex_descval.set_column_value('ATTRIBUTE11',p_Interface_Rec.ATTRIBUTE11);
1069 fnd_flex_descval.set_column_value('ATTRIBUTE12',p_Interface_Rec.ATTRIBUTE12);
1070 fnd_flex_descval.set_column_value('ATTRIBUTE13',p_Interface_Rec.ATTRIBUTE13);
1071 fnd_flex_descval.set_column_value('ATTRIBUTE14',p_Interface_Rec.ATTRIBUTE14);
1072 fnd_flex_descval.set_column_value('ATTRIBUTE15',p_Interface_Rec.ATTRIBUTE15);
1073
1074 fnd_flex_descval.set_context_value(p_Interface_Rec.ATTRIBUTE_CATEGORY);
1075
1076 IF fnd_flex_descval.validate_desccols('XTR','XTR_DEALS_DESC_FLEX') then
1077 if (fnd_flex_descval.is_valid) then
1078 null;
1079 else
1080 --RV l_segment := to_char(fnd_flex_descval.error_segment) ;
1081 --RV p_error_segment := 'Attribute'||l_segment;
1082 return(FALSE);
1083 end if;
1084
1085 if (fnd_flex_descval.value_error OR
1086 fnd_flex_descval.unsupported_error) then
1087 --RV l_segment := to_char(fnd_flex_descval.error_segment) ;
1088 --RV p_error_segment := 'Attribute'||l_segment;
1089 return(FALSE);
1090 end if;
1091
1092 return(TRUE);
1093
1094 ELSE
1095 l_segment := to_char(fnd_flex_descval.error_segment) ;
1096 -- RV
1097 --if l_segment Is Not Null Then
1098 -- p_error_segment := 'Attribute'||l_segment;
1099 --else
1100 -- p_error_segment := 'AttributeCategory';
1101 --end If;
1102 --/
1103
1104 If l_segment Is Null Then
1105 p_error_segment := 'AttributeCategory';
1106 End If;
1107
1108 return(FALSE);
1109 END IF;
1110
1111
1112 END val_desc_flex;
1113 */
1114
1115 /*------------------------------------------------------------------------------------------*/
1116 PROCEDURE copy_from_interface_to_fx(ARec_Interface IN xtr_deals_interface%rowtype ) is
1117 /*------------------------------------------------------------------------------------------*/
1118 l_deal_subtype varchar2(10);
1119 BEGIN
1120
1121 G_Fx_Main_Rec.EXTERNAL_DEAL_ID := NULL;
1122 G_Fx_Main_Rec.DEAL_TYPE := NULL;
1123 G_Fx_Main_Rec.DEALER_CODE := NULL;
1124 G_Fx_Main_Rec.COMPANY_CODE := NULL;
1125 G_Fx_Main_Rec.CPARTY_CODE := NULL;
1126 G_Fx_Main_Rec.CLIENT_CODE := NULL;
1127 G_Fx_Main_Rec.PORTFOLIO_CODE := NULL;
1128 G_Fx_Main_Rec.LIMIT_CODE := NULL;
1129 G_Fx_Main_Rec.DEAL_SUBTYPE := NULL;
1130 G_Fx_Main_Rec.PRODUCT_TYPE := NULL;
1131 G_Fx_Main_Rec.DEAL_DATE := NULL;
1132 G_Fx_Main_Rec.VALUE_DATE := NULL;
1133 G_Fx_Main_Rec.CURRENCY_BUY := NULL;
1134 G_Fx_Main_Rec.CURRENCY_SELL := NULL;
1135 G_Fx_Main_Rec.BUY_AMOUNT := NULL;
1136 G_Fx_Main_Rec.SELL_AMOUNT := NULL;
1137 G_Fx_Main_Rec.BUY_ACCOUNT_NO := NULL;
1138 G_Fx_Main_Rec.SELL_ACCOUNT_NO := NULL;
1139 G_Fx_Main_Rec.BASE_RATE := NULL;
1140 G_Fx_Main_Rec.TRANSACTION_RATE := NULL;
1141 G_Fx_Main_Rec.COMMENTS := NULL;
1142 G_Fx_Main_Rec.EXTERNAL_COMMENTS := NULL;
1143 G_Fx_Main_Rec.INTERNAL_TICKET_NO := NULL;
1144 G_Fx_Main_Rec.EXTERNAL_CPARTY_NO := NULL;
1145 G_Fx_Main_Rec.CPARTY_ACCOUNT_NO := NULL;
1146 G_Fx_Main_Rec.CPARTY_REF := NULL;
1147 G_Fx_Main_Rec.MARKET_DATA_SET := NULL;
1148 G_Fx_Main_Rec.DEAL_LINKING_CODE := NULL;
1149 G_Fx_Main_Rec.BROKERAGE_CODE := NULL;
1150 G_Fx_Main_Rec.BROKERAGE_RATE := NULL;
1151 G_Fx_Main_Rec.BROKERAGE_AMOUNT := NULL;
1152 G_Fx_Main_Rec.BROKERAGE_CURRENCY := NULL;
1153 G_Fx_Main_Rec.PRICING_MODEL := NULL;
1154 G_Fx_Main_Rec.BUY_HCE_AMOUNT := NULL;
1155 G_Fx_Main_Rec.SELL_HCE_AMOUNT := NULL;
1156 G_Fx_Main_Rec.FORWARD_HCE_AMOUNT := NULL;
1157 G_Fx_Main_Rec.PORTFOLIO_AMOUNT := NULL;
1158 G_Fx_Main_Rec.DUAL_AUTHORISATION_BY := NULL;
1159 G_Fx_Main_Rec.DUAL_AUTHORISATION_ON := to_date(NULL);
1160 G_Fx_Main_Rec.STATUS_CODE := NULL;
1161 G_Fx_Main_Rec.ATTRIBUTE_CATEGORY := NULL;
1162 G_Fx_Main_Rec.ATTRIBUTE1 := NULL;
1163 G_Fx_Main_Rec.ATTRIBUTE2 := NULL;
1164 G_Fx_Main_Rec.ATTRIBUTE3 := NULL;
1165 G_Fx_Main_Rec.ATTRIBUTE4 := NULL;
1166 G_Fx_Main_Rec.ATTRIBUTE5 := NULL;
1167 G_Fx_Main_Rec.ATTRIBUTE6 := NULL;
1168 G_Fx_Main_Rec.ATTRIBUTE7 := NULL;
1169 G_Fx_Main_Rec.ATTRIBUTE8 := NULL;
1170 G_Fx_Main_Rec.ATTRIBUTE9 := NULL;
1171 G_Fx_Main_Rec.ATTRIBUTE10 := NULL;
1172 G_Fx_Main_Rec.ATTRIBUTE11 := NULL;
1173 G_Fx_Main_Rec.ATTRIBUTE12 := NULL;
1174 G_Fx_Main_Rec.ATTRIBUTE13 := NULL;
1175 G_Fx_Main_Rec.ATTRIBUTE14 := NULL;
1176 G_Fx_Main_Rec.ATTRIBUTE15 := NULL;
1177
1178
1179 /*--------------------------------------------*/
1180 /* Find the actual deal subtype */
1181 /*--------------------------------------------*/
1182 SELECT deal_subtype
1183 INTO l_deal_subtype
1184 FROM xtr_deal_subtypes
1185 WHERE deal_type = G_Fx_Deal_Type
1186 AND user_deal_subtype = ARec_Interface.DEAL_SUBTYPE
1187 AND rownum = 1;
1188
1189 /*--------------------------------------------*/
1190 /* Copying values into the Global Record Type */
1191 /*--------------------------------------------*/
1192 G_Fx_Main_Rec.EXTERNAL_DEAL_ID := ARec_Interface.EXTERNAL_DEAL_ID ;
1193 G_Fx_Main_Rec.DEAL_TYPE := G_Fx_Deal_Type ;
1194 G_Fx_Main_Rec.DEALER_CODE := ARec_Interface.DEALER_CODE ;
1195 G_Fx_Main_Rec.COMPANY_CODE := ARec_Interface.COMPANY_CODE ;
1196 G_Fx_Main_Rec.CPARTY_CODE := ARec_Interface.CPARTY_CODE ;
1197 G_Fx_Main_Rec.CLIENT_CODE := ARec_Interface.CLIENT_CODE ;
1198 G_Fx_Main_Rec.PORTFOLIO_CODE := ARec_Interface.PORTFOLIO_CODE ;
1199 G_Fx_Main_Rec.LIMIT_CODE := ARec_Interface.LIMIT_CODE ;
1200 G_Fx_Main_Rec.DEAL_SUBTYPE := l_deal_subtype ;
1201 G_Fx_Main_Rec.PRODUCT_TYPE := ARec_Interface.PRODUCT_TYPE ;
1202 G_Fx_Main_Rec.DEAL_DATE := ARec_Interface.DATE_A ;
1203 G_Fx_Main_Rec.VALUE_DATE := ARec_Interface.DATE_B ;
1204 G_Fx_Main_Rec.CURRENCY_BUY := ARec_Interface.CURRENCY_A ;
1205 G_Fx_Main_Rec.CURRENCY_SELL := ARec_Interface.CURRENCY_B ;
1206 G_Fx_Main_Rec.NO_OF_DAYS := G_Fx_Main_Rec.VALUE_DATE - G_Fx_Main_Rec.DEAL_DATE;
1207 G_Fx_Main_Rec.BUY_AMOUNT := ARec_Interface.AMOUNT_A ;
1208 G_Fx_Main_Rec.SELL_AMOUNT := ARec_Interface.AMOUNT_B ;
1209 G_Fx_Main_Rec.BUY_ACCOUNT_NO := ARec_Interface.ACCOUNT_NO_A ;
1210 G_Fx_Main_Rec.SELL_ACCOUNT_NO := ARec_Interface.ACCOUNT_NO_B ;
1211 G_Fx_Main_Rec.BASE_RATE := ARec_Interface.RATE_A ;
1212 G_Fx_Main_Rec.TRANSACTION_RATE := ARec_Interface.RATE_B ;
1213 G_Fx_Main_Rec.COMMENTS := ARec_Interface.COMMENTS ;
1214 G_Fx_Main_Rec.EXTERNAL_COMMENTS := ARec_Interface.EXTERNAL_COMMENTS ;
1215 G_Fx_Main_Rec.INTERNAL_TICKET_NO := ARec_Interface.INTERNAL_TICKET_NO;
1216 G_Fx_Main_Rec.EXTERNAL_CPARTY_NO := ARec_Interface.EXTERNAL_CPARTY_NO;
1217 G_Fx_Main_Rec.CPARTY_ACCOUNT_NO := ARec_Interface.CPARTY_ACCOUNT_NO;
1218 G_Fx_Main_Rec.CPARTY_REF := NULL ; --bug 304164
1219 G_Fx_Main_Rec.PRICING_MODEL := G_Pricing_Model ;
1220 G_Fx_Main_Rec.MARKET_DATA_SET := ARec_Interface.MARKET_DATA_SET ;
1221 G_Fx_Main_Rec.DEAL_LINKING_CODE := ARec_Interface.DEAL_LINKING_CODE;
1222 if ARec_Interface.BROKERAGE_CODE is not null then
1223 G_Fx_Main_Rec.BROKERAGE_CODE := ARec_Interface.BROKERAGE_CODE ;
1224 G_Fx_Main_Rec.BROKERAGE_RATE := ARec_Interface.RATE_C ;
1225 G_FX_Main_Rec.BROKERAGE_AMOUNT := ARec_Interface.AMOUNT_C ;
1226 G_Fx_Main_Rec.BROKERAGE_CURRENCY := ARec_Interface.BROKERAGE_CURRENCY;
1227 else
1228 G_Fx_Main_Rec.BROKERAGE_CODE := null;
1229 G_Fx_Main_Rec.BROKERAGE_RATE := null;
1230 G_FX_Main_Rec.BROKERAGE_AMOUNT := null;
1231 G_Fx_Main_Rec.BROKERAGE_CURRENCY := null;
1232 end if;
1233 G_Fx_Main_Rec.DUAL_AUTHORISATION_BY := ARec_Interface.DUAL_AUTHORIZATION_BY;
1234 G_Fx_Main_Rec.DUAL_AUTHORISATION_ON := ARec_Interface.DUAL_AUTHORIZATION_ON;
1235 G_Fx_Main_Rec.STATUS_CODE := 'CURRENT';
1236
1237 /*--------------------------------------------------------------------*/
1238 /* Flexfields will be implemented in Patchset G. */
1239 /*--------------------------------------------------------------------*/
1240 G_Fx_Main_Rec.ATTRIBUTE_CATEGORY := ARec_Interface.ATTRIBUTE_CATEGORY;
1241 G_Fx_Main_Rec.ATTRIBUTE1 := ARec_Interface.ATTRIBUTE1 ;
1242 G_Fx_Main_Rec.ATTRIBUTE2 := ARec_Interface.ATTRIBUTE2 ;
1243 G_Fx_Main_Rec.ATTRIBUTE3 := ARec_Interface.ATTRIBUTE3 ;
1244 G_Fx_Main_Rec.ATTRIBUTE4 := ARec_Interface.ATTRIBUTE4 ;
1245 G_Fx_Main_Rec.ATTRIBUTE5 := ARec_Interface.ATTRIBUTE5 ;
1246 G_Fx_Main_Rec.ATTRIBUTE6 := ARec_Interface.ATTRIBUTE6 ;
1247 G_Fx_Main_Rec.ATTRIBUTE7 := ARec_Interface.ATTRIBUTE7 ;
1248 G_Fx_Main_Rec.ATTRIBUTE8 := ARec_Interface.ATTRIBUTE8 ;
1249 G_Fx_Main_Rec.ATTRIBUTE9 := ARec_Interface.ATTRIBUTE9 ;
1250 G_Fx_Main_Rec.ATTRIBUTE10 := ARec_Interface.ATTRIBUTE10 ;
1251 G_Fx_Main_Rec.ATTRIBUTE11 := ARec_Interface.ATTRIBUTE11 ;
1252 G_Fx_Main_Rec.ATTRIBUTE12 := ARec_Interface.ATTRIBUTE12 ;
1253 G_Fx_Main_Rec.ATTRIBUTE13 := ARec_Interface.ATTRIBUTE13 ;
1254 G_Fx_Main_Rec.ATTRIBUTE14 := ARec_Interface.ATTRIBUTE14 ;
1255 G_Fx_Main_Rec.ATTRIBUTE15 := ARec_Interface.ATTRIBUTE15 ;
1256
1257 END copy_from_interface_to_fx;
1258
1259 /*------------------------------------------------------------*/
1260 /* The following code implements the calc_rates process */
1261 /*------------------------------------------------------------*/
1262 PROCEDURE CALC_RATES(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
1263 error OUT NOCOPY boolean) is
1264
1265 l_bkr_amt_type varchar2(30);
1266 l_dummy_char varchar2(30);
1267
1268 --rvallams
1269 roundfac number(3,2);
1270
1271 cursor rnd(p_curr in VARCHAR2) is
1272 select m.rounding_factor
1273 from xtr_master_currencies_v m
1274 where m.currency = p_curr;
1275
1276
1277 begin
1278
1279 error := FALSE;
1280
1281 /*--------------------------------------------------------------------------------------------*/
1282 /* This process checks the values of the three columns to make sure that they are all in sync */
1283 /*--------------------------------------------------------------------------------------------*/
1284 VALIDATE_BUY_SELL_AMOUNT(ARec_Interface.deal_type, error);
1285
1286
1287 --rvallams bug 2383157 begin
1288
1289 /*--------------------------------------------------------------------------------------------*/
1290 /* Store the amounts rounded as per the precision of the currency */
1291 /*--------------------------------------------------------------------------------------------*/
1292 if error <> TRUE then
1293
1294 open rnd(G_Fx_Main_Rec.currency_buy);
1295 fetch rnd into roundfac;
1296 close rnd;
1297 G_Fx_Main_Rec.buy_amount := round(G_Fx_Main_Rec.buy_amount,roundfac);
1298
1299 open rnd(G_Fx_Main_Rec.currency_sell);
1300 fetch rnd into roundfac;
1301 close rnd;
1302 G_Fx_Main_Rec.sell_amount := round(G_Fx_Main_Rec.sell_amount,roundfac);
1303
1304 end if;
1305
1306 --rvallams bug 2383157 end
1307
1308
1309 /*--------------------------------------------------------------------------------------------*/
1310 /* This process checks brokerage values. */
1311 /*--------------------------------------------------------------------------------------------*/
1312 if error <> TRUE then
1313 xtr_fps2_p.tax_brokerage_amt_type(G_FX_Deal_Type,
1314 ARec_Interface.brokerage_code,
1315 null,
1316 l_bkr_amt_type,
1317 l_dummy_char);
1318 if G_Fx_Main_Rec.deal_date is not null and
1319 G_Fx_Main_Rec.buy_amount is not null and
1320 G_Fx_Main_Rec.sell_amount is not null and
1321 ARec_Interface.brokerage_code is not null then
1322 if ARec_Interface.amount_c is null then
1323 CALC_BROKERAGE_AMT(ARec_Interface.deal_type, l_bkr_amt_type, error);
1324 end if;
1325
1326 if G_Fx_Main_Rec.brokerage_amount is not null and G_Fx_Main_Rec.brokerage_currency is null then
1327 if l_bkr_amt_type = 'BUY' then
1328 G_Fx_Main_Rec.brokerage_currency := ARec_Interface.currency_a;
1329 else
1330 G_Fx_Main_Rec.brokerage_currency := ARec_Interface.currency_b;
1331 end if;
1332 end if;
1333
1334 end if;
1335 end if;
1336
1337 if error <> TRUE then
1338 CALC_HCE_AMOUNTS(ARec_Interface.deal_type, error);
1339 end if;
1340
1341
1342 end CALC_RATES;
1343
1344
1345 /*--------------------------------------------------------------------------------*/
1346 PROCEDURE CALC_HCE_AMOUNTS (p_user_deal_type IN VARCHAR2, p_error OUT NOCOPY BOOLEAN) is
1347 /*--------------------------------------------------------------------------------*/
1348
1349 home_currency varchar2(15);
1350 roundfac number;
1351 bid_rate number;
1352 forward_hce_first number;
1353 forward_hce_second number;
1354 limit_weighting number;
1355 dummy_char varchar2(20);
1356 dummy_num number;
1357
1358 cursor rnd_fac is
1359 select p.home_currency,
1360 m.rounding_factor
1361 from xtr_parties_v p,
1362 xtr_master_currencies_v m
1363 where p.party_code = G_Fx_Main_Rec.company_code
1364 and p.party_type = 'C'
1365 and m.currency = p.home_currency;
1366
1367 cursor hc_rate is
1368 select round((G_Fx_Main_Rec.buy_amount/ s.hce_rate ),roundfac)
1369 from xtr_master_currencies_v s
1370 where s.currency = G_Fx_Main_Rec.currency_buy;
1371
1372 cursor calc_hce is
1373 select round(((G_Fx_Main_Rec.buy_amount / G_Fx_Main_Rec.base_rate -
1374 G_Fx_Main_Rec.sell_amount )/ s.hce_rate),roundfac),
1375 round(((G_Fx_Main_Rec.buy_amount * G_Fx_Main_Rec.base_rate -
1376 G_Fx_Main_Rec.sell_amount )/ s.hce_rate),roundfac)
1377 from xtr_master_currencies_v s
1378 where s.currency = G_Fx_Main_Rec.currency_sell ;
1379
1380 BEGIN
1381
1382 p_error := FALSE;
1383
1384 /* ------------------------------------------------------------------------ */
1385 /* Determine home currency and rounding factor for subsequent calculations. */
1386 /* ------------------------------------------------------------------------ */
1387 open rnd_fac;
1388 fetch rnd_fac into home_currency, roundfac;
1389 if rnd_fac%notfound then
1390 close rnd_fac;
1391 /* cannot find home currency rate */
1392 xtr_import_deal_data.log_interface_errors(G_Fx_Main_Rec.External_Deal_Id,p_user_deal_type,
1393 'CompanyCode','XTR_880');
1394 p_error := TRUE;
1395 return;
1396 end if;
1397 close rnd_fac;
1398
1399 /* ------------------------------------------------------------------------------------------------------*/
1400 /* Calculation for buy amount differs depending on whether the buy or sell currency is the home currency.*/
1401 /* ------------------------------------------------------------------------------------------------------*/
1402 /* 1. buy currency = home currency. If buy curr = home curr then buy hce amount is set to buy amount */
1403 /* ------------------------------------------------------------------------------------------------------*/
1404
1405 if G_Fx_Main_Rec.currency_buy = home_currency then
1406 G_Fx_Main_Rec.buy_hce_amount := G_Fx_Main_Rec.buy_amount;
1407 G_Fx_Main_Rec.sell_hce_amount := G_Fx_Main_Rec.buy_amount;
1408 end if;
1409
1410
1411 /* ------------------------------------------------------------------------------------------------------*/
1412 /* 2. sell currency = home currency. If sell curr = home curr then sell hce amount is set to sell amount */
1413 /* ------------------------------------------------------------------------------------------------------*/
1414
1415 if G_Fx_Main_Rec.currency_sell = home_currency then
1416 G_Fx_Main_Rec.buy_hce_amount := G_Fx_Main_Rec.sell_amount;
1417 G_Fx_Main_Rec.sell_hce_amount := G_Fx_Main_Rec.sell_amount;
1418 end if;
1419
1420
1421 /* ------------------------------------------------------------------------------------------------------*/
1422 /* 3. home currency <> sell or buy currencies. If home currency is not sell or buy currency then */
1423 /* buy hce amount is calculated as buy amount /spot_rates.hce_rate for buy curr */
1424 /* ------------------------------------------------------------------------------------------------------*/
1425
1426 if (G_Fx_Main_Rec.currency_buy <> home_currency and
1427 G_Fx_Main_Rec.currency_sell <> home_currency) then
1428 open hc_rate;
1429 fetch hc_rate into G_Fx_Main_Rec.buy_hce_amount;
1430 if hc_rate%notfound then
1431 close hc_rate;
1432 /* Cannot find home currency rate */
1433 xtr_import_deal_data.log_interface_errors(G_Fx_Main_Rec.External_Deal_Id,p_user_deal_type,
1434 'CurrencyA','XTR_880');
1435 p_error := TRUE;
1436 return;
1437 end if;
1438 G_Fx_Main_Rec.sell_hce_amount := G_Fx_Main_Rec.buy_hce_amount;
1439 close hc_rate;
1440 end if;
1441
1442 /* -------------------------------------------------------------------
1443 Forward hce amount. Calculation for forward buy amount differs,
1444 depending on whether the buy or sell currency is the home ccy
1445 If buy curr = home curr then
1446 forward hce amount is set to buy amount - sell amount/base rate
1447 else, if sell curr = home curr then
1448 forward hce amount is set to buy amount/base rate - sell amount */
1449 /*------------------------------------------------------------------*/
1450
1451 if G_Fx_Main_Rec.currency_buy = home_currency then
1452 G_Fx_Main_Rec.forward_hce_amount := round(G_Fx_Main_Rec.buy_amount -
1453 (G_Fx_Main_Rec.sell_amount/G_Fx_Main_Rec.base_rate),roundfac);
1454 elsif G_Fx_Main_Rec.currency_sell = home_currency then
1455 G_Fx_Main_Rec.forward_hce_amount := round((G_Fx_Main_Rec.buy_amount/G_Fx_Main_Rec.base_rate -
1456 G_Fx_Main_Rec.sell_amount),roundfac);
1457 end if;
1458
1459 /* -----------------------------------------------------------------------
1460 If home currency is not sell or buy currency then forward hce
1461 amount is calculated as :(buy amount/base rate - sell amount)/hce
1462 for sell ccy if sell currency quoted first in buy/sell combinations or
1463 (buy amount*base rate - sell amount)/hce for sell currency if sell
1464 currency quoted second in buy/sell combinations. */
1465 /*-----------------------------------------------------------------------*/
1466
1467 if (G_Fx_Main_Rec.currency_buy <> home_currency and G_Fx_Main_Rec.currency_sell <> home_currency) then
1468 open calc_hce;
1469 fetch calc_hce into forward_hce_first, forward_hce_second;
1470 if calc_hce%notfound then
1471 close calc_hce;
1472 xtr_import_deal_data.log_interface_errors(G_Fx_Main_Rec.External_Deal_Id,p_user_deal_type,
1473 'CurrencyB','XTR_886'); -- Unable to find spot rate sell data
1474 p_error := TRUE;
1475 return;
1476 end if;
1477
1478 if G_Fx_Main_Rec.currency_sell = g_currency_first then
1479 G_Fx_Main_Rec.forward_hce_amount := forward_hce_first;
1480 else
1481 G_Fx_Main_Rec.forward_hce_amount := forward_hce_second;
1482 end if;
1483
1484 close calc_hce;
1485
1486 end if;
1487
1488 /*-----------------------------------------------------------*/
1489 /* portfolio amount. set portfolio amount to buy hce amount. */
1490 /*-----------------------------------------------------------*/
1491 G_Fx_Main_Rec.portfolio_amount := G_Fx_Main_Rec.buy_hce_amount;
1492
1493
1494 end CALC_HCE_AMOUNTS;
1495
1496 /*------------------------------------------------------------*/
1497 PROCEDURE CALC_BROKERAGE_AMT(p_user_deal_type IN VARCHAR2,
1498 p_bkr_amt_type IN VARCHAR2,
1499 p_error OUT NOCOPY BOOLEAN) IS
1500 /*------------------------------------------------------------*/
1501 l_dummy_num number;
1502 l_dummy_char varchar2(30);
1503 l_amt number;
1504 l_ccy varchar2(30);
1505 l_err_code number(8);
1506 l_level varchar2(2) := ' ';
1507
1508 l_mine number;
1509
1510 BEGIN
1511 if (p_bkr_amt_type = 'BUY' and G_Fx_Main_Rec.currency_buy is not null and G_Fx_Main_Rec.buy_amount is not null) or
1512 (p_bkr_amt_type = 'SELL' and G_Fx_Main_Rec.currency_sell is not null and G_Fx_Main_Rec.sell_amount is not null) then
1513
1514 if p_bkr_amt_type = 'BUY' then
1515 l_ccy := G_Fx_Main_Rec.currency_buy;
1516 l_amt := G_Fx_Main_Rec.buy_amount;
1517 else
1518 l_ccy := G_Fx_Main_Rec.currency_sell;
1519 l_amt := G_Fx_Main_Rec.sell_amount;
1520 end if;
1521 xtr_fps1_p.calc_tax_brokerage(G_Fx_Main_Rec.deal_type,
1522 G_Fx_Main_Rec.deal_date,
1523 null,
1524 G_Fx_Main_Rec.brokerage_code,
1525 l_ccy,
1526 0,
1527 0,
1528 null,
1529 0,
1530 l_dummy_num,
1531 p_bkr_amt_type,
1532 l_amt,
1533 G_Fx_Main_Rec.brokerage_rate,
1534 l_dummy_num,
1535 l_dummy_num,
1536 G_Fx_Main_Rec.brokerage_amount,
1537 l_dummy_num,
1538 l_err_code,
1539 l_level);
1540
1541 check_for_error(p_user_deal_type, l_err_code, l_level);
1542
1543 if nvl(l_level,'X') = 'E' then
1544 p_error := TRUE;
1545 else
1546 p_error := FALSE;
1547 end if;
1548
1549 end if;
1550
1551 END CALC_BROKERAGE_AMT;
1552
1553 /*------------------------------------------------------------*/
1554 PROCEDURE VALIDATE_BUY_SELL_AMOUNT (p_user_deal_type IN VARCHAR2,
1555 p_error OUT NOCOPY boolean) is
1556 /*------------------------------------------------------------*/
1557 l_error_amt BOOLEAN := FALSE;
1558
1559 BEGIN
1560
1561 chk_buy_sell_amount(p_user_deal_type,g_currency_first, l_error_amt);
1562
1563 if (l_error_amt = TRUE) then
1564 p_error := TRUE;
1565 else
1566 p_error := FALSE;
1567 end if;
1568
1569 END VALIDATE_BUY_SELL_AMOUNT;
1570
1571
1572 /*------------------------------------------------------------*/
1573 PROCEDURE CHK_BUY_SELL_AMOUNT (p_user_deal_type IN VARCHAR2,
1574 p_currency_first IN varchar2,
1575 p_error IN OUT NOCOPY BOOLEAN) is
1576 /*------------------------------------------------------------*/
1577
1578 roundfac number(3,2);
1579 v_new_buy_amount number;
1580 v_new_sell_amount number;
1581 l_tol number;
1582
1583 /*
1584 cursor rnd is
1585 select m.rounding_factor
1586 from xtr_master_currencies_v m
1587 where m.currency = G_Fx_Main_Rec.currency_sell;
1588 */
1589
1590 --RV 2342574
1591 cursor rnd(p_curr in VARCHAR2) is
1592 select m.rounding_factor
1593 from xtr_master_currencies_v m
1594 where m.currency = p_curr;
1595
1596 Cursor tol is
1597 select parameter_value
1598 from xtr_company_parameters
1599 where company_code = G_Fx_Main_Rec.COMPANY_CODE
1600 and parameter_code = 'IMPORT_FXTOL';
1601
1602 BEGIN
1603 if nvl(G_Fx_Main_Rec.buy_amount,nvl(G_Fx_Main_Rec.sell_amount,0)) <> 0 and
1604 G_Fx_Main_Rec.transaction_rate is not null then
1605
1606 /* 2342574
1607 open rnd;
1608 fetch rnd into roundfac;
1609 close rnd;
1610 */
1611 open tol;
1612 fetch tol into l_tol;
1613 close tol;
1614
1615 if G_Fx_Main_Rec.currency_sell = p_currency_first then
1616 --RV 2342574
1617 open rnd(G_Fx_Main_Rec.currency_sell);
1618 fetch rnd into roundfac;
1619 close rnd;
1620 v_new_sell_amount := round(nvl(G_Fx_Main_Rec.buy_amount,0) / G_Fx_Main_Rec.transaction_rate,nvl(roundfac,2));
1621
1622 -- if v_new_sell_amount <> NVL(G_Fx_Main_Rec.sell_amount,0) then -- rvallams bug 2397061
1623
1624 if v_new_sell_amount < round(NVL(G_Fx_Main_Rec.sell_amount,0) - l_tol/power(10,nvl(roundfac,0)),nvl(roundfac,2))
1625 OR v_new_sell_amount > round(NVL(G_Fx_Main_Rec.sell_amount,0) + l_tol/power(10,nvl(roundfac,0)),nvl(roundfac,2)) then
1626
1627 xtr_import_deal_data.log_interface_errors(G_Fx_Main_Rec.External_Deal_Id,p_user_deal_type,
1628 'AmountA','XTR_INV_BUY_AMT');
1629 xtr_import_deal_data.log_interface_errors(G_Fx_Main_Rec.External_Deal_Id,p_user_deal_type,
1630 'AmountB','XTR_INV_SELL_AMT');
1631 p_error := TRUE;
1632 end if;
1633 else
1634
1635 --RV 2342574
1636 open rnd(G_Fx_Main_Rec.currency_buy);
1637 fetch rnd into roundfac;
1638 close rnd;
1639
1640 v_new_buy_amount := round(nvl(G_Fx_Main_Rec.sell_amount,0)/ G_Fx_Main_Rec.transaction_rate,nvl(roundfac,2));
1641
1642 -- if (v_new_buy_amount <> NVL(G_Fx_Main_Rec.buy_amount,0)) then -- rvallams bug 2397061
1643
1644 if v_new_buy_amount < round(NVL(G_Fx_Main_Rec.buy_amount,0) - l_tol/power(10,nvl(roundfac,0)),nvl(roundfac,2))
1645 OR v_new_buy_amount > round(NVL(G_Fx_Main_Rec.buy_amount,0) + l_tol/power(10,nvl(roundfac,0)),nvl(roundfac,2)) then
1646
1647 xtr_import_deal_data.log_interface_errors(G_Fx_Main_Rec.External_Deal_Id,p_user_deal_type,
1648 'AmountA','XTR_INV_BUY_AMT');
1649 xtr_import_deal_data.log_interface_errors(G_Fx_Main_Rec.External_Deal_Id,p_user_deal_type,
1650 'AmountB','XTR_INV_SELL_AMT');
1651 p_error := TRUE;
1652 end if;
1653 end if;
1654
1655 end if;
1656
1657 END CHK_BUY_SELL_AMOUNT;
1658
1659
1660 /*------------------------------------------------------------*/
1661 PROCEDURE CREATE_FX_DEAL(ARec_Fx IN XTR_DEALS%ROWTYPE,
1662 p_deal_no IN NUMBER) IS
1663 /*------------------------------------------------------------*/
1664
1665 cursor FIND_USER (p_fnd_user in number) is
1666 select dealer_code
1667 from xtr_dealer_codes_v
1668 where user_id = p_fnd_user;
1669
1670 l_user xtr_dealer_codes.dealer_code%TYPE;
1671 l_dual_user xtr_dealer_codes.dealer_code%TYPE;
1672 l_dual_date DATE;
1673
1674 Begin
1675
1676 open FIND_USER(G_User_Id);
1677 fetch FIND_USER into l_user;
1678 close FIND_USER;
1679
1680 l_dual_user := ARec_Fx.DUAL_AUTHORISATION_BY;
1681 l_dual_date := ARec_Fx.DUAL_AUTHORISATION_ON;
1682 if ((l_dual_user is not null and l_dual_date is null) or
1683 (l_dual_user is null and l_dual_date is not null)) then
1684 if l_dual_date is null then
1685 l_dual_date := trunc(sysdate);
1686 elsif l_dual_user is null then
1687 l_dual_user := l_user;
1688 end if;
1689 end if;
1690
1691 INSERT INTO xtr_deals
1692 (
1693 EXTERNAL_DEAL_ID ,
1694 DEAL_NO ,
1695 DEAL_TYPE ,
1696 DEALER_CODE ,
1697 COMPANY_CODE ,
1698 CPARTY_CODE ,
1699 CLIENT_CODE ,
1700 PORTFOLIO_CODE ,
1701 LIMIT_CODE ,
1702 DEAL_SUBTYPE ,
1703 PRODUCT_TYPE ,
1704 DEAL_DATE ,
1705 VALUE_DATE ,
1706 BASE_DATE ,
1707 CURRENCY_BUY ,
1708 CURRENCY_SELL ,
1709 BUY_AMOUNT ,
1710 SELL_AMOUNT ,
1711 BUY_ACCOUNT_NO ,
1712 SELL_ACCOUNT_NO ,
1713 BASE_RATE ,
1714 TRANSACTION_RATE ,
1715 COMMENTS ,
1716 EXTERNAL_COMMENTS ,
1717 INTERNAL_TICKET_NO ,
1718 EXTERNAL_CPARTY_NO ,
1719 CPARTY_ACCOUNT_NO ,
1720 CPARTY_REF ,
1721 NO_OF_DAYS ,
1722 PRICING_MODEL ,
1723 MARKET_DATA_SET ,
1724 DEAL_LINKING_CODE ,
1725 BROKERAGE_CODE ,
1726 BROKERAGE_RATE ,
1727 BROKERAGE_AMOUNT ,
1728 BROKERAGE_CURRENCY ,
1729 BUY_HCE_AMOUNT ,
1730 SELL_HCE_AMOUNT ,
1731 FORWARD_HCE_AMOUNT ,
1732 PORTFOLIO_AMOUNT ,
1733 STATUS_CODE ,
1734 DUAL_AUTHORISATION_BY ,
1735 DUAL_AUTHORISATION_ON ,
1736 CREATED_BY ,
1737 CREATED_ON ,
1738 ATTRIBUTE_CATEGORY ,
1739 ATTRIBUTE1 ,
1740 ATTRIBUTE2 ,
1741 ATTRIBUTE3 ,
1742 ATTRIBUTE4 ,
1743 ATTRIBUTE5 ,
1744 ATTRIBUTE6 ,
1745 ATTRIBUTE7 ,
1746 ATTRIBUTE8 ,
1747 ATTRIBUTE9 ,
1748 ATTRIBUTE10 ,
1749 ATTRIBUTE11 ,
1750 ATTRIBUTE12 ,
1751 ATTRIBUTE13 ,
1752 ATTRIBUTE14 ,
1753 ATTRIBUTE15 ,
1754 REQUEST_ID ,
1755 PROGRAM_APPLICATION_ID ,
1756 PROGRAM_ID ,
1757 PROGRAM_UPDATE_DATE )
1758 VALUES
1759 (
1760 ARec_Fx.EXTERNAL_DEAL_ID ,
1761 p_deal_no ,
1762 ARec_Fx.DEAL_TYPE ,
1763 ARec_Fx.DEALER_CODE ,
1764 ARec_Fx.COMPANY_CODE ,
1765 ARec_Fx.CPARTY_CODE ,
1766 ARec_Fx.CLIENT_CODE ,
1767 ARec_Fx.PORTFOLIO_CODE ,
1768 ARec_Fx.LIMIT_CODE ,
1769 ARec_Fx.DEAL_SUBTYPE ,
1770 ARec_Fx.PRODUCT_TYPE ,
1771 ARec_Fx.DEAL_DATE ,
1772 ARec_Fx.VALUE_DATE ,
1773 ARec_Fx.DEAL_DATE ,
1774 ARec_Fx.CURRENCY_BUY ,
1775 ARec_Fx.CURRENCY_SELL ,
1776 ARec_Fx.BUY_AMOUNT ,
1777 ARec_Fx.SELL_AMOUNT ,
1778 ARec_Fx.BUY_ACCOUNT_NO ,
1779 ARec_Fx.SELL_ACCOUNT_NO ,
1780 ARec_Fx.BASE_RATE ,
1781 ARec_Fx.TRANSACTION_RATE ,
1782 ARec_Fx.COMMENTS ,
1783 ARec_Fx.EXTERNAL_COMMENTS ,
1784 ARec_Fx.INTERNAL_TICKET_NO ,
1785 ARec_Fx.EXTERNAL_CPARTY_NO ,
1786 Arec_Fx.CPARTY_ACCOUNT_NO ,
1787 ARec_Fx.CPARTY_REF ,
1788 ARec_Fx.NO_OF_DAYS ,
1789 ARec_Fx.PRICING_MODEL ,
1790 ARec_Fx.MARKET_DATA_SET ,
1791 ARec_Fx.DEAL_LINKING_CODE ,
1792 ARec_Fx.BROKERAGE_CODE ,
1793 ARec_Fx.BROKERAGE_RATE ,
1794 ARec_Fx.BROKERAGE_AMOUNT ,
1795 ARec_Fx.BROKERAGE_CURRENCY ,
1796 ARec_Fx.BUY_HCE_AMOUNT ,
1797 ARec_Fx.SELL_HCE_AMOUNT ,
1798 ARec_Fx.FORWARD_HCE_AMOUNT ,
1799 ARec_Fx.PORTFOLIO_AMOUNT ,
1800 ARec_Fx.STATUS_CODE ,
1801 l_dual_user ,
1802 l_dual_date ,
1803 nvl(l_user,G_User_Id) ,
1804 g_curr_date ,
1805 --decode(ARec_Fx.ATTRIBUTE_CATEGORY,'Global Data Elements','',ARec_Fx.ATTRIBUTE_CATEGORY) ,
1806 ARec_Fx.ATTRIBUTE_CATEGORY ,
1807 ARec_Fx.ATTRIBUTE1 ,
1808 ARec_Fx.ATTRIBUTE2 ,
1809 ARec_Fx.ATTRIBUTE3 ,
1810 ARec_Fx.ATTRIBUTE4 ,
1811 ARec_Fx.ATTRIBUTE5 ,
1812 ARec_Fx.ATTRIBUTE6 ,
1813 ARec_Fx.ATTRIBUTE7 ,
1814 ARec_Fx.ATTRIBUTE8 ,
1815 ARec_Fx.ATTRIBUTE9 ,
1816 ARec_Fx.ATTRIBUTE10 ,
1817 ARec_Fx.ATTRIBUTE11 ,
1818 ARec_Fx.ATTRIBUTE12 ,
1819 ARec_Fx.ATTRIBUTE13 ,
1820 ARec_Fx.ATTRIBUTE14 ,
1821 ARec_Fx.ATTRIBUTE15 ,
1822 FND_GLOBAL.conc_request_id ,
1823 FND_GLOBAL.prog_appl_id ,
1824 FND_GLOBAL.conc_program_id ,
1825 g_curr_date
1826 );
1827
1828 xtr_reval_process_p.xtr_ins_init_fv(ARec_Fx.COMPANY_CODE,p_deal_no,ARec_Fx.DEAL_TYPE,NULL);
1829
1830 if l_dual_user is not null then
1831 UPDATE xtr_deal_date_amounts
1832 SET dual_authorisation_by = l_dual_user,
1833 dual_authorisation_on = l_dual_date
1834 WHERE deal_number = p_deal_no;
1835
1836 UPDATE xtr_confirmation_details
1837 SET confirmation_validated_by = l_dual_user,
1838 confirmation_validated_on = l_dual_date
1839 WHERE deal_no = p_deal_no;
1840
1841 UPDATE xtr_deals
1842 SET dual_authorisation_on = l_dual_date
1843 WHERE deal_no = p_deal_no;
1844 end if;
1845
1846 END CREATE_FX_DEAL;
1847
1848 /*------------------------------------------------------------------------*/
1849 PROCEDURE CHECK_FOR_ERROR( p_user_deal_type IN VARCHAR2,
1850 l_err_code IN NUMBER,
1851 l_level IN VARCHAR2 ) is
1852 /*------------------------------------------------------------------------*/
1853
1854 begin
1855 declare
1856 new_error_code varchar2(30);
1857 Begin
1858 new_error_code := 'XTR_'|| to_char(l_err_code); -- XTR_886 Unable to find Spot Rate.
1859 if nvl(l_level,'X') = 'E' then
1860 xtr_import_deal_data.log_interface_errors(G_Fx_Main_Rec.External_Deal_Id, p_user_deal_type,
1861 'AmountC',new_error_code);
1862 end if;
1863 End;
1864 END CHECK_FOR_ERROR;
1865
1866 END XTR_FX_TRANSFERS_PKG;