[Home] [Help]
PACKAGE BODY: APPS.XTR_NI_TRANSFERS_PKG
Source
1 PACKAGE BODY XTR_NI_TRANSFERS_PKG AS
2 /* $Header: xtrimnib.pls 120.1 2005/06/29 10:06:24 csutaria noship $*/
3
4 /* Stub for consistency sake */
5 /*-------------------------------------------------------------------------------------*/
6 PROCEDURE TRANSFER_NI_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_NI_DEALS(ARec_Interface,user_error,mandatory_error,validation_error,limit_error,v_dummy);
15 END;
16
17 /*-------------------------------------------------------------------------------------*/
18 PROCEDURE TRANSFER_NI_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 v_limit_log_return NUMBER;
27 l_risk_code_holder XTR_DEALS_INTERFACE.cparty_code%TYPE;
28
29 BEGIN
30
31 -------------------------
32 -- Initialise Variables
33 -------------------------
34 g_user_id := fnd_global.user_id;
35 g_curr_date := trunc(sysdate);
36 g_ni_deal_subtype := null;
37 g_ni_deal_type := 'NI';
38 g_no_of_days := null;
39 g_year_basis := null;
40 g_num_parcels := 0;
41
42
43
44 --------------------------------------------------------------------------------------------------------
45 --* Perform the following to purge all the related data in the error table before processing the record
46 --------------------------------------------------------------------------------------------------------
47 delete from xtr_interface_errors
48 where external_deal_id = ARec_Interface.external_deal_id
49 and deal_type = ARec_Interface.deal_type;
50
51 ----------------------------------------------------------------------------------------------------
52 --* The following code checks if user has permissions to transfer the deal (company authorization)
53 ----------------------------------------------------------------------------------------------------
54 XTR_IMPORT_DEAL_DATA.CHECK_USER_AUTH(ARec_Interface.external_deal_id,
55 ARec_Interface.deal_type,
56 ARec_Interface.company_code,
57 user_error);
58
59
60 if (user_error <> TRUE) then
61
62 --------------------------------------------------------------------------------
63 --* The following code does mandatory field validation specific to the NI deals
64 --------------------------------------------------------------------------------
65 CHECK_MANDATORY_FIELDS(ARec_Interface, mandatory_error);
66
67
68 if (mandatory_error <> TRUE) then
69
70 --------------------------------------------------------------------------------------
71 --* The following code performs the business logic validation specific to the NI deals
72 --------------------------------------------------------------------------------------
73 VALIDATE_DEALS(ARec_Interface, validation_error);
74
75 if (validation_error <> TRUE) then
76
77 ------------------------------------------------------------------
78 --* Perform limit checks
79 ------------------------------------------------------------------
80
81 if g_ni_deal_subtype = 'ISSUE' then
82 l_risk_code_holder := G_Ni_Main_Rec.cparty_code;
83 else
84 l_risk_code_holder := G_Ni_Main_Rec.acceptor_code;
85 end if;
86
87 v_limit_log_return := XTR_LIMITS_P.LOG_FULL_LIMITS_CHECK (
88 G_Ni_Main_Rec.deal_no,
89 1, -- TRANSACTION_NUMBER
90 G_Ni_Main_Rec.company_code,
91 G_Ni_Main_Rec.deal_type,
92 G_Ni_Main_Rec.deal_subtype,
93 l_risk_code_holder,
94 G_Ni_Main_Rec.product_type,
95 G_Ni_Main_Rec.riskparty_limit_code,
96 G_Ni_Main_Rec.riskparty_code, -- limit_party
97 G_Ni_Main_Rec.maturity_date, -- amount_date
98 G_Ni_Main_Rec.maturity_balance_amount,
99 G_Ni_Main_Rec.dealer_code,
100 G_Ni_Main_Rec.currency);
101
102 If Nvl(ARec_Interface.override_limit,'N') = 'N' and v_limit_log_return <> 0 then
103 xtr_import_deal_data.log_interface_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
104 'OverrideLimit','XTR_LIMIT_EXCEEDED');
105 limit_error := TRUE;
106 else
107 limit_error := FALSE;
108
109 end if; /* If Limit needs to be checked */
110
111 end if; /* Validating various fields */
112
113 end if; /* Checking Mandatory values */
114
115 end if; /* Checking User Auth */
116
117 /*----------------------------------------------------------------------------------------------*/
118 /* If the process passed all the previous validation, it would be considered a valid deal entry */
119 /*----------------------------------------------------------------------------------------------*/
120 if user_error <> TRUE and mandatory_error <> TRUE and
121 limit_error <> TRUE and validation_error <> TRUE then
122
123 /*wdk: would like to do get deal_no here, and then calc trans and various inserts*/
124
125 CHECK_ACCRUAL_REVAL(ARec_Interface);
126
127 /*----------------------------------------------------*/
128 /* Call the insert procedure to insert into xtr_deals */
129 /*----------------------------------------------------*/
130 CREATE_NI_DEAL;
131
132 XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(G_Ni_Main_Rec.DEAL_NO,
133 1,
134 Fnd_Global.User_Id,
135 v_limit_log_return);
136
137 deal_num:=g_ni_main_rec.deal_no;
138
139 /*----------------------------------------------------------------------------------*/
140 /* Since the insert is done, we can now delete the rows from the interface tables. */
141 /*----------------------------------------------------------------------------------*/
142 delete from xtr_deals_interface
143 where external_deal_id = ARec_Interface.external_deal_id
144 and deal_type = ARec_Interface.deal_type;
145
146 delete from xtr_transactions_interface
147 where external_deal_id = ARec_Interface.external_deal_id
148 and deal_type = ARec_Interface.deal_type;
149
150 else
151
152 update xtr_deals_interface
153 set load_status_code = 'ERROR',
154 last_update_date = G_Curr_Date,
155 Last_Updated_by = g_user_id
156 where external_deal_id = ARec_Interface.external_deal_id
157 and deal_type = ARec_Interface.deal_type;
158
159 end if;
160
161 end TRANSFER_NI_DEALS;
162
163
164 /*------------------------------------------------------------------------------*/
165 /* The following code implements the CHECK_MANDATORY_FIELDS process */
166 /*------------------------------------------------------------------------------*/
167 PROCEDURE CHECK_MANDATORY_FIELDS(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
168 error OUT NOCOPY BOOLEAN) is
169 /*------------------------------------------------------------------------------*/
170 PROCEDURE CHECK_MANDATORY_TR_FIELDS IS
171
172 CURSOR GET_TRANSACTIONS IS
173 SELECT *
174 FROM XTR_TRANSACTIONS_INTERFACE
175 WHERE EXTERNAL_DEAL_ID = ARec_Interface.external_deal_id
176 AND DEAL_TYPE = ARec_Interface.deal_type;
177
178 l_has_transactions boolean:=false;
179
180 BEGIN
181
182 FOR l_transaction IN GET_TRANSACTIONS LOOP
183
184 l_has_transactions:=true;
185
186 if l_transaction.amount_a is null then
187 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,
188 ARec_Interface.Deal_Type,
189 'AmountA',
190 'XTR_MANDATORY',
191 l_transaction.transaction_no);
192 error := TRUE;
193 end if;
194
195 if l_transaction.amount_b is null and l_transaction.amount_c is null then
196 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,
197 ARec_Interface.Deal_Type,
198 'AmountB',
199 'XTR_MANDATORY_FACEVALUE',
200 l_transaction.transaction_no);
201 error := TRUE;
202 end if;
203
204 if l_transaction.option_a is null then
205 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,
206 ARec_Interface.Deal_Type,
207 'OptionA',
208 'XTR_MANDATORY',
209 l_transaction.transaction_no);
210 error := TRUE;
211 end if;
212
213
214 END LOOP;
215
216 if not(l_has_transactions) then
217 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,
218 ARec_Interface.Deal_Type,
219 'ExternalDealId',
220 'XTR_MANDATORY_TRANSACTIONS');
221 error := TRUE;
222 end if;
223
224 END CHECK_MANDATORY_TR_FIELDS;
225
226 BEGIN
227
228 error := FALSE; /* Defaulting it to No errors */
229
230 if ARec_Interface.dealer_code is null then
231 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
232 'DealerCode','XTR_MANDATORY');
233 error := TRUE;
234 end if;
235
236 if ARec_Interface.company_code is null then
237 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
238 'CompanyCode','XTR_MANDATORY');
239 error := TRUE;
240 end if;
241
242 if ARec_Interface.portfolio_code is null then
243 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
244 'PortfolioCode','XTR_MANDATORY');
245 error := TRUE;
246 end if;
247
248 if ARec_Interface.year_calc_type is null then
249 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
250 'YearCalcType','XTR_MANDATORY');
251 error := TRUE;
252 end if;
253
254 if ARec_Interface.basis_type is null then
255 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
256 'BasisType','XTR_MANDATORY');
257 error := TRUE;
258 end if;
259
260 if ARec_Interface.rounding_type is null then
261 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
262 'RoundingType','XTR_MANDATORY');
263 error := TRUE;
264 end if;
265
266 if ARec_Interface.day_count_type is null then
267 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
268 'DayCountType','XTR_MANDATORY');
269 error := TRUE;
270 end if;
271
272 if ARec_Interface.date_a is null then
273 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
274 'DateA','XTR_MANDATORY');
275 error := TRUE;
276 end if;
277
278 if ARec_Interface.cparty_code is null then
279 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
280 'CpartyCode','XTR_MANDATORY');
281 error := TRUE;
282 end if;
283
284 if ARec_Interface.deal_subtype is null then
285 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
286 'DealSubtype','XTR_MANDATORY');
287 error := TRUE;
288 end if;
289
290 if ARec_Interface.product_type is null then
291 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
292 'ProductType','XTR_MANDATORY');
293 error := TRUE;
294 end if;
295
296 if ARec_Interface.date_b is null then
297 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
298 'DateB','XTR_MANDATORY');
299 error := TRUE;
300 end if;
301
302 if ARec_Interface.date_c is null then
303 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
304 'DateC','XTR_MANDATORY');
305 error := TRUE;
306 end if;
307
308 if ARec_Interface.currency_a is null then
309 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
310 'CurrencyA','XTR_MANDATORY');
311 error := TRUE;
312 end if;
313
314 if ARec_Interface.rate_a is null then
315 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
316 'RateA','XTR_MANDATORY');
317 error := TRUE;
318 end if;
319
320 if ARec_Interface.account_no_a is null then
321 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
322 'AccountNoA','XTR_MANDATORY');
323 error := TRUE;
324 end if;
325
326 if ARec_Interface.brokerage_code is null and
327 (ARec_Interface.rate_c is not null or ARec_Interface.amount_c is not null) then
328 xtr_import_deal_data.log_interface_errors(ARec_Interface.External_Deal_Id,ARec_Interface.Deal_Type,
329 'BrokerageCode','XTR_MANDATORY_BROKERAGE');
330 error := TRUE;
331 end if;
332
333 CHECK_MANDATORY_TR_FIELDS;
334
335 END CHECK_MANDATORY_FIELDS;
336
337
338
339 /*------------------------------------------------------------------------*/
340 /* The following code implements the VALIDATE_DEALS process */
341 /*------------------------------------------------------------------------*/
342 PROCEDURE VALIDATE_DEALS(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
343 error OUT NOCOPY BOOLEAN) is
344 /*------------------------------------------------------------------------*/
345 rate_error boolean := FALSE;
346 validity_error boolean := FALSE;
347 BEGIN
348
349 CHECK_VALIDITY(ARec_Interface, validity_error);
350
351 IF validity_error <> TRUE then
352
353 COPY_FROM_INTERFACE_TO_NI(ARec_Interface);
354
355 CALC_RATES(ARec_Interface, rate_error);
356
357 if (rate_error <> TRUE) then
358 error := FALSE;
359 else
360 error := TRUE;
361 end if;
362 ELSE
363 error := TRUE;
364 END IF;
365
366 END VALIDATE_DEALS;
367
368
369
370 /*-------------------------------------------------------------------------*/
371 PROCEDURE CHECK_VALIDITY(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
372 error OUT NOCOPY BOOLEAN) is
373 /*-------------------------------------------------------------------------*/
374
375
376 l_error number := 0;
377 l_err_segment varchar2(30);
378 l_err_cparty boolean := FALSE;
379 l_err_deal_subtype boolean := FALSE;
380 l_err_deal_date boolean := FALSE;
381 l_err_currency_a boolean := FALSE;
382 l_err_brokerage_code boolean := FALSE;
383
384
385 PROCEDURE VALIDATE_TRANSACTIONS IS
386
387 CURSOR GET_TRANSACTIONS IS
388 SELECT *
389 FROM XTR_TRANSACTIONS_INTERFACE
390 WHERE EXTERNAL_DEAL_ID = ARec_Interface.external_deal_id
391 AND DEAL_TYPE = ARec_Interface.deal_type;
392
393 BEGIN
394
395 FOR l_transaction IN GET_TRANSACTIONS LOOP
396
397 if (l_transaction.amount_a<=0) then
398 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
399 ARec_Interface.Deal_Type,
400 'AmountA',
401 'XTR_VALUE_GE_ZERO',
402 l_transaction.transaction_no);
403 l_error := l_error +1;
404 end if;
405
406 if ( l_transaction.amount_b<0) then
407 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
408 ARec_Interface.Deal_Type,
409 'AmountB',
410 'XTR_VALUE_GE_ZERO',
411 l_transaction.transaction_no);
412 l_error := l_error +1;
413 end if;
414
415 if ( l_transaction.amount_c<0) then
416 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
417 ARec_Interface.Deal_Type,
418 'AmountC',
419 'XTR_VALUE_GE_ZERO',
420 l_transaction.transaction_no);
421 l_error := l_error +1;
422 elsif not ( val_consideration(l_transaction.amount_b,l_transaction.amount_c,ARec_Interface.basis_type)) then
423 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
424 ARec_Interface.Deal_Type,
425 'AmountC',
426 'XTR_INV_CONSIDERATION',
427 l_transaction.transaction_no);
428 l_error := l_error +1;
429 end if;
430
431
432 -- Interest overide is validated later
433
434 if l_err_deal_subtype <> TRUE then
435
436 if not ( val_serial_number(l_transaction.value_a,l_transaction.amount_a)) then
437 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
438 ARec_Interface.Deal_Type,
439 'ValueA',
440 'XTR_INV_SERIAL_NUMBER',
441 l_transaction.transaction_no);
442 l_error := l_error +1;
443 end if;
444 end if;
445
446 if ( l_transaction.option_a not in ('Y','N')) then
447 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
448 ARec_Interface.Deal_Type,
449 'OptionA',
450 'XTR_INV_AVAILABLE',
451 l_transaction.transaction_no);
452 l_error := l_error +1;
453 end if;
454
455
456 /*-------------------------------------------------------------------------------*/
457 /* Transaction Flexfields Validation */
458 /*-------------------------------------------------------------------------------*/
459
460 if not ( xtr_import_deal_data.val_transaction_desc_flex(l_transaction,'XTR_RT_DESC_FLEX',l_err_segment)) then
461 l_error := l_error +1;
462 if l_err_segment is not null and l_err_segment = 'Attribute16' then
463 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
464 ARec_Interface.Deal_Type,
465 l_err_segment,
466 'XTR_INV_DESC_FLEX_API',
467 l_transaction.transaction_no);
468 elsif l_err_segment is not null and l_err_segment = 'AttributeCategory' then
469 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
470 ARec_Interface.Deal_Type,
471 l_err_segment,
472 'XTR_INV_DESC_FLEX_CONTEXT',
473 l_transaction.transaction_no);
474 else
475 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
476 ARec_Interface.Deal_Type,
477 l_err_segment,
478 'XTR_INV_DESC_FLEX',
479 l_transaction.transaction_no);
480 end if;
481 end if;
482
483
484 END LOOP;
485
486 END VALIDATE_TRANSACTIONS;
487
488 PROCEDURE GET_DAYS_RUN IS
489 BEGIN
490 if (ARec_interface.date_b <= ARec_Interface.date_c) then
491 XTR_CALC_P.CALC_DAYS_RUN(ARec_Interface.date_b,
492 ARec_Interface.date_c,
493 ARec_Interface.year_calc_type,
494 g_no_of_days,
495 g_year_basis,
496 null,
497 ARec_Interface.day_count_type,
498 'Y');
499 end if;
500 END GET_DAYS_RUN;
501
502 BEGIN
503
504 /* This procedure will include all the column validations */
505 if not ( val_deal_subtype(ARec_Interface.deal_subtype)) then
506 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
507 ARec_Interface.Deal_Type,
508 'DealSubtype',
509 'XTR_INV_DEAL_SUBTYPE');
510 l_error := l_error +1;
511 l_err_deal_subtype := TRUE;
512 end if;
513
514 if not ( val_cparty_code(ARec_Interface.company_code, ARec_Interface.cparty_code)) then
515 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
516 ARec_Interface.Deal_Type,
517 'CpartyCode',
518 'XTR_INV_CPARTY_CODE');
519 l_error := l_error +1;
520 l_err_cparty := TRUE;
521 end if;
522
523 if l_err_cparty <> TRUE then
524
525 if not ( val_portfolio_code(ARec_Interface.company_code,
526 ARec_Interface.cparty_code,
527 ARec_Interface.portfolio_code)) then
528 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
529 ARec_Interface.Deal_Type,
530 'PortfolioCode',
531 'XTR_INV_PORT_CODE');
532 l_error := l_error +1;
533 end if;
534 end if;
535 /*
536
537 if not ( val_risk_party_code(ARec_Interface.acceptor_code)) then
538 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
539 ARec_Interface.Deal_Type,
540 'AcceptorCode',
541 'XTR_INV_RISK_PARTY_CODE');
542 l_error := l_error +1;
543 end if;
544
545 if not ( val_risk_party_code(ARec_Interface.drawer_code)) then
546 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
547 ARec_Interface.Deal_Type,
548 'DrawerCode',
549 'XTR_INV_RISK_PARTY_CODE');
550 l_error := l_error +1;
551 end if;
552
553 if not ( val_risk_party_code(ARec_Interface.endorser_code)) then
554 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
555 ARec_Interface.Deal_Type,
556 'EndorserCode',
557 'XTR_INV_RISK_PARTY_CODE');
558 l_error := l_error +1;
559 end if;
560 */
561 if l_err_deal_subtype <> TRUE then
562
563 if not ( val_limit_code(ARec_Interface.limit_code,
564 ARec_Interface.company_code,
565 ARec_Interface.acceptor_code,
566 ARec_Interface.endorser_code,
567 ARec_Interface.drawer_code)) then
568 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
569 ARec_Interface.Deal_Type,
570 'LimitCode',
571 'XTR_INV_LIMIT_CODE');
572 l_error := l_error +1;
573 end if;
574 end if;
575
576 if not (val_currencies(ARec_Interface.currency_a)) then
577 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
578 ARec_Interface.Deal_Type,
579 'CurrencyA',
580 'XTR_INV_CURR');
581 l_error := l_error +1;
582 l_err_currency_a := TRUE;
583 end if;
584
585 if l_err_deal_subtype <> TRUE then
586 if not ( val_product_type(ARec_Interface.product_type,g_ni_deal_subtype)) then
587 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
588 ARec_Interface.Deal_Type,
589 'ProductType',
590 'XTR_INV_PRODUCT_TYPE');
591 l_error := l_error +1;
592 end if;
593 end if;
594
595 if not ( val_brokerage_code(ARec_Interface.brokerage_code)) then
596 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
597 ARec_Interface.Deal_Type,
598 'BrokerageCode',
599 'XTR_INV_BROKERAGE_CODE');
600 l_error := l_error +1;
601 l_err_brokerage_code := TRUE;
602 end if;
603
604 if l_err_cparty <> TRUE and l_err_currency_a <> TRUE then
605 if not ( val_cparty_ref(ARec_Interface.cparty_account_no,
606 ARec_Interface.cparty_ref,
607 ARec_Interface.cparty_code,
608 ARec_Interface.currency_a)) then
609 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
610 ARec_Interface.Deal_Type,
611 'CpartyAccountNo', -- CE BANK MIGRATION
612 'XTR_INV_CPARTY_REF');
613 l_error := l_error +1;
614 end if;
615 end if;
616
617 if not ( val_dealer_code(ARec_Interface.dealer_code)) then
618 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
619 ARec_Interface.Deal_Type,
620 'DealerCode',
621 'XTR_INV_DEALER_CODE');
622 l_error := l_error +1;
623 end if;
624
625 if l_err_currency_a <> TRUE then
626 if not ( val_comp_acct_no(ARec_Interface.company_code,
627 ARec_Interface.currency_a,
628 ARec_Interface.account_no_a)) then
629 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
630 ARec_Interface.Deal_Type,
631 'AccountNoA',
632 'XTR_INV_COMP_ACCT_NO');
633 l_error := l_error +1;
634 end if;
635 end if;
636
637 if ARec_Interface.dual_authorization_by is not null and
638 not ( val_dealer_code(ARec_Interface.dual_authorization_by)) then
639 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
640 ARec_Interface.Deal_Type,
641 'DualAuthorizationBy',
642 'XTR_INV_DUAL_AUTH_BY');
643 l_error := l_error +1;
644 end if;
645
646 if not ( val_deal_date(ARec_Interface.date_a)) then
647 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
648 ARec_Interface.Deal_Type,
649 'DateA',
650 'XTR_INV_DEAL_DATE');
651 l_error := l_error +1;
652 l_err_deal_date := TRUE;
653 end if;
654
655 if not ( val_client_code(ARec_Interface.client_code)) then
656 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
657 ARec_Interface.Deal_Type,
658 'ClientCode',
659 'XTR_INV_CLIENT_CODE');
660 l_error := l_error +1;
661 end if;
662
663 if not ( val_deal_linking_code(ARec_Interface.deal_linking_code)) then
664 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
665 ARec_Interface.Deal_Type,
666 'DealLinkingCode',
667 'XTR_INV_LINKING_CODE');
668 l_error := l_error +1;
669 end if;
670
671
672 if not ( val_pricing_model(ARec_Interface.pricing_model)) then
673 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
674 ARec_Interface.Deal_Type,
675 'PricingModel',
676 'XTR_INV_PRICING_MODEL');
677 l_error := l_error +1;
678 end if;
679
680 if not ( val_market_data_set(ARec_Interface.market_data_set)) then
681 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
682 ARec_Interface.Deal_Type,
683 'MarketDataSet',
684 'XTR_INV_MKT_DATA_SET');
685 l_error := l_error +1;
686 end if;
687
688
689 /*WDK: new message */
690
691
692 if not ( val_rounding_type(ARec_Interface.rounding_type)) then
693 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
694 ARec_Interface.Deal_Type,
695 'RoundingType',
696 'XTR_INV_ROUNDING_TYPE');
697 l_error := l_error +1;
698 end if;
699
700 if not ( val_day_count_type(ARec_Interface.day_count_type)) then
701 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
702 ARec_Interface.Deal_Type,
703 'DayCountType',
704 'XTR_INV_DAY_COUNT_TYPE');
705 l_error := l_error +1;
706 end if;
707
708 if not ( val_start_date(ARec_Interface.date_a,
709 ARec_Interface.date_b)) then
710 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
711 ARec_Interface.Deal_Type,
712 'DateB',
713 'XTR_INV_START_DATE');
714 l_error := l_error +1;
715 end if;
716
717 if not ( val_maturity_date(ARec_Interface.date_b,
718 ARec_Interface.date_c)) then
719 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
720 ARec_Interface.Deal_Type,
721 'DateC',
722 'XTR_INV_MATURITY_DATE');
723 l_error := l_error +1;
724 end if;
725
726 if not ( val_year_calc_type(ARec_Interface.year_calc_type)) then
727 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
728 ARec_Interface.Deal_Type,
729 'YearCalcType',
730 'XTR_INV_DAY_COUNT_BASIS');
731 l_error := l_error +1;
732 end if;
733
734 if not ( val_basis_type(ARec_Interface.basis_type)) then
735 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
736 ARec_Interface.Deal_Type,
737 'BasisType',
738 'XTR_INV_BASIS_TYPE');
739 l_error := l_error +1;
740 end if;
741
742 GET_DAYS_RUN;
743
744 if not ( val_trans_rate(ARec_Interface.rate_a,
745 ARec_Interface.currency_a)) then
746 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
747 ARec_Interface.Deal_Type,
748 'RateA',
749 'XTR_VALUE_GE_ZERO'); -- bug 2798328
750 -- 'XTR_INV_TRANS_RATE');
751 l_error := l_error +1;
752 end if;
753
754 if not ( val_client_settle(ARec_Interface.settle_action_reqd)) then
755 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
756 ARec_Interface.Deal_Type,
757 'SettleActionReqd',
758 'XTR_INV_CLIENT_SETTLE');
759 l_error := l_error +1;
760 end if;
761
762 if not ( val_year_calc_day_count_combo(ARec_Interface.year_calc_type,
763 ARec_Interface.day_count_type)) then
764 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
765 ARec_Interface.Deal_Type,
766 'DayCountType',
767 'XTR_CHK_30_BOTH');
768 l_error := l_error +1;
769 end if;
770
771 if l_err_deal_subtype <> TRUE then
772 if not ( val_principal_tax_code(ARec_Interface.schedule_a)) then
773 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
774 ARec_Interface.Deal_Type,
775 'ScheduleA',
776 'XTR_INV_PRINCIPAL_TAX_CODE');
777 l_error := l_error +1;
778 end if;
779
780 if not ( val_interest_tax_code(ARec_Interface.schedule_b)) then
781 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
782 ARec_Interface.Deal_Type,
783 'ScheduleB',
784 'XTR_INV_INTEREST_TAX_CODE');
785 l_error := l_error +1;
786 end if;
787 end if;
788
789 if ( ARec_Interface.option_a not in ('Y','N')) then
790 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
791 ARec_Interface.Deal_Type,
792 'OptionA',
793 'XTR_INV_DEFAULT_TAX_CODE');
794 l_error := l_error +1;
795 end if;
796
797
798
799 VALIDATE_TRANSACTIONS;
800
801
802
803 /*-------------------------------------------------------------------------------*/
804 /* Flexfields Validation */
805 /*-------------------------------------------------------------------------------*/
806
807 if not ( xtr_import_deal_data.val_desc_flex(ARec_Interface,'XTR_DEALS_DESC_FLEX',l_err_segment)) then
808 l_error := l_error +1;
809 if l_err_segment is not null and l_err_segment = 'Attribute16' then
810 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
811 ARec_Interface.Deal_Type,
812 l_err_segment,
813 'XTR_INV_DESC_FLEX_API');
814 elsif l_err_segment is not null and l_err_segment = 'AttributeCategory' then
815 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
816 ARec_Interface.Deal_Type,
817 l_err_segment,
818 'XTR_INV_DESC_FLEX_CONTEXT');
819 else
820 xtr_import_deal_data.log_interface_errors( ARec_Interface.External_Deal_Id,
821 ARec_Interface.Deal_Type,
822 l_err_segment,
823 'XTR_INV_DESC_FLEX');
824 end if;
825 end if;
826
827 if l_error > 0 then
828 error := TRUE;
829 else
830 error := FALSE;
831 end if;
832
833 END CHECK_VALIDITY;
834
835 /*--------------------------------------------------------------------*/
836 FUNCTION val_deal_date (p_date_a IN date) return BOOLEAN is
837 /*--------------------------------------------------------------------*/
838 BEGIN
839 return (p_date_a<=g_curr_date);
840 END val_deal_date;
841
842 /*--------------------------------------------------------------------*/
843 FUNCTION val_start_date (p_date_a IN date,
844 p_date_b IN date) return BOOLEAN is
845 /*--------------------------------------------------------------------*/
846 BEGIN
847 return (p_date_a<=p_date_b);
848 END val_start_date;
849
850 /*--------------------------------------------------------------------*/
851 FUNCTION val_maturity_date (p_date_a IN date,
852 p_date_b IN date) return BOOLEAN is
853 /*--------------------------------------------------------------------*/
854 BEGIN
855 return (p_date_a<=p_date_b);
856 END val_maturity_date;
857
858 /*--------------------------------------------------------------------*/
859 FUNCTION val_client_code(p_client_code IN varchar2) return BOOLEAN is
860 /*--------------------------------------------------------------------*/
861 l_count NUMBER;
862 BEGIN
863 IF (p_client_code is null) then
864 return true;
865 end if;
866
867 select count(*)
868 into l_count
869 from xtr_parties_v
870 where party_type = 'CP'
871 and party_category = 'CL'
872 and party_code = p_client_code;
873
874 return (l_count>0);
875 END val_client_code;
876
877 /*----------------------------------------------------------------------------*/
878 FUNCTION val_portfolio_code(p_company_code IN varchar2,
879 p_cparty_code IN varchar2,
880 p_portfolio_code IN varchar2) return BOOLEAN is
881 /*----------------------------------------------------------------------------*/
882 l_count NUMBER;
883 BEGIN
884 select count(*)
885 into l_count
886 from xtr_portfolios_v
887 where company_code = p_company_code
888 and (external_party is null or external_party = p_cparty_code)
889 and portfolio = p_portfolio_code;
890
891 return (l_count>0);
892
893 END val_portfolio_code;
894
895 /*----------------------------------------------------------------------------*/
896 FUNCTION val_currencies ( p_currency IN varchar2) return BOOLEAN is
897 /*----------------------------------------------------------------------------*/
898 l_count NUMBER;
899 BEGIN
900 Select count(*)
901 Into l_count
902 from xtr_master_currencies_v
903 where nvl(authorised,'N') = 'Y'
904 And currency = p_currency;
905
906 return (l_count>0);
907 END val_currencies;
908
909 /*----------------------------------------------------------------------------*/
910 FUNCTION val_comp_acct_no(p_company_code IN varchar2,
911 p_currency IN varchar2,
912 p_account_no IN varchar2) return BOOLEAN is
913 /*----------------------------------------------------------------------------*/
914 l_count NUMBER;
915 BEGIN
916 select count(*)
917 into l_count
918 from xtr_bank_accounts_v
919 where party_code = p_company_code
920 and currency = p_currency
921 and nvl(setoff_account_yn,'N') = 'N'
922 and account_number = p_account_no;
923
924 return (l_count>0);
925 END val_comp_acct_no;
926
927
928 /*----------------------------------------------------------------------------*/
929 FUNCTION val_cparty_ref( p_cparty_account_no IN varchar2,
930 p_cparty_ref IN varchar2,
931 p_cparty_code IN varchar2,
932 p_currency IN varchar2) return BOOLEAN is
933 /*----------------------------------------------------------------------------*/
934 l_count NUMBER;
935 BEGIN
936 IF (p_cparty_ref is null and p_cparty_account_no is null) then
937 return true;
938 end if;
939 select count(*)
940 into l_count
941 from xtr_bank_accounts_v
942 where currency = p_currency
943 and party_code = p_cparty_code
944 and account_number = p_cparty_account_no
945 and nvl(authorised,'N') = 'Y'
946 and bank_short_code = p_cparty_ref;
947
948 return (l_count>0);
949 END val_cparty_ref;
950
951
952 /*--------------------------------------------------------------------------------*/
953 FUNCTION val_deal_linking_code( p_deal_linking_code IN varchar2) return BOOLEAN is
954 /*--------------------------------------------------------------------------------*/
955 l_count NUMBER;
956 BEGIN
957 IF (p_deal_linking_code is null) then
958 return true;
959 end if;
960 select count(*)
961 into l_count
962 from xtr_deal_linking_v
963 where deal_linking_code = p_deal_linking_code;
964
965 return (l_count>0);
966 END val_deal_linking_code;
967
968 /*--------------------------------------------------------------------------------*/
969 FUNCTION val_brokerage_code ( p_brokerage_code IN varchar2) return BOOLEAN is
970 /*--------------------------------------------------------------------------------*/
971 l_count NUMBER;
972 BEGIN
973 IF (p_brokerage_code is null) then
974 return true;
975 end if;
976
977 select count(*)
978 into l_count
979 from xtr_tax_brokerage_setup_v a, xtr_deduction_calcs_v b
980 where a.reference_code = p_brokerage_code
981 and a.deal_type = 'NI'
982 and a.deduction_type = 'B'
983 and a.deal_type = b.deal_type
984 and a.calc_type = b.calc_type
985 and nvl(a.authorised,'N') = 'Y';
986
987 return (l_count>0);
988 END val_brokerage_code;
989
990
991 /*--------------------------------------------------------------------------------*/
992 FUNCTION val_dealer_code(p_dealer_code IN VARCHAR2) return BOOLEAN is
993 /*--------------------------------------------------------------------------------*/
994 l_count NUMBER;
995 BEGIN
996 select count(*)
997 into l_count
998 from xtr_dealer_codes_v
999 where dealer_code = p_dealer_code;
1000
1001 return (l_count>0);
1002 END val_dealer_code;
1003
1004 /*--------------------------------------------------------------------------------*/
1005 FUNCTION val_cparty_code(p_company_code IN VARCHAR2,
1006 p_cparty_code IN VARCHAR2) return BOOLEAN is
1007 /*--------------------------------------------------------------------------------*/
1008 l_count NUMBER;
1009 BEGIN
1010 select count(*)
1011 into l_count
1012 from xtr_party_info_v
1013 where party_type in ('CP','C')
1014 and ((party_type = 'CP' and mm_cparty='Y')
1015 or party_type = 'C')
1016 and party_code = p_cparty_code
1017 and party_code <> p_company_code
1018 and nvl(authorised,'N') = 'Y';
1019
1020 return (l_count>0);
1021 END val_cparty_code;
1022
1023
1024 /*--------------------------------------------------------------------------------*/
1025 FUNCTION val_deal_subtype(p_user_deal_subtype IN VARCHAR2) return BOOLEAN is
1026 /*--------------------------------------------------------------------------------*/
1027 l_count NUMBER;
1028 cursor get_deal_subtype is
1029 select deal_subtype
1030 from xtr_deal_subtypes
1031 where deal_type = 'NI'
1032 and user_deal_subtype = p_user_deal_subtype
1033 and nvl(authorised,'N') = 'Y'
1034 and rownum = 1;
1035 BEGIN
1036 open get_deal_subtype;
1037 fetch get_deal_subtype into g_ni_deal_subtype;
1038 if get_deal_subtype%NOTFOUND then
1039 close get_deal_subtype;
1040 return false;
1041 end if;
1042 close get_deal_subtype;
1043 ------------------------------------------------------------------------------------------------------
1044 --* Note : Deal_subtype column in the view is actually referring to the user_deal_subtype of the table.
1045 ------------------------------------------------------------------------------------------------------
1046 select count(*)
1047 into l_count
1048 from xtr_auth_deal_subtypes_v a,
1049 xtr_deal_subtypes b
1050 where a.deal_type = 'NI'
1051 and a.deal_type = b.deal_type
1052 and a.deal_subtype = b.user_deal_subtype
1053 and b.deal_subtype in ('BUY','ISSUE')
1054 and b.deal_subtype = g_ni_deal_subtype;
1055
1056 return (l_count>0);
1057 END val_deal_subtype;
1058
1059 /*--------------------------------------------------------------------------------*/
1060 FUNCTION val_product_type(p_product_type IN VARCHAR2,
1061 p_deal_subtype IN VARCHAR2) return BOOLEAN is
1062 /*--------------------------------------------------------------------------------*/
1063 /* p_deal_subtype is the system deal_subtype not the user deal subtype */
1064 l_count Number;
1065 BEGIN
1066
1067 select count(*)
1068 into l_count
1069 from xtr_product_types_v
1070 where deal_type = 'NI'
1071 and product_auth = 'Y'
1072 and product_type = p_product_type
1073 and product_type in(select product_type
1074 from xtr_auth_product_types_v
1075 where deal_type='NI'
1076 and deal_subtype=p_deal_subtype);
1077
1078 return (l_count>0);
1079 END val_product_type;
1080
1081 /*--------------------------------------------------------------------------------*/
1082 FUNCTION val_pricing_model(p_pricing_model IN VARCHAR2) return BOOLEAN is
1083 /*--------------------------------------------------------------------------------*/
1084 l_count Number;
1085 BEGIN
1086 select count(*)
1087 into l_count
1088 from xtr_price_models
1089 where code = p_pricing_model
1090 and deal_type = 'NI'
1091 and nvl(authorized,'N') = 'Y';
1092
1093 return (l_count>0);
1094 END val_pricing_model;
1095
1096 /*--------------------------------------------------------------------------------*/
1097 FUNCTION val_market_data_set(p_market_data_set IN VARCHAR2) return BOOLEAN is
1098 /*--------------------------------------------------------------------------------*/
1099 l_count number;
1100 BEGIN
1101 select count(*)
1102 into l_count
1103 from xtr_rm_md_sets
1104 where set_code = p_market_data_set
1105 and nvl(authorized_yn,'N') = 'Y';
1106
1107 return (p_market_data_set is null or l_count>0);
1108 END val_market_data_set;
1109
1110 /*--------------------------------------------------------------------------------*/
1111 FUNCTION val_risk_party_code(p_party_code IN VARCHAR2) return BOOLEAN is
1112 /*--------------------------------------------------------------------------------*/
1113 /* Generic function to be used by Risk Party (Acceptor/Drawer/Endorser) */
1114 l_count number;
1115 BEGIN
1116 /* bug 2798328
1117 select count(*)
1118 into l_count
1119 from xtr_parties_v
1120 where party_code=p_party_code;
1121
1122 return (p_party_code is null or l_count>0);
1123 */
1124 return true;
1125 END val_risk_party_code;
1126
1127
1128 /*--------------------------------------------------------------------------------*/
1129 FUNCTION val_limit_code(p_limit_code IN VARCHAR2,
1130 p_company_code IN VARCHAR2,
1131 p_acceptor_code IN VARCHAR2,
1132 p_endorser_code IN VARCHAR2,
1133 p_drawer_code IN VARCHAR2) return BOOLEAN is
1134 /*--------------------------------------------------------------------------------*/
1135 l_count number;
1136
1137 BEGIN
1138 select count(*)
1139 into l_count
1140 from xtr_counterparty_limits_v a,
1141 xtr_limit_types_v b
1142 where a.company_code = p_company_code
1143 and (a.cparty_code = p_acceptor_code or a.cparty_code = p_endorser_code or a.cparty_code = p_drawer_code)
1144 and a.limit_code <> 'SETTLE'
1145 and a.limit_type = b.limit_type
1146 and ((fx_invest_fund_type in ('X','I') and g_ni_deal_subtype in ('BUY','COVER')) or
1147 (fx_invest_fund_type in ('X','F') and g_ni_deal_subtype in ('SELL','SHORT','ISSUE')))
1148 and a.authorised='Y' and nvl(a.expiry_date,sysdate+1)>sysdate
1149 and a.limit_code = p_limit_code;
1150
1151 return (p_limit_code is null or l_count>0);
1152 END val_limit_code;
1153
1154
1155 /*--------------------------------------------------------------------------------*/
1156 FUNCTION val_rounding_type(p_rounding_type IN VARCHAR2) return BOOLEAN is
1157 /*--------------------------------------------------------------------------------*/
1158 l_count number;
1159
1160 BEGIN
1161 select count(*)
1162 into l_count
1163 from fnd_lookups
1164 where lookup_type='XTR_ROUNDING_TYPE'
1165 and lookup_code=p_rounding_type;
1166
1167 return (l_count>0);
1168 END val_rounding_type;
1169
1170 /*--------------------------------------------------------------------------------*/
1171 FUNCTION val_day_count_type(p_day_count_type IN VARCHAR2) return BOOLEAN is
1172 /*--------------------------------------------------------------------------------*/
1173 l_count number;
1174
1175 BEGIN
1176 select count(*)
1177 into l_count
1178 from fnd_lookups
1179 where lookup_type='XTR_DAY_COUNT_TYPE'
1180 and lookup_code=p_day_count_type;
1181
1182 return (l_count>0);
1183 END val_day_count_type;
1184
1185 /*--------------------------------------------------------------------------------*/
1186 FUNCTION val_year_calc_type(p_year_calc_type IN VARCHAR2) return BOOLEAN is
1187 /*--------------------------------------------------------------------------------*/
1188 l_count number;
1189
1190 BEGIN
1191 select count(*)
1192 into l_count
1193 from fnd_lookups
1194 where lookup_type='XTR_DAY_COUNT_BASIS'
1195 AND lookup_code IN ('30/','30E+/','30E/','ACTUAL/ACTUAL','ACTUAL360','ACTUAL365')
1196 and lookup_code=p_year_calc_type;
1197
1198 return (l_count>0);
1199 END val_year_calc_type;
1200
1201 /*--------------------------------------------------------------------------------*/
1202 FUNCTION val_year_calc_day_count_combo(p_year_calc_type IN VARCHAR2,
1203 p_day_count_type IN VARCHAR2) return BOOLEAN is
1204 /*--------------------------------------------------------------------------------*/
1205 BEGIN
1206 return not(substr(p_year_calc_type,1,2) = '30' and p_day_count_type = 'B');
1207
1208 END val_year_calc_day_count_combo;
1209
1210 /*--------------------------------------------------------------------------------*/
1211 FUNCTION val_basis_type(p_basis_type IN VARCHAR2) return BOOLEAN is
1212 /*--------------------------------------------------------------------------------*/
1213 l_count number;
1214
1215 BEGIN
1216 select count(*)
1217 into l_count
1218 from fnd_lookups
1219 where lookup_type='XTR_DISCOUNT_YIELD'
1220 and lookup_code=p_basis_type;
1221
1222 return (l_count>0);
1223 END val_basis_type;
1224
1225 /*--------------------------------------------------------------------------------*/
1226 FUNCTION val_trans_rate(p_trans_rate IN VARCHAR2,
1227 p_currency IN VARCHAR2) return BOOLEAN is
1228 /*--------------------------------------------------------------------------------*/
1229 BEGIN
1230 return (p_trans_rate>=0); -- bug 2798328
1231 END val_trans_rate;
1232
1233
1234 /*--------------------------------------------------------------------------------*/
1235 FUNCTION val_client_settle(p_client_settle IN VARCHAR2) return BOOLEAN is
1236 /*--------------------------------------------------------------------------------*/
1237 l_count number;
1238
1239 BEGIN
1240 select count(*)
1241 into l_count
1242 from fnd_lookups
1243 where lookup_type='XTR_PRINCIPAL_SETTLED_BY'
1244 and lookup_code=p_client_settle;
1245
1246 return (l_count>0);
1247 END val_client_settle;
1248
1249
1250 /*--------------------------------------------------------------------------------*/
1251 FUNCTION val_principal_tax_code(p_tax_code IN VARCHAR2) return BOOLEAN is
1252 /*--------------------------------------------------------------------------------*/
1253 l_count number;
1254
1255 BEGIN
1256 if (p_tax_code is null) then
1257 return true;
1258 end if;
1259 select count(*)
1260 into l_count
1261 from xtr_tax_brokerage_setup a,
1262 fnd_lookups lu, xtr_tax_deduction_calcs c
1263 where a.deal_type = 'NI'
1264 and lu.lookup_type='XTR_TAX_CALC_TYPES'
1265 and lu.lookup_code=a.calc_type
1266 and a.deduction_type = 'T'
1267 and a.deal_type = c.deal_type
1268 and a.calc_type = c.calc_type
1269 and a.tax_settle_method = c.tax_settle_method
1270 and nvl(a.authorised, 'N') = 'Y'
1271 and c.principal_or_income_tax='P'
1272 and c.deal_subtype=g_ni_deal_subtype
1273 and a.reference_code=p_tax_code;
1274
1275 return (l_count>0);
1276 END val_principal_tax_code;
1277
1278 /*--------------------------------------------------------------------------------*/
1279 FUNCTION val_interest_tax_code(p_tax_code IN VARCHAR2) return BOOLEAN is
1280 /*--------------------------------------------------------------------------------*/
1281 l_count number;
1282
1283 BEGIN
1284 if (p_tax_code is null) then
1285 return true;
1286 end if;
1287 select count(*)
1288 into l_count
1289 from xtr_tax_brokerage_setup a,
1290 fnd_lookups lu, xtr_tax_deduction_calcs c
1291 where a.deal_type = 'NI'
1292 and lu.lookup_type='XTR_TAX_CALC_TYPES'
1293 and lu.lookup_code=a.calc_type
1294 and a.deduction_type = 'T'
1295 and a.deal_type = c.deal_type
1296 and a.calc_type = c.calc_type
1297 and a.tax_settle_method = c.tax_settle_method
1298 and nvl(a.authorised, 'N') = 'Y'
1299 and c.principal_or_income_tax='I'
1300 and c.deal_subtype=g_ni_deal_subtype
1301 and a.reference_code=p_tax_code;
1302
1303 return (l_count>0);
1304 END val_interest_tax_code;
1305
1306
1307 /*--------------------------------------------------------------------------------*/
1308 FUNCTION val_interest (p_company_code IN varchar2,
1309 p_cparty_code IN varchar2,
1310 p_deal_type IN varchar2,
1311 p_currency_code IN varchar2,
1312 p_int_amount IN number,
1313 p_original_amount IN number) RETURN boolean IS
1314 /*--------------------------------------------------------------------------------*/
1315 /* -------------------------------------------------------------------
1316 Tolerance check logic:
1317 For each level, set the weight as the followings,
1318 company --- waight 8 (null is waight 0)
1319 counter party --- waight 4 (null is waight 0)
1320 deal type --- waight 2 (null is waight 0)
1321 currency code --- waight 1 (null is waight 0)
1322
1323 From the matched interest tolerances,
1324 this procedure use the tolerance values of the highest total waight
1325 --------------------------------------------------------------------*/
1326
1327 l_total_weight number :=0;
1328 l_limit_amount number;
1329 l_amount_percent number;
1330 l_currency varchar2(15);
1331 l_tol_id number; -- interest_tolerance_id for debug
1332
1333 l_max_weight number :=0;
1334 l_use_limit_amount number;
1335 l_use_amount_percent number;
1336 l_use_currency varchar2(15);
1337 l_use_tol_id number default null; -- interest_tolerance_id for debug
1338
1339 l_function_currency varchar2(15); -- company function currency
1340 l_rate number:=1; -- conversino rate
1341 l_int_amount number; -- The converted interest amount
1342 l_system_currency varchar2(15); -- System Functional Currency
1343
1344 l_allow_override VARCHAR2(1);
1345 cursor get_allow_override is
1346 Select allow_override
1347 From Xtr_Dealer_Codes
1348 Where user_id = fnd_global.user_id;
1349
1350 cursor chk_int_tol is
1351 select to_number(
1352 decode(it.company_code,null,0,8)
1353 + decode(it.cparty_code, null,0,4)
1354 + decode(it.deal_type, null,0,2)
1355 + decode(it.currency_code,null,0,1)) total_weight,
1356 it.limit_amount,
1357 it.amount_percent,
1358 it.currency_code,
1359 it.interest_tolerance_id
1360 from xtr_interest_tolerances it
1361 where it.company_code = p_company_code
1362 and nvl(it.cparty_code, p_cparty_code) = p_cparty_code
1363 and nvl(it.deal_type, p_deal_type) = p_deal_type
1364 and nvl(it.currency_code, p_currency_code) = p_currency_code
1365 and authorized='Y';
1366
1367 cursor sys_curr is
1368 select PARAM_VALUE
1369 from XTR_PRO_PARAM
1370 where param_name ='SYSTEM_FUNCTIONAL_CCY';
1371
1372 BEGIN
1373
1374 l_int_amount := p_int_amount;
1375
1376 if l_int_amount = p_original_amount then
1377 return true;
1378 end if;
1379
1380 open get_allow_override;
1381 fetch get_allow_override into l_allow_override;
1382 close get_allow_override;
1383
1384 if (NVL(l_allow_override,'N')<>'Y') then
1385 return false;
1386 end if;
1387
1388 open sys_curr;
1389 fetch sys_curr into l_system_currency;
1390 close sys_curr;
1391
1392 open chk_int_tol;
1393 LOOP
1394 fetch chk_int_tol into l_total_weight, l_limit_amount,l_amount_percent,l_currency,l_tol_id;
1395
1396 EXIT WHEN chk_int_tol%NOTFOUND;
1397 if l_total_weight > l_max_weight then
1398 l_max_weight :=l_total_weight;
1399 l_use_limit_amount := l_limit_amount;
1400 l_use_amount_percent := l_amount_percent;
1401 l_use_currency := l_currency;
1402 l_use_tol_id := l_tol_id;
1403 end if;
1404
1405 END LOOP;
1406
1407 close chk_int_tol;
1408
1409 -- Interest tolerances are not setup.
1410 if l_max_weight =0
1411 or (l_use_limit_amount is null and l_use_amount_percent is null)
1412 then
1413 return false;
1414 end if;
1415
1416 if l_use_currency is null then
1417
1418 select nvl(set_of_books_currency,l_system_currency)
1419 into l_function_currency
1420 from xtr_parties_v
1421 where party_code = p_company_code;
1422
1423 if l_function_currency <> p_currency_code then
1424 xtr_fps2_p.currency_cross_rate(l_function_currency,
1425 p_currency_code,
1426 l_rate);
1427
1428 if l_rate is null then
1429 return false;
1430 else
1431 if l_use_limit_amount is not null then
1432 l_use_limit_amount := l_use_limit_amount * l_rate;
1433 end if;
1434 end if;
1435
1436 end if;
1437 end if;
1438
1439 if l_use_limit_amount is not null then
1440 if abs(p_original_amount - l_int_amount) > l_use_limit_amount then
1441 return false;
1442 end if;
1443 end if;
1444
1445 if l_use_amount_percent is not null then
1446 if abs(p_original_amount - l_int_amount)
1447 > abs(p_original_amount*l_use_amount_percent/100) then
1448 return false;
1449 end if;
1450 end if;
1451
1452 return true;
1453 END val_interest;
1454
1455 /*--------------------------------------------------------------------------------*/
1456 FUNCTION val_consideration(p_face_value IN VARCHAR2,
1457 p_consideration IN VARCHAR2,
1458 p_basis_type IN VARCHAR2) return BOOLEAN is
1459 /*--------------------------------------------------------------------------------*/
1460
1461 BEGIN
1462 if ((p_face_value is not null and p_consideration is not null) or (p_consideration is not null and p_basis_type='DISCOUNT')) then
1463 return false;
1464 end if;
1465 return true;
1466 END val_consideration;
1467
1468 /*--------------------------------------------------------------------------------*/
1469 FUNCTION val_serial_number(p_serial_number IN VARCHAR2,
1470 p_parcel_count IN NUMBER) return BOOLEAN is
1471 /*--------------------------------------------------------------------------------*/
1472
1473 l_count NUMBER;
1474
1475 BEGIN
1476 if (p_serial_number is null) then
1477 return true;
1478 end if;
1479 if (g_ni_deal_subtype<>'ISSUE') then
1480 return false;
1481 end if;
1482 if (p_parcel_count > 1) then
1483 return false;
1484 end if;
1485 select count(*)
1486 into l_count
1487 from xtr_bill_bond_issues_v
1488 where ni_or_bond='NI'
1489 and issue_date is null
1490 and status is null
1491 and serial_number=p_serial_number;
1492
1493 return l_count>0;
1494 END val_serial_number;
1495
1496
1497 /* ------------- END FIELD VALIDATION SECTION ------------------------------------*/
1498
1499 PROCEDURE CHECK_ACCRUAL_REVAL(ARec_interface IN xtr_deals_interface%ROWTYPE) IS
1500
1501 /*--------------------------------------------------------------------------------*/
1502 PROCEDURE CHK_TRANS_RATE(p_trans_rate IN VARCHAR2,
1503 p_currency IN VARCHAR2) is
1504 /*--------------------------------------------------------------------------------*/
1505 l_count number;
1506 l_tolerance number;
1507 v_err_code NUMBER(8);
1508 v_err_level VARCHAR2(2) := ' ';
1509
1510 cursor get_tolerance is
1511 select tolerance
1512 from xtr_deal_subtypes
1513 where deal_type='NI'
1514 and deal_subtype=g_ni_deal_subtype;
1515
1516 BEGIN
1517 open get_tolerance;
1518 fetch get_tolerance into l_tolerance;
1519 if get_tolerance%NOTFOUND then
1520 close get_tolerance;
1521 XTR_IMPORT_DEAL_DATA.log_deal_warning(FND_MESSAGE.GET_STRING('XTR','XTR_598')); -- rate not within acceptable tolerance
1522 return;
1523 end if;
1524 close get_tolerance;
1525
1526 XTR_FPS3_P.CHK_TOLERANCE(p_trans_rate,
1527 p_currency,
1528 l_tolerance,
1529 nvl(g_no_of_days,30),
1530 '%',
1531 v_err_code,
1532 v_err_level);
1533
1534 if (v_err_code is not null) then
1535 XTR_IMPORT_DEAL_DATA.log_deal_warning(FND_MESSAGE.GET_STRING('XTR','XTR_598')); -- rate not within acceptable tolerance
1536 end if;
1537 END chk_trans_rate;
1538
1539
1540 PROCEDURE CHK_RVL_DATE(P_COMPANY IN VARCHAR2) IS
1541
1542 cursor cur_date is
1543 select PARAMETER_VALUE_CODE from XTR_COMPANY_PARAMETERS P
1544 where p.company_code = p_company
1545 and p.parameter_code = 'ACCNT_TSDTM';
1546
1547 l_date_type varchar2(30);
1548 l_date date;
1549
1550 cursor cur_reval(p_date in date) IS
1551 SELECT count(*)
1552 FROM
1553 xtr_batches b,xtr_batch_events e
1554 WHERE
1555 b.company_code = p_company
1556 AND b.batch_id = e.batch_id
1557 AND e.event_code = 'REVAL'
1558 AND b.period_end >= p_date;
1559
1560 l_dummy number;
1561 l_btn number;
1562 l_logMessage VARCHAR2(255);
1563
1564 BEGIN
1565 Open cur_date;
1566 Fetch cur_date into l_date_type;
1567 Close cur_date;
1568
1569 If l_date_type = 'TRADE' then
1570 l_date := ARec_interface.DATE_A; --DEAL_DATE
1571 Elsif l_date_type = 'SETTLE' then
1572 l_date := ARec_interface.DATE_B; --START_DATE
1573 End If;
1574
1575 Open cur_reval(l_date);
1576 Fetch cur_reval into l_dummy;
1577
1578 If nvl(l_dummy,0) > 0 then
1579 FND_MESSAGE.Set_Name ('XTR', 'XTR_IMPORT_BEFORE_REVAL');
1580 FND_MESSAGE.Set_Token ('DATE',l_date);
1581 l_logMessage:=FND_MESSAGE.Get;
1582 XTR_IMPORT_DEAL_DATA.log_deal_warning(l_logMessage);
1583 End If;
1584 END;
1585
1586 PROCEDURE CHK_ACCRL_DATE(P_COMPANY IN VARCHAR2) IS
1587
1588 cursor cur_date is
1589 select PARAMETER_VALUE_CODE from XTR_COMPANY_PARAMETERS P
1590 where p.company_code = p_company
1591 and p.parameter_code = 'ACCNT_TSDTM';
1592
1593 l_date_type varchar2(30);
1594 l_date date;
1595
1596 cursor cur_accrl(p_date in date) IS
1597 SELECT count(*)
1598 FROM
1599 xtr_batches b,xtr_batch_events e
1600 WHERE
1601 b.company_code = p_company
1602 AND b.batch_id = e.batch_id
1603 AND e.event_code = 'ACCRUAL'
1604 AND b.period_end >= p_date;
1605
1606 l_dummy number;
1607 l_btn number;
1608 l_logMessage VARCHAR2(255);
1609 BEGIN
1610 Open cur_date;
1611 Fetch cur_date into l_date_type;
1612 Close cur_date;
1613
1614 If l_date_type = 'TRADE' then
1615 l_date := ARec_interface.DATE_A; --DEAL_DATE
1616 Elsif l_date_type = 'SETTLE' then
1617 l_date := ARec_interface.DATE_B; --START_DATE
1618 End If;
1619
1620 Open cur_accrl(l_date);
1621 Fetch cur_accrl into l_dummy;
1622
1623 If nvl(l_dummy,0) > 0 then
1624 FND_MESSAGE.Set_Name ('XTR', 'XTR_IMPORT_BEFORE_ACCRUAL');
1625 FND_MESSAGE.Set_Token ('DATE',l_date);
1626 l_logMessage:=FND_MESSAGE.Get;
1627 XTR_IMPORT_DEAL_DATA.log_deal_warning(l_logMessage);
1628 End If;
1629 END;
1630
1631 BEGIN CHK_TRANS_RATE(ARec_interface.rate_a,ARec_interface.currency_a); -- bug 2798328
1632 CHK_ACCRL_DATE(ARec_interface.COMPANY_CODE);
1633 CHK_RVL_DATE(ARec_interface.COMPANY_CODE);
1634 END CHECK_ACCRUAL_REVAL;
1635
1636
1637 /* ------------- BEGIN DATA POPULATION SECTION -----------------------------------*/
1638
1639 /*------------------------------------------------------------------------------------------*/
1640 PROCEDURE copy_from_interface_to_ni(ARec_Interface IN xtr_deals_interface%rowtype ) is
1641 /*------------------------------------------------------------------------------------------*/
1642
1643 CURSOR GET_RISKPARTY_CODE IS
1644 select a.cparty_code
1645 from xtr_counterparty_limits_v a,
1646 xtr_limit_types_v b
1647 where a.company_code = G_Ni_Main_Rec.company_code
1648 and (a.cparty_code = G_Ni_Main_Rec.acceptor_code
1649 or a.cparty_code = G_Ni_Main_Rec.endorser_code
1650 or a.cparty_code = G_Ni_Main_Rec.drawer_code)
1651 and a.limit_code <> 'SETTLE'
1652 and a.limit_type = b.limit_type
1653 and ((fx_invest_fund_type in ('X','I') and G_Ni_Main_Rec.deal_subtype in ('BUY','COVER')) or
1654 (fx_invest_fund_type in ('X','F') and G_Ni_Main_Rec.deal_subtype in ('SELL','SHORT','ISSUE')))
1655 and a.authorised='Y' and nvl(a.expiry_date,sysdate+1)>sysdate
1656 and a.limit_code=G_Ni_Main_Rec.riskparty_limit_code
1657 and rownum=1;
1658
1659 PROCEDURE COPY_TR_FROM_INTERFACE IS
1660 CURSOR GET_TRANSACTIONS IS
1661 SELECT *
1662 FROM XTR_TRANSACTIONS_INTERFACE
1663 WHERE EXTERNAL_DEAL_ID = ARec_Interface.external_deal_id
1664 AND DEAL_TYPE = ARec_Interface.deal_type;
1665
1666
1667 i Number:=0;
1668 BEGIN
1669 G_Ni_Parcel_Rec.DELETE;
1670 G_Ni_Trans_Flex.DELETE;
1671
1672 for l_transactions in get_transactions loop
1673 i:=i+1;
1674
1675 G_Ni_Parcel_Rec(i).DEAL_NO := NULL;
1676 G_Ni_Parcel_Rec(i).PARCEL_SPLIT_NO := l_transactions.TRANSACTION_NO; --This hack maintains a reference for interest overide errors and should be overwritten
1677 G_Ni_Parcel_Rec(i).PARCEL_SIZE := l_transactions.AMOUNT_A;
1678 G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT := l_transactions.AMOUNT_B;
1679 G_Ni_Parcel_Rec(i).CONSIDERATION := l_transactions.AMOUNT_C;
1680 G_Ni_Parcel_Rec(i).INTEREST := l_transactions.AMOUNT_D;
1681 G_Ni_Parcel_Rec(i).STATUS_CODE := 'CURRENT';
1682 G_Ni_Parcel_Rec(i).DEAL_SUBTYPE := G_Ni_Deal_Subtype;
1683 G_Ni_Parcel_Rec(i).AVAILABLE_FOR_RESALE := l_transactions.OPTION_A;
1684 G_Ni_Parcel_Rec(i).PARCEL_REMAINING := l_transactions.AMOUNT_A;
1685 G_Ni_Parcel_Rec(i).SELECT_NUMBER := NULL;
1686 G_Ni_Parcel_Rec(i).SELECT_FV_AMOUNT := NULL;
1687 G_Ni_Parcel_Rec(i).REFERENCE_NUMBER := NULL;
1688 G_Ni_Parcel_Rec(i).RESERVE_PARCEL := NULL;
1689 G_Ni_Parcel_Rec(i).OLD_SELECT_NUMBER := NULL;
1690 G_Ni_Parcel_Rec(i).SERIAL_NUMBER := l_transactions.VALUE_A;
1691 G_Ni_Parcel_Rec(i).SERIAL_NUMBER_IN := NULL;
1692 G_Ni_Parcel_Rec(i).ISSUE_BANK := NULL;
1693 G_Ni_Parcel_Rec(i).ORIGINAL_AMOUNT := NULL;
1694
1695 G_Ni_Trans_Flex(i).ATTRIBUTE_CATEGORY := l_transactions.ATTRIBUTE_CATEGORY;
1696 G_Ni_Trans_Flex(i).ATTRIBUTE1 := l_transactions.ATTRIBUTE1;
1697 G_Ni_Trans_Flex(i).ATTRIBUTE2 := l_transactions.ATTRIBUTE2;
1698 G_Ni_Trans_Flex(i).ATTRIBUTE3 := l_transactions.ATTRIBUTE3;
1699 G_Ni_Trans_Flex(i).ATTRIBUTE4 := l_transactions.ATTRIBUTE4;
1700 G_Ni_Trans_Flex(i).ATTRIBUTE5 := l_transactions.ATTRIBUTE5;
1701 G_Ni_Trans_Flex(i).ATTRIBUTE6 := l_transactions.ATTRIBUTE6;
1702 G_Ni_Trans_Flex(i).ATTRIBUTE7 := l_transactions.ATTRIBUTE7;
1703 G_Ni_Trans_Flex(i).ATTRIBUTE8 := l_transactions.ATTRIBUTE8;
1704 G_Ni_Trans_Flex(i).ATTRIBUTE9 := l_transactions.ATTRIBUTE9;
1705 G_Ni_Trans_Flex(i).ATTRIBUTE10 := l_transactions.ATTRIBUTE10;
1706 G_Ni_Trans_Flex(i).ATTRIBUTE11 := l_transactions.ATTRIBUTE11;
1707 G_Ni_Trans_Flex(i).ATTRIBUTE12 := l_transactions.ATTRIBUTE12;
1708 G_Ni_Trans_Flex(i).ATTRIBUTE13 := l_transactions.ATTRIBUTE13;
1709 G_Ni_Trans_Flex(i).ATTRIBUTE14 := l_transactions.ATTRIBUTE14;
1710 G_Ni_Trans_Flex(i).ATTRIBUTE15 := l_transactions.ATTRIBUTE15;
1711
1712 end loop;
1713 g_num_parcels:=i;
1714 END COPY_TR_FROM_INTERFACE;
1715
1716 BEGIN
1717 G_Ni_Main_Rec.EXTERNAL_DEAL_ID := NULL;
1718 G_Ni_Main_Rec.FREQUENCY := NULL;
1719 G_Ni_Main_Rec.DEAL_TYPE := NULL;
1720 G_Ni_Main_Rec.BROKERAGE_AMOUNT_HCE := NULL;
1721 G_Ni_Main_Rec.TAX_AMOUNT_HCE := NULL;
1722 G_Ni_Main_Rec.MATURITY_BALANCE_HCE_AMOUNT := NULL;
1723 G_Ni_Main_Rec.RISKPARTY_CODE := NULL;
1724 G_Ni_Main_Rec.YEAR_BASIS := NULL;
1725 G_Ni_Main_Rec.INTEREST_HCE_AMOUNT := NULL;
1726 G_Ni_Main_Rec.START_HCE_AMOUNT := NULL;
1727 G_Ni_Main_Rec.PORTFOLIO_AMOUNT := NULL;
1728 G_Ni_Main_Rec.MATURITY_HCE_AMOUNT := NULL;
1729 G_Ni_Main_Rec.PREMIUM_ACCOUNT_NO := NULL;
1730 G_Ni_Main_Rec.NI_DEAL_NO := NULL;
1731 G_Ni_Main_Rec.RENEG_DATE := NULL;
1732 G_Ni_Main_Rec.DEAL_NO := NULL;
1733 G_Ni_Main_Rec.STATUS_CODE := NULL;
1734 G_Ni_Main_Rec.DEALER_CODE := NULL;
1735 G_Ni_Main_Rec.DEAL_DATE := NULL;
1736 G_Ni_Main_Rec.COMPANY_CODE := NULL;
1737 G_Ni_Main_Rec.CPARTY_CODE := NULL;
1738 G_Ni_Main_Rec.CLIENT_CODE := NULL;
1739 G_Ni_Main_Rec.PORTFOLIO_CODE := NULL;
1740 G_Ni_Main_Rec.KNOCK_TYPE := NULL;
1741 G_Ni_Main_Rec.NI_PROFIT_LOSS := NULL;
1742 G_Ni_Main_Rec.DEAL_SUBTYPE := NULL;
1743 G_Ni_Main_Rec.PRODUCT_TYPE := NULL;
1744 G_Ni_Main_Rec.CURRENCY := NULL;
1745 G_Ni_Main_Rec.YEAR_CALC_TYPE := NULL;
1746 G_Ni_Main_Rec.START_DATE := NULL;
1747 G_Ni_Main_Rec.MATURITY_DATE := NULL;
1748 G_Ni_Main_Rec.NO_OF_DAYS := NULL;
1749 G_Ni_Main_Rec.MATURITY_AMOUNT := NULL;
1750 G_Ni_Main_Rec.MATURITY_BALANCE_AMOUNT := NULL;
1751 G_Ni_Main_Rec.START_AMOUNT := NULL;
1752 G_Ni_Main_Rec.CALC_BASIS := NULL;
1753 G_Ni_Main_Rec.INTEREST_RATE := NULL;
1754 G_Ni_Main_Rec.INTEREST_AMOUNT := NULL;
1755 G_Ni_Main_Rec.ORIGINAL_AMOUNT := NULL;
1756 G_Ni_Main_Rec.ROUNDING_TYPE := NULL;
1757 G_Ni_Main_Rec.DAY_COUNT_TYPE := NULL;
1758 G_Ni_Main_Rec.COMMENTS := NULL;
1759 G_Ni_Main_Rec.INTERNAL_TICKET_NO := NULL;
1760 G_Ni_Main_Rec.EXTERNAL_COMMENTS := NULL;
1761 G_Ni_Main_Rec.EXTERNAL_CPARTY_NO := NULL;
1762 G_Ni_Main_Rec.MATURITY_ACCOUNT_NO := NULL;
1763 G_Ni_Main_Rec.CPARTY_ACCOUNT_NO := NULL;
1764 G_Ni_Main_Rec.CPARTY_REF := NULL;
1765 G_Ni_Main_Rec.PRINCIPAL_SETTLED_BY := NULL;
1766 G_Ni_Main_Rec.SECURITY_ID := NULL;
1767 G_Ni_Main_Rec.MARGIN := NULL;
1768 G_Ni_Main_Rec.PRICING_MODEL := NULL;
1769 G_Ni_Main_Rec.MARKET_DATA_SET := NULL;
1770 G_Ni_Main_Rec.DEAL_LINKING_CODE := NULL;
1771 G_Ni_Main_Rec.ACCEPTOR_CODE := NULL;
1772 G_Ni_Main_Rec.ACCEPTOR_NAME := NULL;
1773 G_Ni_Main_Rec.DRAWER_CODE := NULL;
1774 G_Ni_Main_Rec.DRAWER_NAME := NULL;
1775 G_Ni_Main_Rec.ENDORSER_CODE := NULL;
1776 G_Ni_Main_Rec.ENDORSER_NAME := NULL;
1777 G_Ni_Main_Rec.RISKPARTY_LIMIT_CODE := NULL;
1778 G_Ni_Main_Rec.TAX_CODE := NULL;
1779 G_Ni_Main_Rec.TAX_RATE := NULL;
1780 G_Ni_Main_Rec.TAX_AMOUNT := NULL;
1781 G_Ni_Main_Rec.TAX_SETTLED_REFERENCE := NULL;
1782 G_Ni_Main_Rec.INCOME_TAX_CODE := NULL;
1783 G_Ni_Main_Rec.INCOME_TAX_RATE := NULL;
1784 G_Ni_Main_Rec.INCOME_TAX_AMOUNT := NULL;
1785 G_Ni_Main_Rec.INCOME_TAX_SETTLED_REF := NULL;
1786 G_Ni_Main_Rec.ATTRIBUTE_CATEGORY := NULL;
1787 G_Ni_Main_Rec.ATTRIBUTE1 := NULL;
1788 G_Ni_Main_Rec.ATTRIBUTE2 := NULL;
1789 G_Ni_Main_Rec.ATTRIBUTE3 := NULL;
1790 G_Ni_Main_Rec.ATTRIBUTE4 := NULL;
1791 G_Ni_Main_Rec.ATTRIBUTE5 := NULL;
1792 G_Ni_Main_Rec.ATTRIBUTE6 := NULL;
1793 G_Ni_Main_Rec.ATTRIBUTE7 := NULL;
1794 G_Ni_Main_Rec.ATTRIBUTE8 := NULL;
1795 G_Ni_Main_Rec.ATTRIBUTE9 := NULL;
1796 G_Ni_Main_Rec.ATTRIBUTE10 := NULL;
1797 G_Ni_Main_Rec.ATTRIBUTE11 := NULL;
1798 G_Ni_Main_Rec.ATTRIBUTE12 := NULL;
1799 G_Ni_Main_Rec.ATTRIBUTE13 := NULL;
1800 G_Ni_Main_Rec.ATTRIBUTE14 := NULL;
1801 G_Ni_Main_Rec.ATTRIBUTE15 := NULL;
1802
1803
1804
1805 /*--------------------------------------------*/
1806 /* Copying values into the Global Record Type */
1807 /*--------------------------------------------*/
1808 select xtr_deals_s.nextval
1809 into G_Ni_Main_Rec.DEAL_NO
1810 from dual;
1811
1812 G_Ni_Main_Rec.EXTERNAL_DEAL_ID := ARec_Interface.EXTERNAL_DEAL_ID;
1813 G_Ni_Main_Rec.DEAL_TYPE := G_Ni_Deal_Type;
1814 G_Ni_Main_Rec.DEALER_CODE := ARec_Interface.DEALER_CODE;
1815 G_Ni_Main_Rec.COMPANY_CODE := ARec_Interface.COMPANY_CODE;
1816 G_Ni_Main_Rec.CPARTY_CODE := ARec_Interface.CPARTY_CODE;
1817 G_Ni_Main_Rec.CLIENT_CODE := ARec_Interface.CLIENT_CODE;
1818 G_Ni_Main_Rec.PORTFOLIO_CODE := ARec_Interface.PORTFOLIO_CODE;
1819 G_Ni_Main_Rec.DEAL_SUBTYPE := G_Ni_Deal_Subtype;
1820 G_Ni_Main_Rec.PRODUCT_TYPE := ARec_Interface.PRODUCT_TYPE;
1821 G_Ni_Main_Rec.YEAR_CALC_TYPE := ARec_Interface.YEAR_CALC_TYPE;
1822 G_Ni_Main_Rec.DEAL_DATE := ARec_Interface.DATE_A;
1823 G_Ni_Main_Rec.START_DATE := ARec_Interface.DATE_B;
1824 G_Ni_Main_Rec.MATURITY_DATE := ARec_Interface.DATE_C;
1825 G_Ni_Main_Rec.CALC_BASIS := ARec_Interface.BASIS_TYPE;
1826 G_Ni_Main_Rec.ROUNDING_TYPE := ARec_Interface.ROUNDING_TYPE;
1827 G_Ni_Main_Rec.DAY_COUNT_TYPE := ARec_Interface.DAY_COUNT_TYPE;
1828 G_Ni_Main_Rec.CURRENCY := ARec_Interface.CURRENCY_A;
1829 G_Ni_Main_Rec.NO_OF_DAYS := g_no_of_days;
1830 G_Ni_Main_Rec.YEAR_BASIS := g_year_basis;
1831 G_Ni_Main_Rec.MARGIN := ARec_Interface.AMOUNT_A;
1832 G_Ni_Main_Rec.MATURITY_ACCOUNT_NO := ARec_Interface.ACCOUNT_NO_A;
1833 G_Ni_Main_Rec.INTEREST_RATE := ARec_Interface.RATE_A;
1834 G_Ni_Main_Rec.COMMENTS := ARec_Interface.COMMENTS;
1835 G_Ni_Main_Rec.EXTERNAL_COMMENTS := ARec_Interface.EXTERNAL_COMMENTS ;
1836 G_Ni_Main_Rec.INTERNAL_TICKET_NO := ARec_Interface.INTERNAL_TICKET_NO;
1837 G_Ni_Main_Rec.EXTERNAL_CPARTY_NO := ARec_Interface.EXTERNAL_CPARTY_NO;
1838 G_Ni_Main_Rec.CPARTY_ACCOUNT_NO := ARec_Interface.CPARTY_ACCOUNT_NO;
1839 G_Ni_Main_Rec.CPARTY_REF := NULL; --bug 3034164
1840 G_Ni_Main_Rec.PRINCIPAL_SETTLED_BY := ARec_Interface.SETTLE_ACTION_REQD;
1841 G_Ni_Main_Rec.SECURITY_ID := ARec_Interface.SECURITY_ID;
1842 G_Ni_Main_Rec.PRICING_MODEL := ARec_Interface.PRICING_MODEL;
1843 G_Ni_Main_Rec.MARKET_DATA_SET := ARec_Interface.MARKET_DATA_SET;
1844 G_Ni_Main_Rec.DEAL_LINKING_CODE := ARec_Interface.DEAL_LINKING_CODE;
1845 G_Ni_Main_Rec.ACCEPTOR_CODE := ARec_Interface.ACCEPTOR_CODE;
1846 G_Ni_Main_Rec.DRAWER_CODE := ARec_Interface.DRAWER_CODE;
1847 G_Ni_Main_Rec.ENDORSER_CODE := ARec_Interface.ENDORSER_CODE;
1848 G_Ni_Main_Rec.RISKPARTY_LIMIT_CODE := ARec_Interface.LIMIT_CODE;
1849
1850 if G_Ni_Main_Rec.RISKPARTY_LIMIT_CODE is not null then
1851 open get_riskparty_code;
1852 fetch get_riskparty_code into G_Ni_Main_Rec.RISKPARTY_CODE;
1853 close get_riskparty_code;
1854 end if;
1855
1856 if ARec_Interface.BROKERAGE_CODE is not null then
1857 G_Ni_Main_Rec.BROKERAGE_CODE := ARec_Interface.BROKERAGE_CODE;
1858 G_Ni_Main_Rec.BROKERAGE_RATE := ARec_Interface.RATE_C;
1859 G_NI_Main_Rec.BROKERAGE_AMOUNT := ARec_Interface.AMOUNT_C;
1860 G_Ni_Main_Rec.BROKERAGE_CURRENCY := ARec_Interface.BROKERAGE_CURRENCY;
1861 else
1862 G_Ni_Main_Rec.BROKERAGE_CODE := null;
1863 G_Ni_Main_Rec.BROKERAGE_RATE := null;
1864 G_NI_Main_Rec.BROKERAGE_AMOUNT := null;
1865 G_Ni_Main_Rec.BROKERAGE_CURRENCY := null;
1866 end if;
1867
1868 G_Ni_Main_Rec.TAX_CODE := ARec_Interface.SCHEDULE_A;
1869 --G_Ni_Main_Rec.TAX_RATE := Calculated
1870 --G_Ni_Main_Rec.TAX_AMOUNT := Calculated
1871 --G_Ni_Main_Rec.TAX_SETTLED_REFERENCE := Calculated
1872 G_Ni_Main_Rec.INCOME_TAX_CODE := ARec_Interface.SCHEDULE_B;
1873 --G_Ni_Main_Rec.INCOME_TAX_RATE := Calculated
1874 --G_Ni_Main_Rec.INCOME_TAX_AMOUNT := Calculated
1875 --G_Ni_Main_Rec.INCOME_TAX_SETTLED_REF:= Calculated
1876
1877 if (ARec_Interface.OPTION_A='Y') then
1878 G_DO_TAX_DEFAULTING:=true;
1879 else
1880 G_DO_TAX_DEFAULTING:=false;
1881 end if;
1882
1883 G_Ni_Main_Rec.DUAL_AUTHORISATION_BY := ARec_Interface.DUAL_AUTHORIZATION_BY;
1884 G_Ni_Main_Rec.DUAL_AUTHORISATION_ON := ARec_Interface.DUAL_AUTHORIZATION_ON;
1885 G_Ni_Main_Rec.STATUS_CODE := 'CURRENT';
1886 G_Ni_Main_Rec.KNOCK_TYPE := 'N';
1887
1888 /*--------------------------------------------------------------------*/
1889 /* Flexfields will be implemented in Patchset G. */
1890 /*--------------------------------------------------------------------*/
1891 G_Ni_Main_Rec.ATTRIBUTE_CATEGORY := ARec_Interface.ATTRIBUTE_CATEGORY;
1892 G_Ni_Main_Rec.ATTRIBUTE1 := ARec_Interface.ATTRIBUTE1;
1893 G_Ni_Main_Rec.ATTRIBUTE2 := ARec_Interface.ATTRIBUTE2;
1894 G_Ni_Main_Rec.ATTRIBUTE3 := ARec_Interface.ATTRIBUTE3;
1895 G_Ni_Main_Rec.ATTRIBUTE4 := ARec_Interface.ATTRIBUTE4;
1896 G_Ni_Main_Rec.ATTRIBUTE5 := ARec_Interface.ATTRIBUTE5;
1897 G_Ni_Main_Rec.ATTRIBUTE6 := ARec_Interface.ATTRIBUTE6;
1898 G_Ni_Main_Rec.ATTRIBUTE7 := ARec_Interface.ATTRIBUTE7;
1899 G_Ni_Main_Rec.ATTRIBUTE8 := ARec_Interface.ATTRIBUTE8;
1900 G_Ni_Main_Rec.ATTRIBUTE9 := ARec_Interface.ATTRIBUTE9;
1901 G_Ni_Main_Rec.ATTRIBUTE10 := ARec_Interface.ATTRIBUTE10;
1902 G_Ni_Main_Rec.ATTRIBUTE11 := ARec_Interface.ATTRIBUTE11;
1903 G_Ni_Main_Rec.ATTRIBUTE12 := ARec_Interface.ATTRIBUTE12;
1904 G_Ni_Main_Rec.ATTRIBUTE13 := ARec_Interface.ATTRIBUTE13;
1905 G_Ni_Main_Rec.ATTRIBUTE14 := ARec_Interface.ATTRIBUTE14;
1906 G_Ni_Main_Rec.ATTRIBUTE15 := ARec_Interface.ATTRIBUTE15;
1907
1908 COPY_TR_FROM_INTERFACE;
1909
1910 END copy_from_interface_to_ni;
1911
1912 /*------------------------------------------------------------*/
1913 /* The following code implements the calc_rates process */
1914 /*------------------------------------------------------------*/
1915 PROCEDURE CALC_RATES(ARec_Interface IN XTR_DEALS_INTERFACE%ROWTYPE,
1916 error OUT NOCOPY boolean) is
1917
1918 l_bkr_amt_type varchar2(30);
1919 l_dummy_char varchar2(30);
1920
1921 --rvallams
1922 roundfac number(3,2);
1923
1924 cursor rnd(p_curr in VARCHAR2) is
1925 select m.rounding_factor
1926 from xtr_master_currencies_v m
1927 where m.currency = p_curr;
1928
1929
1930 begin
1931
1932 error := FALSE;
1933
1934 /*--------------------------------------------------------------------------------------------*/
1935 /* This process checks the values of the three columns to make sure that they are all in sync */
1936 /*--------------------------------------------------------------------------------------------*/
1937
1938 --rvallams bug 2383157 begin
1939
1940 /*--------------------------------------------------------------------------------------------*/
1941 /* Store the amounts rounded as per the precision of the currency */
1942 /*--------------------------------------------------------------------------------------------*/
1943 /*
1944 if error <> TRUE then
1945
1946 --WDK: do we need any rounding?
1947
1948 open rnd(G_Ni_Main_Rec.currency);
1949 fetch rnd into roundfac;
1950 close rnd;
1951 G_Ni_Main_Rec.buy_amount := round(G_Ni_Main_Rec.buy_amount,roundfac);
1952
1953
1954 end if;
1955 */
1956 --rvallams bug 2383157 end
1957
1958
1959 /*--------------------------------------------------------------------------------------------*/
1960 /* This process calculates all of the splits secondary data and total values */
1961 /*--------------------------------------------------------------------------------------------*/
1962 if error <> TRUE then
1963 CALC_TOTAL_SPLITS(ARec_Interface.deal_type, error);
1964 end if;
1965
1966 /*--------------------------------------------------------------------------------------------*/
1967 /* This process checks brokerage values. */
1968 /*--------------------------------------------------------------------------------------------*/
1969 if error <> TRUE then
1970 xtr_fps2_p.tax_brokerage_amt_type(G_NI_Deal_Type,
1971 G_Ni_Main_Rec.brokerage_code,
1972 null,
1973 l_bkr_amt_type,
1974 l_dummy_char);
1975 if G_Ni_Main_Rec.brokerage_code is not null then
1976 if ARec_Interface.amount_c is null then
1977 CALC_BROKERAGE_AMT(ARec_Interface.deal_type, l_bkr_amt_type, error);
1978 end if;
1979
1980 if G_Ni_Main_Rec.brokerage_amount is not null and G_Ni_Main_Rec.brokerage_currency is null then
1981 G_Ni_Main_Rec.brokerage_currency := ARec_Interface.currency_a;
1982 end if;
1983
1984 end if;
1985 end if;
1986
1987 if error <> TRUE then
1988 CALC_HCE_AMOUNTS(ARec_Interface.deal_type, error);
1989 end if;
1990
1991
1992 end CALC_RATES;
1993
1994
1995 /* Local Procedure to maintain totals of Multiple 'Split Block' */
1996 /*--------------------------------------------------------------------------------*/
1997 PROCEDURE CALC_TOTAL_SPLITS(p_user_deal_type in VARCHAR2,p_error OUT NOCOPY boolean) is
1998 /*--------------------------------------------------------------------------------*/
1999
2000
2001 cursor HCE is
2002 select nvl(m.HCE_RATE,1)
2003 from xtr_MASTER_CURRENCIES_v m
2004 where m.CURRENCY = G_Ni_Main_Rec.CURRENCY;
2005
2006 cursor RND_FAC is
2007 select m.ROUNDING_FACTOR
2008 from xtr_PARTIES_v p,
2009 xtr_MASTER_CURRENCIES_v m
2010 where p.PARTY_CODE = G_Ni_Main_Rec.COMPANY_CODE
2011 and p.PARTY_TYPE = 'C'
2012 and m.CURRENCY = p.HOME_CURRENCY;
2013
2014 type CONSIDERATION_TYPE is table of number index by binary_integer;
2015 type FACE_VALUE_AMOUNT_TYPE is table of number index by binary_integer;
2016 type INTEREST_TYPE is table of number index by binary_integer;
2017 type ORIGINAL_AMOUNT_TYPE is table of number index by binary_integer;
2018 V_CONSIDERATION CONSIDERATION_TYPE;
2019 V_FACE_VALUE_AMOUNT FACE_VALUE_AMOUNT_TYPE;
2020 V_INTEREST INTEREST_TYPE;
2021 V_ORIGINAL_AMOUNT ORIGINAL_AMOUNT_TYPE;
2022
2023 L_ROUNDING_FACTOR XTR_MASTER_CURRENCIES_V.ROUNDING_FACTOR%TYPE;
2024
2025 G_TOTAL_SIZE NUMBER := 0;
2026 G_TOTAL_FACE_VALUE_AMOUNT NUMBER := 0;
2027 G_TOTAL_CONSIDERATION NUMBER := 0;
2028 G_TOTAL_INTEREST NUMBER := 0;
2029 G_TOTAL_ORIGINAL_AMOUNT NUMBER := 0; --Add Interest Override
2030 G_TOTAL_SIZE_REMAINING NUMBER := 0;
2031 G_TOTAL_PRN_TAX_AMOUNT NUMBER := 0;
2032 G_TOTAL_INT_TAX_AMOUNT NUMBER := 0;
2033 G_HC_RATE NUMBER := 0;
2034
2035 v_dummy_num NUMBER;
2036
2037 /*------ Start Calc_Total_Splits local procedure ------*/
2038
2039 /* Local Procedure to calculate interest on discount basis to derive
2040 start amount. */
2041 /*--------------------------------------------------------------------------------*/
2042 PROCEDURE CALC_START_AMOUNT is
2043 /*--------------------------------------------------------------------------------*/
2044 l_interest_amount NUMBER :=NULL;
2045 BEGIN
2046 if G_Ni_Main_Rec.CALC_BASIS = 'YIELD' then
2047 XTR_fps2_P.DISCOUNT_INTEREST_CALC(G_Ni_Main_Rec.YEAR_BASIS,
2048 G_Ni_Main_Rec.MATURITY_AMOUNT,
2049 G_Ni_Main_Rec.INTEREST_RATE,
2050 G_Ni_Main_Rec.NO_OF_DAYS,
2051 L_ROUNDING_FACTOR,
2052 l_interest_amount, -- Add Interest Override
2053 G_Ni_Main_Rec.ROUNDING_TYPE); -- Add Interest Override
2054 else
2055 XTR_fps2_P.INTEREST_CALCULATOR(G_Ni_Main_Rec.YEAR_BASIS,
2056 G_Ni_Main_Rec.MATURITY_AMOUNT,
2057 G_Ni_Main_Rec.INTEREST_RATE,
2058 G_Ni_Main_Rec.NO_OF_DAYS,
2059 L_ROUNDING_FACTOR,
2060 l_interest_amount, -- Add Interest Override
2061 G_Ni_Main_Rec.ROUNDING_TYPE); -- Add Interest Override
2062 end if;
2063 G_Ni_Main_Rec.ORIGINAL_AMOUNT := l_interest_amount;
2064 G_Ni_Main_Rec.INTEREST_AMOUNT := G_Ni_Main_Rec.ORIGINAL_AMOUNT;
2065 G_Ni_Main_Rec.START_AMOUNT := G_Ni_Main_Rec.MATURITY_AMOUNT - G_Ni_Main_Rec.INTEREST_AMOUNT;
2066 END CALC_START_AMOUNT;
2067
2068
2069
2070
2071 /* Local Procedure to calculate interest on yield basis to derive
2072 face value amount for multiple splits.*/
2073 /*--------------------------------------------------------------------------------*/
2074 PROCEDURE CALC_FACE_VALUE_AMOUNT(p_parcel_num in Number) is
2075 j Number:=p_parcel_num;
2076 /*--------------------------------------------------------------------------------*/
2077 BEGIN
2078 XTR_FPS2_P.INTEREST_CALCULATOR(G_Ni_Main_Rec.YEAR_BASIS,
2079 G_Ni_Parcel_Rec(j).CONSIDERATION,
2080 G_Ni_Main_Rec.INTEREST_RATE,
2081 G_Ni_Main_Rec.NO_OF_DAYS,
2082 L_ROUNDING_FACTOR,
2083 G_Ni_Parcel_Rec(j).ORIGINAL_AMOUNT, -- Add Interest Override
2084 G_Ni_Main_Rec.ROUNDING_TYPE); -- Add Interest Override
2085
2086 IF (G_Ni_Parcel_Rec(j).INTEREST is NULL) then
2087 G_Ni_Parcel_Rec(j).INTEREST := G_Ni_Parcel_Rec(j).ORIGINAL_AMOUNT; -- Add Interest Override
2088 END IF;
2089
2090 G_Ni_Parcel_Rec(j).FACE_VALUE_AMOUNT := G_Ni_Parcel_Rec(j).CONSIDERATION + G_Ni_Parcel_Rec(j).INTEREST;
2091 --G_Ni_Parcel_Rec(j).CONSIDERATION := nvl(G_Ni_Parcel_Rec(j).FACE_VALUE_AMOUNT,0) - nvl(G_Ni_Parcel_Rec(j).INTEREST,0);
2092
2093 END CALC_FACE_VALUE_AMOUNT;
2094
2095 /* Local Procedure to calculate interest on discount basis to derive
2096 consideration amounts for multiple splits. */
2097 /*--------------------------------------------------------------------------------*/
2098 PROCEDURE CALC_CONSIDERATION(p_parcel_num in Number) is
2099 j Number:=p_parcel_num;
2100 /*--------------------------------------------------------------------------------*/
2101 BEGIN
2102 if nvl(G_Ni_Main_Rec.CALC_BASIS,'YIELD') = 'YIELD' then
2103
2104 XTR_FPS2_P.DISCOUNT_INTEREST_CALC(G_Ni_Main_Rec.YEAR_BASIS,
2105 G_Ni_Parcel_Rec(j).FACE_VALUE_AMOUNT,
2106 G_Ni_Main_Rec.INTEREST_RATE,
2107 G_Ni_Main_Rec.NO_OF_DAYS,
2108 L_ROUNDING_FACTOR,
2109 G_Ni_Parcel_Rec(j).ORIGINAL_AMOUNT, --Add Interest Override
2110 G_Ni_Main_Rec.ROUNDING_TYPE); --Add Interest Override
2111
2112 IF (G_Ni_Parcel_Rec(j).INTEREST is NULL) then
2113 G_Ni_Parcel_Rec(j).INTEREST := G_Ni_Parcel_Rec(j).ORIGINAL_AMOUNT; --Add Interest Override
2114 END IF;
2115
2116 else
2117 XTR_FPS2_P.INTEREST_CALCULATOR(G_Ni_Main_Rec.YEAR_BASIS,
2118 G_Ni_Parcel_Rec(j).FACE_VALUE_AMOUNT,
2119 G_Ni_Main_Rec.INTEREST_RATE,
2120 G_Ni_Main_Rec.NO_OF_DAYS,
2121 L_ROUNDING_FACTOR,
2122 G_Ni_Parcel_Rec(j).ORIGINAL_AMOUNT, --Add Interest Override
2123 G_Ni_Main_Rec.ROUNDING_TYPE); --Add Interest Override
2124 IF (G_Ni_Parcel_Rec(j).INTEREST is NULL) then
2125 G_Ni_Parcel_Rec(j).INTEREST := G_Ni_Parcel_Rec(j).ORIGINAL_AMOUNT; --Add Interest Override
2126 END IF;
2127 end if;
2128
2129 G_Ni_Parcel_Rec(j).CONSIDERATION := nvl(G_Ni_Parcel_Rec(j).FACE_VALUE_AMOUNT,0) - nvl(G_Ni_Parcel_Rec(j).INTEREST,0);
2130 END CALC_CONSIDERATION;
2131
2132
2133
2134
2135
2136 /*--------------------------------------------------------------------------------*/
2137 PROCEDURE DEFAULT_TAX_CODES IS
2138 /*--------------------------------------------------------------------------------*/
2139 v_dummy_num NUMBER;
2140 v_dummy_char VARCHAR2(30);
2141 --
2142 BEGIN
2143
2144 if G_Ni_Main_Rec.cparty_code is not null and G_Ni_Main_Rec.deal_subtype is not null and
2145 G_Ni_Main_Rec.product_type is not null then
2146 --Principal Tax
2147 if G_Ni_Main_Rec.tax_code is null then
2148 xtr_fps2_p.TAX_BROKERAGE_DEFAULTING('NI',
2149 G_NI_DEAL_SUBTYPE,
2150 G_Ni_Main_Rec.PRODUCT_TYPE,
2151 nvl(G_Ni_Main_Rec.CLIENT_CODE,G_Ni_Main_Rec.CPARTY_CODE),
2152 G_Ni_Main_Rec.PRINCIPAL_SETTLED_BY,
2153 v_dummy_char,
2154 G_Ni_Main_Rec.TAX_CODE,
2155 v_dummy_char,
2156 G_Ni_Main_Rec.CURRENCY,
2157 v_dummy_char,
2158 v_dummy_char,
2159 v_dummy_char,
2160 v_dummy_char);
2161 end if;
2162 --Interest Tax
2163 if G_Ni_Main_Rec.income_tax_code is null then
2164 xtr_fps2_p.TAX_BROKERAGE_DEFAULTING('NI',
2165 G_NI_DEAL_SUBTYPE,
2166 G_Ni_Main_Rec.PRODUCT_TYPE,
2167 nvl(G_Ni_Main_Rec.CLIENT_CODE,G_Ni_Main_Rec.CPARTY_CODE),
2168 v_dummy_char,
2169 v_dummy_char,
2170 v_dummy_char,
2171 G_Ni_Main_Rec.INCOME_TAX_CODE,
2172 G_Ni_Main_Rec.CURRENCY,
2173 v_dummy_char,
2174 v_dummy_char,
2175 v_dummy_char,
2176 v_dummy_char);
2177 end if;
2178 end if;
2179 END DEFAULT_TAX_CODES;
2180
2181
2182
2183
2184
2185
2186 /* Local Procedure to calculate tax for the parcels. */
2187 /*--------------------------------------------------------------------------------*/
2188 PROCEDURE CALC_TAX_AMT(p_consideration NUMBER,
2189 p_maturity_amt NUMBER,
2190 p_interest NUMBER,
2191 p_start_date DATE,
2192 p_year_basis NUMBER,
2193 p_no_of_days NUMBER,
2194 p_ccy VARCHAR2,
2195 p_prn_tax_calc_type VARCHAR2,
2196 p_prn_tax_code VARCHAR2,
2197 p_int_tax_code VARCHAR2,
2198 p_prn_tax_amt OUT NOCOPY NUMBER,
2199 p_int_tax_amt OUT NOCOPY NUMBER,
2200 p_prn_tax_rate OUT NOCOPY NUMBER,
2201 p_int_tax_rate OUT NOCOPY NUMBER) IS
2202 /*--------------------------------------------------------------------------------*/
2203
2204 v_prn_amt NUMBER;
2205 v_dummy_num NUMBER;
2206
2207 BEGIN
2208 --Principal Tax
2209 if p_prn_tax_code is not null then
2210 if p_prn_tax_calc_type in ('CON_A','CON_F') then
2211 v_prn_amt := p_consideration;
2212 else
2213 v_prn_amt := p_maturity_amt;
2214 end if;
2215 --calculate principal tax
2216 xtr_fps1_p.CALC_TAX_AMOUNT('NI',
2217 p_start_date,
2218 p_prn_tax_code,
2219 null,
2220 p_ccy,
2221 null,
2222 p_year_basis,
2223 p_no_of_days,
2224 v_prn_amt,
2225 p_prn_tax_rate,
2226 null,
2227 v_dummy_num,
2228 p_prn_tax_amt,
2229 v_dummy_num,
2230 v_dummy_num,
2231 v_dummy_num);
2232 end if;
2233 --Interest Tax
2234 if p_int_tax_code is not null then
2235 --calculate interest tax
2236 xtr_fps1_p.CALC_TAX_AMOUNT('NI',
2237 p_start_date,
2238 null,
2239 p_int_tax_code,
2240 p_ccy,
2241 null,
2242 p_year_basis,
2243 p_no_of_days,
2244 null,
2245 v_dummy_num,
2246 p_interest,
2247 p_int_tax_rate,
2248 v_dummy_num,
2249 p_int_tax_amt,
2250 v_dummy_num,
2251 v_dummy_num);
2252 end if;
2253
2254 END CALC_TAX_AMT;
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281 /*--------------------------------------------------------------------------------*/
2282 PROCEDURE GET_TAX_INFO
2283 /*--------------------------------------------------------------------------------*/
2284 IS
2285 --
2286 cursor get_tax_info(p_tax_code VARCHAR2) is
2287 select tax_settle_method,calc_type
2288 from xtr_tax_brokerage_setup_v
2289 where reference_code = p_tax_code;
2290 --
2291 cursor get_tax_codes(p_deal_no NUMBER) is
2292 select tax_code,income_tax_code from xtr_deals
2293 where deal_no=p_deal_no;
2294 --
2295 BEGIN
2296 g_prn_tax_settle_method:=null;
2297 g_prn_tax_calc_type:=null;
2298 g_int_tax_settle_method:=null;
2299 g_int_tax_calc_type:=null;
2300 if (G_Ni_Main_Rec.tax_code is null) then
2301 G_Ni_Main_Rec.tax_rate:=null;
2302 G_Ni_Main_Rec.tax_amount:=null;
2303 else
2304 open get_tax_info(G_Ni_Main_Rec.tax_code);
2305 fetch get_tax_info into G_PRN_TAX_SETTLE_METHOD,G_PRN_TAX_CALC_TYPE;
2306 close get_tax_info;
2307 end if;
2308 if (G_Ni_Main_Rec.income_tax_code is null) then
2309 G_Ni_Main_Rec.income_tax_rate:=null;
2310 G_Ni_Main_Rec.income_tax_amount:=null;
2311 else
2312 open get_tax_info(G_Ni_Main_Rec.income_tax_code);
2313 fetch get_tax_info into G_INT_TAX_SETTLE_METHOD,G_INT_TAX_CALC_TYPE;
2314 close get_tax_info;
2315 end if;
2316 END GET_TAX_INFO;
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328 /*--------------------------------------------------------------------------------*/
2329 FUNCTION TAX_ROUNDING(p_ccy VARCHAR2,
2330 /*--------------------------------------------------------------------------------*/
2331 p_tax_code VARCHAR2,
2332 p_number NUMBER)
2333 RETURN NUMBER IS
2334 --
2335 cursor RND_FAC(p_ccy VARCHAR2) is
2336 select nvl(m.ROUNDING_FACTOR,2)
2337 from xtr_MASTER_CURRENCIES_v m
2338 where m.CURRENCY = p_ccy;
2339 --
2340 cursor get_rounding_rules(p_tax_code VARCHAR2) is
2341 select tax_rounding_rule,tax_rounding_precision
2342 from XTR_TAX_BROKERAGE_SETUP
2343 where reference_code=p_tax_code;
2344 --
2345 v_rounding_rule VARCHAR2(1);
2346 v_rounding_precision VARCHAR2(20);
2347 v_rnd_fac NUMBER;
2348 v_number NUMBER;
2349 --
2350 BEGIN
2351 if p_number is not null and p_ccy is not null
2352 and p_tax_code is not null then
2353 open get_rounding_rules(p_tax_code);
2354 fetch get_rounding_rules into
2355 v_rounding_rule,v_rounding_precision;
2356 close get_rounding_rules;
2357 v_rnd_fac := xtr_fps1_p.get_tax_round_factor
2358 (v_rounding_precision,p_ccy);
2359 v_number := xtr_fps2_p.interest_round(p_number,v_RND_FAC,
2360 v_rounding_rule);
2361 return v_number;
2362 else
2363 return null;
2364 end if;
2365 END TAX_ROUNDING;
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377 /*------ End Calc_Total_Splits local procedure ------*/
2378 begin
2379
2380 p_error := false;
2381
2382 open HCE;
2383 fetch HCE INTO G_HC_RATE;
2384 if HCE%NOTFOUND then
2385 xtr_import_deal_data.log_interface_errors(G_Ni_Main_Rec.External_Deal_Id,p_user_deal_type,
2386 'CurrencyA','XTR_886'); -- Unable to find spot rate data
2387 p_error:=true;
2388 end if;
2389 close HCE;
2390
2391 open RND_FAC;
2392 fetch RND_FAC INTO L_ROUNDING_FACTOR;
2393 if RND_FAC%NOTFOUND then
2394 xtr_import_deal_data.log_interface_errors(G_Ni_Main_Rec.External_Deal_Id,p_user_deal_type,
2395 'CompanyCode','XTR_880');--Unable to find home currency data
2396 p_error:=true;
2397 end if;
2398 close RND_FAC;
2399
2400 if not(p_error) then
2401
2402 /* Clear any old values and tables */
2403
2404
2405 G_FV_AMT_HCE.DELETE;
2406 G_INTEREST_HCE.DELETE;
2407 G_PRN_TAX_AMOUNT.DELETE;
2408 G_INT_TAX_AMOUNT.DELETE;
2409
2410
2411 if (G_DO_TAX_DEFAULTING) then
2412 DEFAULT_TAX_CODES;
2413 end if;
2414
2415 GET_TAX_INFO;
2416
2417 CALC_START_AMOUNT;
2418
2419 for i in 1..g_num_parcels loop
2420
2421
2422 if (G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT is not null) then
2423 CALC_CONSIDERATION(i);
2424 else
2425 CALC_FACE_VALUE_AMOUNT(i);
2426 end if;
2427
2428 if not ( val_interest(G_Ni_Main_Rec.company_code,
2429 G_Ni_Main_Rec.cparty_code,
2430 G_Ni_Deal_type,
2431 G_Ni_Main_Rec.currency,
2432 G_Ni_Parcel_Rec(i).interest,
2433 G_Ni_Parcel_Rec(i).original_amount)) then
2434 xtr_import_deal_data.log_interface_errors( G_Ni_Main_Rec.External_Deal_Id,
2435 p_user_deal_type,
2436 'AmountD',
2437 'XTR_INV_INTEREST',
2438 G_Ni_Parcel_Rec(i).parcel_split_no); --This value temporarily holds the interface transaction_no
2439 p_error := true;
2440 end if;
2441
2442
2443 CALC_TAX_AMT(G_Ni_Parcel_Rec(i).consideration,
2444 G_Ni_Parcel_Rec(i).face_value_amount,
2445 G_Ni_Parcel_Rec(i).interest,
2446 G_Ni_Main_Rec.start_date,
2447 G_Ni_Main_Rec.year_basis,
2448 G_Ni_Main_Rec.no_of_days,
2449 null,
2450 g_prn_tax_calc_type,
2451 G_Ni_Main_Rec.tax_code,
2452 G_Ni_Main_Rec.income_tax_code,
2453 g_prn_tax_amount(i),
2454 g_int_tax_amount(i),
2455 G_Ni_Main_Rec.tax_rate,
2456 G_Ni_Main_Rec.income_tax_rate);
2457
2458
2459 G_FV_AMT_HCE(i) := round(G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT / G_HC_RATE,L_ROUNDING_FACTOR);
2460 G_INTEREST_HCE(i) := round(G_Ni_Parcel_Rec(i).INTEREST / G_HC_RATE,L_ROUNDING_FACTOR);
2461
2462 V_CONSIDERATION(i) := G_Ni_Parcel_Rec(i).PARCEL_SIZE * G_Ni_Parcel_Rec(i).CONSIDERATION;
2463 V_FACE_VALUE_AMOUNT(i) := G_Ni_Parcel_Rec(i).PARCEL_SIZE * G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT;
2464 V_INTEREST(i) := G_Ni_Parcel_Rec(i).PARCEL_SIZE * G_Ni_Parcel_Rec(i).INTEREST;
2465 V_ORIGINAL_AMOUNT(i) := G_Ni_Parcel_Rec(i).PARCEL_SIZE * G_Ni_Parcel_Rec(i).ORIGINAL_AMOUNT;
2466
2467 G_TOTAL_CONSIDERATION := G_TOTAL_CONSIDERATION+NVL(V_CONSIDERATION(i),0);
2468 G_TOTAL_FACE_VALUE_AMOUNT := G_TOTAL_FACE_VALUE_AMOUNT+NVL(V_FACE_VALUE_AMOUNT(i),0);
2469 G_TOTAL_INTEREST := G_TOTAL_INTEREST+NVL(V_INTEREST(i),0);
2470 G_TOTAL_ORIGINAL_AMOUNT := G_TOTAL_ORIGINAL_AMOUNT+NVL(V_ORIGINAL_AMOUNT(i),0);
2471
2472 G_TOTAL_SIZE := nvl(G_TOTAL_SIZE,0) + nvl(G_Ni_Parcel_Rec(i).PARCEL_SIZE,0);
2473
2474 G_TOTAL_SIZE_REMAINING := nvl(G_TOTAL_SIZE_REMAINING,0) +
2475 nvl(G_Ni_Parcel_Rec(i).PARCEL_REMAINING,0);
2476 /* WDK: do we need rounding?
2477 G_TOTAL_FACE_VALUE_AMOUNT := round(nvl(G_TOTAL_FACE_VALUE_AMOUNT,0),L_ROUNDING_FACTOR) +
2478 round((G_Ni_Parcel_Rec(i).PARCEL_SIZE * nvl(G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT,0)),L_ROUNDING_FACTOR);
2479 G_TOTAL_CONSIDERATION := nvl(G_TOTAL_CONSIDERATION,0) +
2480 round(G_Ni_Parcel_Rec(i).PARCEL_SIZE * nvl(G_Ni_Parcel_Rec(i).CONSIDERATION,0),L_ROUNDING_FACTOR);
2481 G_TOTAL_ORIGINAL_AMOUNT := nvl(G_TOTAL_ORIGINAL_AMOUNT,0) +
2482 XTR_FPS2_P.interest_round(G_Ni_Parcel_Rec(i).PARCEL_SIZE *
2483 nvl(G_Ni_Parcel_Rec(i).ORIGINAL_AMOUNT,0),
2484 L_ROUNDING_FACTOR,G_Ni_Main_Rec.ROUNDING_TYPE);
2485 G_TOTAL_INTEREST := nvl(G_TOTAL_INTEREST,0) +
2486 XTR_FPS2_P.interest_round(G_Ni_Parcel_Rec(i).PARCEL_SIZE *
2487 nvl(G_Ni_Parcel_Rec(i).INTEREST,0),
2488 L_ROUNDING_FACTOR,G_Ni_Main_Rec.ROUNDING_TYPE);
2489 */
2490
2491 G_TOTAL_PRN_TAX_AMOUNT := nvl(G_TOTAL_PRN_TAX_AMOUNT,0) + G_Ni_Parcel_Rec(i).PARCEL_SIZE * nvl(g_prn_tax_amount(i),0);
2492 G_TOTAL_INT_TAX_AMOUNT := nvl(G_TOTAL_INT_TAX_AMOUNT,0) + G_Ni_Parcel_Rec(i).PARCEL_SIZE * nvl(g_int_tax_amount(i),0);
2493
2494
2495 G_Ni_Parcel_Rec(i).DEAL_NO := G_Ni_Main_Rec.DEAL_NO;
2496 G_Ni_Parcel_Rec(i).DEAL_SUBTYPE := g_ni_deal_subtype;
2497
2498 G_Ni_Parcel_Rec(i).STATUS_CODE := 'CURRENT';
2499
2500 select xtr_exposure_trans_s.nextval
2501 into G_Ni_Parcel_Rec(i).PARCEL_SPLIT_NO --This overwrites old transaction tracking number
2502 from dual;
2503
2504 END LOOP;
2505
2506 G_Ni_Main_Rec.FREQUENCY := G_TOTAL_SIZE;
2507
2508 G_Ni_Main_Rec.START_AMOUNT := G_TOTAL_CONSIDERATION;
2509 G_Ni_Main_Rec.MATURITY_AMOUNT := G_TOTAL_FACE_VALUE_AMOUNT;
2510 G_Ni_Main_Rec.MATURITY_BALANCE_AMOUNT := G_Ni_Main_Rec.MATURITY_AMOUNT;
2511 G_Ni_Main_Rec.INTEREST_AMOUNT := G_TOTAL_INTEREST;
2512 G_Ni_Main_Rec.ORIGINAL_AMOUNT := G_TOTAL_ORIGINAL_AMOUNT;
2513
2514 G_Ni_Main_Rec.TAX_AMOUNT :=tax_rounding(G_Ni_Main_Rec.currency,G_Ni_Main_Rec.tax_code , G_TOTAL_PRN_TAX_AMOUNT);
2515 G_Ni_Main_Rec.INCOME_TAX_AMOUNT:=tax_rounding(G_Ni_Main_Rec.currency,G_Ni_Main_Rec.income_tax_code, G_TOTAL_INT_TAX_AMOUNT);
2516
2517 end if;
2518 END CALC_TOTAL_SPLITS;
2519
2520
2521 /* Local Procedure to find the rounding factor for the home currency for
2522 this company and use the latest HCE amounts for the chosen currency from
2523 Spot rates to calculate HCE amounts calculate the Limit Amount. Set
2524 portfolio amount to Start HCE Amount */
2525
2526 /*--------------------------------------------------------------------------------*/
2527 PROCEDURE CALC_HCE_AMOUNTS (p_user_deal_type IN VARCHAR2, p_error OUT NOCOPY BOOLEAN) is
2528 /*--------------------------------------------------------------------------------*/
2529
2530 l_roundfac NUMBER(3,2);
2531
2532 cursor rnd_fac is
2533 select m.rounding_factor
2534 from xtr_parties_v p,
2535 xtr_master_currencies_v m
2536 where p.party_code = G_Ni_Main_Rec.company_code
2537 and p.party_type = 'C'
2538 and m.currency = p.home_currency;
2539
2540 cursor HCE is
2541 select round((G_Ni_Main_Rec.START_AMOUNT / nvl(s.HCE_RATE,1)),nvl(l_roundfac,2)),
2542 round((G_Ni_Main_Rec.MATURITY_AMOUNT / nvl(s.HCE_RATE,1)),nvl(l_roundfac,2)),
2543 round((G_Ni_Main_Rec.INTEREST_AMOUNT / nvl(s.HCE_RATE,1)),nvl(l_roundfac,2)),
2544 round((nvl(G_Ni_Main_Rec.BROKERAGE_AMOUNT,0) / nvl(s.HCE_RATE,1)),nvl(l_roundfac,2))
2545 from XTR_MASTER_CURRENCIES_v s
2546 where s.CURRENCY = G_Ni_Main_Rec.CURRENCY;
2547
2548 begin
2549 open RND_FAC;
2550 fetch RND_FAC into l_roundfac;
2551 if RND_FAC%NOTFOUND then
2552 close RND_FAC;
2553 xtr_import_deal_data.log_interface_errors(G_Ni_Main_Rec.External_Deal_Id,p_user_deal_type,
2554 'CompanyCode','XTR_880');--Unable to find home currency data
2555 p_error := true;
2556 end if;
2557 close RND_FAC;
2558
2559 if G_Ni_Main_Rec.CURRENCY is NULL then
2560 G_Ni_Main_Rec.START_HCE_AMOUNT := NULL;
2561 G_Ni_Main_Rec.INTEREST_HCE_AMOUNT := NULL;
2562 G_Ni_Main_Rec.MATURITY_HCE_AMOUNT := NULL;
2563 G_Ni_Main_Rec.PORTFOLIO_AMOUNT := NULL;
2564 else
2565 open HCE;
2566 fetch HCE INTO G_Ni_Main_Rec.START_HCE_AMOUNT, G_Ni_Main_Rec.MATURITY_HCE_AMOUNT,
2567 G_Ni_Main_Rec.INTEREST_HCE_AMOUNT, G_Ni_Main_Rec.BROKERAGE_AMOUNT_HCE;
2568 if HCE%NOTFOUND then
2569 close HCE;
2570 xtr_import_deal_data.log_interface_errors(G_Ni_Main_Rec.External_Deal_Id,p_user_deal_type,
2571 'CurrencyA','XTR_886'); -- Unable to find spot rate data
2572 p_error := true;
2573 end if;
2574 close HCE;
2575 G_Ni_Main_Rec.MATURITY_BALANCE_HCE_AMOUNT := G_Ni_Main_Rec.MATURITY_HCE_AMOUNT;
2576 G_Ni_Main_Rec.PORTFOLIO_AMOUNT := G_Ni_Main_Rec.MATURITY_HCE_AMOUNT;
2577 end if;
2578 p_error := false;
2579 end CALC_HCE_AMOUNTS;
2580
2581 /*------------------------------------------------------------*/
2582 PROCEDURE CALC_BROKERAGE_AMT(p_user_deal_type IN VARCHAR2,
2583 p_bkr_amt_type IN VARCHAR2,
2584 p_error OUT NOCOPY BOOLEAN) IS
2585 /*------------------------------------------------------------*/
2586 l_dummy_num NUMBER;
2587 l_dummy_char VARCHAR2(30);
2588 l_amount NUMBER;
2589 l_err_code NUMBER(8);
2590 l_level VARCHAR2(2) := ' ';
2591 BEGIN
2592 p_error := false;
2593 if ((p_bkr_amt_type = 'INTL_FV' and G_Ni_Main_Rec.MATURITY_AMOUNT is not null) or
2594 G_Ni_Main_Rec.INTEREST_AMOUNT is not null) then
2595 if p_bkr_amt_type = 'INTL_FV' then
2596 l_amount := G_Ni_Main_Rec.MATURITY_AMOUNT;
2597 else
2598 l_amount := G_Ni_Main_Rec.INTEREST_AMOUNT;
2599 end if;
2600 XTR_FPS1_P.CALC_TAX_BROKERAGE(G_Ni_Main_Rec.DEAL_TYPE,
2601 G_Ni_Main_Rec.DEAL_DATE,
2602 null,
2603 G_Ni_Main_Rec.BROKERAGE_CODE,
2604 G_Ni_Main_Rec.CURRENCY,
2605 0,
2606 0,
2607 null,
2608 0,
2609 l_dummy_num,
2610 p_bkr_amt_type,
2611 l_amount,
2612 G_Ni_Main_Rec.BROKERAGE_RATE,
2613 l_dummy_num,
2614 l_dummy_num,
2615 G_Ni_Main_Rec.BROKERAGE_AMOUNT,
2616 l_dummy_num,
2617 l_err_code,
2618 l_level);
2619 if (nvl(l_level,'X')='E') then
2620 xtr_import_deal_data.log_interface_errors(G_Ni_Main_Rec.External_Deal_Id,p_user_deal_type,
2621 'CurrencyA','XTR_886'); -- Unable to find spot rate data
2622 p_error := true;
2623 end if;
2624 end if;
2625
2626 END CALC_BROKERAGE_AMT;
2627
2628 /*------------------------------------------------------------*/
2629 PROCEDURE CREATE_NI_DEAL IS
2630 /*------------------------------------------------------------*/
2631
2632 cursor FIND_USER (p_fnd_user in number) is
2633 select dealer_code
2634 from xtr_dealer_codes_v
2635 where user_id = p_fnd_user;
2636
2637 l_user xtr_dealer_codes.dealer_code%TYPE;
2638 l_dual_user xtr_dealer_codes.dealer_code%TYPE;
2639 l_dual_date DATE;
2640
2641 l_bank_code XTR_BANK_ACCOUNTS_V.BANK_CODE%TYPE;
2642
2643 cursor get_bank_issue_code(p_serial_number in NUMBER) is
2644 select bank_code
2645 from xtr_bill_bond_issues_v
2646 where ni_or_bond='NI'
2647 and serial_number=p_serial_number;
2648
2649
2650 cursor get_bank_code is
2651 select BANK_CODE
2652 from XTR_BANK_ACCOUNTS_V
2653 where ACCOUNT_NUMBER = G_Ni_Main_Rec.Maturity_Account_No
2654 and PARTY_CODE = G_Ni_Main_Rec.Company_Code;
2655
2656
2657 /* -------- to insert parcels into transaction table -------*/
2658 /*------------------------------------------------------------*/
2659 PROCEDURE CREATE_TRANSACTIONS IS
2660 /*------------------------------------------------------------*/
2661 l_tran_no NUMBER:=0;
2662 --
2663 BEGIN
2664 for i in 1..g_num_parcels loop
2665 for j in 1..G_Ni_Parcel_Rec(i).PARCEL_SIZE loop
2666 l_tran_no := l_tran_no + 1;
2667
2668 insert into XTR_ROLLOVER_TRANSACTIONS_V
2669 (DEAL_NUMBER,DEAL_DATE,TRANSACTION_NUMBER,DEAL_TYPE,DEAL_SUBTYPE,
2670 BALANCE_OUT,CURRENCY,PARCEL_SPLIT_NO,CREATED_BY,CREATED_ON,
2671 INTEREST_RATE,START_DATE,MATURITY_DATE,NO_OF_DAYS,COMPANY_CODE,
2672 STATUS_CODE,PRODUCT_TYPE,CLIENT_CODE,INTEREST,ENDORSER_CODE,
2673 ENDORSER_NAME,ACCEPTOR_CODE,ACCEPTOR_NAME,DRAWER_CODE,DRAWER_NAME,
2674 CPARTY_CODE,DEALER_CODE,BALANCE_OUT_HCE,INTEREST_HCE,PORTFOLIO_CODE,
2675 ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,
2676 ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,
2677 ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,
2678 ORIGINAL_AMOUNT, --Add Interest Override
2679 PRINCIPAL_TAX_CODE ,PRINCIPAL_TAX_RATE,
2680 PRINCIPAL_TAX_AMOUNT,PRINCIPAL_TAX_SETTLED_REF,
2681 TAX_CODE ,TAX_RATE,
2682 TAX_AMOUNT ,TAX_SETTLED_REFERENCE
2683 )
2684 values
2685 ------------
2686 -- NEW NI --
2687 ------------
2688 (G_Ni_Main_Rec.DEAL_NO,G_Ni_Main_Rec.DEAL_DATE,l_tran_no,'NI',G_Ni_Main_Rec.DEAL_SUBTYPE,
2689 G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT,G_Ni_Main_Rec.CURRENCY,G_Ni_Parcel_Rec(i).PARCEL_SPLIT_NO,
2690 G_Ni_Main_Rec.CREATED_BY,G_Ni_Main_Rec.CREATED_ON,G_Ni_Main_Rec.INTEREST_RATE,G_Ni_Main_Rec.START_DATE,
2691 G_Ni_Main_Rec.MATURITY_DATE,G_Ni_Main_Rec.NO_OF_DAYS,G_Ni_Main_Rec.COMPANY_CODE,G_Ni_Main_Rec.STATUS_CODE,
2692 G_Ni_Main_Rec.PRODUCT_TYPE,G_Ni_Main_Rec.CLIENT_CODE,G_Ni_Parcel_Rec(i).INTEREST,G_Ni_Main_Rec.ENDORSER_CODE,
2693 G_Ni_Main_Rec.ENDORSER_NAME,G_Ni_Main_Rec.ACCEPTOR_CODE,G_Ni_Main_Rec.ACCEPTOR_NAME,G_Ni_Main_Rec.DRAWER_CODE,
2694 G_Ni_Main_Rec.DRAWER_NAME,G_Ni_Main_Rec.CPARTY_CODE,G_Ni_Main_Rec.DEALER_CODE,G_FV_AMT_HCE(i),
2695 G_INTEREST_HCE(i),G_Ni_Main_Rec.PORTFOLIO_CODE,
2696 G_Ni_Trans_Flex(i).ATTRIBUTE_CATEGORY,G_Ni_Trans_Flex(i).ATTRIBUTE1,G_Ni_Trans_Flex(i).ATTRIBUTE2,
2697 G_Ni_Trans_Flex(i).ATTRIBUTE3,G_Ni_Trans_Flex(i).ATTRIBUTE4,G_Ni_Trans_Flex(i).ATTRIBUTE5,
2698 G_Ni_Trans_Flex(i).ATTRIBUTE6,G_Ni_Trans_Flex(i).ATTRIBUTE7,G_Ni_Trans_Flex(i).ATTRIBUTE8,G_Ni_Trans_Flex(i).ATTRIBUTE9,
2699 G_Ni_Trans_Flex(i).ATTRIBUTE10,G_Ni_Trans_Flex(i).ATTRIBUTE11,G_Ni_Trans_Flex(i).ATTRIBUTE12,G_Ni_Trans_Flex(i).ATTRIBUTE13,
2700 G_Ni_Trans_Flex(i).ATTRIBUTE14,G_Ni_Trans_Flex(i).ATTRIBUTE15,
2701 G_Ni_Parcel_Rec(i).ORIGINAL_AMOUNT, --Add Interest Override
2702 G_Ni_Main_Rec.TAX_CODE ,G_Ni_Main_Rec.TAX_RATE,
2703 G_PRN_TAX_AMOUNT(i) ,null,
2704 G_Ni_Main_Rec.INCOME_TAX_CODE,G_Ni_Main_Rec.INCOME_TAX_RATE,
2705 G_INT_TAX_AMOUNT(i) ,null
2706 );
2707
2708 END LOOP;
2709 END LOOP;
2710 END CREATE_TRANSACTIONS;
2711
2712
2713 --------------------------------------------------------------------
2714 -- Divides the brokerage amount as weighted average across parcels.
2715 -- This is the weighted average version of SPLIT_BROKERAGE_AMOUNT.
2716 --------------------------------------------------------------------
2717 /*------------------------------------------------------------*/
2718 PROCEDURE WEIGHTED_BROKERAGE_AMOUNT IS
2719 /*------------------------------------------------------------*/
2720
2721 cursor CUR_ROUND is
2722 select nvl(a.ROUNDING_FACTOR,2),
2723 nvl(b.ROUNDING_FACTOR,2)
2724 from XTR_MASTER_CURRENCIES_v a,
2725 xtr_MASTER_CURRENCIES_v b,
2726 xtr_PARTIES_v p
2727 where a.CURRENCY = G_Ni_Main_Rec.CURRENCY
2728 and p.PARTY_CODE = G_Ni_Main_Rec.COMPANY_CODE
2729 and p.PARTY_TYPE = 'C'
2730 and b.CURRENCY = p.HOME_CURRENCY;
2731
2732 l_tran_no NUMBER;
2733 l_rounding NUMBER;
2734 l_hce_rounding NUMBER;
2735 l_running_bkge_amt NUMBER;
2736 l_running_bkge_amt_hce NUMBER;
2737 l_total_parcel NUMBER;
2738 l_total_face_value NUMBER;
2739 l_total_face_value_hce NUMBER;
2740 l_tran_face_value NUMBER;
2741 l_tran_face_value_hce NUMBER;
2742 --
2743 BEGIN
2744
2745 if G_Ni_Main_Rec.BROKERAGE_AMOUNT is not null then
2746 ------------
2747 -- NEW NI --
2748 -----------------------------------------------------------------------------------------
2749 -- Divide brokerage amount among parcels
2750 -----------------------------------------------------------------------------------------
2751 open CUR_ROUND;
2752 fetch CUR_ROUND into l_rounding, l_hce_rounding;
2753 close CUR_ROUND;
2754
2755 select count(deal_number),
2756 sum(balance_out),
2757 sum(balance_out_hce)
2758 into l_total_parcel,
2759 l_total_face_value,
2760 l_total_face_value_hce
2761 from XTR_ROLLOVER_TRANSACTIONS
2762 where company_code = G_Ni_Main_Rec.COMPANY_CODE
2763 and deal_number = G_Ni_Main_Rec.DEAL_NO
2764 and deal_type = 'NI'
2765 and brokerage_amount is null;
2766
2767 if nvl(l_total_face_value,-1) > 0 and -- make sure that we don't divide by zero
2768 nvl(l_total_face_value_hce,-1) > 0 then
2769
2770 l_running_bkge_amt := 0;
2771 l_running_bkge_amt_hce := 0;
2772 l_tran_no := 1;
2773
2774 LOOP
2775 if l_total_parcel = 0 then
2776 exit;
2777 end if;
2778 --------------------------------
2779 -- Update RT brokerage amount --
2780 --------------------------------
2781 update XTR_ROLLOVER_TRANSACTIONS_V
2782 set BROKERAGE_AMOUNT = round(decode(l_total_parcel,
2783 1,G_Ni_Main_Rec.BROKERAGE_AMOUNT- l_running_bkge_amt,
2784 (G_Ni_Main_Rec.BROKERAGE_AMOUNT/l_total_face_value)*balance_out),
2785 l_rounding),
2786 BROKERAGE_AMOUNT_HCE = round(decode(l_total_parcel,
2787 1, G_Ni_Main_Rec.BROKERAGE_AMOUNT_HCE- l_running_bkge_amt_hce,
2788 (G_Ni_Main_Rec.BROKERAGE_AMOUNT_HCE/l_total_face_value_hce)*balance_out_hce),
2789 l_hce_rounding)
2790 where company_code = G_Ni_Main_Rec.COMPANY_CODE
2791 and deal_number = G_Ni_Main_Rec.DEAL_NO
2792 and deal_type = 'NI'
2793 and transaction_number = l_tran_no
2794 and brokerage_amount is null;
2795
2796 select balance_out,
2797 balance_out_hce
2798 into l_tran_face_value,
2799 l_tran_face_value_hce
2800 from XTR_ROLLOVER_TRANSACTIONS
2801 where company_code = G_Ni_Main_Rec.COMPANY_CODE
2802 and deal_number = G_Ni_Main_Rec.DEAL_NO
2803 and deal_type = 'NI'
2804 and transaction_number = l_tran_no;
2805
2806 l_running_bkge_amt := round(l_running_bkge_amt +
2807 (G_Ni_Main_Rec.BROKERAGE_AMOUNT/l_total_face_value)*l_tran_face_value,
2808 l_rounding);
2809 l_running_bkge_amt_hce := round(l_running_bkge_amt_hce +
2810 (G_Ni_Main_Rec.BROKERAGE_AMOUNT_HCE/l_total_face_value_hce)*l_tran_face_value_hce,
2811 l_hce_rounding);
2812
2813 l_total_parcel := l_total_parcel - 1;
2814 l_tran_no := l_tran_no + 1;
2815
2816 END LOOP;
2817 end if;
2818 end if;
2819
2820 END WEIGHTED_BROKERAGE_AMOUNT;
2821
2822
2823 /*------------------------------------------------------------*/
2824 PROCEDURE SET_INITIAL_FAIR_VALUE IS
2825 /*------------------------------------------------------------*/
2826
2827 cursor ROLL_ID is
2828 select transaction_number
2829 from xtr_rollover_transactions
2830 where company_code = G_Ni_Main_Rec.company_code
2831 and deal_number = G_Ni_Main_Rec.deal_no
2832 and deal_type = 'NI'
2833 and initial_fair_value is null;
2834
2835 l_tran_no NUMBER;
2836
2837 BEGIN
2838 open ROLL_ID;
2839 fetch ROLL_ID into l_tran_no;
2840 while ROLL_ID%FOUND loop
2841 xtr_reval_process_p.xtr_ins_init_fv(G_Ni_Main_Rec.COMPANY_CODE,G_Ni_Main_Rec.DEAL_NO,'NI',l_TRAN_NO,G_Ni_Main_Rec.day_count_type);
2842 fetch ROLL_ID into l_tran_no;
2843 end loop;
2844 close ROLL_ID;
2845
2846 END SET_INITIAL_FAIR_VALUE;
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861 /*--------------------------------------------------------------------------------*/
2862 PROCEDURE CALL_ONE_STEP_SETTLEMENT (p_prn_exp_number OUT NOCOPY NUMBER,
2863 p_int_exp_number OUT NOCOPY NUMBER)
2864 IS
2865 /*--------------------------------------------------------------------------------*/
2866 --
2867 v_rec xtr_fps2_p.one_step_rec_type;
2868 v_error VARCHAR2(40);
2869 v_settle_method xtr_tax_brokerage_setup.tax_settle_method%Type;
2870 --
2871 BEGIN
2872 --Principal Tax
2873 if g_prn_tax_settle_method='OSG' then
2874 v_rec.p_source := 'TAX';
2875 v_rec.p_schedule_code := G_Ni_Main_Rec.tax_code;
2876 v_rec.p_currency := G_Ni_Main_Rec.currency;
2877 v_rec.p_amount := G_Ni_Main_Rec.tax_amount;
2878 v_rec.p_settlement_date := G_Ni_Main_Rec.start_date;
2879 v_rec.p_settlement_account := G_Ni_Main_Rec.maturity_account_no;
2880 v_rec.p_company_code := G_Ni_Main_Rec.company_code;
2881 v_rec.p_cparty_code := G_Ni_Main_Rec.cparty_code;
2882 v_rec.p_cparty_account_no := G_Ni_Main_Rec.cparty_ref;
2883
2884 XTR_FPS2_P.One_Step_Settlement(v_rec);
2885
2886 v_error := v_rec.p_error;
2887 v_settle_method := v_rec.p_settle_method;
2888 p_prn_exp_number := v_rec.p_exp_number;
2889
2890 update xtr_deals
2891 set tax_settled_reference=p_prn_exp_number
2892 where deal_no=G_Ni_Main_Rec.deal_no;
2893 end if;
2894
2895 --Interest Tax
2896 if g_int_tax_settle_method='OSG' then
2897 v_rec.p_source := 'TAX';
2898 v_rec.p_schedule_code := G_Ni_Main_Rec.income_tax_code;
2899 v_rec.p_currency := G_Ni_Main_Rec.currency;
2900 v_rec.p_amount := G_Ni_Main_Rec.income_tax_amount;
2901 v_rec.p_settlement_date := G_Ni_Main_Rec.start_date;
2902 v_rec.p_settlement_account := G_Ni_Main_Rec.maturity_account_no;
2903 v_rec.p_company_code := G_Ni_Main_Rec.company_code;
2904 v_rec.p_cparty_code := G_Ni_Main_Rec.cparty_code;
2905 v_rec.p_cparty_account_no := G_Ni_Main_Rec.cparty_ref;
2906
2907 XTR_FPS2_P.One_Step_Settlement(v_rec);
2908
2909 v_error := v_rec.p_error;
2910 v_settle_method := v_rec.p_settle_method;
2911 p_int_exp_number := v_rec.p_exp_number;
2912
2913 update xtr_deals
2914 set income_tax_settled_ref=p_int_exp_number
2915 where deal_no=G_Ni_Main_Rec.deal_no;
2916 end if;
2917 END CALL_ONE_STEP_SETTLEMENT;
2918
2919
2920
2921
2922
2923 /*--------------------------------------------------------------------------------*/
2924 PROCEDURE GET_RISK_PARTY_NAMES IS
2925 cursor risk_party_name(p_party_code in VARCHAR2) is
2926 select short_name
2927 from xtr_parties_v
2928 where party_code=p_party_code;
2929
2930 BEGIN
2931 open risk_party_name(G_Ni_Main_Rec.ACCEPTOR_CODE);
2932 fetch risk_party_name into G_Ni_Main_Rec.ACCEPTOR_NAME;
2933 close risk_party_name;
2934
2935 open risk_party_name(G_Ni_Main_Rec.DRAWER_CODE);
2936 fetch risk_party_name into G_Ni_Main_Rec.DRAWER_NAME;
2937 close risk_party_name;
2938
2939 open risk_party_name(G_Ni_Main_Rec.ENDORSER_CODE);
2940 fetch risk_party_name into G_Ni_Main_Rec.ENDORSER_NAME;
2941 close risk_party_name;
2942
2943 END GET_RISK_PARTY_NAMES;
2944
2945 /*--------------------------------------------------------------------------------*/
2946
2947
2948
2949 /*---------- end local procedure to data into secondary tables ----*/
2950
2951
2952 Begin
2953
2954 open FIND_USER(G_User_Id);
2955 fetch FIND_USER into l_user;
2956 close FIND_USER;
2957
2958 l_dual_user := G_Ni_Main_Rec.DUAL_AUTHORISATION_BY;
2959 l_dual_date := G_Ni_Main_Rec.DUAL_AUTHORISATION_ON;
2960 if ((l_dual_user is not null and l_dual_date is null) or
2961 (l_dual_user is null and l_dual_date is not null)) then
2962 if l_dual_date is null then
2963 l_dual_date := trunc(sysdate);
2964 elsif l_dual_user is null then
2965 l_dual_user := l_user;
2966 end if;
2967 end if;
2968
2969 G_Ni_Main_Rec.CREATED_BY:=nvl(l_user,G_User_Id);
2970 G_Ni_Main_Rec.CREATED_ON:=g_curr_date;
2971
2972 GET_RISK_PARTY_NAMES;
2973
2974
2975 INSERT INTO XTR_DEALS
2976 (
2977 EXTERNAL_DEAL_ID,
2978 FREQUENCY,
2979 DEAL_TYPE,
2980 BROKERAGE_AMOUNT_HCE,
2981 TAX_AMOUNT_HCE,
2982 MATURITY_BALANCE_HCE_AMOUNT,
2983 RISKPARTY_CODE,
2984 YEAR_BASIS,
2985 INTEREST_HCE_AMOUNT,
2986 START_HCE_AMOUNT,
2987 PORTFOLIO_AMOUNT,
2988 MATURITY_HCE_AMOUNT,
2989 PREMIUM_ACCOUNT_NO,
2990 NI_DEAL_NO,
2991 RENEG_DATE,
2992 DEAL_NO,
2993 STATUS_CODE,
2994 DEALER_CODE,
2995 DEAL_DATE,
2996 COMPANY_CODE,
2997 CPARTY_CODE,
2998 CLIENT_CODE,
2999 PORTFOLIO_CODE,
3000 KNOCK_TYPE,
3001 NI_PROFIT_LOSS,
3002 DEAL_SUBTYPE,
3003 PRODUCT_TYPE,
3004 CURRENCY,
3005 YEAR_CALC_TYPE,
3006 START_DATE,
3007 MATURITY_DATE,
3008 NO_OF_DAYS,
3009 MATURITY_AMOUNT,
3010 MATURITY_BALANCE_AMOUNT,
3011 START_AMOUNT,
3012 CALC_BASIS,
3013 INTEREST_RATE,
3014 INTEREST_AMOUNT,
3015 ORIGINAL_AMOUNT,
3016 ROUNDING_TYPE,
3017 DAY_COUNT_TYPE,
3018 COMMENTS,
3019 INTERNAL_TICKET_NO,
3020 EXTERNAL_COMMENTS,
3021 EXTERNAL_CPARTY_NO,
3022 MATURITY_ACCOUNT_NO,
3023 CPARTY_ACCOUNT_NO,
3024 CPARTY_REF,
3025 PRINCIPAL_SETTLED_BY,
3026 SECURITY_ID,
3027 MARGIN,
3028 PRICING_MODEL,
3029 MARKET_DATA_SET,
3030 DEAL_LINKING_CODE,
3031 ACCEPTOR_CODE,
3032 ACCEPTOR_NAME,
3033 DRAWER_CODE,
3034 DRAWER_NAME,
3035 ENDORSER_CODE,
3036 ENDORSER_NAME,
3037 RISKPARTY_LIMIT_CODE,
3038 BROKERAGE_CODE,
3039 BROKERAGE_RATE,
3040 BROKERAGE_AMOUNT,
3041 BROKERAGE_CURRENCY,
3042 TAX_CODE,
3043 TAX_RATE,
3044 TAX_AMOUNT,
3045 TAX_SETTLED_REFERENCE,
3046 INCOME_TAX_CODE,
3047 INCOME_TAX_RATE,
3048 INCOME_TAX_AMOUNT,
3049 INCOME_TAX_SETTLED_REF,
3050 DUAL_AUTHORISATION_BY,
3051 DUAL_AUTHORISATION_ON,
3052 CREATED_BY,
3053 CREATED_ON,
3054 ATTRIBUTE_CATEGORY,
3055 ATTRIBUTE1,
3056 ATTRIBUTE2,
3057 ATTRIBUTE3,
3058 ATTRIBUTE4,
3059 ATTRIBUTE5,
3060 ATTRIBUTE6,
3061 ATTRIBUTE7,
3062 ATTRIBUTE8,
3063 ATTRIBUTE9,
3064 ATTRIBUTE10,
3065 ATTRIBUTE11,
3066 ATTRIBUTE12,
3067 ATTRIBUTE13,
3068 ATTRIBUTE14,
3069 ATTRIBUTE15,
3070 REQUEST_ID,
3071 PROGRAM_APPLICATION_ID,
3072 PROGRAM_ID,
3073 PROGRAM_UPDATE_DATE
3074 )
3075 VALUES
3076 (
3077 G_Ni_Main_Rec.EXTERNAL_DEAL_ID,
3078 G_Ni_Main_Rec.FREQUENCY,
3079 G_Ni_Main_Rec.DEAL_TYPE,
3080 G_Ni_Main_Rec.BROKERAGE_AMOUNT_HCE,
3081 G_Ni_Main_Rec.TAX_AMOUNT_HCE,
3082 G_Ni_Main_Rec.MATURITY_BALANCE_HCE_AMOUNT,
3083 G_Ni_Main_Rec.RISKPARTY_CODE,
3084 G_Ni_Main_Rec.YEAR_BASIS,
3085 G_Ni_Main_Rec.INTEREST_HCE_AMOUNT,
3086 G_Ni_Main_Rec.START_HCE_AMOUNT,
3087 G_Ni_Main_Rec.PORTFOLIO_AMOUNT,
3088 G_Ni_Main_Rec.MATURITY_HCE_AMOUNT,
3089 G_Ni_Main_Rec.PREMIUM_ACCOUNT_NO,
3090 G_Ni_Main_Rec.NI_DEAL_NO,
3091 G_Ni_Main_Rec.RENEG_DATE,
3092 G_Ni_Main_Rec.DEAL_NO,
3093 G_Ni_Main_Rec.STATUS_CODE,
3094 G_Ni_Main_Rec.DEALER_CODE,
3095 G_Ni_Main_Rec.DEAL_DATE,
3096 G_Ni_Main_Rec.COMPANY_CODE,
3097 G_Ni_Main_Rec.CPARTY_CODE,
3098 G_Ni_Main_Rec.CLIENT_CODE,
3099 G_Ni_Main_Rec.PORTFOLIO_CODE,
3100 G_Ni_Main_Rec.KNOCK_TYPE,
3101 G_Ni_Main_Rec.NI_PROFIT_LOSS,
3102 G_Ni_Main_Rec.DEAL_SUBTYPE,
3103 G_Ni_Main_Rec.PRODUCT_TYPE,
3104 G_Ni_Main_Rec.CURRENCY,
3105 G_Ni_Main_Rec.YEAR_CALC_TYPE,
3106 G_Ni_Main_Rec.START_DATE,
3107 G_Ni_Main_Rec.MATURITY_DATE,
3108 G_Ni_Main_Rec.NO_OF_DAYS,
3109 G_Ni_Main_Rec.MATURITY_AMOUNT,
3110 G_Ni_Main_Rec.MATURITY_BALANCE_AMOUNT,
3111 G_Ni_Main_Rec.START_AMOUNT,
3112 G_Ni_Main_Rec.CALC_BASIS,
3113 G_Ni_Main_Rec.INTEREST_RATE,
3114 G_Ni_Main_Rec.INTEREST_AMOUNT,
3115 G_Ni_Main_Rec.ORIGINAL_AMOUNT,
3116 G_Ni_Main_Rec.ROUNDING_TYPE,
3117 G_Ni_Main_Rec.DAY_COUNT_TYPE,
3118 G_Ni_Main_Rec.COMMENTS,
3119 G_Ni_Main_Rec.INTERNAL_TICKET_NO,
3120 G_Ni_Main_Rec.EXTERNAL_COMMENTS,
3121 G_Ni_Main_Rec.EXTERNAL_CPARTY_NO,
3122 G_Ni_Main_Rec.MATURITY_ACCOUNT_NO,
3123 G_Ni_Main_Rec.CPARTY_ACCOUNT_NO,
3124 G_Ni_Main_Rec.CPARTY_REF,
3125 G_Ni_Main_Rec.PRINCIPAL_SETTLED_BY,
3126 G_Ni_Main_Rec.SECURITY_ID,
3127 G_Ni_Main_Rec.MARGIN,
3128 G_Ni_Main_Rec.PRICING_MODEL,
3129 G_Ni_Main_Rec.MARKET_DATA_SET,
3130 G_Ni_Main_Rec.DEAL_LINKING_CODE,
3131 G_Ni_Main_Rec.ACCEPTOR_CODE,
3132 G_Ni_Main_Rec.ACCEPTOR_NAME,
3133 G_Ni_Main_Rec.DRAWER_CODE,
3134 G_Ni_Main_Rec.DRAWER_NAME,
3135 G_Ni_Main_Rec.ENDORSER_CODE,
3136 G_Ni_Main_Rec.ENDORSER_NAME,
3137 G_Ni_Main_Rec.RISKPARTY_LIMIT_CODE,
3138 G_Ni_Main_Rec.BROKERAGE_CODE,
3139 G_Ni_Main_Rec.BROKERAGE_RATE,
3140 G_NI_Main_Rec.BROKERAGE_AMOUNT,
3141 G_Ni_Main_Rec.BROKERAGE_CURRENCY,
3142 G_Ni_Main_Rec.TAX_CODE,
3143 G_Ni_Main_Rec.TAX_RATE,
3144 G_Ni_Main_Rec.TAX_AMOUNT,
3145 G_Ni_Main_Rec.TAX_SETTLED_REFERENCE,
3146 G_Ni_Main_Rec.INCOME_TAX_CODE,
3147 G_Ni_Main_Rec.INCOME_TAX_RATE,
3148 G_Ni_Main_Rec.INCOME_TAX_AMOUNT,
3149 G_Ni_Main_Rec.INCOME_TAX_SETTLED_REF,
3150 l_dual_user,
3151 l_dual_date,
3152 G_Ni_Main_Rec.CREATED_BY,
3153 G_Ni_Main_Rec.CREATED_ON,
3154 G_Ni_Main_Rec.ATTRIBUTE_CATEGORY,
3155 G_Ni_Main_Rec.ATTRIBUTE1,
3156 G_Ni_Main_Rec.ATTRIBUTE2,
3157 G_Ni_Main_Rec.ATTRIBUTE3,
3158 G_Ni_Main_Rec.ATTRIBUTE4,
3159 G_Ni_Main_Rec.ATTRIBUTE5,
3160 G_Ni_Main_Rec.ATTRIBUTE6,
3161 G_Ni_Main_Rec.ATTRIBUTE7,
3162 G_Ni_Main_Rec.ATTRIBUTE8,
3163 G_Ni_Main_Rec.ATTRIBUTE9,
3164 G_Ni_Main_Rec.ATTRIBUTE10,
3165 G_Ni_Main_Rec.ATTRIBUTE11,
3166 G_Ni_Main_Rec.ATTRIBUTE12,
3167 G_Ni_Main_Rec.ATTRIBUTE13,
3168 G_Ni_Main_Rec.ATTRIBUTE14,
3169 G_Ni_Main_Rec.ATTRIBUTE15,
3170 FND_GLOBAL.conc_request_id,
3171 FND_GLOBAL.prog_appl_id,
3172 FND_GLOBAL.conc_program_id,
3173 g_curr_date
3174 );
3175
3176
3177 --Parcels
3178 -- WDK: change to forall
3179 for i in 1..g_num_parcels loop
3180
3181 if (g_ni_deal_subtype = 'ISSUE' and G_Ni_Parcel_Rec(i).SERIAL_NUMBER is not null) then
3182
3183 open get_bank_code;
3184 fetch get_bank_code into l_bank_code;
3185 close get_bank_code;
3186
3187 open get_bank_issue_code(G_Ni_Parcel_Rec(i).SERIAL_NUMBER);
3188 fetch get_bank_issue_code into G_Ni_Parcel_Rec(i).ISSUE_BANK;
3189 close get_bank_issue_code;
3190
3191 update XTR_bill_bond_issues_V
3192 set issue_date = G_Ni_Main_Rec.DEAL_DATE,
3193 deal_number = G_Ni_Main_Rec.DEAL_NO,
3194 status = G_Ni_Parcel_Rec(i).STATUS_CODE,
3195 parcel_split_no = G_Ni_Parcel_Rec(i).PARCEL_SPLIT_NO,
3196 due_date = G_Ni_Main_Rec.MATURITY_DATE,
3197 currency = G_Ni_Main_Rec.CURRENCY,
3198 amount = G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT,
3199 bank_code = l_bank_code
3200 where ni_or_bond = 'NI'
3201 and serial_number = G_Ni_Parcel_Rec(i).SERIAL_NUMBER;
3202
3203 else
3204 G_Ni_Parcel_Rec(i).SERIAL_NUMBER := null;
3205 G_Ni_Parcel_Rec(i).SERIAL_NUMBER_IN := null;
3206 G_Ni_Parcel_Rec(i).ISSUE_BANK := null;
3207 end if;
3208
3209 insert into xtr_parcel_splits(
3210 DEAL_NO,
3211 PARCEL_SPLIT_NO,
3212 PARCEL_SIZE,
3213 FACE_VALUE_AMOUNT,
3214 CONSIDERATION,
3215 INTEREST,
3216 STATUS_CODE,
3217 DEAL_SUBTYPE,
3218 AVAILABLE_FOR_RESALE,
3219 PARCEL_REMAINING,
3220 SELECT_NUMBER,
3221 SELECT_FV_AMOUNT,
3222 REFERENCE_NUMBER,
3223 RESERVE_PARCEL,
3224 OLD_SELECT_NUMBER,
3225 SERIAL_NUMBER,
3226 SERIAL_NUMBER_IN,
3227 ISSUE_BANK,
3228 ORIGINAL_AMOUNT
3229 )
3230 Values(
3231 G_Ni_Parcel_Rec(i).DEAL_NO,
3232 G_Ni_Parcel_Rec(i).PARCEL_SPLIT_NO,
3233 G_Ni_Parcel_Rec(i).PARCEL_SIZE,
3234 G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT,
3235 G_Ni_Parcel_Rec(i).CONSIDERATION,
3236 G_Ni_Parcel_Rec(i).INTEREST,
3237 G_Ni_Parcel_Rec(i).STATUS_CODE,
3238 G_Ni_Parcel_Rec(i).DEAL_SUBTYPE,
3239 G_Ni_Parcel_Rec(i).AVAILABLE_FOR_RESALE,
3240 G_Ni_Parcel_Rec(i).PARCEL_REMAINING,
3241 G_Ni_Parcel_Rec(i).SELECT_NUMBER,
3242 G_Ni_Parcel_Rec(i).SELECT_FV_AMOUNT,
3243 G_Ni_Parcel_Rec(i).REFERENCE_NUMBER,
3244 G_Ni_Parcel_Rec(i).RESERVE_PARCEL,
3245 G_Ni_Parcel_Rec(i).OLD_SELECT_NUMBER,
3246 G_Ni_Parcel_Rec(i).SERIAL_NUMBER,
3247 G_Ni_Parcel_Rec(i).SERIAL_NUMBER_IN,
3248 G_Ni_Parcel_Rec(i).ISSUE_BANK,
3249 G_Ni_Parcel_Rec(i).ORIGINAL_AMOUNT
3250 );
3251
3252 end loop;
3253
3254 CREATE_TRANSACTIONS;
3255
3256 WEIGHTED_BROKERAGE_AMOUNT;
3257
3258 SET_INITIAL_FAIR_VALUE;
3259
3260 CALL_ONE_STEP_SETTLEMENT(G_Ni_Main_Rec.TAX_SETTLED_REFERENCE,G_Ni_Main_Rec.INCOME_TAX_SETTLED_REF);
3261
3262
3263 if l_dual_user is not null then
3264 UPDATE xtr_deal_date_amounts
3265 SET dual_authorisation_by = l_dual_user,
3266 dual_authorisation_on = l_dual_date
3267 WHERE deal_number = G_Ni_Main_Rec.DEAL_NO;
3268
3269 UPDATE xtr_confirmation_details
3270 SET confirmation_validated_by = l_dual_user,
3271 confirmation_validated_on = l_dual_date
3272 WHERE deal_no = G_Ni_Main_Rec.DEAL_NO;
3273
3274 UPDATE xtr_deals
3275 SET dual_authorisation_on = l_dual_date
3276 WHERE deal_no = G_Ni_Main_Rec.DEAL_NO;
3277 end if;
3278
3279 END CREATE_NI_DEAL;
3280
3281 END XTR_NI_TRANSFERS_PKG;