[Home] [Help]
PACKAGE BODY: APPS.XTR_IG_TRANSFERS_PKG
Source
1 PACKAGE BODY XTR_IG_TRANSFERS_PKG AS
2 /* $Header: xtrimigb.pls 120.21 2005/06/29 09:53:26 csutaria ship $ */
3
4
5 -------------------------------------------------------------------------------------------------------------------
6 Procedure Log_IG_Errors(p_Ext_Deal_Id In Varchar2,
7 p_Deal_Type In Varchar2,
8 p_Error_Column In Varchar2,
9 p_Error_Code In Varchar2,
10 p_Field_Name In Varchar2) is
11 -------------------------------------------------------------------------------------------------------------------
12
13 cursor c_text is
14 select text
15 from xtr_sys_languages_vl
16 where item_name = p_Field_Name;
17
18 p_text xtr_sys_languages_vl.text%TYPE;
19
20 begin
21
22 if G_Ig_Source is null and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA, C_CL) then
23 xtr_import_deal_data.log_interface_errors(p_ext_deal_id,
24 p_deal_type,
25 p_error_column,
26 p_error_code);
27
28 else
29 if p_Error_Code in ('XTR_MANDATORY', 'XTR_INV_LIMIT_CODE',
30 'XTR_IMP_DEAL_REVAL_EXIST', 'XTR_IMP_DEAL_ACCRUAL_EXIST',
31 'XTR_LIMIT_EXCEEDED','XTR_INV_DESC_FLEX_API','XTR_INV_DESC_FLEX_CONTEXT',
32 'XTR_INV_DESC_FLEX') then
33 -------------------------------
34 -- Get the dynamic prompt text.
35 -------------------------------
36 open c_text;
37 fetch c_text into p_text;
38 close c_text;
39
40 if p_Error_code = 'XTR_MANDATORY' then
41 FND_MESSAGE.Set_Name('XTR','XTR_MANDATORY_FIELD'); -- AW new message
42 FND_MESSAGE.Set_Token('FIELD', p_text);
43
44 elsif p_Error_code = 'XTR_INV_LIMIT_CODE' then
45 FND_MESSAGE.Set_Name('XTR','XTR_INV_LIMIT_CODE_FIELD'); -- AW new message
46 FND_MESSAGE.Set_Token('LIMIT_CODE', p_text);
47
48 elsif p_Error_code = 'XTR_IMP_DEAL_REVAL_EXIST' then
49 FND_MESSAGE.Set_Name ('XTR', 'XTR_DEAL_REVAL_DONE');
50 FND_MESSAGE.Set_Token ('DATE',p_field_name);
51
52 elsif p_Error_code = 'XTR_IMP_DEAL_ACCRUAL_EXIST' then
53 FND_MESSAGE.Set_Name ('XTR', 'XTR_DEAL_ACCRLS_EXIST');
54 FND_MESSAGE.Set_Token ('DATE',p_field_name);
55
56 elsif p_Error_code in ('XTR_INV_DESC_FLEX_API','XTR_INV_DESC_FLEX_CONTEXT','XTR_INV_DESC_FLEX') then
57 FND_MESSAGE.Set_Name ('XTR', 'XTR_INV_DESC_FLEX_API');
58
59 elsif p_Error_code = 'XTR_LIMIT_EXCEEDED' then
60 null; -- do nothing, return error to calling form to handle limits checks.
61
62 end if;
63 else
64 FND_MESSAGE.Set_Name('XTR', p_Error_Code);
65 end if;
66
67 --*****************************************************************************************************************
68 -- 3800146 Modified for ZBA and CL ******************************************************************************
69 --*****************************************************************************************************************
70 -- Populate message to stack for CE to retrieve
71 if nvl(G_Ig_External_Source,'@@@') in (C_ZBA, C_CL) then
72 fnd_msg_pub.add;
73 --dbms_output.put_line('imig Error = '|| p_error_code||' : '||p_field_name);
74 else
75 APP_EXCEPTION.raise_exception;
76 end if;
77
78 end if;
79
80 end;
81
82
83 /* RV: 2229236 */
84 /*-------------------------------------------------------------------------------------*/
85 /* The following function returns the total outstanding balance for the */
86 /* deal including the current transaction for doign limit check */
87 /*-------------------------------------------------------------------------------------*/
88 -----------------------------------------------------------------------------------------
89 function GET_LIMIT_AMOUNT (p_company_code IN VARCHAR2 ,
90 p_party_code IN VARCHAR2 ,
91 p_currency IN VARCHAR2 ,
92 p_adjust IN NUMBER ,
93 p_action_code IN VARCHAR2) return number is
94 -----------------------------------------------------------------------------------------
95 cursor ins_bal is
96 Select sum(cashflow_amount) balance
97 from xtr_deal_date_amounts
98 where cparty_code = p_party_code
99 and company_code = p_company_code
100 and currency = p_currency
101 and deal_type = 'IG'
102 and amount_type = 'PRINFLW';
103
104 l_balance NUMBER;
105
106 begin
107 open ins_bal;
108 fetch ins_bal into l_balance;
109 close ins_bal;
110
111 if p_action_code = 'PAY' then
112 l_balance := nvl(-l_balance,0)+nvl(p_adjust,0);
113 else
114 l_balance := nvl(-l_balance,0)-nvl(p_adjust,0);
115 end if;
116
117 return (nvl(l_balance,0));
118 exception
119 when others then
120 if ins_bal%ISOPEN then close ins_bal;end if;
121 return (0);
122 end;
123
124
125 /* RV: 2229236 */
126 -----------------------------------------------------------------------------------------------------
127 function IS_COMPANY(p_comp IN VARCHAR2) return boolean is
128 -----------------------------------------------------------------------------------------------------
129 cursor curs(c_comp IN VARCHAR2) is
130 select 'Y'
131 from XTR_PARTIES_V
132 where party_code = c_comp
133 and party_type = 'C';
134
135 l_dummy VARCHAR2(1);
136
137 begin
138 open curs (p_comp);
139 fetch curs into l_dummy;
140 if curs%NOTFOUND then
141 close curs;
142 return(FALSE);
143 else
144 close curs;
145 return(TRUE);
146 end if;
147
148 exception
149 when others then
150 if curs%ISOPEN then close curs; end if;
151 return(FALSE);
152 end;
153
154 /* RV: 2229236 */
155 -----------------------------------------------------------------------------------------------------
156 function IS_MIRROR_DEAL(p_comp IN VARCHAR2,
157 p_cparty IN VARCHAR2,
158 p_curr IN VARCHAR2) return boolean is
159 -----------------------------------------------------------------------------------------------------
160 cursor mirror_deal(c_comp IN Xtr_Intergroup_Transfers.Company_Code%type,
161 c_cparty IN Xtr_Intergroup_Transfers.Party_Code%type,
162 c_curr IN Xtr_Intergroup_Transfers.Currency%type) is
163 select mirror_Deal
164 from xtr_intergroup_transfers
165 where company_Code = c_comp
166 and party_Code = c_cparty
167 and currency = c_curr
168 and mirror_Deal = 'Y';
169
170 l_dummy VARCHAR2(1);
171
172 begin
173 open mirror_deal(p_comp, p_cparty, p_curr);
174 fetch mirror_deal into l_dummy;
175 if mirror_deal%NOTFOUND then
176 close mirror_deal;
177 return(FALSE);
178 else
179 close mirror_deal;
180 return(TRUE);
181 end if;
182
183 exception
184 when others then
185 if mirror_deal%ISOPEN then close mirror_deal; end if;
186 return(FALSE);
187 end;
188
189 -----------------------------------------------------------------------------------------------------
190 --* Procedure to update pricing model of all transactions with the same deal number (ie. same company,
191 --* counterparty, and currency)
192 procedure UPDATE_PRICING_MODEL(p_company_code VARCHAR2,
193 p_party_code VARCHAR2,
194 p_currency VARCHAR2,
195 p_pricing_model VARCHAR2) is
196 -----------------------------------------------------------------------------------------------------
197 cursor get_pricing_model is
198 select pricing_model, deal_number
199 from xtr_intergroup_transfers_v
200 where company_code = p_company_code
201 and party_code = p_party_code
202 and currency = p_currency;
203
204 l_deal_number NUMBER;
205 l_pricing_model VARCHAR2(30);
206
207 BEGIN
208 open get_pricing_model;
209 fetch get_pricing_model into l_pricing_model, l_deal_number;
210 close get_pricing_model;
211
212 if l_deal_number is not null AND p_pricing_model <> l_pricing_model then
213 UPDATE xtr_intergroup_transfers
214 SET pricing_model = p_pricing_model
215 WHERE deal_number = l_deal_number;
216 end if;
217 END;
218
219 -----------------------------------------------------------------------------------------------------
220 --* Procedure to return the default pricing model given a company, counterparty, currency and
221 --* product type. First, if only one pricing model is authorized, that is the default. If one or
222 --* more are authorized, the pricing model for transactions under the same deal number is the default.
223 --* If no such deal exists, default is based on the product type. If default pricing model is null
224 --* for this product type, we compare currency to SOB Currency of the company. If two currencies
225 --* are equal, default is 'NO_REVAL'. If different, default is 'FACE_VALUE'.
226 procedure DEFAULT_PRICING_MODEL(p_company_code IN VARCHAR2,
227 p_party_code IN VARCHAR2,
228 p_currency IN VARCHAR2,
229 p_product_type IN VARCHAR2,
230 p_pricing_model OUT NOCOPY VARCHAR2) is
231 -----------------------------------------------------------------------------------------------------
232 cursor get_pricing_model is
233 select pricing_model
234 from xtr_intergroup_transfers_v
235 where company_code = p_company_code
236 and party_code = p_party_code
237 and currency = p_currency;
238
239 cursor default_pm is
240 select default_pricing_model
241 from xtr_product_types_v
242 where deal_type = 'IG'
243 and product_type = p_product_type;
244
245 cursor number_of_auth_pm is
246 select count(*)
247 from xtr_price_models
248 where deal_type = 'IG'
249 and authorized = 'Y';
250
251 cursor auth_pm is
252 select code
253 from xtr_price_models
254 where deal_type = 'IG'
255 and authorized = 'Y';
256
257 cursor get_sob_currency is
258 select sob.currency_code
259 from xtr_party_info pinfo, gl_sets_of_books sob
260 where pinfo.party_code = p_company_code
261 and pinfo.set_of_books_id = sob.set_of_books_id;
262
263 l_pm VARCHAR2(30);
264 l_dummy NUMBER;
265 l_sob_currency VARCHAR2(30);
266
267 BEGIN
268
269 open number_of_auth_pm;
270 fetch number_of_auth_pm into l_dummy;
271 close number_of_auth_pm;
272 if l_dummy = 1 then
273 open auth_pm;
274 fetch auth_pm into p_pricing_model;
275 close auth_pm;
276 else
277 open get_pricing_model;
278 fetch get_pricing_model into l_pm;
279 close get_pricing_model;
280 if l_pm is not null then
281 p_pricing_model := l_pm;
282 else
283 open default_pm;
284 fetch default_pm into l_pm;
285 close default_pm;
286 if l_pm is not null then
287 p_pricing_model := l_pm;
288 else
289 open get_sob_currency;
290 fetch get_sob_currency into l_sob_currency;
291 close get_sob_currency;
292 if l_sob_currency <> p_currency then
293 p_pricing_model := 'FACE_VALUE';
294 else
295 p_pricing_model := 'NO_REVAL';
296 end if;
297 end if;
298 end if;
299 end if;
300 END;
301
302
303 /* RV: 2229236 */
304 /*-------------------------------------------------------------------------------------*/
305 /* The following code implements the duplicate deal check */
306 /* by best match for the mirror deal */
307 /*-------------------------------------------------------------------------------------*/
308 -----------------------------------------------------------------------------------------
309 procedure CHECK_MIRROR_DUPLICATE(p_company_code IN VARCHAR2,
310 p_party_code IN VARCHAR2,
311 p_currency IN VARCHAR2,
312 p_transfer_date IN DATE,
313 p_action_code IN VARCHAR2,
314 p_principal_adjust IN NUMBER,
315 p_company_account IN VARCHAR2,
316 p_party_account IN VARCHAR2,
317 duplicate_error OUT NOCOPY BOOLEAN) is
318 -----------------------------------------------------------------------------------------
319 l_count NUMBER;
320
321 begin
322 select count(*)
323 into l_count
324 from XTR_INTERGROUP_TRANSFERS
325 where company_code = p_company_code
326 and party_code = p_party_code
327 and currency = p_currency
328 and transfer_date = p_transfer_Date
329 and principal_action = p_action_code
330 and principal_adjust = p_principal_adjust
331 and company_account_no = p_companY_account
332 and party_account_no = p_party_account;
333
334 if (l_count > 0) then
335 duplicate_error := TRUE;
336 else
337 duplicate_error := FALSE;
338 end if;
339
340 end CHECK_MIRROR_DUPLICATE;
341
342
343 --RV 2229236
344 ------------------------------------------------------------------------------------
345 -- Local procedure to insert Jornal structures for new IG deals
346 -- Flows
347 procedure INS_IG_JRNL_STRUC(p_company_code IN VARCHAR2,
348 p_party_code IN VARCHAR2,
349 p_currency IN VARCHAR2,
350 p_party_acct IN VARCHAR2) is
351 ------------------------------------------------------------------------------------
352
353 Cursor ig_jrnl(comp_code IN VARCHAR2,
354 party_code IN VARCHAR2,
355 currency IN VARCHAR2,
356 party_acct_no IN VARCHAR2) is
357 select 'Y'
358 from xtr_ig_journal_structures
359 where company_code = comp_code
360 and cparty_code = party_code
361 and cp_currency = currency
362 and cp_acct_no = party_acct_no;
363
364 Cursor party_dtls(party_code IN VARCHAR2,
365 party_acct_no IN VARCHAR2) is
366 select currency
367 from xtr_bank_Accounts
368 where account_number = party_acct_no
369 and party_code = party_code;
370
371 l_jrnl VARCHAR2(1);
372 l_jrnl_struc_id NUMBER;
373 l_currency VARCHAR2(15);
374
375 begin
376
377 open ig_jrnl(p_company_code,p_party_code,p_currency,p_party_acct);
378 fetch ig_jrnl into l_jrnl;
379 close ig_jrnl;
380
381 if l_jrnl is null then
382
383 select xtr_ig_journal_structures_s.nextval into l_jrnl_struc_id from dual;
384
385 open party_dtls(p_party_code,p_party_acct);
386 fetch party_dtls into l_currency;
387 close party_dtls;
388
389 insert into xtr_ig_journal_structures( xtr_ig_journal_structure_id,company_code,cparty_code,
390 cp_currency,cp_acct_no,created_by,creation_Date,last_updated_by,
391 last_update_date,last_update_login)
392 values (l_jrnl_struc_id,p_company_code,p_party_code,
393 l_currency,p_party_acct,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
397
394 SYSDATE,FND_GLOBAL.LOGIN_ID);
395 end if;
396 end;
398
399 -----------------------------------------------------------------------------------------------------
400 function VALID_CPARTY_CODE(p_comp IN VARCHAR2,
401 p_cparty IN VARCHAR2) return boolean is
402 -----------------------------------------------------------------------------------------------------
403 cursor curs(c_comp IN VARCHAR2, c_cparty IN VARCHAR2) is
404 select 'Y'
405 from XTR_PARTIES_V
406 where party_code = c_cparty
407 and ((internal_pty = 'Y' and cross_ref_to_other_party = c_comp)
408 or (party_type = 'C' and party_code <> c_comp));
409
410 l_dummy VARCHAR2(1);
411
412 begin
413 open curs (p_comp , p_cparty);
414 fetch curs into l_dummy;
415 if curs%NOTFOUND then
416 close curs;
417 return(FALSE);
418 else
419 close curs;
420 return(TRUE);
421 end if;
422
423 exception
424 when others then
425 if curs%ISOPEN then close curs; end if;
426 return(FALSE);
427 end;
428
429 -----------------------------------------------------------------------------------------------------
430 function VALID_TRANSFER_DATE(p_transfer_date IN DATE) return boolean is
431 -----------------------------------------------------------------------------------------------------
432
433 begin
434 -- bug 3305424 - relax restriction on transfer_date
435 return(TRUE);
436
437 end;
438
439
440 -----------------------------------------------------------------------------------------------------
441 function VALID_CURRENCY(p_curr IN VARCHAR2) return boolean is
442 -----------------------------------------------------------------------------------------------------
443 cursor curs(c_curr IN VARCHAR2) is
444 select ig_year_basis
445 from XTR_MASTER_CURRENCIES_V
446 where currency = c_curr
447 and nvl(authorised, 'N') = 'Y';
448
449
450
451 begin
452 open curs (p_curr);
453 fetch curs into G_Ig_year_calc_type;
454 if curs%NOTFOUND then
455 close curs;
456 return(FALSE);
457 else
458 close curs;
459 G_Ig_year_calc_type := nvl(G_Ig_year_calc_type,'ACTUAL/ACTUAL');
460 return(TRUE);
461 end if;
462
463 exception
464 when others then
465 if curs%ISOPEN then close curs; end if;
466 return(FALSE);
467 end;
468
469
470 -----------------------------------------------------------------------------------------------------
471 function VALID_COMP_ACCT(p_comp IN VARCHAR2,
472 p_comp_acct IN VARCHAR2,
473 p_curr IN VARCHAR2) return boolean is
474 -----------------------------------------------------------------------------------------------------
475 cursor curs(c_comp IN VARCHAR2, c_comp_acct IN VARCHAR2, c_curr IN VARCHAR2) is
476 select 'Y'
477 from XTR_COMPANY_ACCT_LOV_V
478 where company_code = c_comp
479 and account_number = c_comp_acct
480 and currency = c_curr;
481
482 l_dummy VARCHAR2(1);
483
484 begin
485 open curs (p_comp, p_comp_acct, p_curr);
486 fetch curs into l_dummy;
487 if curs%NOTFOUND then
488 close curs;
489 return(FALSE);
490 else
491 close curs;
492 return(TRUE);
493 end if;
494 exception
495 when others then
496 if curs%ISOPEN then close curs; end if;
497 return(FALSE);
498 end;
499
500
501 -----------------------------------------------------------------------------------------------------
502 function VALID_PARTY_ACCT(p_party IN VARCHAR2,
503 p_party_acct IN VARCHAR2,
504 p_curr IN VARCHAR2) return boolean is
505 -----------------------------------------------------------------------------------------------------
506 cursor curs(c_party IN VARCHAR2,
507 c_curr IN VARCHAR2,
508 c_party_acct IN VARCHAR2) is
509 select 'Y'
510 from XTR_BANK_ACCOUNTS_V
511 where party_code = c_party
512 and currency = c_curr
513 and account_number = c_party_acct
514 and nvl(authorised,'N') = 'Y'
515 and nvl(setoff_account_yn,'N') <> 'Y'
516 and bank_code IS NOT NULL;
517 /* Bug 4322706 Added the last AND condition. */
518
519 l_dummy VARCHAR2(1);
520
521 begin
522 open curs (p_party, p_curr, p_party_acct);
523 fetch curs into l_dummy;
524 if curs%NOTFOUND then
525 close curs;
526 return(FALSE);
527 else
528 close curs;
529 return(TRUE);
530 end if;
531
532 exception
533 when others then
534 if curs%ISOPEN then close curs; end if;
535 return(FALSE);
536 end;
537
538
539 -----------------------------------------------------------------------------------------------------
540 function VALID_ACTION(p_action IN VARCHAR2) return boolean is
541 -----------------------------------------------------------------------------------------------------
545 where deal_type = 'IG'
542 cursor curs(c_action IN VARCHAR2) is
543 select ACTION_CODE
544 from XTR_AMOUNT_ACTIONS
546 and amount_type = 'PRINFLW'
547 and ( (user_action_code = c_action and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA, C_CL)) or
548 (action_code = c_action and nvl(G_Ig_External_Source,'@@@') in (C_ZBA, C_CL)) ); -- 3800146 actual code
549
550 begin
551 open curs (p_action);
552 fetch curs into G_Ig_action;
553 if curs%NOTFOUND then
554 close curs;
555 return(FALSE);
556 else
557 close curs;
558 return(TRUE);
559 end if;
560 exception
561 when others then
562 if curs%ISOPEN then close curs; end if;
563 return(FALSE);
564 end;
565
566 -----------------------------------------------------------------------------------------------------
567 function VALID_PRODUCT(p_product IN VARCHAR2) return boolean is
568 -----------------------------------------------------------------------------------------------------
569 cursor curs(c_product in varchar2) is
570 select 'Y'
571 from XTR_AUTH_PRODUCT_TYPES_V
572 where deal_type = 'IG'
573 and Product_Type = c_product;
574
575 l_dummy VARCHAR2(1);
576
577 begin
578 open curs (p_product);
579 fetch curs into l_dummy;
580 if curs%NOTFOUND then
581 close curs;
582 return(FALSE);
583 else
584 close curs;
585 return(TRUE);
586 end if;
587
588 exception
589 when others then
590 if curs%ISOPEN then close curs; end if;
591 return(FALSE);
592 end;
593
594 -----------------------------------------------------------------------------------------------------
595 function VALID_PORTFOLIO(p_comp IN VARCHAR2,
596 p_cparty IN VARCHAR2,
597 p_portfolio IN VARCHAR2) return boolean is
598 -----------------------------------------------------------------------------------------------------
599 cursor curs(c_comp in varchar2, c_cparty in varchar2, c_portfolio in varchar2) is
600 select 'Y'
601 from XTR_PORTFOLIOS_V
602 where company_code = c_comp
603 and nvl(cmf_yn,'N') = 'N'
604 and (external_party is null or external_party = c_cparty)
605 and portfolio = c_portfolio;
606
607 l_dummy VARCHAR2(1);
608
609 begin
610 open curs (p_comp, p_cparty, p_portfolio);
611 fetch curs into l_dummy;
612 if curs%NOTFOUND then
613 close curs;
614 return(FALSE);
615 else
616 close curs;
617 return(TRUE);
618 end if;
619
620 exception
621 when others then
622 if curs%ISOPEN then close curs; end if;
623 return(FALSE);
624 end;
625
626 --------------------------------------------------------------------------------------------
627 FUNCTION VALID_PRICING_MODEL(p_pricing_model IN VARCHAR2) return BOOLEAN is
628 --------------------------------------------------------------------------------------------
629 cursor cur_pricing is
630 select code
631 from xtr_price_models
632 where code = p_pricing_model
633 and deal_type = 'IG'
634 and nvl(authorized,'N') = 'Y';
635
636 l_dummy VARCHAR2(30);
637
638 BEGIN
639 open cur_pricing;
640 fetch cur_pricing into l_dummy;
641 if cur_pricing%NOTFOUND then
642 close cur_pricing;
643 return(FALSE);
644 end if;
645 close cur_pricing;
646 return(TRUE);
647
648 END VALID_PRICING_MODEL;
649
650
651 -- Bug 2994712
652
653 /*--------------------------------------------------------------------------------*/
654 FUNCTION valid_deal_linking_code( p_deal_linking_code IN varchar2) return BOOLEAN is
655 /*--------------------------------------------------------------------------------*/
656 l_temp varchar2(1);
657 BEGIN
658 IF p_deal_linking_code is not null then
659 BEGIN
660 select 'Y'
661 into l_temp
662 from xtr_deal_linking_v
663 where deal_linking_code = p_deal_linking_code
664 and rownum = 1;
665 EXCEPTION
666 WHEN NO_DATA_FOUND THEN
667 return(FALSE);
668 END;
669 END IF;
670 return TRUE;
671 END valid_deal_linking_code;
672
673 -- Bug 2994712
674
675 -- Bug 2684411
676 /*--------------------------------------------------------------------------------*/
677 FUNCTION valid_dealer_code(p_dealer_code IN VARCHAR2) return BOOLEAN is
678 /*--------------------------------------------------------------------------------*/
679 l_temp varchar2(1);
680 BEGIN
681 BEGIN
682 select 'Y'
683 into l_temp
684 from xtr_dealer_codes_v
685 where dealer_code = p_dealer_code
686 and rownum = 1;
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 return(FALSE);
690 END;
691 return(TRUE);
692 END valid_dealer_code;
693
694 -- Bug 2684411
695
696
697 /*----------------2549633--------------*/
698 -----------------------------------------------------------------------------------------------------
699 function VALID_DAY_COUNT_TYPE(p_day_count_type IN VARCHAR2) return boolean is
703 select count(*)
700 -----------------------------------------------------------------------------------------------------
701 l_dummy NUMBER;
702 begin
704 into l_dummy
705 from fnd_lookups
706 where lookup_type='XTR_DAY_COUNT_TYPE'
707 and lookup_code=p_day_count_type;
708
709 if (l_dummy=1) then
710 return(TRUE);
711 else
712 return(FALSE);
713 end if;
714 end;
715
716 -----------------------------------------------------------------------------------------------------
717 function VALID_ROUNDING_TYPE(p_rounding_type IN VARCHAR2) return boolean is
718 -----------------------------------------------------------------------------------------------------
719 l_dummy NUMBER;
720 begin
721 select count(*)
722 into l_dummy
723 from fnd_lookups
724 where lookup_type='XTR_ROUNDING_TYPE'
725 and lookup_code=p_rounding_type;
726
727 if (l_dummy=1) then
728 return(TRUE);
729 else
730 return(FALSE);
731 end if;
732 end;
733
734 /*----------------2549633--------------*/
735
736 -----------------------------------------------------------------------------------------------------
737 function VALID_LIMIT_CODE(p_comp IN VARCHAR2,
738 p_cparty IN VARCHAR2,
739 p_limit IN VARCHAR2,
740 p_limit_type IN VARCHAR2) return boolean is
741 --p_balance IN NUMBER) return boolean is
742 -----------------------------------------------------------------------------------------------------
743 cursor curs(c_comp in varchar2, c_cparty in varchar2, c_limit in varchar2, c_limit_type in varchar2) is
744 select 'Y'
745 from xtr_counterparty_limits_v a,
746 xtr_limit_types_v b
747 where a.company_code = c_comp
748 and a.cparty_code = c_cparty
749 and a.limit_code = c_limit
750 and a.limit_type = b.limit_type
751 and nvl(a.authorised,'N') = 'Y'
752 and (a.expiry_date > G_Ig_curr_date or a.expiry_date is null )
753 and ((b.fx_invest_fund_type in ('F','X') and c_limit_type = 'FUND')
754 or (b.fx_invest_fund_type in ('I','X') and c_limit_type = 'INVEST'));
755 --and ((b.fx_invest_fund_type='F' and c_balance < 0)
756 --or (b.fx_invest_fund_type='I' and c_balance > 0) or c_balance = 0)
757
758 l_dummy VARCHAR2(1);
759
760 begin
761
762 if p_limit is not null then
763 open curs (p_comp, p_cparty, p_limit, p_limit_type);
764 fetch curs into l_dummy;
765 if curs%NOTFOUND then
766 close curs;
767 return(FALSE);
768 else
769 close curs;
770 return(TRUE);
771 end if;
772
773 else
774 return(TRUE);
775 end if;
776
777 exception
778 when others then
779 if curs%ISOPEN then close curs; end if;
780 return(FALSE);
781 end;
782
783 -----------------------------------------------------------------------------------------------------
784 function VALID_PRINCIPAL_ADJUST(p_value IN NUMBER) return boolean is
785 -----------------------------------------------------------------------------------------------------
786 begin
787 if nvl(p_value,0) < 0 then
788 return(FALSE);
789 else
790 return(TRUE);
791 end if;
792 end;
793
794 -----------------------------------------------------------------------------------------------------
795 function VALID_SETTLEMENT_FLAG(p_value IN VARCHAR2) return boolean is -- 3800146
796 -----------------------------------------------------------------------------------------------------
797 begin
798 if nvl(p_value,'N') not in ('Y','N') then
799 return(FALSE);
800 else
801 return(TRUE);
802 end if;
803 end;
804
805
806 -----------------------------------------------------------------------------------------------------
807 function VALID_COMP_REPORTING_CCY (p_comp IN VARCHAR2) return boolean is
808 -----------------------------------------------------------------------------------------------------
809 cursor RND_FAC is
810 select 'Y'
811 from xtr_PARTIES_v p,
812 xtr_MASTER_CURRENCIES_v m
813 where p.PARTY_CODE = p_comp
814 and m.CURRENCY = p.HOME_CURRENCY;
815
816 l_dummy VARCHAR2(1);
817
818 begin
819
820 open RND_FAC;
821 fetch RND_FAC into l_dummy;
822 if RND_FAC%NOTFOUND then
823 close RND_FAC;
824 return(FALSE);
825 end if;
826 close RND_FAC;
827 return(TRUE);
828
829 end;
830
831
832 -----------------------------------------------------------------------------------------------------
833 procedure VALID_IG_ACCT(p_comp IN VARCHAR2,
834 p_cparty IN VARCHAR2,
835 p_curr IN VARCHAR2,
836 p_transfer_date IN DATE,
837 p_ext_deal_no IN VARCHAR2,
838 p_deal_type IN VARCHAR2,
842 l_deal_no NUMBER;
839 p_error IN OUT NOCOPY BOOLEAN) is
840 -----------------------------------------------------------------------------------------------------
841
843 l_tran_no NUMBER;
844 l_batch NUMBER;
845
846 -- Find existing reval details for this deal.
847 cursor cur_reval_deal IS
848 SELECT rd.batch_id
849 FROM xtr_intergroup_transfers it,
850 xtr_revaluation_details rd
851 WHERE it.deal_number = l_deal_no
852 AND it.deal_number = rd.deal_no
853 AND period_to >= p_transfer_date; -- bug 4367386
854
855 -- Find existing accrual details for this deal.
856 cursor cur_accrl_deal IS
857 SELECT r.batch_id
858 FROM xtr_batches b,
859 xtr_batch_events e,
860 xtr_accrls_amort r
861 WHERE r.deal_no = l_deal_no
862 AND r.batch_id = b.batch_id
863 AND b.batch_id = e.batch_id
864 AND r.period_to >= p_transfer_date -- bug 4367386
865 AND e.event_code = 'ACCRUAL';
866
867 begin
868
869 p_error := FALSE;
870
871 ------------------------------------------
872 -- Find out if this is an existing deal.
873 ------------------------------------------
874 GET_DEAL_TRAN_NUMBERS(p_comp,
875 p_cparty,
876 p_curr,
877 l_deal_no,
878 l_tran_no,
879 'N'); -- do not generate a new deal number
880
881 -------------------------------------------------
882 -- Validation required only for existing deal.
883 -------------------------------------------------
884 if l_deal_no is not null then
885 Open cur_reval_deal;
886 Fetch cur_reval_deal into l_batch;
887 Close cur_reval_deal;
888 if l_batch is NOT NULL THEN
889 Log_IG_Errors(p_ext_deal_no,p_deal_type,'DateA','XTR_IMP_DEAL_REVAL_EXIST',
890 to_char(p_transfer_date));
891 p_error := TRUE;
892 End If;
893
894 Open cur_accrl_deal;
895 Fetch cur_accrl_deal into l_batch;
896 Close cur_accrl_deal;
897 If l_batch is NOT NULL THEN
898 Log_IG_Errors(p_ext_deal_no,p_deal_type,'DateA','XTR_IMP_DEAL_ACCRL_EXIST',
899 to_char(p_transfer_date));
900 p_error := TRUE;
901 End If;
902
903 end if;
904
905 end; /* VALID_IG_ACCT */
906
907
908 -----------------------------------------------------------------------------------------------------
909 --* procedure to copy the values from interface record to Ig Record
910 procedure COPY_FROM_INTERFACE_TO_IG(ARec_Interface IN XTR_DEALS_INTERFACE%rowtype ) is
911 -----------------------------------------------------------------------------------------------------
912 begin
913
914 G_Ig_Main_Rec.ACCUM_INTEREST_BF := 0 ;
915 G_Ig_Main_Rec.ACCUM_INTEREST_BF_HCE := 0 ;
916 G_Ig_Main_Rec.BALANCE_BF := 0 ;
917 G_Ig_Main_Rec.BALANCE_BF_HCE := 0 ;
918 G_Ig_Main_Rec.BALANCE_OUT := 0 ;
919 G_Ig_Main_Rec.BALANCE_OUT_HCE := 0 ;
920 G_Ig_Main_Rec.COMMENTS := null;
921 G_Ig_Main_Rec.COMPANY_ACCOUNT_NO := null;
922 G_Ig_Main_Rec.COMPANY_CODE := null;
923 G_Ig_Main_Rec.CREATED_BY := null;
924 G_Ig_Main_Rec.CREATED_ON := null;
925 G_Ig_Main_Rec.CURRENCY := null;
926 G_Ig_Main_Rec.DEAL_NUMBER := 0 ;
927 G_Ig_Main_Rec.DEAL_TYPE := null;
928 G_Ig_Main_Rec.INTEREST := 0 ;
929 G_Ig_Main_Rec.INTEREST_HCE := 0 ;
930 G_Ig_Main_Rec.INTEREST_RATE := 0 ;
931 G_Ig_Main_Rec.INTEREST_SETTLED := 0 ;
932 G_Ig_Main_Rec.INTEREST_SETTLED_HCE := 0 ;
933 G_Ig_Main_Rec.LIMIT_CODE := null;
934 G_Ig_Main_Rec.LIMIT_CODE_INVEST := null;
935 G_Ig_Main_Rec.COMMENTS := null;
936 G_Ig_Main_Rec.NO_OF_DAYS := 0 ;
937 G_Ig_Main_Rec.PARTY_ACCOUNT_NO := null;
938 G_Ig_Main_Rec.PARTY_CODE := null;
939 G_Ig_Main_Rec.PORTFOLIO := null;
940 G_Ig_Main_Rec.PRINCIPAL_ACTION := null;
941 G_Ig_Main_Rec.PRINCIPAL_ADJUST := 0 ;
942 G_Ig_Main_Rec.PRINCIPAL_ADJUST_HCE := 0 ;
943 G_Ig_Main_Rec.PRODUCT_TYPE := null;
944 G_Ig_Main_Rec.PRICING_MODEL := null;
945 G_Ig_Main_Rec.SETTLE_DATE := null;
946 G_Ig_Main_Rec.TRANSACTION_NUMBER := 0 ;
947 G_Ig_Main_Rec.TRANSFER_DATE := null;
948 G_Ig_Main_Rec.UPDATED_BY := null;
949 G_Ig_Main_Rec.UPDATED_ON := null;
950 G_Ig_Main_Rec.ACCRUAL_INTEREST := 0 ;
951 G_Ig_Main_Rec.FIRST_BATCH_ID := null ;
952 G_Ig_Main_Rec.LAST_BATCH_ID := null ;
953 G_Ig_Main_Rec.ATTRIBUTE_CATEGORY := null;
957 G_Ig_Main_Rec.ATTRIBUTE4 := null;
954 G_Ig_Main_Rec.ATTRIBUTE1 := null;
955 G_Ig_Main_Rec.ATTRIBUTE2 := null;
956 G_Ig_Main_Rec.ATTRIBUTE3 := null;
958 G_Ig_Main_Rec.ATTRIBUTE5 := null;
959 G_Ig_Main_Rec.ATTRIBUTE6 := null;
960 G_Ig_Main_Rec.ATTRIBUTE7 := null;
961 G_Ig_Main_Rec.ATTRIBUTE8 := null;
962 G_Ig_Main_Rec.ATTRIBUTE9 := null;
963 G_Ig_Main_Rec.ATTRIBUTE10 := null;
964 G_Ig_Main_Rec.ATTRIBUTE11 := null;
965 G_Ig_Main_Rec.ATTRIBUTE12 := null;
966 G_Ig_Main_Rec.ATTRIBUTE13 := null;
967 G_Ig_Main_Rec.ATTRIBUTE14 := null;
968 G_Ig_Main_Rec.ATTRIBUTE15 := null;
969 G_Ig_Main_Rec.EXTERNAL_DEAL_ID := null;
970 G_Ig_Main_Rec.REQUEST_ID := 0 ;
971 G_Ig_Main_Rec.PROGRAM_APPLICATION_ID := 0 ;
972 G_Ig_Main_Rec.PROGRAM_ID := 0 ;
973 G_Ig_Main_Rec.PROGRAM_UPDATE_DATE := null;
974 --* Add for Interest Override project
975 G_Ig_Main_Rec.ROUNDING_TYPE := null;
976 G_Ig_Main_Rec.DAY_COUNT_TYPE := null;
977 G_Ig_Main_Rec.ORIGINAL_AMOUNT := 0;
978 G_Ig_Main_Rec.DEAL_LINKING_CODE := null; -- Bug 2994712
979 G_Ig_Main_Rec.DEALER_CODE := null; -- Bug 2684411
980 G_Ig_Main_Rec.EXTERNAL_SOURCE := null; -- Bug 3800146 -- *******************************************************
981
982 --* ==============================================================================================
983 --*
984 --* Column Mapping from Interface Table To the InterGroup Transfers Table
985 --*
986 --* Description Int Column IG Column
987 --* ------------------------------ -------------------------- -------------------
988 --* External ID EXTERNAL_DEAL_ID EXTERNAL_DEAL_ID
989 --* Deal Type DEAL_TYPE DEAL_TYPE
990 --* Transfer Date DATE_A TRANSFER_DATE
991 --* Company COMPANY_CODE COMPANY_CODE
992 --* Intercompany Party CPARTY_CODE PARTY_CODE
993 --* Currency CURRENCY_A CURRENCY
994 --* Company Account ACCOUNT_NO_A COMPANY_ACCOUNT_NO
995 --* Party Account ACCOUNT_NO_B PARTY_ACCOUNT_NO
996 --* Action ACTION_CODE PRINCIPAL_ACTION
997 --* Principal Adjust AMOUNT_A PRINCIPAL_ADJUST
998 --* Product Type PRODUCT_TYPE PRODUCT_TYPE
999 --* Pricing Model PRICING_MODEL PRICING_MODEL
1000 --* Portfolio PORTFOLIO_CODE PORTFOLIO
1001 --* Fund Limit LIMIT_CODE LIMIT_CODE
1002 --* Invest Limit LIMIT_CODE_B LIMIT_CODE_INVEST
1003 --* Comments COMMENTS COMMENTS
1004 --* Interest Rate RATE_A INTEREST_RATE
1005 --* Descriptive Flexfield Category ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
1006 --* Descriptive Flexfields ATTRIBUTE1 - ATTRIBUTE15 ATTRIBUTE1 - ATTRIBUTE15
1007 --* External Source EXTERNAL_SOURCE EXTERNAL_SOURCE -- 3800146 *****************************************************
1008 --*
1009 --* =============================================================================================
1010 --*
1011
1012 G_Ig_Main_Rec.external_deal_id := ARec_Interface.external_deal_id;
1013 G_Ig_Main_Rec.deal_type := 'IG';
1014 G_Ig_Main_Rec.transfer_date := ARec_Interface.date_a;
1015 G_Ig_Main_Rec.company_code := ARec_Interface.company_code;
1016 G_Ig_Main_Rec.party_code := ARec_Interface.cparty_code;
1017 G_Ig_Main_Rec.currency := ARec_Interface.currency_a;
1018 G_Ig_Main_Rec.company_account_no := ARec_Interface.account_no_a;
1019 G_Ig_Main_Rec.party_account_no := ARec_Interface.account_no_b;
1020 G_Ig_Main_Rec.principal_action := G_Ig_action;
1021 G_Ig_Main_Rec.principal_adjust := ARec_Interface.amount_a;
1022 G_Ig_Main_Rec.interest_rate := ARec_Interface.rate_a;
1023 G_Ig_Main_Rec.product_type := ARec_Interface.product_type;
1024 G_Ig_Main_Rec.pricing_model := ARec_Interface.pricing_model;
1025 G_Ig_Main_Rec.portfolio := ARec_Interface.portfolio_code;
1026 G_Ig_Main_Rec.limit_code := ARec_Interface.limit_code;
1027 G_Ig_Main_Rec.limit_code_invest := ARec_Interface.limit_code_b;
1028 G_Ig_Main_Rec.comments := ARec_Interface.comments;
1029 G_Ig_Main_Rec.attribute_category := ARec_Interface.attribute_category;
1030 G_Ig_Main_Rec.attribute1 := ARec_Interface.attribute1;
1031 G_Ig_Main_Rec.attribute2 := ARec_Interface.attribute2;
1032 G_Ig_Main_Rec.attribute3 := ARec_Interface.attribute3;
1033 G_Ig_Main_Rec.attribute4 := ARec_Interface.attribute4;
1034 G_Ig_Main_Rec.attribute5 := ARec_Interface.attribute5;
1035 G_Ig_Main_Rec.attribute6 := ARec_Interface.attribute6;
1036 G_Ig_Main_Rec.attribute7 := ARec_Interface.attribute7;
1037 G_Ig_Main_Rec.attribute8 := ARec_Interface.attribute8;
1038 G_Ig_Main_Rec.attribute9 := ARec_Interface.attribute9;
1039 G_Ig_Main_Rec.attribute10 := ARec_Interface.attribute10;
1040 G_Ig_Main_Rec.attribute11 := ARec_Interface.attribute11;
1041 G_Ig_Main_Rec.attribute12 := ARec_Interface.attribute12;
1042 G_Ig_Main_Rec.attribute13 := ARec_Interface.attribute13;
1043 G_Ig_Main_Rec.attribute14 := ARec_Interface.attribute14;
1044 G_Ig_Main_Rec.attribute15 := ARec_Interface.attribute15;
1045 --* Add for Interest Override
1046 G_Ig_Main_Rec.Rounding_Type := ARec_Interface.Rounding_Type;
1047 G_Ig_Main_Rec.Day_Count_Type := ARec_Interface.Day_Count_Type;
1048 G_Ig_Main_Rec.Original_Amount := ARec_Interface.Original_Amount;
1049 G_Ig_Main_Rec.Deal_Linking_Code := ARec_Interface.Deal_Linking_Code; -- Bug 2994712
1053 --###########################################################################################################################
1050 G_Ig_Main_Rec.Dealer_Code := ARec_Interface.Dealer_Code; -- Bug 2684411
1051 G_Ig_Main_Rec.External_Source := ARec_Interface.External_Source; -- Bug 3800146 ************************************************
1052
1054 -- 3800146 Do not default for ZBA/CL. Mandatory check already done for ZBA/CL
1055 --###########################################################################################################################
1056 if G_Ig_Main_Rec.pricing_model is NULL and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA, C_CL) then
1057 DEFAULT_PRICING_MODEL(G_Ig_Main_Rec.company_code,
1058 G_Ig_Main_Rec.party_code,
1059 G_Ig_Main_Rec.currency,
1060 G_Ig_Main_Rec.product_type,
1061 G_Ig_Main_Rec.pricing_model);
1062 end if;
1063 --###########################################################################################################################
1064
1065 end; /* COPY_FROM_INTERFACE_TO_IG */
1066
1067
1068 -----------------------------------------------------------------------------------------------------
1069 -- Local procedure to calculate balance out, interest amount, num of days for each transfer.
1070 -- Also determine that this is the latest date for a transfer to or from this account.
1071 --
1072 procedure CALC_DETAILS is
1073 -----------------------------------------------------------------------------------------------------
1074 --
1075 l_trans_no number;
1076 prv_date Date;
1077 prv_int_rate number;
1078 roundfac number;
1079 yr_basis number;
1080
1081 prv_accrual_int NUMBER;
1082 --* Add for Interest Project
1083 l_day_count_type VARCHAR2(1);
1084 l_rounding_type VARCHAR2(1);
1085 l_first_trans_flag VARCHAR2(1);
1086 l_oldest_date DATE;
1087 l_prv_day_count_type VARCHAR2(1);
1088 --* Add End
1089
1090 --
1091 cursor RND_YR is
1092 select ROUNDING_FACTOR
1093 from xtr_MASTER_CURRENCIES_v
1094 where CURRENCY = G_Ig_Main_Rec.CURRENCY;
1095 --
1096 cursor LATEST_DATE is
1097 select a.TRANSFER_DATE,
1098 a.BALANCE_OUT ,
1099 a.BALANCE_OUT_HCE,
1100 a.INTEREST_RATE,
1101 (nvl(a.ACCUM_INTEREST_BF,0) + nvl(a.INTEREST,0) - nvl(a.INTEREST_SETTLED,0)),
1102 (nvl(a.ACCUM_INTEREST_BF_HCE,0) + nvl(a.INTEREST_HCE,0) - nvl(a.INTEREST_SETTLED_HCE,0)),
1103 a.TRANSACTION_NUMBER,
1104 a.ACCRUAL_INTEREST,
1105 a.ROUNDING_TYPE, --* Add for Interest Project
1106 a.DAY_COUNT_TYPE --* Add for Interest Project
1107 from xtr_INTERGROUP_TRANSFERS_v a
1108 where a.PARTY_CODE = G_Ig_Main_Rec.PARTY_CODE
1109 and a.COMPANY_CODE = G_Ig_Main_Rec.COMPANY_CODE
1110 and a.CURRENCY = G_Ig_Main_Rec.CURRENCY
1111 and a.TRANSFER_DATE <= G_Ig_Main_Rec.TRANSFER_DATE
1112 order by a.TRANSFER_DATE desc, a.TRANSACTION_NUMBER desc;
1113 --
1114 --* Add for Interest Project
1115 cursor OLDEST_DATE is
1116 select min(TRANSFER_DATE)
1117 from xtr_intergroup_transfers
1118 where party_code = G_Ig_Main_Rec.PARTY_CODE
1119 and company_code = G_Ig_Main_Rec.COMPANY_CODE
1120 and currency = G_Ig_Main_Rec.CURRENCY;
1121 --
1122 cursor PRV_DAY_COUNT_TYPE is
1123 SELECT DAY_COUNT_TYPE
1124 from XTR_INTERGROUP_TRANSFERS
1125 where company_code = G_Ig_Main_Rec.COMPANY_CODE
1126 and party_code = G_Ig_Main_Rec.PARTY_CODE
1127 and currency = G_Ig_Main_Rec.CURRENCY
1128 and transfer_date = (select max(transfer_date)
1129 from xtr_intergroup_transfers
1130 where company_code = G_Ig_Main_Rec.COMPANY_CODE
1131 and party_code = G_Ig_Main_Rec.PARTY_CODE
1132 and currency = G_Ig_Main_Rec.CURRENCY
1133 and transfer_date < prv_date)
1134 order by transaction_number desc;
1135
1136
1137 --* Add End
1138
1139 begin
1140 -- OLD in XTRINING.fmb ??? if Latest_Date%ISOPEN then
1141 -- OLD in XTRINING.fmb ??? close Latest_Date;
1142 -- OLD in XTRINING.fmb ??? end if;
1143
1144 if G_Ig_Main_Rec.CURRENCY is NOT NULL then
1145 open LATEST_DATE;
1146 fetch LATEST_DATE into prv_date,G_Ig_Main_Rec.BALANCE_BF,G_Ig_Main_Rec.BALANCE_BF_HCE,
1147 prv_int_rate,G_Ig_Main_Rec.ACCUM_INTEREST_BF,
1148 G_Ig_Main_Rec.ACCUM_INTEREST_BF_HCE,l_trans_no,prv_accrual_int,
1149 l_rounding_type,l_day_count_type; --* Add day count type for Interest Project
1150 if LATEST_DATE%NOTFOUND then
1151 prv_date := G_Ig_Main_Rec.TRANSFER_DATE;
1152 prv_int_rate := 0;
1153 G_Ig_Main_Rec.BALANCE_BF := 0;
1154 G_Ig_Main_Rec.BALANCE_BF_HCE := 0;
1155 G_Ig_Main_Rec.ACCUM_INTEREST_BF := 0;
1156 G_Ig_Main_Rec.ACCUM_INTEREST_BF_HCE := 0;
1157 prv_accrual_int :=0;
1158 --* Add for Interest Project
1159 G_Ig_Main_Rec.ORIGINAL_AMOUNT := 0;
1160 --* Add End
1161
1162 else
1163 if G_Ig_Main_Rec.TRANSACTION_NUMBER is NOT NULL then
1164 if G_Ig_Main_Rec.TRANSFER_DATE = prv_date then
1165 loop
1166 exit when ( prv_date < G_Ig_Main_Rec.TRANSFER_DATE or
1167 l_trans_no < G_Ig_Main_Rec.TRANSACTION_NUMBER);
1171 G_Ig_Main_Rec.ACCUM_INTEREST_BF_HCE,
1168 fetch LATEST_DATE into prv_date, G_Ig_Main_Rec.BALANCE_BF,
1169 G_Ig_Main_Rec.BALANCE_BF_HCE, prv_int_rate,
1170 G_Ig_Main_Rec.ACCUM_INTEREST_BF,
1172 l_trans_no, prv_accrual_int,
1173 l_rounding_type,l_day_count_type;
1174 if LATEST_DATE%NOTFOUND then
1175 prv_date := G_Ig_Main_Rec.TRANSFER_DATE;
1176 prv_int_rate := 0;
1177 G_Ig_Main_Rec.BALANCE_BF := 0;
1178 G_Ig_Main_Rec.BALANCE_BF_HCE := 0;
1179 G_Ig_Main_Rec.ACCUM_INTEREST_BF := 0;
1180 G_Ig_Main_Rec.ACCUM_INTEREST_BF_HCE := 0;
1181 prv_accrual_int :=0;
1182 --* Add for Interest Project
1183 G_Ig_Main_Rec.ORIGINAL_AMOUNT := 0;
1184 --* Add End
1185
1186 Exit;
1187 end if;
1188 end loop;
1189 end if;
1190 end if;
1191 end if;
1192
1193 open RND_YR;
1194 fetch RND_YR into roundfac;
1195 close RND_YR;
1196
1197 if G_Ig_Main_Rec.TRANSFER_DATE > prv_date then
1198
1199 --* Add for Interest Project
1200 /* Decide First Transaction Flag */
1201 l_first_trans_flag := 'N';
1202 OPEN OLDEST_DATE;
1203 FETCH OLDEST_DATE into l_oldest_date;
1204 CLOSE OLDEST_DATE;
1205
1206 if l_day_count_type = 'B' and l_oldest_date = prv_date then
1207 l_first_trans_flag := 'Y';
1208 elsif (l_day_count_type = 'B' or l_day_count_type ='F')
1209 and l_oldest_date <> prv_date then
1210
1211 -- Select Day Count type of previous transaction
1212 OPEN PRV_DAY_COUNT_TYPE;
1213 FETCH PRV_DAY_COUNT_TYPE into l_prv_day_count_type;
1214 CLOSE PRV_DAY_COUNT_TYPE;
1215
1216 if l_day_count_type = 'F' and (l_prv_day_count_type = 'B' or l_prv_day_count_type = 'L') then
1217 prv_date := prv_date + 1;
1218 elsif l_day_count_type ='B' and l_prv_day_count_type = 'L' then
1219 l_first_trans_flag := 'Y';
1220 end if;
1221 end if;
1222 --* Add End
1223
1224 XTR_CALC_P.CALC_DAYS_RUN(prv_date,
1225 G_Ig_Main_Rec.TRANSFER_DATE,
1226 G_Ig_year_calc_type,
1227 G_Ig_Main_Rec.NO_OF_DAYS,
1228 yr_basis,
1229 null,
1230 l_day_count_type, -- Add for Interest Project
1231 l_first_trans_flag); -- Add for Interest Project
1232 else
1233 G_Ig_Main_Rec.NO_OF_DAYS :=0;
1234 yr_basis :=365;
1235 end if;
1236 -- Original Code
1237 -- G_Ig_Main_Rec.INTEREST := round(G_Ig_Main_Rec.BALANCE_BF * prv_int_rate / 100 *
1238 -- G_Ig_Main_Rec.NO_OF_DAYS / yr_basis,roundfac);
1239
1240 G_Ig_Main_Rec.INTEREST := xtr_fps2_p.interest_round(G_Ig_Main_Rec.BALANCE_BF * prv_int_rate / 100 *
1241 G_Ig_Main_Rec.NO_OF_DAYS / yr_basis,roundfac,l_rounding_type);
1242 end if;
1243
1244 if G_Ig_Main_Rec.PRINCIPAL_ADJUST is not null then
1245 if G_Ig_Main_Rec.PRINCIPAL_ACTION = 'PAY' then
1246 G_Ig_Main_Rec.BALANCE_OUT := nvl(G_Ig_Main_Rec.BALANCE_BF,0)+nvl(G_Ig_Main_Rec.PRINCIPAL_ADJUST,0);
1247 elsif G_Ig_Main_Rec.PRINCIPAL_ACTION = 'REC' then
1248 G_Ig_Main_Rec.BALANCE_OUT := nvl(G_Ig_Main_Rec.BALANCE_BF,0)-nvl(G_Ig_Main_Rec.PRINCIPAL_ADJUST,0);
1249 end if;
1250
1251 -- OLD in XTRINING.fmb ??? close LATEST_DATE;
1252
1253 G_Ig_Main_Rec.ACCRUAL_INTEREST := nvl(prv_accrual_int,0) + nvl(G_Ig_Main_Rec.INTEREST,0);
1254
1255 --* Add for Interest Project
1256 G_Ig_Main_Rec.ORIGINAL_AMOUNT := nvl(G_Ig_Main_Rec.ACCUM_INTEREST_BF,0) + nvl(G_Ig_Main_Rec.INTEREST,0);
1257 --* Add End
1258
1259 end if;
1260
1261 close LATEST_DATE; -- NEW
1262
1263 end; /* CALC_DETAILS */
1264
1265
1266
1267 -----------------------------------------------------------------------------------------------------
1268 procedure CALC_HCE_AMTS is
1269 -----------------------------------------------------------------------------------------------------
1270 roundfac NUMBER(3,2);
1271
1272 cursor RND_FAC is
1273 select m.ROUNDING_FACTOR
1274 from xtr_PARTIES_v p,
1275 xtr_MASTER_CURRENCIES_v m
1276 where p.PARTY_CODE = G_Ig_Main_Rec.COMPANY_CODE
1277 and m.CURRENCY = p.HOME_CURRENCY;
1278
1279 cursor HCE_AMT is
1280 select nvl(round((nvl(G_Ig_Main_Rec.INTEREST,0) / s.hce_rate),roundfac),0),
1281 nvl(round((G_Ig_Main_Rec.PRINCIPAL_ADJUST / s.hce_rate),roundfac),0),
1282 nvl(round((G_Ig_Main_Rec.BALANCE_OUT / s.hce_rate),roundfac),0),
1283 nvl(round((nvl(G_Ig_Main_Rec.INTEREST_SETTLED,0) / s.hce_rate),roundfac),0)
1284 from xtr_MASTER_CURRENCIES_v s
1285 where s.CURRENCY = G_Ig_Main_Rec.CURRENCY;
1286
1287 begin
1288
1289 if G_Ig_Main_Rec.CURRENCY is NOT NULL and G_Ig_Main_Rec.COMPANY_CODE is NOT NULL then
1290 open RND_FAC;
1291 fetch RND_FAC into roundfac;
1292 close RND_FAC;
1293
1294 open HCE_AMT;
1295 fetch HCE_AMT into G_Ig_Main_Rec.INTEREST_HCE,G_Ig_Main_Rec.PRINCIPAL_ADJUST_HCE,
1299
1296 G_Ig_Main_Rec.BALANCE_OUT_HCE,G_Ig_Main_Rec.INTEREST_SETTLED_HCE;
1297 close HCE_AMT;
1298 end if;
1300 end; /* CALC_HCE_AMTS */
1301
1302
1303 -----------------------------------------------------------------------------------------------------
1304 -- Local procedure to calculate interest amount, num of days for each
1305 -- transfer. Also determine that this is the latest date for a transfer
1306 -- to or from this account.
1307 procedure CALCULATE_VALUES (ARec_Interface IN XTR_DEALS_INTERFACE%rowtype,
1308 p_err_limit OUT NOCOPY VARCHAR2) is
1309 -----------------------------------------------------------------------------------------------------
1310 begin
1311
1312 COPY_FROM_INTERFACE_TO_IG(ARec_Interface);
1313
1314 CALC_DETAILS; -- Need to calculate current transaction's BALANCE_OUT before doing CASCADE_RECALC.
1315
1316 CALC_HCE_AMTS;
1317
1318 -- either
1319
1320 /* RV 2229236: Using function Get_Limit_Amount to get the total balance for limit check.
1321 CASCADE_RECALC(ARec_Interface.company_code,
1322 ARec_Interface.cparty_code,
1323 ARec_Interface.currency_a,
1324 ARec_Interface.date_a,
1325 ARec_Interface.limit_code,
1326 ARec_Interface.limit_code_b,
1327 'N');
1328 -- p_err_limit);
1329 */
1330 /* or
1331 CASCADE_RECALC(G_Ig_Main_Rec.company_code,
1332 G_Ig_Main_Rec.party_code,
1333 G_Ig_Main_Rec.currency,
1334 G_Ig_Main_Rec.transfer_date,
1335 G_Ig_Main_Rec.limit_code,
1336 G_Ig_Main_Rec.limit_code_invest,
1337 'N');
1338 -- p_err_limit);
1339 */
1340
1341 end;
1342
1343
1344 -----------------------------------------------------------------------------------------------------
1345 procedure CHECK_MANDATORY_FIELDS(ARec_Interface IN XTR_DEALS_INTERFACE%rowtype, p_error OUT NOCOPY BOOLEAN) is
1346 -----------------------------------------------------------------------------------------------------
1347 begin
1348
1349 p_error := FALSE;
1350
1351 if ARec_Interface.company_code is null then
1352 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1353 'CompanyCode','XTR_MANDATORY','IG.COMPANY_CODE');
1354 p_error := TRUE;
1355 end if;
1356
1357 if ARec_Interface.date_a is null then
1358 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1359 'DateA','XTR_MANDATORY','IG.TRANSFER_DATE');
1360 p_error := TRUE;
1361 end if;
1362
1363
1364 if ARec_Interface.cparty_code is null then
1365 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1366 'CpartyCode','XTR_MANDATORY','IG.PARTY_CODE');
1367 p_error := TRUE;
1368 end if;
1369
1370
1371 if ARec_Interface.currency_a is null then
1372 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1373 'CurrencyA','XTR_MANDATORY','IG.CURRENCY');
1374 p_error := TRUE;
1375 end if;
1376
1377
1378 if ARec_Interface.account_no_a is null then
1379 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1380 'AccountNoA','XTR_MANDATORY','IG.COMPANY_ACCOUNT_NO');
1381 p_error := TRUE;
1382 end if;
1383
1384
1385 if ARec_Interface.account_no_b is null then
1386 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1387 'AccountNoB','XTR_MANDATORY','IG_PARTY_ACCOUNT_NO');
1388 p_error := TRUE;
1389 end if;
1390
1391
1392 if ARec_Interface.action_code is null then
1393 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1394 'ActionCode','XTR_MANDATORY','IG.PRINCIPAL_ACTION_DSP');
1395 p_error := TRUE;
1396 end if;
1397
1398
1399 if ARec_Interface.amount_a is null then
1400 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1401 'AmountA','XTR_MANDATORY','IG.PRINCIPAL_ADJUST');
1402 p_error := TRUE;
1403 end if;
1404
1405
1406 if ARec_Interface.rate_a is null then
1407 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1408 'RateA','XTR_MANDATORY','IG.INTEREST_RATE');
1409 p_error := TRUE;
1410 end if;
1411
1412
1413 if ARec_Interface.product_type is null then
1414 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1415 'ProductType','XTR_MANDATORY','IG.PRODUCT_TYPE');
1416 p_error := TRUE;
1417 end if;
1418
1419
1420 if ARec_Interface.portfolio_code is null then
1421 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1425
1422 'PortfolioCode','XTR_MANDATORY','IG.PORTFOLIO');
1423 p_error := TRUE;
1424 end if;
1426 -- Bug 2684411
1427 if ARec_Interface.dealer_code is null then
1428 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1429 'DealerCode','XTR_MANDATORY','IG.DEALER_CODE');
1430 p_error := TRUE;
1431 end if;
1432 -- Bug 2684411
1433
1434
1435 /*------------------------------------------------------ 2549633-------------------------------------------------------- */
1436
1437 if ARec_Interface.rounding_type is null then
1438 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1439 'RoundingType','XTR_MANDATORY','IG.ROUNDING_TYPE');
1440 p_error := TRUE;
1441 end if;
1442
1443 if ARec_Interface.day_count_type is null then
1444 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1445 'DayCountType','XTR_MANDATORY','IG.DAY_COUNT_TYPE');
1446 p_error := TRUE;
1447 end if;
1448
1449 /*------------------------------------------------------ 2549633-------------------------------------------------------- */
1450
1451 /*------------------------------------------------------ 2229236-------------------------------------------------------- */
1452
1453 if G_Ig_Source is null AND is_company(ARec_Interface.cparty_code)
1454 AND ARec_Interface.mirror_portfolio_code is null then
1455 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1456 'MirrorPortfolioCode','XTR_MANDATORY','IG.MIRROR_PORTFOLIO_CODE');
1457 p_error := TRUE;
1458 end if;
1459
1460 if G_Ig_Source is null AND is_company(ARec_Interface.cparty_code)
1461 AND ARec_Interface.mirror_product_type is null then
1462 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1463 'MirrorProductType','XTR_MANDATORY','IG.MIRROR_PRODUCT_TYPE');
1464 p_error := TRUE;
1465 end if;
1466
1467 -- Bug 2684411
1468 if G_Ig_Source is null AND is_company(ARec_Interface.cparty_code)
1469 AND ARec_Interface.mirror_dealer_code is null then
1470 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1471 'MirrorDealerCode','XTR_MANDATORY','IG.MIRROR_DEALER_CODE');
1472 p_error := TRUE;
1473 end if;
1474 -- Bug 2684411
1475
1476
1477 /*------------------------------------------------------ 2229236-------------------------------------------------------- */
1478
1479 --************************************************************************************************
1480 -- 3800146 Mandatory Check Pricing Model for ZBA/CL
1481 --************************************************************************************************
1482
1483 if nvl(G_Ig_External_Source,'@@@') in (C_ZBA,C_CL) and ARec_Interface.pricing_model is null then
1484 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1485 null,'XTR_MANDATORY','IG.PRICING_MODEL');
1486 p_error := TRUE;
1487 end if;
1488
1489 if nvl(G_Ig_External_Source,'@@@') in (C_ZBA,C_CL) and IS_COMPANY(ARec_Interface.cparty_code) and
1490 ARec_Interface.mirror_pricing_model is null then
1491 log_IG_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1492 null,'XTR_MANDATORY','IG.PRICING_MODEL'); -- same prompt as main deal
1493 p_error := TRUE;
1494 end if;
1495 --************************************************************************************************
1496
1497
1498 end; /* CHECK_MANDATORY_FIELDS */
1499
1500
1501 -----------------------------------------------------------------------------------------------------
1502 procedure VALIDATE_DEALS(ARec_Interface IN XTR_DEALS_INTERFACE%rowtype, p_error OUT NOCOPY BOOLEAN) is
1503 -----------------------------------------------------------------------------------------------------
1504 l_err_segment VARCHAR2(30);
1505 l_err_cparty BOOLEAN := FALSE;
1506 l_err_currency BOOLEAN := FALSE;
1507 l_err_accounting BOOLEAN := FALSE;
1508 l_mirror_err_accounting BOOLEAN := FALSE;
1509 l_err_limit VARCHAR2(10) := NULL;
1510 l_zba_duplicate BOOLEAN := FALSE; -- 3800146
1511
1512 begin
1513
1514 p_error := FALSE;
1515
1516 if not valid_cparty_code(ARec_Interface.Company_Code,ARec_Interface.CParty_Code) then
1517 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1518 'CpartyCode','XTR_INV_INTERCOMPANY');
1519 p_error := TRUE;
1520 l_err_cparty := TRUE;
1521 end if;
1522
1523
1524 if not valid_transfer_date(ARec_Interface.Date_A) then
1525 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1526 'DateA','XTR_104');
1527 p_error := TRUE;
1528 end if;
1529
1530
1531 if not valid_currency(ARec_Interface.Currency_A) then
1535 l_err_currency := TRUE;
1532 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1533 'CurrencyA','XTR_INV_CURR');
1534 p_error := TRUE;
1536 end if;
1537
1538
1539 -----------------------------------------------------------------------------------------
1540 --* The following code performs the accounting logic validation specific to the IG deals
1541 -----------------------------------------------------------------------------------------
1542 VALID_IG_ACCT(ARec_Interface.company_code,
1543 ARec_Interface.cparty_code,
1544 ARec_Interface.currency_a,
1545 ARec_Interface.date_a,
1546 ARec_Interface.external_deal_id,
1547 ARec_Interface.deal_type,
1548 l_err_accounting);
1549
1550
1551 IF l_err_accounting = TRUE THEN
1552
1553 p_error := TRUE;
1554
1555 else
1556
1557 if not valid_comp_reporting_ccy(ARec_Interface.company_code) then
1558 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1559 'CompanyCode','XTR_880');
1560 p_error := TRUE;
1561 end if;
1562
1563
1564 if l_err_currency <> TRUE then
1565 if not valid_comp_acct(ARec_Interface.Company_Code, ARec_Interface.Account_No_A,
1566 ARec_Interface.Currency_A) then
1567 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1568 'AccountNoA','XTR_INV_COMP_ACCT_NO');
1569 p_error := TRUE;
1570 end if;
1571 end if;
1572
1573
1574 if l_err_cparty <> TRUE and l_err_currency <> TRUE then
1575 if not valid_party_acct(ARec_Interface.CParty_Code, ARec_Interface.Account_No_B,
1576 ARec_Interface.Currency_A) then
1577 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1578 'AccountNoB','XTR_INV_PARTY_ACCT_NO');
1579 p_error := TRUE;
1580 end if;
1581 end if;
1582
1583
1584 if not valid_action(ARec_Interface.Action_Code) then
1585 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1586 'ActionCode','XTR_INV_ACTION');
1587 p_error := TRUE;
1588 end if;
1589
1590
1591 if not valid_product(ARec_Interface.Product_Type) then
1592 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1593 'ProductType','XTR_INV_PRODUCT_TYPE');
1594 p_error := TRUE;
1595 end if;
1596
1597 --Bug 2994712
1598 if not valid_deal_linking_code(ARec_Interface.Deal_Linking_Code) then
1599 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1600 'DealLinkingCode','XTR_INV_LINKING_CODE');
1601 p_error := TRUE;
1602 end if;
1603 --Bug 2994712
1604
1605 --Bug 2684411
1606 if not valid_dealer_code(ARec_Interface.Dealer_Code) then
1607 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1608 'DealerCode','XTR_INV_DEALER_CODE');
1609 p_error := TRUE;
1610 end if;
1611 --Bug 2684411
1612
1613 if not valid_pricing_model(ARec_Interface.Pricing_Model) then
1614 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1615 'PricingModel','XTR_INV_PRICING_MODEL');
1616 /* Bug 4319476 */
1617 p_error := TRUE;
1618 end if;
1619
1620 if l_err_cparty <> TRUE then
1621 if not valid_portfolio(ARec_Interface.Company_Code,ARec_Interface.CParty_Code,
1622 ARec_Interface.Portfolio_Code) then
1623 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1624 'PortfolioCode','XTR_INV_PORT_CODE');
1625 p_error := TRUE;
1626 end if;
1627 end if;
1628
1629
1630 if not valid_principal_adjust(ARec_Interface.Amount_A) then
1631 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1632 'AmountA','XTR_56');
1633 p_error := TRUE;
1634 end if;
1635
1636 --------------------------------------------------------------------------
1637 -- 3800146 Settlement Flag
1638 --------------------------------------------------------------------------
1639 if not valid_settlement_flag(ARec_Interface.settlement_flag) then
1640 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1641 'SettlementFlag','FND_CP_ITEM_YES_NO');
1642 p_error := TRUE;
1643 end if;
1644
1645
1646 /*------------------------------------------------------ 2549633-------------------------------------------------------- */
1647
1648 if not valid_rounding_type(ARec_Interface.Rounding_Type) then
1652 end if;
1649 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1650 'RoundingType','XTR_INV_ROUNDING_TYPE');
1651 p_error := TRUE;
1653
1654 if not valid_day_count_type(ARec_Interface.Day_Count_Type) then
1655 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1656 'DayCountType','XTR_INV_DAY_COUNT_TYPE');
1657 p_error := TRUE;
1658 end if;
1659
1660 /*------------------------------------------------------ 2549633-------------------------------------------------------- */
1661
1662
1663 /*---------------------------------------------------------------------------------------*/
1664 if G_Ig_Source is null AND is_company(ARec_Interface.cparty_code) then
1665 VALID_IG_ACCT(ARec_Interface.cparty_code,
1666 ARec_Interface.company_code,
1667 ARec_Interface.currency_a,
1668 ARec_Interface.date_a,
1669 ARec_Interface.external_deal_id,
1670 ARec_Interface.deal_type,
1671 l_mirror_err_accounting);
1672 if l_mirror_err_accounting = TRUE then
1673 p_error := TRUE;
1674 else
1675
1676 if not valid_product(ARec_Interface.Mirror_Product_Type) then
1677 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1678 'MirrorProductType','XTR_INV_PRODUCT_TYPE');
1679 p_error := TRUE;
1680 end if;
1681
1682 if not valid_portfolio(ARec_Interface.CParty_Code,ARec_Interface.Company_Code,
1683 ARec_Interface.Mirror_Portfolio_Code) then
1684 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1685 'MirrorPortfolioCode','XTR_INV_PORT_CODE');
1686 p_error := TRUE;
1687 end if;
1688
1689 if not valid_pricing_model(ARec_Interface.Mirror_Pricing_Model) then
1690 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1691 'MirrorPricingModel','XTR_INV_PRICING_MODEL');
1692 p_error := TRUE;
1693 end if;
1694
1695 -- Bug 2994712
1696 if not valid_deal_linking_code(ARec_Interface.Mirror_Deal_Linking_Code) then
1697 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1698 'MirrorDealLinkingCode','XTR_INV_LINKING_CODE');
1699 p_error := TRUE;
1700 end if;
1701 -- Bug 2994712
1702
1703 -- Bug 2684411
1704 if not valid_dealer_code(ARec_Interface.Mirror_Dealer_Code) then
1705 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1706 'MirrorDealerCode','XTR_INV_DEALER_CODE');
1707 p_error := TRUE;
1708 end if;
1709 -- Bug 2684411
1710
1711
1712 if ARec_Interface.mirror_limit_code_fund is not null or
1713 ARec_Interface.mirror_limit_code_invest is not null then
1714
1715 --------------------------------
1716 -- Check individual limit code
1717 --------------------------------
1718 if ARec_Interface.mirror_limit_code_fund is not null and
1719 not valid_limit_code(ARec_Interface.cparty_code,ARec_Interface.company_code,
1720 ARec_Interface.mirror_limit_code_fund,'FUND') then
1721 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1722 'MirrorLimitCodeFund','XTR_INV_LIMIT_CODE','IG.LIMIT_CODE');
1723 l_err_limit := 'FUND';
1724 p_error := TRUE;
1725 end if;
1726
1727 if ARec_Interface.mirror_limit_code_invest is not null and
1728 not valid_limit_code(ARec_Interface.cparty_code,ARec_Interface.company_code,
1729 ARec_Interface.mirror_limit_code_invest,'INVEST') then
1730 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1731 'MirrorLimitCodeInvest','XTR_INV_LIMIT_CODE','IG.LIMIT_CODE_INVEST');
1732 l_err_limit := 'INVEST';
1733 p_error := TRUE;
1734 end if;
1735 end if;
1736 end if; /* l_mirror_err_accounting = TRUE */
1737 end if;
1738
1739 /*---------------------------------------------------------------------------------------*/
1740
1741
1742 --*****************************************************************************************************************
1743 -- 3800146 Skip DFF validation for ZBA/Cash Leveling ************************************************************
1744 --*****************************************************************************************************************
1745 if nvl(G_Ig_External_Source,'@@@') not in (C_ZBA,C_CL) then
1746
1747 if not ( xtr_import_deal_data.val_desc_flex(ARec_Interface,'XTR_IG_DESC_FLEX',l_err_segment)) then
1751 ARec_Interface.deal_type,
1748 p_error := TRUE;
1749 if l_err_segment is not null and l_err_segment = 'Attribute16' then
1750 Log_IG_Errors( ARec_Interface.external_deal_id,
1752 l_err_segment,
1753 'XTR_INV_DESC_FLEX_API');
1754 elsif l_err_segment is not null and l_err_segment = 'AttributeCategory' then
1755 Log_IG_Errors( ARec_Interface.external_deal_id,
1756 ARec_Interface.deal_type,
1757 l_err_segment,
1758 'XTR_INV_DESC_FLEX_CONTEXT');
1759 else
1760 Log_IG_Errors( ARec_Interface.external_deal_id,
1761 ARec_Interface.deal_type,
1762 l_err_segment,
1763 'XTR_INV_DESC_FLEX');
1764 end if;
1765 end if;
1766
1767 end if;
1768 ------------------------------------------------------------------------------------------------------
1769
1770
1771 IF l_err_cparty <> TRUE THEN
1772
1773 --#########################################################################################################################
1774 -- 3800146 ZBA Duplicate check based on FINAL actual values (after Derivation but before ANY calculation)
1775 --#########################################################################################################################
1776 l_zba_duplicate := FALSE;
1777 if nvl(G_Ig_External_Source,'@@@') = C_ZBA then
1778 XTR_WRAPPER_API_P.CHK_ZBA_IG_DUPLICATE (ARec_Interface.company_code, -- p_company_code,
1779 ARec_Interface.cparty_code, -- p_intercompany_code,
1780 ARec_Interface.currency_a, -- p_currency,
1781 ARec_Interface.amount_a, -- p_transfer_amount,
1782 ARec_Interface.date_a, -- p_transfer_date,
1783 ARec_Interface.action_code, -- p_action_code,
1784 ARec_Interface.portfolio_code, -- p_company_portfolio,
1785 ARec_Interface.product_type, -- p_company_product_type,
1786 ARec_Interface.mirror_portfolio_code, -- p_intercompany_portfolio,
1787 ARec_Interface.mirror_product_type, -- p_intercompany_product_type,
1788 ARec_Interface.account_no_a, -- p_company_account_no,
1789 ARec_Interface.account_no_b, -- p_party_account_no,
1790 l_zba_duplicate);
1791 end if;
1792 --########################################################################################################################
1793
1794 IF l_zba_duplicate THEN
1795 -- ################# Duplicate Error ########################
1796 p_error := TRUE; -- ################# Duplicate Error ########################
1797 -- ################# Duplicate Error ########################
1798 else
1799 IF ARec_Interface.limit_code is not null OR
1800 ARec_Interface.limit_code_b is not null THEN
1801
1802 --------------------------------
1803 -- Check individual limit code
1804 --------------------------------
1805 if ARec_Interface.limit_code is not null and
1806 not valid_limit_code(ARec_Interface.company_code, ARec_Interface.cparty_code,
1807 ARec_Interface.limit_code,'FUND') then
1808 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1809 'LimitCode','XTR_INV_LIMIT_CODE','IG.LIMIT_CODE');
1810 l_err_limit := 'FUND';
1811 p_error := TRUE;
1812 end if;
1813
1814 if ARec_Interface.limit_code_b is not null and
1815 not valid_limit_code(ARec_Interface.company_code, ARec_Interface.cparty_code,
1816 ARec_Interface.limit_code_b,'INVEST') then
1817 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
1818 'LimitCodeB','XTR_INV_LIMIT_CODE','IG.LIMIT_CODE_INVEST');
1819 l_err_limit := 'INVEST';
1820 p_error := TRUE;
1821 end if;
1822
1823 IF l_err_limit is null THEN -- *********** No limit Error ****************
1824
1828 CALCULATE_VALUES(ARec_Interface, l_err_limit); -- ################# I M P O R T A N T !!! #################
1825 ----------------------------
1826 -- Calculate Balance
1827 ---------------------------- -- ################# I M P O R T A N T !!! #################
1829 -- ################# I M P O R T A N T !!! #################
1830 END IF; /* l_err_limit is null */
1831
1832 END IF; /* ARec_Interface.limit_code or ARec_Interface.limit_code_b is not null */
1833
1834 END IF; /* duplicate check for ZBA only */
1835
1836 END IF; /* l_err_cparty <> TRUE */
1837
1838 END IF; /* l_err_accounting = TRUE */
1839
1840 end; /* VALIDATE_DEALS */
1841
1842
1843
1844 -----------------------------------------------------------------------------------------------------
1845 -- Generate a new deal number if p_new_deal = 'Y', otherwise find out if this is existing IG deal.
1846 -----------------------------------------------------------------------------------------------------
1847 procedure GET_DEAL_TRAN_NUMBERS(p_comp IN VARCHAR2,
1848 p_cparty IN VARCHAR2,
1849 p_curr IN VARCHAR2,
1850 p_deal_no IN OUT NOCOPY NUMBER,
1851 p_tran_no IN OUT NOCOPY NUMBER,
1852 p_new_deal IN VARCHAR2) is
1853 -----------------------------------------------------------------------------------------------------
1854 cursor deal_number_cursor(c_comp IN Xtr_Intergroup_Transfers.Company_Code%type,
1855 c_cparty IN Xtr_Intergroup_Transfers.Party_Code%type,
1856 c_curr IN Xtr_Intergroup_Transfers.Currency%type) is
1857 select deal_number,
1858 transaction_number
1859 from xtr_intergroup_transfers
1860 where company_Code = c_comp
1861 and party_Code = c_cparty
1862 and currency = c_curr
1863 order by transaction_number desc;
1864
1865 cursor new_deal_num is
1866 select XTR_DEALS_S.nextval
1867 from dual;
1868
1869 begin
1870
1871 p_deal_no := null;
1872
1873 open deal_number_cursor(p_comp, p_cparty, p_curr);
1874 fetch deal_number_cursor into p_deal_no, p_tran_no;
1875 if deal_number_cursor%NOTFOUND and nvl(p_new_deal,'Y') = 'Y' then
1876
1877 --* No Deal Exists in that combination , so need to
1878 --* create a new deal number and the transaction number will
1879 --* be incremented then on.
1880 --*
1881 open new_deal_num;
1882 fetch new_deal_num into p_deal_no;
1883 close new_deal_num;
1884 p_tran_no := 1;
1885
1886 else
1887 p_tran_no := p_tran_no + 1;
1888 end if;
1889
1890 close deal_number_cursor;
1891
1892 end; /* GET_DEAL_TRAN_NUMBERS */
1893
1894
1895 -----------------------------------------------------------------------------------------------------
1896 --* Table Handler For Xtr_Interroup_Transfers For Inserting Row
1897 procedure CREATE_IG_DEAL(ARec_IG IN XTR_INTERGROUP_TRANSFERS%rowtype ) is
1898 -----------------------------------------------------------------------------------------------------
1899 begin
1900
1901 Insert into XTR_INTERGROUP_TRANSFERS(
1902 EXTERNAL_DEAL_ID ,
1903 ACCUM_INTEREST_BF ,
1904 ACCUM_INTEREST_BF_HCE ,
1905 BALANCE_BF ,
1906 BALANCE_BF_HCE ,
1907 BALANCE_OUT ,
1908 BALANCE_OUT_HCE ,
1909 COMMENTS ,
1910 COMPANY_ACCOUNT_NO ,
1911 COMPANY_CODE ,
1912 CREATED_BY ,
1913 CREATED_ON ,
1914 CURRENCY ,
1915 DEAL_NUMBER ,
1916 DEAL_TYPE ,
1917 INTEREST ,
1918 INTEREST_HCE ,
1919 INTEREST_RATE ,
1920 INTEREST_SETTLED ,
1921 INTEREST_SETTLED_HCE ,
1922 LIMIT_CODE ,
1923 LIMIT_CODE_INVEST ,
1924 NO_OF_DAYS ,
1925 PARTY_ACCOUNT_NO ,
1926 PARTY_CODE ,
1927 PORTFOLIO ,
1928 PRINCIPAL_ACTION ,
1929 PRINCIPAL_ADJUST ,
1930 PRINCIPAL_ADJUST_HCE ,
1931 PRODUCT_TYPE ,
1932 PRICING_MODEL ,
1933 SETTLE_DATE ,
1934 TRANSACTION_NUMBER ,
1935 TRANSFER_DATE ,
1936 ACCRUAL_INTEREST ,
1937 FIRST_BATCH_ID ,
1938 LAST_BATCH_ID ,
1939 ATTRIBUTE_CATEGORY ,
1940 ATTRIBUTE1 ,
1941 ATTRIBUTE2 ,
1942 ATTRIBUTE3 ,
1943 ATTRIBUTE4 ,
1944 ATTRIBUTE5 ,
1945 ATTRIBUTE6 ,
1946 ATTRIBUTE7 ,
1947 ATTRIBUTE8 ,
1948 ATTRIBUTE9 ,
1952 ATTRIBUTE13 ,
1949 ATTRIBUTE10 ,
1950 ATTRIBUTE11 ,
1951 ATTRIBUTE12 ,
1953 ATTRIBUTE14 ,
1954 ATTRIBUTE15 ,
1955 REQUEST_ID ,
1956 PROGRAM_APPLICATION_ID ,
1957 PROGRAM_ID ,
1958 PROGRAM_UPDATE_DATE ,
1959 MIRROR_DEAL ,
1960 MIRROR_DEAL_NUMBER ,
1961 MIRROR_TRANSACTION_NUMBER ,
1962 ROUNDING_TYPE ,
1963 DAY_COUNT_TYPE ,
1964 ORIGINAL_AMOUNT ,
1965 UPDATED_BY ,
1966 UPDATED_ON ,
1967 DEAL_LINKING_CODE ,
1968 DEALER_CODE,
1969 EXTERNAL_SOURCE -- 3800146 -- **********************************************************************************
1970 )
1971 Values
1972 (
1973 decode(G_Ig_External_Source,null,ARec_IG.EXTERNAL_DEAL_ID,null), -- 3800146 not needed for ZBA/CL ******************************
1974 ARec_IG.ACCUM_INTEREST_BF ,
1975 ARec_IG.ACCUM_INTEREST_BF_HCE ,
1976 ARec_IG.BALANCE_BF ,
1977 ARec_IG.BALANCE_BF_HCE ,
1978 ARec_IG.BALANCE_OUT ,
1979 ARec_IG.BALANCE_OUT_HCE ,
1980 ARec_IG.COMMENTS ,
1981 ARec_IG.COMPANY_ACCOUNT_NO ,
1982 ARec_IG.COMPANY_CODE ,
1983 G_Ig_user ,
1984 G_Ig_SysDate ,
1985 ARec_IG.CURRENCY ,
1986 ARec_IG.DEAL_NUMBER ,
1987 ARec_IG.DEAL_TYPE ,
1988 ARec_IG.INTEREST ,
1989 ARec_IG.INTEREST_HCE ,
1990 ARec_IG.INTEREST_RATE ,
1991 ARec_IG.INTEREST_SETTLED ,
1992 ARec_IG.INTEREST_SETTLED_HCE ,
1993 ARec_IG.LIMIT_CODE ,
1994 ARec_IG.LIMIT_CODE_INVEST ,
1995 ARec_IG.NO_OF_DAYS ,
1996 ARec_IG.PARTY_ACCOUNT_NO ,
1997 ARec_IG.PARTY_CODE ,
1998 ARec_IG.PORTFOLIO ,
1999 ARec_IG.PRINCIPAL_ACTION ,
2000 ARec_IG.PRINCIPAL_ADJUST ,
2001 ARec_IG.PRINCIPAL_ADJUST_HCE ,
2002 ARec_IG.PRODUCT_TYPE ,
2003 ARec_IG.PRICING_MODEL ,
2004 ARec_IG.SETTLE_DATE ,
2005 ARec_IG.TRANSACTION_NUMBER ,
2006 ARec_IG.TRANSFER_DATE ,
2007 ARec_IG.ACCRUAL_INTEREST ,
2008 ARec_IG.FIRST_BATCH_ID ,
2009 ARec_IG.LAST_BATCH_ID ,
2010 ARec_IG.ATTRIBUTE_CATEGORY ,
2011 ARec_IG.ATTRIBUTE1 ,
2012 ARec_IG.ATTRIBUTE2 ,
2013 ARec_IG.ATTRIBUTE3 ,
2014 ARec_IG.ATTRIBUTE4 ,
2015 ARec_IG.ATTRIBUTE5 ,
2016 ARec_IG.ATTRIBUTE6 ,
2017 ARec_IG.ATTRIBUTE7 ,
2018 ARec_IG.ATTRIBUTE8 ,
2019 ARec_IG.ATTRIBUTE9 ,
2020 ARec_IG.ATTRIBUTE10 ,
2021 ARec_IG.ATTRIBUTE11 ,
2022 ARec_IG.ATTRIBUTE12 ,
2023 ARec_IG.ATTRIBUTE13 ,
2024 ARec_IG.ATTRIBUTE14 ,
2025 ARec_IG.ATTRIBUTE15 ,
2026 fnd_global.conc_request_id ,
2027 fnd_global.prog_appl_id ,
2028 fnd_global.conc_program_id ,
2029 G_Ig_SysDate ,
2030 G_Ig_mirror_deal ,
2031 G_Ig_orig_deal_no ,
2032 G_Ig_orig_trans_no ,
2033 ARec_IG.ROUNDING_TYPE ,
2034 ARec_IG.DAY_COUNT_TYPE ,
2035 Arec_IG.ORIGINAL_AMOUNT ,
2036 -- ARec_IG.MIRROR_DEAL ,
2037 -- Arec_IG.MIRROR_DEAL_NUMBER ,
2038 -- Arec_IG.MIRROR_TRANSACTION_NUMBER ,
2039 G_Ig_user ,
2040 G_Ig_SysDate ,
2041 Arec_IG.DEAL_LINKING_CODE ,
2042 Arec_IG.DEALER_CODE,
2043 Arec_IG.EXTERNAL_SOURCE -- 3800146 *************************************************************************
2044 );
2045
2046 UPDATE_PRICING_MODEL(ARec_IG.COMPANY_CODE,
2047 ARec_IG.PARTY_CODE,
2048 ARec_IG.CURRENCY,
2049 ARec_IG.PRICING_MODEL);
2050
2051 end; /* CREATE_IG_DEAL */
2052
2053 --****************************************************************************************************
2054 -- 3800146 To settle a cashflow *******************************************************************
2055 --****************************************************************************************************
2056 procedure SETTLE_DDA (p_settle_flag IN VARCHAR2,
2057 p_actual_settle IN DATE,
2061 p_settle_date OUT NOCOPY DATE,
2058 p_settle OUT NOCOPY VARCHAR2,
2059 p_settle_no OUT NOCOPY NUMBER,
2060 p_settle_auth OUT NOCOPY VARCHAR2,
2062 p_trans_mts OUT NOCOPY VARCHAR2,
2063 p_audit_indic OUT NOCOPY VARCHAR2) is
2064
2065 begin
2066
2067
2068 if nvl(p_settle_flag,'N') = 'Y' then -- NOTE : Current IG form does not pass in value but it calls this for mirror deal.
2069
2070 select XTR_SETTLEMENT_NUMBER_S.NEXTVAL into p_settle_no from DUAL;
2071
2072 p_settle := 'Y';
2073 p_settle_auth := G_Ig_user;
2074 p_settle_date := p_actual_settle;
2075 p_trans_mts := 'Y';
2076 p_audit_indic := 'Y';
2077
2078 else
2079
2080 p_settle := 'N';
2081 p_settle_no := null;
2082 p_settle_auth := null;
2083 p_settle_date := null;
2084 p_trans_mts := null;
2085 p_audit_indic := null;
2086
2087 end if;
2088
2089 end;
2090
2091 -----------------------------------------------------------------------------------------------------
2092 -- Local procedure to insert Deal Date Rows for Principal Interest
2093 -- Flows
2094 procedure INS_DEAL_DATE_AMTS is
2095 -----------------------------------------------------------------------------------------------------
2096
2097 l_settle VARCHAR2(1);
2098 l_settle_no NUMBER;
2099 l_settle_auth xtr_deal_date_amounts.SETTLEMENT_AUTHORISED_BY%TYPE;
2100 l_settle_date DATE;
2101 l_trans_mts VARCHAR2(1);
2102 l_audit_indic VARCHAR2(1);
2103 l_dummy_num NUMBER;
2104 l_dummy_err VARCHAR2(80);
2105
2106 begin
2107 if G_Ig_Main_Rec.PRINCIPAL_ADJUST <> 0 then
2108
2109 --*****************************************************************************************************************
2110 -- 3800146 For settlement. Only affects PRINFLW ***************************************************************
2111 --*****************************************************************************************************************
2112 SETTLE_DDA (G_Ig_settlement_flag,
2113 G_Ig_Main_Rec.TRANSFER_DATE,
2114 l_settle,
2115 l_settle_no,
2116 l_settle_auth,
2117 l_settle_date,
2118 l_trans_mts,
2119 l_audit_indic);
2120 --*****************************************************************************************************************
2121
2122 insert into xtr_DEAL_DATE_AMOUNTS_v
2123 (DEAL_TYPE,AMOUNT_TYPE,DATE_TYPE,DEAL_NUMBER,TRANSACTION_NUMBER,
2124 TRANSACTION_DATE,CURRENCY,AMOUNT,HCE_AMOUNT,AMOUNT_DATE,
2125 CASHFLOW_AMOUNT,COMPANY_CODE,ACCOUNT_NO,ACTION_CODE,CPARTY_ACCOUNT_NO,
2126 STATUS_CODE,CPARTY_CODE,
2127 SETTLE,SETTLEMENT_NUMBER,SETTLEMENT_AUTHORISED_BY,ACTUAL_SETTLEMENT_DATE,TRANS_MTS,AUDIT_INDICATOR, -- 3800146 ******************
2128 DEAL_SUBTYPE,PRODUCT_TYPE, PORTFOLIO_CODE,
2129 dual_authorisation_by, dual_authorisation_on)
2130 ---,LIMIT_CODE)
2131 values
2132 ('IG','PRINFLW','COMENCE',G_Ig_Main_Rec.DEAL_NUMBER,G_Ig_Main_Rec.TRANSACTION_NUMBER,
2133 G_Ig_curr_date,G_Ig_Main_Rec.CURRENCY,abs(G_Ig_Main_Rec.PRINCIPAL_ADJUST),
2134 abs(G_Ig_Main_Rec.PRINCIPAL_ADJUST_HCE),G_Ig_Main_Rec.TRANSFER_DATE,
2135 decode(G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,NULL,0,
2136 decode(G_Ig_Main_Rec.PRINCIPAL_ACTION,'PAY',(-1) * G_Ig_Main_Rec.PRINCIPAL_ADJUST,
2137 G_Ig_Main_Rec.PRINCIPAL_ADJUST)),G_Ig_Main_Rec.COMPANY_CODE,
2138 G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,G_Ig_Main_Rec.PRINCIPAL_ACTION,G_Ig_Main_Rec.PARTY_ACCOUNT_NO,
2139 'CURRENT',G_Ig_Main_Rec.PARTY_CODE,
2140 l_settle,l_settle_no,l_settle_auth,l_settle_date,l_trans_mts,l_audit_indic, -- 3800146 *******************************
2141 decode(G_Ig_Main_Rec.PRINCIPAL_ACTION,'PAY',
2142 'INVEST','FUND'),G_Ig_Main_Rec.PRODUCT_TYPE,G_Ig_Main_Rec.PORTFOLIO,
2143 G_Ig_user, G_Ig_curr_date);
2144
2145 --*****************************************************************************************************************
2146 -- 3800146 For settlement. **************************************************************************************
2147 --*****************************************************************************************************************
2148 if nvl(G_Ig_settlement_flag,'N') = 'Y' then
2149 -- Condition Added Bug 4313886
2153 G_Ig_Main_Rec.COMPANY_CODE,
2150 if(G_Ig_Main_Rec.PRINCIPAL_ACTION = 'PAY') then
2151
2152 XTR_SETTLEMENT_SUMMARY_P.INS_SETTLEMENT_SUMMARY(l_settle_no,
2154 G_Ig_Main_Rec.CURRENCY,
2155 (-1) * G_Ig_Main_Rec.PRINCIPAL_ADJUST,
2156 l_settle_date, -- settlement date
2157 G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,
2158 G_Ig_Main_Rec.PARTY_ACCOUNT_NO,
2159 null,
2160 'A',
2161 G_Ig_user_id,
2162 G_Ig_curr_date, -- creation date
2163 G_Ig_External_Source,
2164 G_Ig_Main_Rec.PARTY_CODE, -- cparty code
2165 l_dummy_num);
2166 else
2167
2168 XTR_SETTLEMENT_SUMMARY_P.INS_SETTLEMENT_SUMMARY(l_settle_no,
2169 G_Ig_Main_Rec.COMPANY_CODE,
2170 G_Ig_Main_Rec.CURRENCY,
2171 G_Ig_Main_Rec.PRINCIPAL_ADJUST,
2172 l_settle_date, -- settlement date
2173 G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,
2174 G_Ig_Main_Rec.PARTY_ACCOUNT_NO,
2175 null,
2176 'A',
2177 G_Ig_user_id,
2178 G_Ig_curr_date, -- creation date
2179 G_Ig_External_Source,
2180 G_Ig_Main_Rec.PARTY_CODE, -- cparty code
2181 l_dummy_num);
2182 end if;
2183 ------------------------
2184 -- Workflow Notification
2185 ------------------------
2186 if nvl(G_Main_log_id,0) <> 0 then
2187 XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(l_settle_no,
2188 null,
2189 G_Ig_user,
2190 G_Main_log_id);
2191
2192 XTR_WORKFLOW_PKG.START_WORKFLOW('XTR_LIMITS_NOTIFICATION','XTR',G_Ig_Main_Rec.DEAL_NUMBER,
2193 G_Ig_Main_Rec.TRANSACTION_NUMBER, 'IG',G_Main_log_id);
2194 end if;
2195 end if;
2196
2197 end if; -- G_Ig_Main_Rec.PRINCIPAL_ADJUST <> 0
2198
2199 /*-----------------------------------------------------------------*/
2200 /* This will not be executed for Open API since interest is zero. */
2201 /*-----------------------------------------------------------------*/
2202 if nvl(G_Ig_Main_Rec.INTEREST_SETTLED,0) <> 0 then
2203
2204 insert into xtr_DEAL_DATE_AMOUNTS_v
2205 (DEAL_TYPE,AMOUNT_TYPE,DATE_TYPE,DEAL_NUMBER,TRANSACTION_NUMBER,
2206 TRANSACTION_DATE,CURRENCY,AMOUNT,HCE_AMOUNT,AMOUNT_DATE,
2207 CASHFLOW_AMOUNT,COMPANY_CODE,ACCOUNT_NO,ACTION_CODE,CPARTY_ACCOUNT_NO,
2208 STATUS_CODE,CPARTY_CODE,SETTLE,DEAL_SUBTYPE,PRODUCT_TYPE,
2209 PORTFOLIO_CODE,
2210 dual_authorisation_by, dual_authorisation_on) ---- ,LIMIT_CODE)
2211 values
2212 ('IG','INTSET','SETTLE',G_Ig_Main_Rec.DEAL_NUMBER,G_Ig_Main_Rec.TRANSACTION_NUMBER,G_Ig_curr_date,
2213 G_Ig_Main_Rec.CURRENCY,abs(G_Ig_Main_Rec.INTEREST_SETTLED),abs(G_Ig_Main_Rec.INTEREST_SETTLED_HCE),
2214 nvl(G_Ig_Main_Rec.SETTLE_DATE,G_Ig_Main_Rec.TRANSFER_DATE),
2215 decode(G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,null,0,G_Ig_Main_Rec.INTEREST_SETTLED),
2216 G_Ig_Main_Rec.COMPANY_CODE,G_Ig_Main_Rec.COMPANY_ACCOUNT_NO,
2217 decode(sign(G_Ig_Main_Rec.INTEREST_SETTLED),-1,'PAY','REC'),
2218 G_Ig_Main_Rec.PARTY_ACCOUNT_NO,'CURRENT',G_Ig_Main_Rec.PARTY_CODE,'N',
2219 decode(sign(G_Ig_Main_Rec.INTEREST_SETTLED),-1,'FUND','INVEST'),
2220 G_Ig_Main_Rec.PRODUCT_TYPE,G_Ig_Main_Rec.PORTFOLIO,
2221 G_Ig_user, G_Ig_curr_date);
2222 end if;
2226
2223
2224 end; /* INS_DEAL_DATE_AMTS */
2225
2227 -----------------------------------------------------------------------------------------------------
2228 -- Local procedure to insert Deal Date Rows for Principal Interest for Mirror Transaction
2229 -- Flows
2230 procedure INS_MIRROR_DEAL_DATE_AMTS is
2231 -----------------------------------------------------------------------------------------------------
2232 --*****************************************************************************************************************
2233 -- 3800146 set values for settlement ***************************************************************************
2234 --*****************************************************************************************************************
2235 l_settle VARCHAR2(1);
2236 l_settle_no NUMBER;
2237 l_settle_auth xtr_deal_date_amounts.SETTLEMENT_AUTHORISED_BY%TYPE;
2238 l_settle_date DATE;
2239 l_trans_mts VARCHAR2(1);
2240 l_audit_indic VARCHAR2(1);
2241 l_dummy_num NUMBER;
2242 l_dummy_err VARCHAR2(80);
2243
2244 begin
2245 if G_Ig_Mirror_Rec.PRINCIPAL_ADJUST <> 0 then
2246
2247 --*****************************************************************************************************************
2248 -- 3800146 Only affects PRINFLW *********************************************************************************
2249 --*****************************************************************************************************************
2250 SETTLE_DDA (G_Ig_settlement_flag,
2251 G_Ig_Mirror_Rec.TRANSFER_DATE,
2252 l_settle,
2253 l_settle_no,
2254 l_settle_auth,
2255 l_settle_date,
2256 l_trans_mts,
2257 l_audit_indic);
2258 -----------------------------------------------------------------
2259
2260 insert into xtr_DEAL_DATE_AMOUNTS_v
2261 (DEAL_TYPE,AMOUNT_TYPE,DATE_TYPE,DEAL_NUMBER,TRANSACTION_NUMBER,
2262 TRANSACTION_DATE,CURRENCY,AMOUNT,HCE_AMOUNT,AMOUNT_DATE,
2263 CASHFLOW_AMOUNT,COMPANY_CODE,ACCOUNT_NO,ACTION_CODE,CPARTY_ACCOUNT_NO,
2264 STATUS_CODE,CPARTY_CODE,
2265 SETTLE,SETTLEMENT_NUMBER,SETTLEMENT_AUTHORISED_BY,ACTUAL_SETTLEMENT_DATE,TRANS_MTS,AUDIT_INDICATOR, -- 3800146 ****************
2266 DEAL_SUBTYPE,PRODUCT_TYPE, PORTFOLIO_CODE,
2267 dual_authorisation_by, dual_authorisation_on)
2268 ---,LIMIT_CODE)
2269 values
2270 ('IG','PRINFLW','COMENCE',G_Ig_Mirror_Rec.DEAL_NUMBER,G_Ig_Mirror_Rec.TRANSACTION_NUMBER,
2271 G_Ig_curr_date,G_Ig_Mirror_Rec.CURRENCY,abs(G_Ig_Mirror_Rec.PRINCIPAL_ADJUST),
2272 abs(G_Ig_Mirror_Rec.PRINCIPAL_ADJUST_HCE),G_Ig_Mirror_Rec.TRANSFER_DATE,
2273 decode(G_Ig_Mirror_Rec.COMPANY_ACCOUNT_NO,NULL,0,
2274 decode(G_Ig_Mirror_Rec.PRINCIPAL_ACTION,'PAY',(-1) * G_Ig_Mirror_Rec.PRINCIPAL_ADJUST,
2275 G_Ig_Mirror_Rec.PRINCIPAL_ADJUST)),G_Ig_Mirror_Rec.COMPANY_CODE,
2276 G_Ig_Mirror_Rec.COMPANY_ACCOUNT_NO,G_Ig_Mirror_Rec.PRINCIPAL_ACTION,G_Ig_Mirror_Rec.PARTY_ACCOUNT_NO,
2277 'CURRENT',G_Ig_Mirror_Rec.PARTY_CODE,
2278 l_settle,l_settle_no,l_settle_auth,l_settle_date,l_trans_mts,l_audit_indic, -- 3800146 ***************************
2282 --*****************************************************************************************************************
2279 decode(G_Ig_Mirror_Rec.PRINCIPAL_ACTION,'PAY', 'INVEST','FUND'),G_Ig_Mirror_Rec.PRODUCT_TYPE,G_Ig_Mirror_Rec.PORTFOLIO,
2280 G_Ig_user, G_Ig_curr_date);
2281
2283 -- 3800146 For settlement. **************************************************************************************
2284 --*****************************************************************************************************************
2285 if nvl(G_Ig_settlement_flag,'N') = 'Y' then
2286 XTR_SETTLEMENT_SUMMARY_P.INS_SETTLEMENT_SUMMARY(l_settle_no,
2287 G_Ig_Mirror_Rec.COMPANY_CODE,
2288 G_Ig_Mirror_Rec.CURRENCY,
2289 G_Ig_Mirror_Rec.PRINCIPAL_ADJUST,
2290 l_settle_date, -- settlement date
2291 G_Ig_Mirror_Rec.COMPANY_ACCOUNT_NO,
2292 G_Ig_Mirror_Rec.PARTY_ACCOUNT_NO,
2293 null,
2294 'A',
2295 G_Ig_user_id,
2296 G_Ig_curr_date, -- creation date
2297 G_Ig_External_Source,
2298 G_Ig_Mirror_Rec.PARTY_CODE, -- cparty code
2299 l_dummy_num);
2300 ------------------------
2301 -- Workflow Notification
2302 ------------------------
2303 if nvl(G_Mirror_log_id,0) <> 0 then
2304 XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(l_settle_no,
2305 null,
2306 G_Ig_user,
2307 G_Mirror_log_id);
2308
2309 XTR_WORKFLOW_PKG.START_WORKFLOW('XTR_LIMITS_NOTIFICATION','XTR',G_Ig_Mirror_Rec.DEAL_NUMBER,
2310 G_Ig_Mirror_Rec.TRANSACTION_NUMBER, 'IG',G_Mirror_log_id);
2311 end if;
2312
2313 end if;
2314
2315 end if;
2316
2317 end; /* INS_MIRROR_DEAL_DATE_AMTS */
2318
2319
2320 -----------------------------------------------------------------------------------------------------
2321 -- Local Procedure to re calculate amounts for subsequent records where previous records have been ammended.
2322 procedure CASCADE_RECALC(p_company_code IN VARCHAR2,
2323 p_party_code IN VARCHAR2,
2324 p_currency IN VARCHAR2,
2325 p_transfer_date IN DATE,
2326 p_fund_limit IN VARCHAR2,
2327 p_invest_limit IN VARCHAR2,
2328 p_update IN VARCHAR2,
2329 p_rounding_type IN VARCHAR2, --* Add for Interest Override
2330 p_day_count_type IN VARCHAR2, --* Add for Interest Override
2331 p_types_update IN VARCHAR2) is --* Add for Interest Override
2332 -- p_error OUT VARCHAR2) is
2333 -----------------------------------------------------------------------------------------------------
2334
2335 -- only if there are subsequent transactions;
2336
2337 l_count NUMBER;
2338 l_int_rate NUMBER;
2339 l_int_settled NUMBER;
2340 l_deal_num NUMBER;
2341 l_trans_num NUMBER;
2342 l_bal_out NUMBER := 0;
2343 l_bal_out_hce NUMBER := 0;
2344 l_prin_action VARCHAR2(7);
2345 l_prin_adj NUMBER;
2346 l_accum_int_bf NUMBER;
2347 l_accum_int_bf_hce NUMBER;
2348 l_date DATE;
2349 l_interest NUMBER;
2350 l_interest_hce NUMBER;
2351 l_days NUMBER;
2352 l_fund_limit VARCHAR2(7);
2353 l_invest_limit VARCHAR2(7);
2354 l_limit_type VARCHAR2(10);
2355 l_product VARCHAR2(10);
2356 l_portfolio VARCHAR2(10);
2357 l_rowid VARCHAR2(30);
2358 l_accrual_int NUMBER;
2359 prv_date DATE;
2360 prv_bal_out NUMBER;
2361 prv_bal_out_hce NUMBER;
2362 prv_accum_int NUMBER;
2363 prv_int_rate NUMBER;
2364 roundfac NUMBER;
2365 hce_roundfac NUMBER;
2366 yr_basis NUMBER;
2367 acct VARCHAR2(20);
2368 hce_rate NUMBER;
2369 prv_accrual_int NUMBER;
2370 trans_date DATE;
2371 v_ig_year_basis VARCHAR2(20);
2372 --
2373 --* Add for Interest Project
2374 l_oldest_date DATE;
2375 l_first_trans_flag VARCHAR2(1);
2376 l_prv_day_count_type VARCHAR2(1);
2377 l_rounding_type VARCHAR2(1);
2378 l_day_count_type VARCHAR2(1);
2379 l_interest_to_date NUMBER;
2380 --
2381
2382 cursor GET_HCE is
2383 select a.hce_rate,
2384 a.rounding_factor,
2388 xtr_PARTIES_V p,
2385 a.ig_year_basis,
2386 m.rounding_factor
2387 from xtr_MASTER_CURRENCIES_V a,
2389 xtr_MASTER_CURRENCIES_V m
2390 where a.CURRENCY = p_currency
2391 and p.PARTY_CODE = p_company_code
2392 and m.CURRENCY = p.HOME_CURRENCY;
2393 --
2394 cursor RECALC_DATE is
2395 select rowid,
2396 DEAL_NUMBER,
2397 TRANSFER_DATE,
2398 nvl(BALANCE_OUT,0),
2399 nvl(BALANCE_OUT_HCE,0),
2400 nvl(PRINCIPAL_ADJUST,0),
2401 PRINCIPAL_ACTION,
2402 INTEREST_RATE,
2403 nvl(INTEREST_SETTLED,0),
2404 nvl(NO_OF_DAYS,0),
2405 TRANSACTION_NUMBER,
2406 nvl(ACCUM_INTEREST_BF,0),
2407 nvl(INTEREST,0),
2408 LIMIT_CODE,
2409 LIMIT_CODE_INVEST,
2410 PRODUCT_TYPE,
2411 PORTFOLIO,
2412 nvl(ACCRUAL_INTEREST,0),
2413 ROUNDING_TYPE, --* Add for Interest Override
2414 DAY_COUNT_TYPE --* Add for Interest Override
2415 from XTR_INTERGROUP_TRANSFERS_v
2416 where PARTY_CODE = p_party_code
2417 and CURRENCY = p_currency
2418 and COMPANY_CODE = p_company_code
2419 and TRANSFER_DATE >= p_transfer_date
2420 order by TRANSFER_DATE asc, TRANSACTION_NUMBER asc;
2421 --
2422 /* This will be checked by VALID_LIMIT_CODE. Don't need to check for final balance.
2423 cursor get_type (c_limit_code IN VARCHAR2) is
2424 select b.fx_invest_fund_type
2425 from xtr_counterparty_limits_v a,
2426 xtr_limit_types_v b
2427 where a.company_code = p_company_code
2428 and a.cparty_code = p_party_code
2429 and a.limit_type = b.limit_type
2430 and a.limit_code = c_limit_code;
2431 */
2432
2433 --* Add for Interest Override Project
2434 cursor PRV_DAY_COUNT_TYPE is
2435 SELECT DAY_COUNT_TYPE
2436 FROM XTR_INTERGROUP_TRANSFERS
2437 WHERE PARTY_CODE = p_party_code
2438 AND CURRENCY = p_currency
2439 AND COMPANY_CODE = p_company_code
2440 AND TRANSFER_DATE = (select max(transfer_date)
2441 from xtr_intergroup_transfers
2442 where party_code = p_party_code
2443 and currency = p_currency
2444 and company_code = p_company_code
2445 and transfer_date < prv_date)
2446 order by transaction_number desc;
2447
2448
2449 begin
2450
2451 -- p_error := NULL;
2452 -- G_Ig_bal_out := NULL; --RV 2229236
2453
2454 if p_transfer_date is not null then
2455
2456 open GET_HCE;
2457 fetch GET_HCE INTO hce_rate, roundfac, v_ig_year_basis, hce_roundfac;
2458 close GET_HCE;
2459
2460 --* Add for Interest Project
2461 /* Get the oldest transfer date */
2462 select min(TRANSFER_DATE)
2463 into l_oldest_date
2464 from XTR_INTERGROUP_TRANSFERS
2465 where PARTY_CODE = p_party_code
2466 and CURRENCY = p_currency
2467 and COMPANY_CODE = p_company_code;
2468 --* Add End
2469
2470 --
2471 l_count := 0;
2472
2473 open RECALC_DATE;
2474 fetch RECALC_DATE INTO l_rowid, l_deal_num, l_date,l_bal_out, l_bal_out_hce,
2475 l_prin_adj, l_prin_action, l_int_rate, l_int_settled,
2476 l_days, l_trans_num, l_accum_int_bf, l_interest,
2477 l_fund_limit, l_invest_limit,l_product, l_portfolio, l_accrual_int,
2478 l_rounding_type, l_day_count_type; --* Add for Interest Override
2479 --l_limit_code, l_product, l_portfolio, l_accrual_int;
2480 while RECALC_DATE%FOUND LOOP
2481 if l_count <> 0 then
2482
2483 if l_prin_action = 'REC' then
2484 l_prin_adj := (-1) * l_prin_adj;
2485 end if;
2486
2487 l_bal_out := nvl(prv_bal_out,0) + nvl(l_prin_adj,0);
2488 l_bal_out_hce := nvl(round(l_bal_out / hce_rate,hce_roundfac),0);
2489 l_accum_int_bf := nvl(prv_accum_int,0);
2490 l_accum_int_bf_hce := nvl(round(l_accum_int_bf / hce_rate,hce_roundfac),0);
2491
2492 if l_date > prv_date then
2493
2494 --* Add for Interest Project
2495 /* Check the transations to decide First Transaction Flag */
2496 l_first_trans_flag := 'N';
2497 if nvl(p_day_count_type,l_day_count_type)= 'B' and prv_date = l_oldest_date then -- This transaction is oldest
2498 l_first_trans_flag := 'Y';
2499 else
2500 if (nvl(p_day_count_type,l_day_count_type) = 'B'
2501 or nvl(p_day_count_type,l_day_count_type) = 'F') and prv_date <> l_oldest_date then
2502
2503 OPEN PRV_DAY_COUNT_TYPE;
2504 FETCH PRV_DAY_COUNT_TYPE INTO l_prv_day_count_type;
2505 CLOSE PRV_DAY_COUNT_TYPE;
2506
2507 if (l_prv_day_count_type = 'B' or l_prv_day_count_type = 'L') and
2508 nvl(p_day_count_type,l_day_counT_type) = 'F' then
2509 prv_date := prv_date + 1;
2510 elsif l_prv_day_count_type = 'F' and nvl(p_day_count_type,l_day_count_type) = 'B' then
2511 l_first_trans_flag := 'Y';
2515 --* Add End
2512 end if;
2513 end if;
2514 end if;
2516
2517
2518 XTR_CALC_P.CALC_DAYS_RUN(prv_date,
2519 l_date,
2520 nvl(v_ig_year_basis, 'ACTUAL/ACTUAL'),
2521 l_days,
2522 yr_basis,
2523 null,
2524 nvl(p_day_count_type,l_day_count_type), -- Add for Interest Project
2525 l_first_trans_flag); -- Add for Interest Project
2526
2527 else
2528 l_days := 0;
2529 yr_basis := 365;
2530 end if;
2531 -- Original Code
2532 -- l_interest := round(prv_bal_out * prv_int_rate / 100 * l_days / yr_basis,roundfac);
2533 --* Add for Interest Project
2534 l_interest := xtr_fps2_p.interest_round(prv_bal_out * prv_int_rate / 100 * l_days / yr_basis,roundfac,
2535 nvl(p_rounding_type,l_rounding_type));
2536 l_interest_hce := nvl(round(l_interest / hce_rate,hce_roundfac),0);
2537
2538 l_accrual_int := nvl(prv_accrual_int,0) + nvl(l_interest,0);
2539
2540 if p_update = 'Y' then
2541 update xtr_INTERGROUP_TRANSFERS_v
2542 set BALANCE_BF = prv_bal_out,
2543 BALANCE_BF_HCE = prv_bal_out_hce,
2544 BALANCE_OUT = l_bal_out,
2545 BALANCE_OUT_HCE = l_bal_out_hce,
2546 ACCUM_INTEREST_BF = l_accum_int_bf,
2547 ACCUM_INTEREST_BF_HCE = l_accum_int_bf_hce,
2548 INTEREST = l_interest,
2549 INTEREST_HCE = l_interest_hce,
2550 NO_OF_DAYS = l_days,
2551 ACCRUAL_INTEREST = l_accrual_int,
2552 DAY_COUNT_TYPE = nvl(p_day_count_type,l_day_count_type),
2553 ROUNDING_TYPE = nvl(p_rounding_type,l_rounding_type),
2554 ORIGINAL_AMOUNT = l_accum_int_bf + l_interest
2555
2556 where rowid = l_rowid;
2557 end if;
2558
2559 END IF;
2560 --* Add for Interest Project
2561 --* When this flag is set, all transaction should be recalculated and replaced by new types.
2562
2563 if p_types_update = 'Y' and p_update ='Y' and l_count = 0 then
2564 update xtr_INTERGROUP_TRANSFERS_v
2565 set DAY_COUNT_TYPE = nvl(p_day_count_type,l_day_count_type),
2566 ROUNDING_TYPE = nvl(p_rounding_type,l_rounding_type)
2567 where rowid=l_rowid;
2568 end if;
2569 --* Add End
2570
2571
2572 prv_bal_out := nvl(l_bal_out,0);
2573 prv_bal_out_hce := nvl(round(prv_bal_out / hce_rate,hce_roundfac),0);
2574 prv_accum_int := nvl(l_accum_int_bf,0) + nvl(l_interest,0) - nvl(l_int_settled,0);
2575 prv_date := l_date;
2576 prv_int_rate := l_int_rate;
2580
2577 prv_accrual_int := nvl(l_accrual_int,0);
2578
2579 l_count := l_count + 1;
2581 EXIT WHEN RECALC_DATE%NOTFOUND;
2582 fetch RECALC_DATE INTO l_rowid, l_deal_num, l_date,l_bal_out, l_bal_out_hce,
2583 l_prin_adj, l_prin_action, l_int_rate, l_int_settled,
2584 l_days, l_trans_num, l_accum_int_bf, l_interest,
2585 l_fund_limit, l_invest_limit,l_product, l_portfolio, l_accrual_int,
2586 l_rounding_type, l_day_count_type;
2587 --l_limit_code, l_product, l_portfolio, l_accrual_int;
2588 END LOOP;
2589
2590 close RECALC_DATE;
2591
2592 --------------------------------
2593 -- To be used by limits check --
2594 --------------------------------
2595 -- G_Ig_bal_out := l_bal_out; -- RV 2229236
2596 --------------------------------
2597
2598 /* The check is handled in VALID_LIMIT_CODE
2599 if p_update = 'N' then
2600
2601 if l_bal_out < 0 and p_fund_limit is not null then
2602 open get_type(p_fund_limit);
2603 fetch get_type into l_limit_type;
2604 close get_type;
2605 if l_limit_type = 'I' then
2606 p_error := 'FUND'; -- Limit code should be a FUND type.
2607 end if;
2608 elsif l_bal_out > 0 and p_invest_limit is not null then
2609 open get_type(p_invest_limit);
2610 fetch get_type into l_limit_type;
2611 close get_type;
2612 if l_limit_type = 'I' then
2613 p_error := 'INVEST'; -- Limit code should be a INVEST type.
2614 end if;
2615 end if;
2616 */
2617
2618 if p_update = 'Y' then
2619
2620 update xtr_deal_date_amounts_v
2621 set AMOUNT = abs(nvl(l_bal_out,0)),
2622 HCE_AMOUNT = abs(nvl(l_bal_out_hce,0)),
2623 AMOUNT_DATE = nvl(l_date,G_Ig_curr_date),
2624 --LIMIT_CODE = nvl(l_limit_code,'NILL'),
2625 LIMIT_CODE = decode(sign(nvl(l_bal_out,0)),-1,nvl(p_fund_limit,'NILL'),
2626 nvl(p_invest_limit,'NILL')),
2627 LIMIT_PARTY = p_party_code,
2628 PORTFOLIO_CODE = l_portfolio,
2629 PRODUCT_TYPE = l_product,
2630 DEAL_SUBTYPE = decode(sign(nvl(l_bal_out,0)),-1,'FUND','INVEST'),
2631 ACTION_CODE = decode(sign(nvl(l_bal_out,0)),-1,'PAY','REC'),
2632 TRANSACTION_RATE = l_int_rate
2633 where DEAL_TYPE = 'IG'
2634 and DEAL_NUMBER = l_deal_num
2635 and AMOUNT_TYPE = 'BAL'
2636 and CPARTY_CODE = p_party_code
2637 and CURRENCY = p_currency
2638 and COMPANY_CODE = p_company_code;
2639
2640 if SQL%NOTFOUND and l_count <> 0 then
2641 insert into xtr_DEAL_DATE_AMOUNTS_v (DEAL_TYPE, AMOUNT_TYPE,
2642 DATE_TYPE, DEAL_NUMBER,
2643 TRANSACTION_NUMBER, TRANSACTION_DATE,
2644 AMOUNT_DATE, COMPANY_CODE,
2645 STATUS_CODE, CPARTY_CODE, LIMIT_PARTY,
2646 LIMIT_CODE, PORTFOLIO_CODE,
2647 CURRENCY, TRANSACTION_RATE,
2648 AMOUNT, HCE_AMOUNT,
2649 ACTION_CODE,
2650 DEAL_SUBTYPE,
2651 PRODUCT_TYPE,
2652 DUAL_AUTHORISATION_BY, DUAL_AUTHORISATION_ON)
2653 values ('IG', 'BAL',
2654 'BALANCE', l_deal_num,
2655 l_trans_num, l_date,
2656 l_date, p_company_code,
2657 'CURRENT', p_party_code, p_party_code,
2658 -- nvl(l_limit_code,'NILL'),
2659 decode(sign(nvl(l_bal_out,0)),-1,nvl(p_fund_limit,'NILL'),
2660 nvl(p_invest_limit,'NILL')),
2661 l_portfolio,
2662 p_currency, l_int_rate,
2663 abs(l_bal_out), abs(nvl(L_bal_out_hce,0)),
2664 decode(sign(nvl(l_bal_out,0)),-1,'PAY','REC'),
2665 decode(sign(nvl(l_bal_out,0)),-1,'FUND','INVEST'),
2666 nvl(l_product,'NOT APPLIC'),
2670 end if; /* p_update = 'Y' */
2667 G_Ig_user, G_Ig_curr_date);
2668 end if;
2669
2671
2672 end if; /* p_transfer_date is null */
2673
2674 end; /* CASCADE_RECALC */
2675
2676
2677 -------------------------------------------------------------------------------------------------------
2678 procedure MIRROR_INIT(p_mirror_deal IN VARCHAR2,
2679 p_mirror_deal_no IN NUMBER,
2680 p_mirror_trans_no IN NUMBER,
2681 p_rounding_type IN VARCHAR2, --* Added for Interest Override
2682 p_day_count_type IN VARCHAR2) is --* Added for Interest Override
2683 -------------------------------------------------------------------------------------------------------
2684 Begin
2685 G_Ig_Mirror_Deal := p_mirror_deal ;
2686 G_Ig_Orig_Deal_No := p_mirror_deal_no ;
2687 G_Ig_Orig_Trans_No := p_mirror_trans_no;
2688 --* Added for Interest Override
2689 G_Ig_Rounding_Type := p_rounding_type ;
2690 G_Ig_Day_Count_Type := p_day_count_type ;
2691 End;
2692
2693
2694
2695 -----------------------------------------------------------------------------------------------------
2696 --* Main procedure for Import Deal Record that calls all the validation APIs
2697 --* stub for backwards compatibility
2698 procedure TRANSFER_IG_DEALS( ARec_Interface IN XTR_DEALS_INTERFACE%rowtype,
2699 p_source IN VARCHAR2,
2700 user_error OUT NOCOPY BOOLEAN,
2701 mandatory_error OUT NOCOPY BOOLEAN,
2702 validation_error OUT NOCOPY BOOLEAN,
2703 limit_error OUT NOCOPY BOOLEAN) is
2704 -----------------------------------------------------------------------------------------------------
2705
2706 --*****************************************************************************************************************
2707 -- 3800146 for backward compatibility *****************************************************************************
2708 --*****************************************************************************************************************
2709 v_dummy NUMBER;
2710 v_dummy2 NUMBER;
2711 v_dummy3 NUMBER;
2712 v_dummy4 NUMBER;
2713 BEGIN
2714 TRANSFER_IG_DEALS(ARec_Interface,p_source,user_error,mandatory_error,validation_error,limit_error,v_dummy,
2715 v_dummy2, v_dummy3, v_dummy4);
2716 END TRANSFER_IG_DEALS;
2717 -----------------------------------------------------------------------------------------------------
2718
2719
2720 --*****************************************************************************************************************
2721 -- 3800146 backwards compatibility ******************************************************************************
2722 --*****************************************************************************************************************
2723 procedure TRANSFER_IG_DEALS( ARec_Interface IN XTR_DEALS_INTERFACE%rowtype,
2724 p_source IN VARCHAR2,
2725 user_error OUT NOCOPY BOOLEAN,
2726 mandatory_error OUT NOCOPY BOOLEAN,
2727 validation_error OUT NOCOPY BOOLEAN,
2728 limit_error OUT NOCOPY BOOLEAN,
2729 deal_num OUT NOCOPY NUMBER) is
2730 v_dummy2 NUMBER;
2731 v_dummy3 NUMBER;
2732 v_dummy4 NUMBER;
2733
2734 BEGIN
2735 TRANSFER_IG_DEALS(ARec_Interface,p_source,user_error,mandatory_error,validation_error,limit_error,deal_num,
2736 v_dummy2, v_dummy3, v_dummy4);
2737 END TRANSFER_IG_DEALS;
2738 -----------------------------------------------------------------------------------------------------
2739
2740
2741 -----------------------------------------------------------------------------------------------------
2742 --* Main procedure for Import Deal Record that calls all the validation APIs
2743 --* and then finally calls the Insert Table Handler.
2744 procedure TRANSFER_IG_DEALS( ARec_Interface IN XTR_DEALS_INTERFACE%rowtype,
2745 p_source IN VARCHAR2,
2746 user_error OUT NOCOPY BOOLEAN,
2747 mandatory_error OUT NOCOPY BOOLEAN,
2748 validation_error OUT NOCOPY BOOLEAN,
2749 limit_error OUT NOCOPY BOOLEAN,
2750 deal_num OUT NOCOPY NUMBER,
2751 tran_num OUT NOCOPY NUMBER, -- ***********************************************************************
2752 mirror_deal_num OUT NOCOPY NUMBER, -- 3800146 New return params for ZBA/CL *******************************
2753 mirror_tran_num OUT NOCOPY NUMBER) is -- ***********************************************************************
2754 -----------------------------------------------------------------------------------------------------
2755
2756 v_limit_log_return NUMBER;
2757 v_mirror_limit_log_return NUMBER;
2758 l_deal_subtype VARCHAR2(10);
2759 l_mirror_deal_subtype VARCHAR2(10);
2760 l_dummy VARCHAR2(10);
2761 l_dummy_num NUMBER;
2762 l_limit_code VARCHAR2(10);
2763 l_mirror_limit_code VARCHAR2(10);
2764 l_mirror_ig_action VARCHAR2(7); --RV
2765
2766 duplicate_error BOOLEAN := FALSE;
2767
2768 cursor FIND_USER (p_fnd_user in number) is
2769 select dealer_code
2770 from xtr_dealer_codes_v
2771 where user_id = p_fnd_user;
2772
2773 --*****************************************************************************************************************
2774 -- 3800146 ******************************************************************************************************
2775 --*****************************************************************************************************************
2776 cursor USER_ACCESS (l_comp VARCHAR2) is
2780 and party_code = l_comp;
2777 select 'Y'
2778 from xtr_parties_v
2779 where party_type = 'C'
2781 /*
2782 temp_Interface_rate_a XTR_DEALS_INTERFACE.RATE_A%TYPE;
2783 temp_Interface_Rounding_Type XTR_DEALS_INTERFACE.ROUNDING_TYPE%TYPE;
2784 temp_Interface_Day_Count_Type XTR_DEALS_INTERFACE.DAY_COUNT_TYPE%TYPE;
2785 temp_Interface_pricing_model XTR_DEALS_INTERFACE.PRICING_MODEL%TYPE;
2786 temp_Interface_mirror_pricing XTR_DEALS_INTERFACE.PRICING_MODEL%TYPE;
2787 */
2788
2789 begin
2790
2791
2792 -------------------------
2793 -- Initialise Variables
2794 -------------------------
2795 /*------------------ Rvallams: Bug# 2229236 -------------------------*/
2796 G_Ig_Source := p_source;
2797
2798 --*****************************************************************************************************************
2799 -- 3800146 IG/IAC Redesign **************************************************************************************
2800 --*****************************************************************************************************************
2801 G_Ig_External_Source := ARec_Interface.external_source;
2802 G_Ig_Settlement_Flag := ARec_Interface.settlement_flag;
2803 G_Main_log_id := null;
2804 G_Mirror_log_id := null;
2805 deal_num := null;
2806 tran_num := null;
2807 mirror_deal_num := null;
2808 mirror_tran_num := null;
2809 --*****************************************************************************************************************
2810 -- bug 4368177 Made the following variables null
2811 if G_Ig_Source is null and not is_company(ARec_Interface.cparty_code) then
2812 G_Ig_mirror_deal := null;
2813 G_Ig_orig_deal_no := null;
2814 G_Ig_orig_trans_no := null;
2815 end if;
2816
2817
2818 user_error := FALSE;
2819 mandatory_error := FALSE;
2820 validation_error := FALSE;
2821 limit_error := FALSE;
2822
2823 G_Ig_user_id := fnd_global.user_id;
2824 open FIND_USER(G_Ig_User_Id);
2825 fetch FIND_USER into G_Ig_user;
2826 close FIND_USER;
2827
2831 --******************************************************************************************************
2828 Select sysdate Into G_Ig_SysDate From Dual;
2829 G_Ig_curr_date := Trunc(G_Ig_SysDate);
2830
2832 --* Perform the following to purge all the related data in the error table before processing the record
2833 --
2834 --******************************************************************************************************
2835 -- 3800146 Not necessary for ZBA/CL *****************************************************************
2836 --******************************************************************************************************
2837 if G_Ig_Source is null and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA,C_CL) then
2838 delete from xtr_interface_errors
2839 where external_deal_id = ARec_Interface.external_deal_id
2840 and deal_type = ARec_Interface.deal_type;
2841 end if;
2842
2843 ----------------------------------------------------------------------------------------------------
2844 --* The following code checks if user has permissions to transfer the deal (company authorization)
2845 ----------------------------------------------------------------------------------------------------
2846 if nvl(G_Ig_External_Source,'@@@') in (C_ZBA,C_CL) then
2847 -- *************************************************************************************************
2848 -- 3800146 to check for user access when coming from ZBA and CL **********************************
2849 -- *************************************************************************************************
2850 open USER_ACCESS(ARec_Interface.company_code);
2851 fetch USER_ACCESS into l_dummy;
2852 if USER_ACCESS%NOTFOUND then
2853 Log_Ig_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,null,'XTR_INV_COMP_CODE');
2854 user_error := TRUE;
2855 end if;
2856 close USER_ACCESS;
2857
2858 if user_error <> TRUE then
2859 if IS_COMPANY(ARec_Interface.cparty_code) then
2860 open USER_ACCESS(ARec_Interface.cparty_code);
2861 fetch USER_ACCESS into l_dummy;
2862 if USER_ACCESS%NOTFOUND then
2863 Log_Ig_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,null,'XTR_INV_COMP_CODE');
2864 user_error := TRUE;
2865 end if;
2866 close USER_ACCESS;
2867 end if;
2868 end if;
2869
2870 else
2871 xtr_import_deal_data.CHECK_USER_AUTH(ARec_Interface.external_deal_id,
2872 ARec_Interface.deal_type,
2873 ARec_Interface.company_code,
2874 user_error);
2875 end if;
2876
2877 if (user_error <> TRUE) then
2878
2879 --------------------------------------------------------------------------------
2880 --* The following code does mandatory field validation specific to the IG deals
2881 --------------------------------------------------------------------------------
2882 CHECK_MANDATORY_FIELDS(ARec_Interface,mandatory_error);
2883
2884
2885 if (mandatory_error <> TRUE) then
2886
2887 --------------------------------------------------------------------------------------
2888 --* The following code performs the business logic validation specific to the IG deals
2889 --------------------------------------------------------------------------------------
2890 VALIDATE_DEALS(ARec_Interface, validation_error);
2891
2892 if (validation_error <> TRUE) then
2893
2894 --------------------------------------------------------------------------------------
2895 -- If limit code is not null, then this would have been calculated in VALIDATE_DEALS.
2896 --------------------------------------------------------------------------------------
2897 if ARec_Interface.limit_code is null and
2898 ARec_Interface.limit_code_b is null then
2899 CALCULATE_VALUES(ARec_Interface, l_dummy);
2900 end if;
2901
2902 ------------------------------------------------------------------
2906 l_deal_subtype := 'INVEST';
2903 --* Perform limit checks
2904 ------------------------------------------------------------------
2905 if G_Ig_Main_Rec.Principal_Action = 'PAY' then
2907 l_mirror_deal_subtype := 'FUND';
2908 else
2909 l_deal_subtype := 'FUND';
2910 l_mirror_deal_subtype := 'INVEST';
2911 end if;
2912
2913 --RV 2229236: Using function GET_LIMIT_AMOUNT to get the total amount for limit check
2914
2915 G_Ig_bal_out := GET_LIMIT_AMOUNT(ARec_Interface.company_code,
2916 ARec_Interface.cparty_code,
2917 ARec_Interface.currency_a,
2918 G_Ig_Main_Rec.PRINCIPAL_ADJUST,
2919 G_Ig_Main_Rec.Principal_Action);
2920
2921
2922 -----------------------------------------------------------------------------
2923 -- Should be based on the total balance, not the current transaction balance.
2924 -----------------------------------------------------------------------------
2925 if nvl(G_Ig_bal_out,0) < 0 then
2926 l_limit_code := G_Ig_Main_Rec.limit_code;
2927 l_mirror_limit_code := ARec_Interface.mirror_limit_code_invest;
2928 else
2929 l_limit_code := G_Ig_Main_Rec.limit_code_invest;
2930 l_mirror_limit_code := ARec_Interface.mirror_limit_code_fund;
2931 end if;
2932
2933 if G_Ig_Source is null then --rvallams 2229236
2934 v_limit_log_return := xtr_limits_p.log_full_limits_check (null,
2935 G_Ig_Main_Rec.TRANSACTION_NUMBER,
2936 G_Ig_Main_Rec.COMPANY_CODE,
2937 G_Ig_Main_Rec.DEAL_TYPE,
2938 l_deal_subtype,
2939 G_Ig_Main_Rec.PARTY_CODE,
2940 G_Ig_Main_Rec.PRODUCT_TYPE,
2941 l_limit_code, -- G_Ig_Main_Rec.LIMIT_CODE,
2942 G_Ig_Main_Rec.PARTY_CODE, -- LIMIT_PARTY
2943 G_Ig_Main_Rec.TRANSFER_DATE, -- AMOUNT_DATE
2944 abs(G_Ig_bal_out), -- G_Ig_Main_Rec.PRINCIPAL_ADJUST,
2945 G_Ig_user,
2946 G_Ig_Main_Rec.CURRENCY);
2947 If Nvl(ARec_Interface.override_limit,'N') = 'N' and v_limit_log_return <> 0 then
2948 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
2949 'OverrideLimit','XTR_LIMIT_EXCEEDED');
2950 limit_error := TRUE;
2951 elsif Nvl(ARec_Interface.override_limit,'N') not in ('N', 'Y') then -- 3800146
2952 limit_error := TRUE;
2953 else
2954 limit_error := FALSE;
2955
2956 end if; /* If Limit needs to be checked */
2957
2958 /*****************************************************************************************************************/
2959 /* 3800146 Settlement Limits **********************************************************************************/
2960 /*****************************************************************************************************************/
2961 if nvl(G_Ig_Settlement_flag,'N') = 'Y' and limit_error <> TRUE then
2962 -------------------------------------------------------------------------------------------------
2963 -- NOTE: Cashflow can be +/- depending on action. Please refer to INS_DEAL_DATE_AMTS procedure.
2964 -------------------------------------------------------------------------------------------------
2965 if G_Ig_Main_Rec.PRINCIPAL_ACTION = 'PAY' then
2966 l_dummy_num := -1;
2967 else
2968 l_dummy_num := 1;
2969 end if;
2970 G_Main_log_id := XTR_limits_P.LOG_FULL_LIMITS_CHECK (null,
2971 G_Ig_Main_Rec.TRANSACTION_NUMBER,
2972 '@'||G_Ig_Main_Rec.COMPANY_CODE, -- @ calling from settlements
2973 G_Ig_Main_Rec.DEAL_TYPE,
2974 l_deal_subtype,
2978 G_Ig_Main_Rec.PARTY_CODE,
2975 G_Ig_Main_Rec.PARTY_CODE,
2976 G_Ig_Main_Rec.PRODUCT_TYPE,
2977 l_limit_code,
2979 G_Ig_curr_date,
2980 l_dummy_num*G_Ig_Main_Rec.PRINCIPAL_ADJUST, --cashflow/2428516
2981 G_Ig_user,
2982 G_Ig_Main_Rec.CURRENCY);
2983
2984 If Nvl(ARec_Interface.override_limit,'N') = 'N' and G_Main_log_id <> 0 then
2985 Log_IG_Errors(ARec_Interface.external_deal_id, ARec_Interface.deal_type,
2986 'OverrideLimit','XTR_LIMIT_EXCEEDED');
2987 XTR_limits_P.maintain_excess_log(G_Main_log_id,'D',null);
2988 limit_error := TRUE;
2989 elsif Nvl(ARec_Interface.override_limit,'N') not in ('N', 'Y') then -- 3800146
2990 limit_error := TRUE;
2991 else
2992 limit_error := FALSE;
2993 end if;
2994
2995 end if;
2996 /*****************************************************************************************************************/
2997
2998 end if; --rvallams 2229236
2999
3000 /*-----------------------------------------------------------------------------------------------------*/
3001 --rvallams 2229236
3002
3003 if G_Ig_Source is null and is_company(ARec_Interface.cparty_code) and
3004 limit_error <> TRUE then
3005 v_mirror_limit_log_return := xtr_limits_p.log_full_limits_check (null,
3006 null,-- G_Ig_Main_Rec.TRANSACTION_NUMBER,
3007 G_Ig_Main_Rec.PARTY_CODE,
3008 G_Ig_Main_Rec.DEAL_TYPE,
3009 l_mirror_deal_subtype,
3010 G_Ig_Main_Rec.COMPANY_CODE,
3011 ARec_Interface.MIRROR_PRODUCT_TYPE,
3012 l_mirror_limit_code, -- G_Ig_Main_Rec.LIMIT_CODE,
3013 G_Ig_Main_Rec.COMPANY_CODE, -- LIMIT_PARTY
3014 G_Ig_Main_Rec.TRANSFER_DATE, -- AMOUNT_DATE
3015 abs(G_Ig_bal_out), -- G_Ig_Main_Rec.PRINCIPAL_ADJUST,
3016 G_Ig_user,
3017 G_Ig_Main_Rec.CURRENCY);
3018
3019 If Nvl(ARec_Interface.override_limit,'N') = 'N' and v_mirror_limit_log_return <> 0 then
3020 Log_IG_Errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,
3021 'OverrideLimit','XTR_LIMIT_EXCEEDED');
3022 limit_error := TRUE;
3023 elsif Nvl(ARec_Interface.override_limit,'N') not in ('N', 'Y') then -- 3800146
3024 limit_error := TRUE;
3025 else
3026 limit_error := FALSE;
3027 end if; /* If Limit needs to be checked */
3028
3029 /*****************************************************************************************************************/
3030 /* 3800146 Settlement Limits **********************************************************************************/
3031 /*****************************************************************************************************************/
3032 if nvl(G_Ig_Settlement_flag,'N') = 'Y' and limit_error <> TRUE then
3033 ----------------------------------------------------------------------------------------------------------------
3034 -- NOTE: G_Ig_Main_Rec.PRINCIPAL_ACTION is the action of main deal, action will be reversed for mirror deal.
3035 ----------------------------------------------------------------------------------------------------------------
3036 if G_Ig_Main_Rec.PRINCIPAL_ACTION = 'PAY' then
3037 l_dummy_num := 1;
3038 else
3039 l_dummy_num := -1;
3040 end if;
3041 G_Mirror_log_id := XTR_limits_P.LOG_FULL_LIMITS_CHECK(null,
3042 null,
3043 '@'||G_Ig_Main_Rec.PARTY_CODE, -- @ call from settlements
3044 G_Ig_Main_Rec.DEAL_TYPE,
3045 l_mirror_deal_subtype,
3046 G_Ig_Main_Rec.COMPANY_CODE,
3047 ARec_Interface.MIRROR_PRODUCT_TYPE,
3048 l_mirror_limit_code,
3049 G_Ig_Main_Rec.COMPANY_CODE,
3050 G_Ig_curr_date,
3054
3051 l_dummy_num*G_Ig_Main_Rec.PRINCIPAL_ADJUST,--cashflow/2428516
3052 G_Ig_user,
3053 G_Ig_Main_Rec.CURRENCY);
3055 If Nvl(ARec_Interface.override_limit,'N') = 'N' and G_Mirror_log_id <> 0 then
3056 Log_IG_Errors(ARec_Interface.external_deal_id, ARec_Interface.deal_type,
3057 'OverrideLimit','XTR_LIMIT_EXCEEDED');
3058 XTR_limits_P.maintain_excess_log(G_Mirror_log_id,'D',null);
3059 limit_error := TRUE;
3060 elsif Nvl(ARec_Interface.override_limit,'N') not in ('N', 'Y') then -- 3800146
3061 limit_error := TRUE;
3062 else
3063 limit_error := FALSE;
3064 end if;
3065
3066 end if;
3067 /*****************************************************************************************************************/
3068
3069 end if; --rvallams 2229236
3070 /*-------------------------------------------------------------------------------------------------------*/
3071
3072 end if; /* Validating various fields */
3073
3074 end if; /* Checking Mandatory values */
3075
3076 end if; /* Checking User Auth */
3077
3078 /*----------------------------------------------------------------------------------------------*/
3079 /* If the process passed all the previous validation, it would be considered a valid deal entry */
3080 /*----------------------------------------------------------------------------------------------*/
3081 if user_error <> TRUE and mandatory_error <> TRUE and
3082 limit_error <> TRUE and validation_error <> TRUE then
3083
3084 if G_Ig_Source is null and is_company(ARec_Interface.cparty_code) then
3085 if G_ig_action = 'PAY' then
3086 l_mirror_ig_action := 'REC';
3087 else
3088 l_mirror_ig_action := 'PAY';
3089 end if;
3090
3091 if is_mirror_deal(G_Ig_Main_Rec.company_code,
3092 G_Ig_Main_Rec.party_code,
3093 G_Ig_Main_Rec.currency) then
3094 G_Ig_Main_Rec.mirror_deal := 'Y';
3095 else
3096 G_Ig_Main_Rec.mirror_deal := null;
3097 end if;
3098
3099 G_Ig_Mirror_Deal := G_Ig_Main_Rec.mirror_deal; --RV BUG 2293339
3100
3101 -- ****************************************************************************************************************
3102 -- 3800146 Do not check for ZBA and CL. Done in wrapper ********************************************************
3103 -- ****************************************************************************************************************
3104 duplicate_error := FALSE; -- 3800146 initialise
3105
3106 if nvl(G_Ig_External_Source,'@@@') not in (C_ZBA,C_CL) then
3107 CHECK_MIRROR_DUPLICATE(G_Ig_Main_Rec.party_code,
3108 G_Ig_Main_Rec.company_code,
3109 G_Ig_Main_Rec.currency,
3110 G_Ig_Main_Rec.transfer_date,
3111 l_mirror_ig_action,
3112 G_Ig_Main_Rec.principal_adjust,
3113 G_Ig_Main_Rec.party_account_no,
3114 G_Ig_Main_Rec.company_account_no,
3115 duplicate_error);
3116 end if;
3117
3118 end if;
3119
3120 if duplicate_error <> TRUE then
3121 GET_DEAL_TRAN_NUMBERS(G_Ig_Main_Rec.company_code,
3122 G_Ig_Main_Rec.party_code,
3123 G_Ig_Main_Rec.currency,
3124 G_Ig_Main_Rec.deal_number,
3125 G_Ig_Main_Rec.transaction_number,
3126 'Y'); -- generate a new deal number
3127
3128
3129
3130 XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(G_Ig_Main_Rec.Deal_number,
3131 G_Ig_Main_Rec.Transaction_number,
3132 G_Ig_user,
3133 v_limit_log_return);
3134
3135 --**********************************************************************************************************
3136 -- 3800146 return transaction number also to ZBA/CL *******************************************************
3137 -- *********************************************************************************************************
3138 deal_num:=G_Ig_Main_Rec.Deal_number;
3139 tran_num:=G_Ig_Main_Rec.Transaction_number;
3140 --**********************************************************************************************************
3141
3142 /*-------------------------------------------------------------------*/
3143 /* Call the insert procedure to insert into xtr_intergroup_transfers */
3144 /*-------------------------------------------------------------------*/
3145
3146 CREATE_IG_DEAL(G_Ig_Main_Rec);
3147
3148 /*-----------------------------------------*/
3149 /* Create journal structures: RV 2229236 */
3150 /*-----------------------------------------*/
3151
3152 INS_IG_JRNL_STRUC(G_Ig_Main_Rec.company_code,
3153 G_Ig_Main_Rec.party_code,
3154 G_Ig_Main_Rec.currency,
3155 G_Ig_Main_Rec.party_account_no);
3156
3160 INS_DEAL_DATE_AMTS;
3157 /*--------------------------------*/
3158 /* Create DDA rows for new deal. */
3159 /*--------------------------------*/
3161
3162 /*-------------------------------------------------*/
3163 /* Update balance_out of subsequent transactions. */
3164 /*-------------------------------------------------*/
3165 CASCADE_RECALC(G_Ig_Main_Rec.company_code,
3166 G_Ig_Main_Rec.party_code,
3167 G_Ig_Main_Rec.currency,
3168 G_Ig_Main_Rec.transfer_date,
3169 G_Ig_Main_Rec.limit_code,
3170 G_Ig_Main_Rec.limit_code_invest,
3171 'Y',
3172 G_Ig_Main_Rec.Rounding_Type,
3173 G_Ig_Main_Rec.Day_Count_Type);
3174 -- l_dummy);
3175
3176 -----------------------------------------------------------------------------------------
3177 -----------------------------------------------------------------------------------------
3178 if G_Ig_Source is null and is_company(ARec_Interface.cparty_code) then
3179
3180 GET_DEAL_TRAN_NUMBERS(G_Ig_Main_Rec.party_code,
3181 G_Ig_Main_Rec.company_code,
3182 G_Ig_Main_Rec.currency,
3183 G_Ig_Mirror_Rec.deal_number,
3184 G_Ig_Mirror_Rec.transaction_number,
3185 'Y');
3186
3187 XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(G_Ig_Mirror_Rec.Deal_number,
3188 G_Ig_Mirror_Rec.Transaction_number,
3189 G_Ig_user,
3190 v_mirror_limit_log_return);
3191
3192
3193 if G_Ig_Main_Rec.mirror_deal is not null and G_Ig_Main_Rec.mirror_deal = 'Y' then
3194 G_Ig_mirror_Rec.mirror_deal := null;
3195 else
3196 G_Ig_mirror_Rec.mirror_deal := 'Y';
3197 end if;
3198
3199 G_Ig_Mirror_Rec.ACCUM_INTEREST_BF := -G_Ig_Main_Rec.ACCUM_INTEREST_BF ;
3200 G_Ig_Mirror_Rec.ACCUM_INTEREST_BF_HCE := -G_Ig_Main_Rec.ACCUM_INTEREST_BF_HCE ;
3201 G_Ig_Mirror_Rec.BALANCE_BF := -G_Ig_Main_Rec.BALANCE_BF ;
3202 G_Ig_Mirror_Rec.BALANCE_BF_HCE := -G_Ig_Main_Rec.BALANCE_BF_HCE ;
3203 G_Ig_Mirror_Rec.BALANCE_OUT := -G_Ig_Main_Rec.BALANCE_OUT ;
3204 G_Ig_Mirror_Rec.BALANCE_OUT_HCE := -G_Ig_Main_Rec.BALANCE_OUT_HCE ;
3205 G_Ig_Mirror_Rec.COMMENTS := null;
3206 G_Ig_Mirror_Rec.COMPANY_ACCOUNT_NO := G_Ig_Main_Rec.PARTY_ACCOUNT_NO;
3207 G_Ig_Mirror_Rec.COMPANY_CODE := G_Ig_Main_Rec.PARTY_CODE;
3208 G_Ig_Mirror_Rec.CREATED_BY := G_Ig_Main_Rec.CREATED_BY;
3209 G_Ig_Mirror_Rec.CREATED_ON := G_Ig_Main_Rec.CREATED_ON;
3210 G_Ig_Mirror_Rec.CURRENCY := G_Ig_Main_Rec.CURRENCY;
3211 G_Ig_Mirror_Rec.DEAL_NUMBER := G_Ig_Mirror_Rec.deal_number ;
3212 G_Ig_Mirror_Rec.DEAL_TYPE := G_Ig_Main_Rec.DEAL_TYPE;
3213 G_Ig_Mirror_Rec.INTEREST := -G_Ig_Main_Rec.INTEREST ;
3214 G_Ig_Mirror_Rec.INTEREST_HCE := -G_Ig_Main_Rec.INTEREST_HCE ;
3215 G_Ig_Mirror_Rec.INTEREST_RATE := G_Ig_Main_Rec.INTEREST_RATE ;
3216 G_Ig_Mirror_Rec.INTEREST_SETTLED := -G_Ig_Main_Rec.INTEREST_SETTLED ;
3217 G_Ig_Mirror_Rec.INTEREST_SETTLED_HCE := -G_Ig_Main_Rec.INTEREST_SETTLED_HCE ;
3218 G_Ig_Mirror_Rec.LIMIT_CODE := ARec_Interface.MIRROR_LIMIT_CODE_FUND;
3219 G_Ig_Mirror_Rec.LIMIT_CODE_INVEST := ARec_Interface.MIRROR_LIMIT_CODE_INVEST;
3220 G_Ig_Mirror_Rec.NO_OF_DAYS := G_Ig_Main_Rec.NO_OF_DAYS ;
3221 G_Ig_Mirror_Rec.PARTY_ACCOUNT_NO := G_Ig_Main_Rec.COMPANY_ACCOUNT_NO;
3222 G_Ig_Mirror_Rec.PARTY_CODE := G_Ig_Main_Rec.COMPANY_CODE;
3223 G_Ig_Mirror_Rec.PORTFOLIO := ARec_Interface.MIRROR_PORTFOLIO_CODE;
3224 G_Ig_Mirror_Rec.PRINCIPAL_ACTION := l_mirror_ig_action;
3225 G_Ig_Mirror_Rec.PRINCIPAL_ADJUST := G_Ig_Main_Rec.PRINCIPAL_ADJUST ;
3226 G_Ig_Mirror_Rec.PRINCIPAL_ADJUST_HCE := G_Ig_Main_Rec.PRINCIPAL_ADJUST_HCE ;
3227 G_Ig_Mirror_Rec.PRODUCT_TYPE := ARec_Interface.MIRROR_PRODUCT_TYPE;
3228 G_Ig_Mirror_Rec.PRICING_MODEL := ARec_Interface.MIRROR_PRICING_MODEL;
3229 --Bug 2994712
3230 G_Ig_Mirror_Rec.DEAL_LINKING_CODE := ARec_Interface.MIRROR_DEAL_LINKING_CODE;
3231 --Bug 2994712
3232 --Bug 2684411
3233 G_Ig_Mirror_Rec.DEALER_CODE := ARec_Interface.MIRROR_DEALER_CODE;
3234 --Bug 2684411
3235 G_Ig_Mirror_Rec.SETTLE_DATE := G_Ig_Main_Rec.SETTLE_DATE;
3236 G_Ig_Mirror_Rec.TRANSACTION_NUMBER := G_Ig_Mirror_Rec.transaction_number ;
3237 G_Ig_Mirror_Rec.TRANSFER_DATE := G_Ig_Main_Rec.TRANSFER_DATE;
3238 G_Ig_Mirror_Rec.UPDATED_BY := G_Ig_Main_Rec.UPDATED_BY;
3239 G_Ig_Mirror_Rec.UPDATED_ON := G_Ig_Main_Rec.UPDATED_ON;
3240 G_Ig_Mirror_Rec.ACCRUAL_INTEREST := -G_Ig_Main_Rec.ACCRUAL_INTEREST ;
3241 G_Ig_Mirror_Rec.FIRST_BATCH_ID := null ;
3242 G_Ig_Mirror_Rec.LAST_BATCH_ID := null ;
3243 G_Ig_Mirror_Rec.ATTRIBUTE_CATEGORY := null;
3244 G_Ig_Mirror_Rec.ATTRIBUTE1 := null;
3245 G_Ig_Mirror_Rec.ATTRIBUTE2 := null;
3246 G_Ig_Mirror_Rec.ATTRIBUTE3 := null;
3247 G_Ig_Mirror_Rec.ATTRIBUTE4 := null;
3248 G_Ig_Mirror_Rec.ATTRIBUTE5 := null;
3252 G_Ig_Mirror_Rec.ATTRIBUTE9 := null;
3249 G_Ig_Mirror_Rec.ATTRIBUTE6 := null;
3250 G_Ig_Mirror_Rec.ATTRIBUTE7 := null;
3251 G_Ig_Mirror_Rec.ATTRIBUTE8 := null;
3253 G_Ig_Mirror_Rec.ATTRIBUTE10 := null;
3254 G_Ig_Mirror_Rec.ATTRIBUTE11 := null;
3255 G_Ig_Mirror_Rec.ATTRIBUTE12 := null;
3256 G_Ig_Mirror_Rec.ATTRIBUTE13 := null;
3257 G_Ig_Mirror_Rec.ATTRIBUTE14 := null;
3258 G_Ig_Mirror_Rec.ATTRIBUTE15 := null;
3259 G_Ig_Mirror_Rec.EXTERNAL_DEAL_ID := G_Ig_Main_Rec.EXTERNAL_DEAL_ID||'_M';
3260 G_Ig_Mirror_Rec.REQUEST_ID := G_Ig_Main_Rec.REQUEST_ID ;
3261 G_Ig_Mirror_Rec.PROGRAM_APPLICATION_ID := G_Ig_Main_Rec.PROGRAM_APPLICATION_ID ;
3262 G_Ig_Mirror_Rec.PROGRAM_ID := G_Ig_Main_Rec.PROGRAM_ID ;
3263 G_Ig_Mirror_Rec.PROGRAM_UPDATE_DATE := G_Ig_Main_Rec.PROGRAM_UPDATE_DATE;
3264 G_Ig_Mirror_Deal := G_Ig_mirror_Rec.mirror_deal;
3265 G_Ig_Orig_Deal_No := G_Ig_Main_Rec.deal_number;
3266 G_Ig_Orig_Trans_No := G_Ig_Main_Rec.transaction_number;
3267 G_Ig_Mirror_Rec.ROUNDING_TYPE := G_Ig_Main_Rec.ROUNDING_TYPE ;
3268 G_Ig_Mirror_Rec.DAY_COUNT_TYPE := G_Ig_Main_Rec.DAY_COUNT_TYPE ;
3269 G_Ig_Mirror_Rec.ORIGINAL_AMOUNT := -G_Ig_Main_Rec.ORIGINAL_AMOUNT ;
3270 G_Ig_Mirror_Rec.External_Source := G_Ig_Main_Rec.External_Source; -- Bug 3800146 ************************************************
3271
3272 --###########################################################################################################################
3273 -- 3800146 Do not default for ZBA/CL
3274 --###########################################################################################################################
3275 if G_Ig_Mirror_Rec.PRICING_MODEL is NULL and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA,C_CL) then
3276 DEFAULT_PRICING_MODEL(G_Ig_Mirror_Rec.COMPANY_CODE,
3277 G_Ig_Mirror_Rec.PARTY_CODE,
3278 G_Ig_Mirror_Rec.CURRENCY,
3279 G_Ig_Mirror_Rec.PRODUCT_TYPE,
3280 G_Ig_Mirror_Rec.PRICING_MODEL);
3281 end if;
3282
3283 update xtr_intergroup_transfers
3284 set mirror_deal_number = G_Ig_Mirror_Rec.deal_number,
3285 mirror_transaction_number = G_Ig_Mirror_Rec.transaction_number
3286 where deal_number = G_Ig_Main_Rec.deal_number
3287 and transaction_number = G_Ig_Main_Rec.transaction_number;
3288
3289 --**********************************************************************************************************
3290 -- 3800146 return to ZBA/CL
3291 -- *********************************************************************************************************
3292 mirror_deal_num := G_Ig_Mirror_Rec.deal_number;
3293 mirror_tran_num := G_Ig_Mirror_Rec.transaction_number;
3294 --**********************************************************************************************************
3295
3296 CREATE_IG_DEAL(G_Ig_Mirror_Rec);
3297
3298 /*-----------------------------------------*/
3299 /* Create journal structures: RV 2229236 */
3300 /*-----------------------------------------*/
3301 INS_IG_JRNL_STRUC(G_Ig_Mirror_Rec.company_code,
3302 G_Ig_Mirror_Rec.party_code,
3303 G_Ig_Mirror_Rec.currency,
3304 G_Ig_Mirror_Rec.party_account_no);
3305
3306 INS_MIRROR_DEAL_DATE_AMTS;
3307 CASCADE_RECALC(G_Ig_Mirror_Rec.company_code,
3308 G_Ig_Mirror_Rec.party_code,
3309 G_Ig_Mirror_Rec.currency,
3310 G_Ig_Mirror_Rec.transfer_date,
3311 G_Ig_Mirror_Rec.limit_code,
3312 G_Ig_Mirror_Rec.limit_code_invest,
3313 'Y');
3314 end if;
3315 -------------------------------------------------------------------------------------------------------------
3316 -------------------------------------------------------------------------------------------------------------
3317
3318
3319 /*---------------------------------------------------------------------------------*/
3320 /* Since the insert is done, we can now delete the rows from the interface table. */
3321 /*---------------------------------------------------------------------------------*/
3322 --******************************************************************************************************
3326 delete from xtr_deals_interface
3323 -- 3800146 Not necessary for ZBA/CL *****************************************************************
3324 --******************************************************************************************************
3325 if G_Ig_Source is null and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA,C_CL) then
3327 where external_deal_id = ARec_Interface.external_deal_id
3328 and deal_type = ARec_Interface.deal_type;
3329 end if;
3330
3331 else /* if mirror duplicate error */
3332 --******************************************************************************************************
3333 -- 3800146 Not necessary for ZBA/CL *****************************************************************
3334 --******************************************************************************************************
3335 if G_Ig_Source is null and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA,C_CL) then
3336 update xtr_deals_interface
3337 set load_status_code = 'DUPLICATE_DEAL_ID',
3338 last_update_date = G_Ig_SysDate,
3339 last_Updated_by = G_Ig_user_id
3340 where external_deal_id = ARec_Interface.external_deal_id
3341 and deal_type = ARec_Interface.deal_type;
3342
3343 log_ig_errors(ARec_Interface.external_deal_id,ARec_Interface.deal_type,null,'XTR_MIRROR_DUPLICATE_ERROR');
3344 validation_error := true;
3345 end if;
3346
3347 end if;
3348 else /* if any other errors */
3349
3350 /*---------------------------------------------*/
3351 /* Deal interface has error. Do not import. */
3352 /*---------------------------------------------*/
3353 --******************************************************************************************************
3354 -- 3800146 Not necessary for ZBA/CL *****************************************************************
3355 --******************************************************************************************************
3356 if G_Ig_Source is null and nvl(G_Ig_External_Source,'@@@') not in (C_ZBA,C_CL) then
3357 update xtr_deals_interface
3358 set load_status_code = 'ERROR',
3359 last_update_date = G_Ig_SysDate,
3360 last_Updated_by = G_Ig_user_id
3361 where external_deal_id = ARec_Interface.external_deal_id
3362 and deal_type = ARec_Interface.deal_type;
3363
3364 end if;
3365
3366 end if;
3367
3368 end; /* TRANSFER_IG_DEALS */
3369
3370
3371 END;
3372