[Home] [Help]
PACKAGE BODY: APPS.XTR_WRAPPER_API_P
Source
1 Package body XTR_WRAPPER_API_P as
2 /* $Header: xtrwrapb.pls 120.21 2011/09/16 10:26:13 rtumati ship $ */
3
4 --Local procedure for doing Reval/Accrual related Validations.
5
6 Procedure bank_balance_validate(P_COMPANY_CODE IN VARCHAR2,
7 P_ACCOUNT_NUMBER IN VARCHAR2,
8 P_CURRENCY_CODE IN VARCHAR2,
9 P_BALANCE_DATE IN DATE,
10 P_EVENT_CODE IN VARCHAR2,
11 P_ACTION IN VARCHAR2,
12 P_RESULT OUT NOCOPY VARCHAR2,
13 P_ERROR_MSG OUT NOCOPY VARCHAR2);
14
15 -- Package that is called by CE for Reconciliation
16
17 Procedure Xtr_Wrapper_API(P_XTR_Procedure_Code IN VARCHAR2,
18 P_Settlement_Summary_ID IN NUMBER,
19 P_Task IN VARCHAR2,
20 P_Reconciled_Method IN CHAR,
21 P_Org_ID IN NUMBER,
22 P_ce_bank_account_id IN NUMBER,
23 P_currency_Code IN VARCHAR2,
24 P_Sec_Bank_Account_ID IN NUMBER,
25 P_Trans_Amount IN NUMBER,
26 P_Balance_Date IN DATE,
27 P_Balance_Amount_A IN NUMBER,
28 P_Balance_Amount_B IN NUMBER,
29 P_Balance_Amount_C IN NUMBER,
30 P_One_Day_Float IN NUMBER,
31 P_Two_Day_Float IN NUMBER,
32 P_Result OUT NOCOPY VARCHAR2,
33 P_error_Msg OUT NOCOPY VARCHAR2) is
34 BEGIN
35 /* This procedure is called by CE before or after reconciliation */
36 IF (P_XTR_Procedure_Code = 1) THEN
37 Bank_Account_Verification(P_org_ID,
38 P_ce_bank_account_id,
39 P_Currency_Code,
40 P_Result,
41 P_Error_Msg);
42 ELSIF (P_XTR_Procedure_Code = 2) THEN
43 Reconciliation(P_Settlement_Summary_ID,
44 P_Task,
45 P_Reconciled_Method,
46 P_Result,null,null);
47 ELSIF (P_XTR_Procedure_Code = 3) THEN
48 IF (P_Balance_Amount_A IS NOT NULL) THEN
49 Bank_Balance_Upload(P_Org_ID,
50 P_ce_bank_account_id,
51 P_Currency_Code,
52 P_Balance_Date,
53 P_Balance_Amount_A,
54 P_Balance_Amount_B,
55 P_Balance_Amount_C,
56 P_One_Day_Float,
57 P_Two_Day_Float,
58 P_Result,
59 P_error_Msg);
60 END IF;
61 ELSE
62 p_result := 'XTR5_FAIL';
63 END IF;
64 END Xtr_Wrapper_API;
65
66 Procedure Reconciliation(P_Settlement_Summary_ID IN Number,
67 P_Task IN Varchar2,
68 P_Reconciled_Method IN Char,
69 P_Result OUT NOCOPY Varchar2,P_RECON_AMT IN NUMBER,
70 P_VAL_DATE IN DATE ) is
71 v_settlement_number number;
72 v_net_Id number;
73 v_reconciled_reference number;
74 l_adjust number;
75 l_dda_amount number;
76 u_deal_number Number;
77 u_transaction_number Number;
78 u_date_type XTR_DEAL_DATE_AMOUNTS.DATE_TYPE%TYPE;
79 u_amount_type XTR_DEAL_DATE_AMOUNTS.AMOUNT_TYPE%TYPE;
80 u_amount_date XTR_DEAL_DATE_AMOUNTS.AMOUNT_DATE%TYPE;
81 u_currency XTR_DEAL_DATE_AMOUNTS.CURRENCY%TYPE;
82 u_account_number XTR_DEAL_DATE_AMOUNTS.ACCOUNT_NO%TYPE;
83 u_Settlement_Number XTR_DEAL_DATE_AMOUNTS.SETTLEMENT_NUMBER%TYPE;
84 l_amount NUMBER;
85 l_hce_amt NUMBER;
86 l_test_number NUMBER;
87 l_cashflow_amt NUMBER;
88 l_deal_type XTR_DEAL_DATE_AMOUNTS.DEAL_TYPE%TYPE;
89
90 Cursor C1 is
91 Select *
92 From Xtr_Deal_Date_Amounts
93 Where settlement_number in
94 (Select settlement_number
95 From Xtr_Settlement_Summary
96 Where net_ID = p_settlement_summary_ID)
97 For update of reconciled_reference;
98
99
100 cursor pop_dda_var is
101 select deal_number,transaction_number,date_type,amount_type,amount_date,currency,account_no,settlement_number
102 from xtr_deal_date_amounts Where settlement_number in
103 (Select settlement_number
104 From Xtr_Settlement_Summary
105 Where settlement_summary_id = p_settlement_summary_ID);
106
107
108 cursor DDA (P_DEAL_NO number, P_TRANS_NO number, P_DATE_TYPE varchar2,
109 P_AMOUNT_TYPE varchar2, P_AMOUNT_DATE date, P_CURR varchar2,
110 P_ACCOUNT_NO varchar2, P_SETTLEMENT_NUMBER number) is
111 select rowid,
112 RECONCILED_REFERENCE,
113 RECONCILED_PASS_CODE,
114 RECONCILED_DAYS_ADJUST,
115 AMOUNT_DATE,
116 DATE_TYPE,
117 AMOUNT,
118 HCE_AMOUNT,
119 CASHFLOW_AMOUNT,
120 /******** code below added by Ilavenil for CE Reconciliation project *******/
121 SETTLEMENT_NUMBER,
122 NETOFF_NUMBER
123 from XTR_DEAL_DATE_AMOUNTS_V
124 where DEAL_NUMBER = p_deal_no
125 and TRANSACTION_NUMBER = p_trans_no
126 and DATE_TYPE = p_date_type
127 and AMOUNT_TYPE = p_amount_type
128 and AMOUNT_DATE = p_amount_date
129 and CURRENCY = p_curr
130 and ACCOUNT_NO = p_account_no
131 /**** AND clause below added by Ilavenil for CE Recon project ***/
132 and SETTLEMENT_NUMBER = nvl(p_settlement_number, settlement_number)
133 and STATUS_CODE <> 'CANCELLED'
134 /******/
135 for update of reconciled_reference, reconciled_pass_code, reconciled_days_adjust,
136 amount_date, date_type, amount, hce_amount, cashflow_amount;
137 --
138 dda_row dda%rowtype;
139
140 Begin
141
142 /* After CE is done with Reconciliation this procedure updates the necessary records in XTR tables to denote the completion
143 of Reconciliation */
144
145 If p_task = 'REC' then
146
147 -- ER 7601596 Start
148
149 BEGIN --12733323
150 select deal_type into l_deal_type from xtr_deal_date_amounts where settlement_number in
151 (Select settlement_number
152 From Xtr_Settlement_Summary
153 Where settlement_summary_id = p_settlement_summary_ID);
154 EXCEPTION
155 WHEN No_Data_Found THEN
156 l_deal_type:='X';
157 END;
158
159
160
161 if l_deal_type ='RTMM' then
162
163 /* Calculate Adjustments */
164 select amount into l_dda_amount from xtr_deal_date_amounts where settlement_number in
165 (Select settlement_number
166 From Xtr_Settlement_Summary
167 Where settlement_summary_id = p_settlement_summary_ID);
168
169 l_adjust := nvl(abs(P_RECON_AMT),0) - nvl(abs(l_dda_amount),0);
170
171
172 /* Update DDA */
173
174 Open pop_dda_var ;
175
176
177 fetch pop_dda_var into u_deal_number, u_transaction_number, u_date_type,
178 u_amount_type,u_amount_date,u_currency,u_account_number,u_Settlement_Number;
179
180 close pop_dda_var ;
181
182 Open DDA (u_deal_number, u_transaction_number, u_date_type,
183 u_amount_type,u_amount_date,u_currency,u_account_number,u_Settlement_Number);
184
185 Fetch DDA into dda_row;
186
187 While DDA%FOUND loop
188
189 l_amount := nvl(DDA_ROW.amount,0) + nvl(l_adjust,0);
190
191
192 if nvl(DDA_ROW.hce_amount, 0) <> 0 then
193 l_hce_amt := nvl(DDA_ROW.hce_amount,0) + nvl(l_adjust,0) *
194 (DDA_ROW.amount / DDA_ROW.hce_amount);
195 else
196 l_hce_amt := 0;
197 end if;
198
199 If (DDA_ROW.cashflow_amount <= 0) then
200 l_cashflow_amt := nvl(DDA_ROW.cashflow_amount,0) - l_adjust;
201 Else
202 l_cashflow_amt := nvl(DDA_ROW.cashflow_amount,0) + l_adjust;
203 End If;
204
205 Select Xtr_Deal_Date_Amounts_S.Nextval
206 Into v_reconciled_reference
207 From dual;
208
209
210 update XTR_DEAL_DATE_AMOUNTS_V
211 set RECONCILED_REFERENCE = v_reconciled_reference,
212 RECONCILED_PASS_CODE = decode(DATE_TYPE,'FORCAST','^'||'M','M'),
213 RECONCILED_DAYS_ADJUST = (nvl(P_VAL_DATE,SYSDATE) - U_AMOUNT_DATE),
214 AMOUNT_DATE = decode(DATE_TYPE,'FORCAST',nvl(P_VAL_DATE,SYSDATE),AMOUNT_DATE),
215 DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
216 AMOUNT = decode(DATE_TYPE,'FORCAST',l_amount,AMOUNT),
217 HCE_AMOUNT = decode(DATE_TYPE,'FORCAST',l_hce_amt,HCE_AMOUNT),
218 CASHFLOW_AMOUNT = decode(DATE_TYPE,'FORCAST',l_cashflow_amt,CASHFLOW_AMOUNT)
219 where rowid = DDA_ROW.rowid;
220
221 Update Xtr_Settlement_Summary
222 Set status = 'R'
223 Where settlement_number = DDA_ROW.Settlement_Number;
224
225 exit;
226
227 END LOOP;
228
229 Close DDA;
230 COMMIT;
231
232
233
234
235
236 XTR_AUTO_RECONCILIATION.UPDATE_ROLL_TRANS_RTMM(
237 null,
238 v_reconciled_reference,
239 v_reconciled_reference,
240 'MANUAL',P_VAL_DATE);
241 commit;
242 -- ER 7601596 End
243 else
244
245 /* This is to update Xtr_Settlement_Summary about the successful completion of Reconciliation */
246 Update xtr_settlement_summary
247 set status = 'R'
248 Where settlement_summary_id = p_settlement_summary_ID;
249
250 If SQL%Found then
251 P_Result := 'XTR2_SUCCESS';
252 Else
253 P_Result := 'XTR2_FAIL';
254 End if;
255
256 Select settlement_number
257 Into v_settlement_number
258 From Xtr_Settlement_Summary
259 Where settlement_summary_id = p_settlement_summary_ID;
260
261 Select Xtr_Deal_Date_Amounts_S.Nextval
262 Into v_reconciled_reference
263 From dual;
264
265 /* This is to update DDA about the successful completion of Reconciliation */
266 Update Xtr_Deal_Date_Amounts
267 Set Reconciled_Reference = v_reconciled_reference,
268 Reconciled_Pass_Code = p_reconciled_method
269 Where settlement_number = v_settlement_number;
270
271 If SQL%NOTFOUND then
272 For C1_Rec in C1
273 Loop
274 Update Xtr_Deal_Date_Amounts
275 Set reconciled_reference = v_reconciled_reference,
276 reconciled_pass_code = p_reconciled_method
277 Where current of C1;
278 End Loop;
279 End if;
280
281 End if;
282
283 Else
284 -- ER 7601596 Start for Unreconciliation
285
286 BEGIN --12733323
287 select distinct deal_type into l_deal_type from xtr_deal_date_amounts where settlement_number in
288 (Select settlement_number
289 From Xtr_Settlement_Summary
290 Where settlement_summary_id = p_settlement_summary_ID);
291 EXCEPTION
292 WHEN No_Data_Found THEN
293 l_deal_type:='X';
294 END;
295
296
297 if l_deal_type ='RTMM' then
298 declare
299 v_recon_ref number;
300 begin
301
302 SELECT DISTINCT reconciled_reference into v_recon_ref FROM xtr_deal_date_amounts
303 WHERE settlement_number IN
304 (SELECT settlement_number FROM xtr_settlement_summary WHERE settlement_summary_id = p_settlement_summary_ID);
305
306 XTR_AUTO_RECONCILIATION.REVERSE_ROLL_TRANS_RTMM(
307 null,
308 v_recon_ref,
309 v_recon_ref,
310 'MANUAL',P_VAL_DATE); end;
311 end if;
312 -- ER 7601596 End
313
314 /* After successful Un-Reconciliation, Xtr_Settlement_Summary is updated for availability of record for future reconciliation */
315 Update xtr_settlement_summary
316 set status = 'A'
317 Where settlement_summary_id = p_settlement_summary_ID;
318 If SQL%Found then
319 P_Result := 'XTR2_SUCCESS';
320 Else
321 P_Result := 'XTR2_FAIL';
322 End if;
323
324 Select settlement_number
325 Into v_settlement_number
326 From Xtr_Settlement_Summary
327 Where settlement_summary_id = p_settlement_summary_ID;
328
329 Update Xtr_Deal_Date_Amounts
330 Set Reconciled_Reference = null,
331 Reconciled_Pass_Code = null
332 Where settlement_number = v_settlement_number;
333
334 If SQL%NOTFOUND then
335 For C1_Rec in C1
336 Loop
337 /* After successful un-reconciliation, DDA is updated such that the records are available for future reconciliation */
338 Update Xtr_Deal_Date_Amounts
339 Set reconciled_reference = null,
340 reconciled_pass_code = null
341 Where current of C1;
342 End Loop;
343 End if;
344 -- ER 7601596 Start
345 Update Xtr_Deal_Date_Amounts
346 Set settlement_number = null
347 Where settlement_number = v_settlement_number and deal_type ='RTMM' and amount_type='PRINFLW'
348 and exists(select 1 from Xtr_Deal_Date_Amounts where settlement_number = v_settlement_number and deal_type ='RTMM' and amount_type='INTSET') ;
349 -- ER 7601596 End
350 End if;
351
352 If p_result = 'XTR2_SUCCESS' then
353 commit;
354 Else
355 Rollback;
356 End if;
357
358 End Reconciliation;
359
360 Procedure Bank_Account_Verification(P_ORG_ID IN NUMBER,
361 P_ce_bank_account_id IN NUMBER,
362 P_CURRENCY_CODE IN VARCHAR2,
363 P_RESULT OUT NOCOPY VARCHAR2,
364 P_ERROR_MSG OUT NOCOPY VARCHAR2)is
365
366
367 cursor GET_AUTH_COMPANY is
368 select xp.PARTY_CODE COMPANY_CODE
369 from XTR_PARTY_INFO xp -- BUG 2811315
370 where xp.legal_entity_id = P_ORG_ID -- bug 3862743
371 and xp.authorised = 'Y';
372
373
374 cursor GET_COMPANY is
375 select xp.PARTY_CODE COMPANY_CODE
376 from XTR_PARTY_INFO xp
377 where xp.legal_entity_id = P_ORG_ID; -- bug 3862743
378
379 cursor FIND_AP_ACCT_ID is
380 select ce_bank_account_id from
381 xtr_bank_accounts
382 where ce_bank_account_id = P_ce_bank_account_id
383 and ce_bank_account_id is not null;
384
385 Cursor CUR_AUTH_ACCT IS
386 select ce_bank_account_id from
387 xtr_bank_accounts b,
388 xtr_party_info p
389 where b.ce_bank_account_id is not null
390 and ce_bank_account_id = P_ce_bank_account_id
391 and p.party_code = b.party_code
392 and p.party_type = b.party_type
393 and p.legal_entity_id = P_ORG_ID -- bug 3862743
394 and currency = P_CURRENCY_CODE
395 and b.authorised = 'Y';
396
397 Cursor CUR_UNAUTH_ACCT IS
398 select ce_bank_account_id,account_number from
399 xtr_bank_accounts b,
400 xtr_party_info p
401 where b.ce_bank_account_id is not null
402 and ce_bank_account_id = P_ce_bank_account_id
403 and p.party_code = b.party_code
404 and p.party_type = b.party_type
405 and p.legal_entity_id = P_ORG_ID -- bug 3862743
406 and currency = P_CURRENCY_CODE;
407
408 ap_acct_id NUMBER;
409 l_company VARCHAR2(30);
410 l_account_no VARCHAR2(30);
411
412 Begin
413 open FIND_AP_ACCT_ID;
414 fetch FIND_AP_ACCT_ID into ap_acct_id;
415 if FIND_AP_ACCT_ID%NOTFOUND then
416 P_RESULT := 'XTR1_AP';
417 Else
418 open GET_AUTH_COMPANY;
419 fetch GET_AUTH_COMPANY into l_company;
420 if GET_AUTH_COMPANY%FOUND then
421 /*Authorised Company found.Check the Account. */
422 open CUR_AUTH_ACCT;
423 fetch CUR_AUTH_ACCT into ap_acct_id;
424 if CUR_AUTH_ACCT%FOUND then
425 P_RESULT := 'XTR1_SHARED';
426 else
427 open CUR_UNAUTH_ACCT;
428 fetch CUR_UNAUTH_ACCT into ap_acct_id,l_account_no;
429 /* if if the Account exists but is not Authorised */
430 if CUR_UNAUTH_ACCT%FOUND then
431 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_ACCT_AUTH');
432 FND_MESSAGE.SET_TOKEN('P_ACCOUNT',l_account_no);
433 P_ERROR_MSG := FND_MESSAGE.GET;
434 /* if if the Account is not set up for the company and currency */
435 Else
436 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_INV_ACCT');
437 FND_MESSAGE.SET_TOKEN('P_ACCOUNT_ID',P_ce_bank_account_id);
438 FND_MESSAGE.SET_TOKEN('P_COMPANY',l_company);
439 FND_MESSAGE.SET_TOKEN('P_CURRENCY',P_CURRENCY_CODE);
440 P_ERROR_MSG := FND_MESSAGE.GET;
441 End if;
442 close CUR_UNAUTH_ACCT;
443 P_RESULT := 'XTR1_NOT_SETUP';
444 end if;
445 close CUR_AUTH_ACCT;
446
447 else
448 /*Authorised Company not found. See if Company exists at all*/
449
450 open GET_COMPANY;
451 fetch GET_COMPANY into l_company;
452 if GET_COMPANY%FOUND then
453 /* This means Company is not Authorised or the user doesnot have access*/
454 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_COMP_AUTH');
455 FND_MESSAGE.SET_TOKEN('P_COMPANY',l_company);
456 P_ERROR_MSG := FND_MESSAGE.GET;
457 /* No Company is setup with the given org ID*/
458 else
459 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_INV_COMP');
460 FND_MESSAGE.SET_TOKEN('P_ORG_ID',P_ORG_ID);
461 P_ERROR_MSG := FND_MESSAGE.GET;
462 end if;
463 Close GET_COMPANY;
464 P_RESULT := 'XTR1_NOT_SETUP';
465 end if;
466 close GET_AUTH_COMPANY;
467 End If;
468 Close FIND_AP_ACCT_ID;
469 End Bank_Account_Verification;
470
471
472 /*------------------------------------------------------------------------------/
473 Bank_Balance_Upload takes input parameters from 'CE' and uploads
474 balances into XTR_BANK_BALANCES if the data passes validations.
475 USes local procedure bank_balance_validate for doing reval or accrual
476 related validations.
477
478 Output Params:
479 P_RESULT: Possible values are 'XTR3_BU_WARNING',XTR3_BU_SUCCESS,XTR3_BU_FAIL
480 1) 'XTR3_BU_WARNING' : indicates various validation errors/warnings.
481 2) 'XTR3_BU_SUCCESS' : Successful Upload.
482 3) 'XTR3_BU_FAIL' :Failure for some other reasons.
483
484
485 P_ERROR_MSG: Will return the following messages:
486 XTR_BANK_BALANCE_VAL_ERROR,
487 XTR_BANK_BALANCE_VAL_WARN
488 XTR_BANK_BAL_OVERWRITE
489 XTR_BANK_BAL_FUTURE_DATE
490
491 /---------------------------------------------------------------------*/
492
493 Procedure Bank_Balance_Upload(P_ORG_ID IN NUMBER,
494 P_ce_bank_account_id IN NUMBER,
495 P_CURRENCY_CODE IN VARCHAR2,
496 P_BALANCE_DATE IN DATE,
497 P_BALANCE_AMOUNT_A IN NUMBER,
498 P_BALANCE_AMOUNT_B IN NUMBER,
499 P_BALANCE_AMOUNT_C IN NUMBER,
500 P_ONE_DAY_FLOAT IN NUMBER,
501 P_TWO_DAY_FLOAT IN NUMBER,
502 P_RESULT OUT NOCOPY VARCHAR2,
503 P_ERROR_MSG OUT NOCOPY VARCHAR2) is
504 l_dummy VARCHAR2(1);
505 l_comp VARCHAR2(7);
506 l_ccy VARCHAR2(15);
507 l_setoff VARCHAR2(5);
508 acct_no VARCHAR2(20);
509 new_company VARCHAR2(7);
510 new_date DATE;
511 new_bal_ledger NUMBER;
512 new_bal_cashflow NUMBER;
513 new_bal_intcalc NUMBER;
514 new_bal_ledger_hce NUMBER;
515 v_cross_ref XTR_PARTY_INFO.cross_ref_to_other_party%TYPE;
516 v_dummy_num NUMBER;
517 int_rate NUMBER;
518 roundfac NUMBER;
519 yr_basis NUMBER;
520 l_no_days NUMBER;
521 l_setoff_recalc_date DATE;
522 l_prv_date DATE;
523 l_prv_rate NUMBER;
524 l_prv_bal NUMBER;
525 l_int_bf NUMBER;
526 l_int_cf NUMBER;
527 l_interest NUMBER;
528 l_new_rate NUMBER;
529 l_hc_rate NUMBER;
530 l_yr_type VARCHAR2(20);
531 --add
532 l_limit_code varchar2(7);
533 l_portfolio_code varchar2(7);
534 l_bank_code varchar2(7);
535 --
536 l_prv_accrual_int NUMBER;
537 l_accrual_int NUMBER;
538 cursor RNDING is
539 select ROUNDING_FACTOR,YEAR_BASIS,HCE_RATE
540 from XTR_MASTER_CURRENCIES_V
541 where CURRENCY = l_ccy;
542
543 --
544 Cursor CUR_ACCT_NO IS
545 select account_number from
546 xtr_bank_accounts b,
547 xtr_party_info p
548 where b.ce_bank_account_id is not null
549 and ce_bank_account_id = P_ce_bank_account_id
550 and p.party_code = b.party_code
551 and p.party_type = b.party_type
552 and p.legal_entity_id = P_ORG_ID -- bug 3862743
553 and currency = P_CURRENCY_CODE
554 and b.authorised = 'Y';
555
556 --
557 cursor ACCT_DETAILS is
558 select
559 PARTY_CODE,CURRENCY,SETOFF,PORTFOLIO_CODE,BANK_CODE,nvl(YEAR_CALC_TYPE,'ACTUAL/ACTUAL')
560 YEAR_CALC_TYPE
561 ,rounding_type, day_count_type -- Added for Interest Override
562 from XTR_BANK_ACCOUNTS
563 where ACCOUNT_NUMBER = acct_no
564 and PARTY_CODE = new_company;
565 --
566 cursor PREV_DETAILS is
567 select
568 a.BALANCE_DATE,a.BALANCE_CFLOW,a.ACCUM_INT_CFWD,a.INTEREST_RATE,A.ACCRUAL_INTEREST,
569 a.rounding_type, a.day_count_type -- Added for Interest Override
570 from XTR_BANK_BALANCES a
571 where a.ACCOUNT_NUMBER = acct_no
572 and a.COMPANY_CODE = new_company
573 and a.BALANCE_DATE = (select max(b.BALANCE_DATE)
574 from XTR_BANK_BALANCES b
575 where b.ACCOUNT_NUMBER = acct_no
576 and b.COMPANY_CODE = new_company);
577 --
578 cursor CHK_EXISTING_DATE is
579 select 'x'
580 from XTR_BANK_BALANCES
581 where ACCOUNT_NUMBER = acct_no
582 and COMPANY_CODE = new_company
583 and TRUNC(BALANCE_DATE) = TRUNC(new_date);
584 --
585 cursor GET_LIM_CODE_BAL is
586 select LIMIT_CODE
587 from XTR_BANK_BALANCES
588 where ACCOUNT_NUMBER = acct_no
589 and COMPANY_CODE = new_company
590 and BALANCE_DATE < new_date
591 and ((new_bal_ledger >= 0 and BALANCE_CFLOW >= 0)
592 or (new_bal_ledger <= 0 and BALANCE_CFLOW <= 0))
593 order by BALANCE_DATE;
594 --
595 cursor GET_LIM_CODE_CPARTY is
596 select cl.LIMIT_CODE
597 from XTR_COUNTERPARTY_LIMITS cl, XTR_LIMIT_TYPES lt
598 where cl.COMPANY_CODE = new_company
599 and cl.CPARTY_CODE = l_bank_code
600 and cl.LIMIT_TYPE = lt.LIMIT_TYPE
601 and ((new_bal_ledger >= 0 and lt.FX_INVEST_FUND_TYPE = 'I')
602 or (new_bal_ledger <= 0 and lt.FX_INVEST_FUND_TYPE = 'OD'));
603 --
604 cursor CROSS_REF is
605 select CROSS_REF_TO_OTHER_PARTY
606 from XTR_PARTIES_V
607 where PARTY_CODE = l_comp;
608 --
609 cursor GET_COMPANY is
610 select xp.PARTY_CODE COMPANY_CODE
611 from XTR_PARTY_INFO xp
612 where xp.legal_entity_id = P_ORG_ID; -- bug 3862743
613
614
615 -- Added for Interest Override
616 CURSOR oldest_date IS
617 SELECT MIN(a.balance_date)
618 FROM xtr_bank_balances a
619 WHERE a.account_number = acct_no
620 AND a.COMPANY_CODE = new_company;
621
622 CURSOR PRV_PRV_DETAILS IS
623 SELECT a.day_count_type
624 FROM xtr_bank_balances a
625 WHERE a.account_number = acct_no
626 AND a.COMPANY_CODE = new_company
627 AND a.balance_date = (select max(b.BALANCE_DATE)
628 from XTR_BANK_BALANCES b
629 where b.ACCOUNT_NUMBER = acct_no
630 and b.COMPANY_CODE = new_company
631 AND b.balance_date < l_prv_date);
632
633 l_rounding_type VARCHAR2(1);
634 l_day_count_type VARCHAR2(1);
635 l_prv_rounding_type VARCHAR2(1);
636 l_prv_day_count_type VARCHAR2(1);
637 l_oldest_date DATE;
638 l_first_trans_flag VARCHAR2(1);
639 l_original_amount NUMBER;
640 l_prv_prv_day_count_type VARCHAR2(1);
641
642 Begin
643 Open CUR_ACCT_NO;
644 FETCH CUR_ACCT_NO INTO acct_no;
645 CLOSE CUR_ACCT_NO;
646
647 new_date := TRUNC(p_balance_date);
648 new_bal_ledger := nvl(p_balance_amount_a,0);
649 new_bal_cashflow := nvl(p_balance_amount_b,new_bal_ledger);
650 new_bal_intcalc := nvl(p_balance_amount_c,0)-new_bal_ledger;
651 open GET_COMPANY;
652 fetch GET_COMPANY INTO new_company;
653 close GET_COMPANY;
654
655 If p_balance_date <= trunc(sysdate) then --bug 1038388
656
657 open PREV_DETAILS;
658 fetch PREV_DETAILS INTO
659 l_prv_date,l_prv_bal,l_int_bf,l_prv_rate,l_prv_accrual_int,
660 l_prv_rounding_type, l_prv_day_count_type; -- Added for Interest Override
661 if PREV_DETAILS%NOTFOUND then
662 l_prv_date := trunc(new_date);
663 l_prv_bal := 0;
664 l_prv_rate := 0;
665 l_int_bf := 0;
666 l_no_days := 0;
667 l_prv_accrual_int := 0;
668 l_prv_rounding_type := NULL;
669 l_prv_day_count_type := NULL;
670 end if;
671 close PREV_DETAILS;
672 open ACCT_DETAILS;
673 fetch ACCT_DETAILS INTO
674 l_comp,l_ccy,l_setoff,l_portfolio_code,l_bank_code,l_yr_type,
675 l_rounding_type, l_day_count_type; -- Added for Interest Override
676 if ACCT_DETAILS%FOUND then -- Account is loaded in the system
677 close ACCT_DETAILS;
678 open RNDING;
679 fetch RNDING INTO roundfac,yr_basis,l_hc_rate;
680 close RNDING;
681 open GET_LIM_CODE_BAL;
682 fetch GET_LIM_CODE_BAL INTO l_limit_code;
683 if GET_LIM_CODE_BAL%NOTFOUND or l_limit_code IS NULL then
684 open GET_LIM_CODE_CPARTY;
685 fetch GET_LIM_CODE_CPARTY INTO l_limit_code;
686 if GET_LIM_CODE_CPARTY%NOTFOUND then
687 l_limit_code := Null;
688 end if;
689 close GET_LIM_CODE_CPARTY;
690 end if;
691 close GET_LIM_CODE_BAL;
692
693 -- Added for Interest Override
694 OPEN oldest_date;
695 FETCH oldest_date INTO l_oldest_date;
696 CLOSE oldest_date;
697 IF l_day_count_type ='B' AND l_prv_date = l_oldest_date THEN
698 l_first_trans_flag :='Y';
699 ELSE
700 l_first_trans_flag := NULL;
701 END IF;
702 --
703 if trunc(l_prv_date) < trunc(new_date) then
704 -- Added for Interest Override
705 OPEN prv_prv_details;
706 FETCH prv_prv_details INTO l_prv_prv_day_count_type;
707 CLOSE prv_prv_details;
708 --
709 XTR_CALC_P.CALC_DAYS_RUN(trunc(l_prv_date),
710
711 trunc(new_date),
712 l_yr_type,
713 l_no_days,
714 yr_basis,
715 NULL,
716 l_prv_day_count_type,
717 l_first_trans_flag);
718 -- Added for Interest Override
719 IF l_prv_date <> l_oldest_date AND l_prv_day_count_type ='F'
720 AND (Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='L'
721 OR Nvl(l_prv_prv_day_count_type,l_prv_day_count_type) ='B')
722 THEN
723 l_no_days := l_no_days -1;
724 END IF;
725 --
726 else
727 l_no_days :=0;
728 yr_basis :=365;
729 end if;
730
731 -- Changed for Interest Override
732 /*
733 l_interest := xtr_fps2_p.interest_round(l_prv_bal * l_prv_rate / 100 * l_no_days
734 / yr_basis,roundfac,l_prv_rounding_type);
735 */
736 -- bug 5192026
737
738 CE_INTEREST_CALC.int_cal_xtr( trunc(l_prv_date),
739 trunc(new_date),
740 p_ce_bank_account_id,
741 l_prv_rate,
742 'TREASURY',
743 l_interest );
744
745 l_interest := nvl(l_interest,0) ; -- Bug 5280690
746
747 l_original_amount := nvl(l_int_bf,0) + nvl(l_interest,0); -- Bug 5280690 added nvl condition
748 l_int_cf := l_original_amount;
749 --
750 l_accrual_int :=nvl(l_prv_accrual_int,0) + nvl(l_interest,0);
751 /*
752 XTR_ACCOUNT_BAL_MAINT_P.FIND_INT_RATE(acct_no,
753 new_bal_ledger,
754 new_company,l_bank_code,l_ccy,new_date,l_new_rate);
755 */
756 -- bug 5192026
757 l_new_rate :=
758 CE_INTEREST_CALC.GET_INTEREST_RATE(p_ce_bank_account_id,new_date
759 , new_bal_ledger,l_new_rate);
760
761
762
763 if l_new_rate is null then
764 l_new_rate := 0;
765 end if;
766 open CHK_EXISTING_DATE;
767 fetch CHK_EXISTING_DATE INTO l_dummy;
768 if CHK_EXISTING_DATE%NOTFOUND then
769 bank_balance_validate(new_company,acct_no,p_currency_code,new_date,'REVAL','INSERT',p_result,p_error_msg);
770 if p_error_msg is null and p_result is null then
771 bank_balance_validate(new_company,acct_no,p_currency_code,new_date,'ACCRUAL','INSERT',p_result,p_error_msg);
772 end if;
773 if nvl(p_result,'XX') = 'XTR3_BU_VAL_ERROR' then
774 P_RESULT := 'XTR3_BU_WARNING';
775 return;
776 elsif nvl(p_result,'XX') = 'XTR3_BU_VAL_WARN' then
777 insert into XTR_BANK_BALANCES
778 (company_code,account_number,balance_date,no_of_days,
779 statement_balance,balance_adjustment,balance_cflow,
780 accum_int_bfwd,interest,interest_rate,interest_settled,
781 interest_settled_hce,accum_int_cfwd,setoff,limit_code,
782 created_on,created_by,accrual_interest,
783 original_amount, rounding_type, day_count_type, -- Added for Interest Override
784 one_day_float, two_day_float)
785 values
786 (l_comp,acct_no,new_date,l_no_days,
787 new_bal_ledger,new_bal_intcalc,new_bal_cashflow,
788 l_int_bf,l_interest,l_new_rate,0,
789 0,l_int_cf,l_setoff,l_limit_code,
790 sysdate, fnd_global.user_id,l_accrual_int,
791 l_original_amount, l_rounding_type, l_day_count_type, -- Added for Interest Override
792 P_ONE_DAY_FLOAT, P_TWO_DAY_FLOAT);
793 new_bal_ledger_hce := round(new_bal_ledger / l_hc_rate,roundfac);
794 P_RESULT := 'XTR3_BU_WARNING';
795 else
796 -- the uploaded date is the latest date then ok to insert
797 insert into XTR_BANK_BALANCES
798 (company_code,account_number,balance_date,no_of_days,
799 statement_balance,balance_adjustment,balance_cflow,
800 accum_int_bfwd,interest,interest_rate,interest_settled,
801 interest_settled_hce,accum_int_cfwd,setoff,limit_code,
802 created_on,created_by,accrual_interest,
803 original_amount, rounding_type, day_count_type, -- Added for Interest Override
804 one_day_float, two_day_float)
805 values
806 (l_comp,acct_no,new_date,l_no_days,
807 new_bal_ledger,new_bal_intcalc,new_bal_cashflow,
808 l_int_bf,l_interest,l_new_rate,0,
809 0,l_int_cf,l_setoff,l_limit_code,
810 sysdate, fnd_global.user_id,l_accrual_int,
811 l_original_amount, l_rounding_type, l_day_count_type, -- Added for Interest Override
812 P_ONE_DAY_FLOAT, P_TWO_DAY_FLOAT);
813 new_bal_ledger_hce := round(new_bal_ledger / l_hc_rate,roundfac);
814 P_RESULT := 'XTR3_BU_SUCCESS';
815 P_ERROR_MSG := NULL;
816 end if;
817 --
818 else
819 bank_balance_validate(new_company,acct_no,p_currency_code,new_date,'REVAL','UPDATE',P_RESULT,P_ERROR_MSG);
820 if p_error_msg is null and p_result is null then
821 bank_balance_validate(new_company,acct_no,p_currency_code,new_date,'ACCRUAL','UPDATE',P_RESULT,P_ERROR_MSG);
822 end if;
823 if nvl(p_result,'XX') = 'XTR3_BU_VAL_ERROR' then
824 P_RESULT := 'XTR3_BU_WARNING';
825 return;
826 elsif nvl(p_result,'XX') = 'XTR3_BU_VAL_WARN' then
827 update XTR_BANK_BALANCES
828 set statement_balance=new_bal_ledger,
829 balance_adjustment=new_bal_intcalc,
830 balance_cflow=new_bal_cashflow,
831 accum_int_bfwd=l_int_bf,
832 interest=l_interest,
833 interest_rate=l_new_rate,
834 interest_settled=0,
835 interest_settled_hce=0,
836 accum_int_cfwd=l_int_cf,
837 setoff=l_setoff,
838 limit_code=l_limit_code,
839 updated_on=sysdate,
840 updated_by=fnd_global.user_id,
841 accrual_interest=l_accrual_int,
842 original_amount = l_original_amount, -- Added for Interest Override
843 rounding_type = l_rounding_type,
844 day_count_type = l_day_count_type,
845 one_day_float = P_ONE_DAY_FLOAT,
846 two_day_float = P_TWO_DAY_FLOAT
847 where ACCOUNT_NUMBER = acct_no
848 and COMPANY_CODE = new_company
849 and TRUNC(BALANCE_DATE) = TRUNC(new_date);
850 P_RESULT := 'XTR3_BU_WARNING';
851 else
852 update XTR_BANK_BALANCES
853 set statement_balance=new_bal_ledger,
854 balance_adjustment=new_bal_intcalc,
855 balance_cflow=new_bal_cashflow,
856 accum_int_bfwd=l_int_bf,
857 interest=l_interest,
858 interest_rate=l_new_rate,
859 interest_settled=0,
860 interest_settled_hce=0,
861 accum_int_cfwd=l_int_cf,
862 setoff=l_setoff,
863 limit_code=l_limit_code,
864 updated_on=sysdate,
865 updated_by=fnd_global.user_id,
866 accrual_interest=l_accrual_int,
867 original_amount = l_original_amount, -- Added for Interest Override
868 rounding_type = l_rounding_type,
869 day_count_type = l_day_count_type,
870 one_day_float = P_ONE_DAY_FLOAT,
871 two_day_float = P_TWO_DAY_FLOAT
872 where ACCOUNT_NUMBER = acct_no
873 and COMPANY_CODE = new_company
874 and TRUNC(BALANCE_DATE) = TRUNC(new_date);
875 -- P_RESULT := 'XTR3_BU_OVERWRITE';
876 P_RESULT := 'XTR3_BU_WARNING';
877 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_OVERWRITE');
878 FND_MESSAGE.SET_TOKEN('P_DATE',trunc(new_date));
879 FND_MESSAGE.SET_TOKEN('P_COMPANY',new_company);
880 FND_MESSAGE.SET_TOKEN('P_ACCOUNT',acct_no);
881 P_ERROR_MSG := FND_MESSAGE.GET;
882 End if;
883 --
884 /* Balance for this date and company already exists in the system. Overwriting everything. */
885 end if;
886 close CHK_EXISTING_DATE;
887 -- P_RESULT := 'XTR3_BU_SUCCESS';
888 --
889 open CROSS_REF;
890 fetch CROSS_REF INTO v_cross_ref;
891 close CROSS_REF;
892 XTR_ACCOUNT_BAL_MAINT_P.UPDATE_BANK_ACCTS(acct_no,
893 l_ccy,
894 l_bank_code,
895 l_portfolio_code,
896 v_cross_ref,
897 l_comp,
898 new_date,
899 v_dummy_num,-- for bug 6247219
900 l_setoff);-- for bug 6247219
901 else
902 -- P_RESULT := 'XTR3_BU_FAIL'; /* This Account does not exist for this company */
903 if ACCT_DETAILS%ISOPEN then
904 close ACCT_DETAILS;
905 end if;
906 end if;
907 else
908 -- P_RESULT := 'XTR3_BU_FUTURE_DATE'; /* Balance date must be less than sysdate */
909 P_RESULT := 'XTR3_BU_WARNING';
910 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_FUTURE_DATE');
911 FND_MESSAGE.SET_TOKEN('P_DATE',trunc(new_date));
912 FND_MESSAGE.SET_TOKEN('P_COMPANY',new_company);
913 FND_MESSAGE.SET_TOKEN('P_ACCOUNT',acct_no);
914 P_ERROR_MSG := FND_MESSAGE.GET;
915 end if;
916 commit;
917 --
918 End Bank_Balance_Upload;
919
920 /*----------------------------------------------------------------------------/
921 bank_balance_validate is a LOCAL PROCEDURE to check the
922 validations related to Accrual or Reval.
923 /----------------------------------------------------------------------------*/
924
925 Procedure bank_balance_validate(P_COMPANY_CODE IN VARCHAR2,
926 P_ACCOUNT_NUMBER IN VARCHAR2,
927 P_CURRENCY_CODE IN VARCHAR2,
928 P_BALANCE_DATE IN DATE,
929 P_EVENT_CODE IN VARCHAR2,
930 P_ACTION IN VARCHAR2,
931 P_RESULT OUT NOCOPY VARCHAR2,
932 P_ERROR_MSG OUT NOCOPY VARCHAR2) IS
933
934 cursor cur_reval_comp IS
935 SELECT max(period_end)
936 FROM
937 xtr_batches b,xtr_batch_events e
938 WHERE
939 b.company_code = p_company_code
940 AND b.batch_id = e.batch_id
941 AND e.event_code = p_event_code;
942
943 --bug 7631275 starts
944 /*cursor cur_reval_insbal IS
945 SELECT max(period_to)
946 FROM
947 xtr_bank_balances bb,xtr_bank_accounts ba,
948 xtr_deal_date_amounts dd,xtr_revaluation_details rd
949 WHERE
950 bb.company_code = p_company_code
951 AND bb.account_number = p_account_number
952 AND bb.company_code = ba.party_code
953 AND bb.account_number = ba.account_number
954 AND ba.currency = p_currency_code
955 AND bb.company_code = dd.company_code
956 AND bb.account_number = dd.account_no
957 AND ba.currency = dd.currency
958 AND dd.deal_number = rd.deal_no;*/
959
960 cursor cur_reval_insbal IS
961 select max(period_to)
962 FROM
963 xtr_bank_accounts ba,
964 xtr_deal_date_amounts dd,
965 xtr_revaluation_details rd
966 WHERE ba.currency = p_currency_code
967 AND ba.currency = dd.currency
968 AND dd.deal_number = rd.deal_no
969 AND dd.company_code = ba.party_code
970 AND dd.account_no = ba.account_number
971 AND dd.company_code = p_company_code
972 and dd.account_no = p_account_number
973 and exists (select 1 from xtr_bank_balances bb
974 where bb.company_code = dd.company_code
975 and bb.account_number = dd.account_no);
976
977 /*cursor cur_accrl_insbal IS
978 SELECT max(period_to)
979 FROM
980 xtr_bank_balances bb,xtr_bank_accounts ba,
981 xtr_deal_date_amounts dd,xtr_accrls_amort aa
982 WHERE
983 bb.company_code = p_company_code
984 AND bb.account_number = p_account_number
985 AND bb.company_code = ba.party_code
986 AND bb.account_number = ba.account_number
987 AND ba.currency = p_currency_code
988 AND bb.company_code = dd.company_code
989 AND bb.account_number = dd.account_no
990 AND ba.currency = dd.currency
991 AND dd.deal_number = aa.deal_no;*/
992
993 cursor cur_accrl_insbal IS
994 select max(period_to)
995 FROM
996 xtr_bank_accounts ba,
997 xtr_deal_date_amounts dd,
998 xtr_accrls_amort aa
999 WHERE ba.currency = p_currency_code
1000 AND ba.currency = dd.currency
1001 AND dd.deal_number = aa.deal_no
1002 AND dd.company_code = ba.party_code
1003 AND dd.account_no = ba.account_number
1004 AND dd.company_code = p_company_code
1005 and dd.account_no = p_account_number
1006 and exists (select 1 from xtr_bank_balances bb
1007 where bb.company_code = dd.company_code
1008 and bb.account_number = dd.account_no);
1009
1010 --bug 7631275 ends
1011 l_rdate date;
1012 l_bdate date;
1013 l_error_code VARCHAR2(30);
1014
1015 BEGIN
1016 If p_event_code = 'REVAL' then
1017 Open cur_reval_insbal;
1018 Fetch cur_reval_insbal into l_rdate;
1019 Close cur_reval_insbal;
1020 Elsif p_event_code = 'ACCRUAL' then
1021 Open cur_accrl_insbal;
1022 Fetch cur_accrl_insbal into l_rdate;
1023 Close cur_accrl_insbal;
1024 End If;
1025
1026 If P_ACTION = 'UPDATE' then
1027 If l_rdate is NOT NULL and l_rdate > p_balance_date then
1028 P_RESULT := 'XTR3_BU_VAL_ERROR';
1029 L_ERROR_CODE := 'XTR_BANK_BALANCE_VAL_ERROR';
1030 End If;
1031 Elsif P_ACTION = 'INSERT' then
1032 If l_rdate is NULL then
1033 Open cur_reval_comp;
1034 Fetch cur_reval_comp into l_bdate;
1035 Close cur_reval_comp;
1036
1037 If l_bdate is NOT NULL and l_bdate > p_balance_date then
1038 P_RESULT := 'XTR3_BU_VAL_WARN';
1039 L_ERROR_CODE := 'XTR_BANK_BALANCE_VAL_WARN';
1040 End If;
1041 Elsif l_rdate is NOT NULL and l_rdate > p_balance_date then
1042 P_RESULT := 'XTR3_BU_VAL_ERROR';
1043 L_ERROR_CODE := 'XTR_BANK_BALANCE_VAL_ERROR';
1044 End If;
1045 End If;
1046 FND_MESSAGE.SET_NAME('XTR',l_error_code);
1047 FND_MESSAGE.SET_TOKEN('P_DATE',trunc(p_balance_date));
1048 FND_MESSAGE.SET_TOKEN('P_COMPANY',p_company_code);
1049 FND_MESSAGE.SET_TOKEN('P_ACCOUNT',p_account_number);
1050 P_ERROR_MSG := FND_MESSAGE.GET;
1051 END;
1052
1053
1054 Procedure Settlement_Validation(
1055 P_SETTLEMENT_SUMMARY_ID IN NUMBER,
1056 P_RESULT OUT NOCOPY VARCHAR2)is
1057 Begin
1058 /* to be implemented later */
1059 null;
1060 End Settlement_Validation;
1061
1062
1063 ----------------------------------------------------------------------------------------------------
1064 -- 3800146 This procedure verifies the type of account: XTR only or AP/XTR Shared
1065 ----------------------------------------------------------------------------------------------------
1066 PROCEDURE ZBA_BANK_ACCOUNT_VERIFICATION (
1067 P_ORG_ID IN NUMBER,
1068 -- org_id of the company for 'Shared'
1069 P_ce_bank_account_id IN NUMBER,
1070 -- ce_bank_account_id for 'Shared or 'AP-only'
1071 P_ACCOUNT_NUMBER IN VARCHAR2,
1072 -- account_number in XTR_BANK_ACCOUNTS
1073 P_CURRENCY IN VARCHAR2,-- currency of transaction
1074 P_BANK_ACCOUNT_ID OUT NOCOPY NUMBER,
1075 --ap_bank_account_id 'Shared' or dummy_bank_account_id 'XTRonly'
1076 P_RESULT OUT NOCOPY VARCHAR2,-- 'PASS' or 'FAIL'
1077 P_ERROR_MSG OUT NOCOPY VARCHAR2) is
1078
1079
1080 l_dummy NUMBER;
1081 l_company XTR_PARTY_INFO.PARTY_CODE%TYPE;
1082 l_auth_flag VARCHAR2(1);
1083 l_acct_no XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
1084 l_dummy_ap_acct NUMBER;
1085
1086 /*-----------------------------------------------------------------------------------*/
1087 /* To check for ORG_ID and AP_BANK_ACCOUNT_ID combination */
1088 /*-----------------------------------------------------------------------------------*/
1089
1090 cursor FIND_AP_ACCT_ID is -- (1) To check for 'Shared' with this P_AP_BANK_ACCOUNT_ID
1091 select 1
1092 from xtr_bank_accounts
1093 where ce_bank_account_id = P_ce_bank_account_id
1094 and ce_bank_account_id is not null;
1095
1096 cursor GET_COMPANY is -- (2) To check for valid company ORG_ID
1097 select xp.PARTY_CODE COMPANY_CODE
1098 from XTR_PARTY_INFO xp
1099 where xp.legal_entity_id = P_ORG_ID; -- bug 3862743
1100
1101
1102 cursor CHK_USER_ACCESS is -- (3) To check for user access to company
1103 select 1
1104 from XTR_PARTIES_V
1105 where party_code = l_company;
1106
1107 Cursor CHK_AUTH_ACCT IS -- (4) To check for ORG_ID and AP_BANK_ID combination
1108 select b.authorised, -- (5) To check for Authorised Account
1109 b.account_number
1110 from xtr_bank_accounts b,
1111 xtr_party_info p
1112 where b.ce_bank_account_id = P_ce_bank_account_id
1113 and b.ce_bank_account_id is not null
1114 and b.currency = P_CURRENCY
1115 and p.party_code = b.party_code
1116 and p.party_type = b.party_type
1117 and p.legal_entity_id = P_ORG_ID; -- bug 3862743
1118
1119 /*-----------------------------------------------------------------------------------*/
1120 /* To check for valid Bank Account Number */
1121 /*-----------------------------------------------------------------------------------*/
1122 Cursor CHK_UNIQUE_ACCT IS -- (a) To check for a single authorised account
1123 select authorised,
1124 party_code,
1125 ce_bank_account_id
1126 from xtr_bank_accounts
1127 where account_number = P_ACCOUNT_NUMBER
1128 and currency = P_CURRENCY
1129 order by authorised desc; -- so 'Y' comes before 'N'
1130
1131 BEGIN
1132
1133 P_BANK_ACCOUNT_ID := null;
1134 P_ERROR_MSG := null;
1135 P_RESULT := null;
1136
1137 /*------------------------------------------------------------------*/
1138 /* Checks Header Account Information: ORG_ID + ce_bank_account_id */
1139 /*------------------------------------------------------------------*/
1140 if P_ORG_ID is not null and P_ce_bank_account_id is not null then
1141
1142 open FIND_AP_ACCT_ID;
1143 fetch FIND_AP_ACCT_ID into l_dummy;
1144 if FIND_AP_ACCT_ID%NOTFOUND then
1145 /*--------------------------------------------*/
1146 /* AP Acct is not setup as a Shared acct. */
1147 /*--------------------------------------------*/
1148 P_RESULT := 'FAIL';
1149 FND_MESSAGE.SET_NAME('XTR','XTR_NOT_AP_XTR_SHARED'); -- new message **************************************************************
1150 FND_MESSAGE.SET_TOKEN('P_AP_ACCOUNT_ID', P_ce_bank_account_id);
1151 FND_MESSAGE.SET_TOKEN('P_CURRENCY', P_CURRENCY);
1152
1153 else
1154
1155 open GET_COMPANY;
1156 fetch GET_COMPANY into l_company;
1157 if GET_COMPANY%NOTFOUND then
1158 /*--------------------------------------------*/
1159 /* ORG_ID not setup as Company in Treasury. */
1160 /*--------------------------------------------*/
1161 -- A company that corresponds to the legal entity for Org. ID P_ORG_ID is not set up in Treasury.
1162 P_RESULT := 'FAIL';
1163 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_INV_COMP');
1164 FND_MESSAGE.SET_TOKEN('P_ORG_ID',P_ORG_ID);
1165
1166 else
1167
1168 open CHK_USER_ACCESS;
1169 fetch CHK_USER_ACCESS into l_dummy;
1170 if CHK_USER_ACCESS%NOTFOUND then
1171 /*--------------------------------------------*/
1172 /* Unauthorised User for this Company */
1173 /*--------------------------------------------*/
1174 -- The user does not have the authority to access the company P_COMPANY.
1175 P_RESULT := 'FAIL';
1176 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_COMP_AUTH');
1177 FND_MESSAGE.SET_TOKEN('P_COMPANY',l_company);
1178
1179 else
1180
1181 open CHK_AUTH_ACCT;
1182 fetch CHK_AUTH_ACCT into l_auth_flag, l_acct_no;
1183 if CHK_AUTH_ACCT%NOTFOUND then
1184 /*------------------------------------------------*/
1185 /* Wrong Org_id and AP_Bank_Account combination */
1186 /*------------------------------------------------*/
1187 P_RESULT := 'FAIL';
1188 FND_MESSAGE.SET_NAME('XTR','XTR_INVALID_ORG_ACCT'); -- new message *******************************************************
1189 FND_MESSAGE.SET_TOKEN('P_ORG_ID',P_ORG_ID);
1190 FND_MESSAGE.SET_TOKEN('P_ACCOUNT_ID',P_ce_bank_account_id);
1191
1192 else
1193 if nvl(l_auth_flag,'N') = 'N' then
1194 /*--------------------------------------*/
1195 /* Account is not authorised for use. */
1196 /*--------------------------------------*/
1197 -- The bank account P_ACCOUNT is not authorized in Treasury.
1198 P_RESULT := 'FAIL';
1199 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_ACCT_AUTH');
1200 FND_MESSAGE.SET_TOKEN('P_ACCOUNT',l_acct_no);
1201
1202 else
1203 /*--------------------------------------*/
1204 /* AP/XTR Shared account. */
1205 /*--------------------------------------*/
1206 P_RESULT := 'PASS';
1207 end if;
1208
1209 end if;
1210 close CHK_AUTH_ACCT;
1211
1212 end if;
1213 close CHK_USER_ACCESS;
1214
1215 end if;
1216 close GET_COMPANY;
1217
1218 end if;
1219 close FIND_AP_ACCT_ID;
1220
1221 elsif P_ACCOUNT_NUMBER is not null then
1222
1223 open CHK_UNIQUE_ACCT;
1224 fetch CHK_UNIQUE_ACCT into l_auth_flag, l_company, l_dummy_ap_acct;
1225 if CHK_UNIQUE_ACCT%NOTFOUND then
1226 /*--------------------------------------------*/
1227 /* Account Number does not exist in Treasury */
1228 /*--------------------------------------------*/
1229 P_RESULT := 'FAIL';
1230 FND_MESSAGE.SET_NAME('XTR','XTR_ACCT_NOT_SETUP'); -- new message ******************************************************************
1231 FND_MESSAGE.SET_TOKEN('P_ACCOUNT_NO',P_ACCOUNT_NUMBER);
1232 FND_MESSAGE.SET_TOKEN('P_CURRENCY',P_CURRENCY);
1233
1234 else
1235 if a_comp(l_company) then
1236 open CHK_USER_ACCESS;
1237 fetch CHK_USER_ACCESS into l_dummy;
1238 if CHK_USER_ACCESS%NOTFOUND then
1239 /*--------------------------------------------*/
1240 /* Unauthorised User for this Company */
1241 /*--------------------------------------------*/
1242 -- The user does not have the authority to access the company P_COMPANY.
1243 P_RESULT := 'FAIL';
1244 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_COMP_AUTH');
1245 FND_MESSAGE.SET_TOKEN('P_COMPANY',l_company);
1246 end if;
1247 close CHK_USER_ACCESS;
1248 end if;
1249
1250 if nvl(P_RESULT,'N') <> 'FAIL' then
1251 if nvl(l_auth_flag,'N') = 'N' then
1252 /*--------------------------------------*/
1253 /* Account is not authorised for use. */
1254 /*--------------------------------------*/
1255 -- The bank account P_ACCOUNT is not authorized in Treasury.
1256 P_RESULT := 'FAIL';
1257 FND_MESSAGE.SET_NAME('XTR','XTR_BANK_BAL_ACCT_AUTH');
1258 FND_MESSAGE.SET_TOKEN('P_ACCOUNT',P_ACCOUNT_NUMBER);
1259
1260 else
1261 P_BANK_ACCOUNT_ID := l_dummy_ap_acct;
1262 fetch CHK_UNIQUE_ACCT into l_auth_flag, l_company, l_dummy_ap_acct;
1263 if CHK_UNIQUE_ACCT%FOUND then
1264 /*--------------------------------------*/
1265 /* Duplicate Account is found. */
1266 /*--------------------------------------*/
1267 P_BANK_ACCOUNT_ID := null;
1268 P_RESULT := 'FAIL';
1269 FND_MESSAGE.SET_NAME('XTR','XTR_DUPLICATE_ACCOUNT'); -- new message ******************************************************
1270 FND_MESSAGE.SET_TOKEN('P_ACCOUNT_NUMBER',P_ACCOUNT_NUMBER);
1271 FND_MESSAGE.SET_TOKEN('P_CURRENCY',P_CURRENCY);
1272
1273 else
1274 P_RESULT := 'PASS';
1275 end if;
1276
1277 end if; -- l_auth_flag = 'N'
1278 end if; -- P_RESULT <> FAIL
1279
1280 end if;
1281 close CHK_UNIQUE_ACCT;
1282
1283 else
1284 P_RESULT := 'FAIL';
1285 FND_MESSAGE.SET_NAME('XTR','XTR_IMPORT_UNEXPECTED_ERROR'); -- existing message ****************************************************
1286
1287 end if; -- P_ORG_ID + P_AP_BANK_ACCOUNT_ID not null
1288
1289
1290 if P_RESULT = 'FAIL' then
1291 P_ERROR_MSG := FND_MESSAGE.GET;
1292 end if;
1293
1294 END ZBA_BANK_ACCOUNT_VERIFICATION;
1295
1296
1297 -----------------------------------------------------------------------------------------------------
1298 -- 3800146
1299 -- This procedure is a copy of FIND_INT_RATE in XTRINING.fmb. If procedure is called from BAL block,
1300 -- that is deal management window, it uses the following logic.
1301 --
1302 -- Procedure to find the applicable interest rate from interest rate ranges for this account.
1303 -- Seach for specific rate defined for company party currency combination. If found, go for it.
1304 -- If not found, then search for global rate defined for company party currency combination.
1305 -- If found go for it. If not found, then return null.
1306 -----------------------------------------------------------------------------------------------------
1307 PROCEDURE FIND_SPECIFIC_GLOBAL_RATE (
1308 p_company_code IN VARCHAR2,
1309 p_party_code IN VARCHAR2,
1310 p_currency IN VARCHAR2,
1311 p_balance_out IN NUMBER,
1312 p_principal_adjust IN NUMBER,
1313 p_transfer_date IN DATE,
1314 p_block IN VARCHAR2,
1315 p_ref_code IN VARCHAR2,
1316 -- currently only 'IG_PRO1075' is used
1317 p_interest_rate OUT NOCOPY NUMBER,
1318 -- currently only design as OUT param
1319 p_warn_message OUT NOCOPY VARCHAR2) is
1320 --
1321 cursor FIND_SPECIFIC_RATE is
1322 select INTEREST_RATE
1323 from xtr_INTEREST_RATE_RANGES_v
1324 where PARTY_CODE = p_party_code
1325 and CURRENCY = nvl(p_currency,CURRENCY)
1326 and REF_CODE = p_ref_code -- 'IG_PRO1075'
1327 and MIN_AMT < to_number(p_balance_out)
1328 and MAX_AMT >= to_number(p_balance_out)
1329 and EFFECTIVE_FROM_DATE = (select max(EFFECTIVE_FROM_DATE)
1330 from xtr_INTEREST_RATE_RANGES_v
1331 where PARTY_CODE = p_PARTY_CODE
1332 and CURRENCY = nvl(p_CURRENCY,CURRENCY)
1333 and REF_CODE = p_ref_code -- 'IG_PRO1075'
1334 and MIN_AMT < to_number(p_BALANCE_OUT)
1335 and MAX_AMT >= to_number(p_BALANCE_OUT)
1336 and EFFECTIVE_FROM_DATE <= p_TRANSFER_DATE );
1337
1338 cursor FIND_GLOBAL_RATE is
1339 select INTEREST_RATE
1340 from xtr_INTEREST_RATE_RANGES_v
1341 where PARTY_CODE = p_COMPANY_CODE
1342 and CURRENCY = nvl(p_CURRENCY,CURRENCY)
1343 and REF_CODE = p_ref_code -- 'IG_PRO1075'
1344 and MIN_AMT < p_BALANCE_OUT
1345 and MAX_AMT >= p_BALANCE_OUT
1346 and EFFECTIVE_FROM_DATE = (select max(EFFECTIVE_FROM_DATE)
1347 from xtr_INTEREST_RATE_RANGES_v
1348 where EFFECTIVE_FROM_DATE <= p_TRANSFER_DATE
1349 and PARTY_CODE = p_COMPANY_CODE
1350 and CURRENCY = nvl(p_CURRENCY,CURRENCY)
1351 and REF_CODE = p_ref_code -- 'IG_PRO1075'
1352 and MIN_AMT < p_BALANCE_OUT
1353 and MAX_AMT >= p_BALANCE_OUT);
1354 --
1355 --
1356 cursor DR_RANGE(p_party varchar2) is
1357 select nvl(INTEREST_RATE,0)
1358 from xtr_INTEREST_RATE_RANGES_v
1359 where PARTY_CODE = p_party
1360 and CURRENCY = nvl(p_CURRENCY,CURRENCY)
1361 and REF_CODE = p_ref_code -- 'IG_PRO1075'
1362 and MAX_AMT >= p_BALANCE_OUT
1363 and MIN_AMT <= 0
1364 and MIN_AMT < 0
1365 and EFFECTIVE_FROM_DATE = (select max(EFFECTIVE_FROM_DATE)
1366 from xtr_INTEREST_RATE_RANGES_v
1367 where EFFECTIVE_FROM_DATE <= p_TRANSFER_DATE
1368 and PARTY_CODE = p_party
1369 and CURRENCY = nvl(p_CURRENCY,CURRENCY)
1370 and REF_CODE = p_ref_code -- 'IG_PRO1075'
1371 and MAX_AMT >= p_BALANCE_OUT
1372 and MIN_AMT <= 0
1373 and MIN_AMT < 0 )
1374 order by MAX_AMT asc;
1375 --
1376
1377 cursor CR_RANGE(p_party varchar2) is
1378 select nvl(INTEREST_RATE,0)
1379 from xtr_INTEREST_RATE_RANGES_v
1380 where PARTY_CODE = p_party
1381 and CURRENCY = nvl(p_CURRENCY,CURRENCY)
1382 and REF_CODE = p_ref_code -- 'IG_PRO1075'
1383 and MIN_AMT <= p_BALANCE_OUT
1384 and MAX_AMT >= 0
1385 and MIN_AMT >= 0
1386 and EFFECTIVE_FROM_DATE = (select max(EFFECTIVE_FROM_DATE)
1387 from xtr_INTEREST_RATE_RANGES_v
1388 where EFFECTIVE_FROM_DATE <= p_TRANSFER_DATE
1389 and PARTY_CODE = p_party
1390 and CURRENCY = nvl(p_CURRENCY,CURRENCY)
1391 and REF_CODE = p_ref_code -- 'IG_PRO1075'
1392 and MIN_AMT <= p_BALANCE_OUT
1393 and MAX_AMT >= 0
1394 and MIN_AMT >= 0)
1395 order by MIN_AMT desc;
1396 --
1397
1398 BEGIN
1399
1400 p_interest_rate := null;
1401 p_warn_message := null;
1402
1403 if p_PRINCIPAL_ADJUST is not null then
1404 ----------------------------------------------------------------------------------
1405 -- Find Specific Rate
1406 ----------------------------------------------------------------------------------
1407 open FIND_SPECIFIC_RATE;
1408 fetch FIND_SPECIFIC_RATE INTO p_INTEREST_RATE;
1409 if FIND_SPECIFIC_RATE%NOTFOUND then
1410 close FIND_SPECIFIC_RATE;
1411 p_INTEREST_RATE := null;
1412 If p_block = 'IG' then
1413 if nvl(p_BALANCE_OUT,0)<=0 then
1414 open DR_RANGE(p_PARTY_CODE);
1415 fetch DR_RANGE into p_INTEREST_RATE;
1416 close DR_RANGE;
1417 else
1418 open CR_RANGE(p_PARTY_CODE);
1419 fetch CR_RANGE into p_INTEREST_RATE;
1420 close CR_RANGE;
1421 end if;
1422 End if;
1423
1424 ----------------------------------------------------------------------------------
1425 -- Find Global Rate
1426 ----------------------------------------------------------------------------------
1427 if p_INTEREST_RATE is null then
1428 open FIND_GLOBAL_RATE;
1429 fetch FIND_GLOBAL_RATE INTO p_INTEREST_RATE;
1430 if FIND_GLOBAL_RATE%NOTFOUND then
1431 close FIND_GLOBAL_RATE;
1432 p_INTEREST_RATE := null;
1433 If p_block = 'IG' then
1434 if nvl(p_BALANCE_OUT,0)<=0 then
1435 open DR_RANGE(p_COMPANY_CODE);
1436 fetch DR_RANGE into p_INTEREST_RATE;
1437 close DR_RANGE;
1438 else
1439 open CR_RANGE(p_COMPANY_CODE);
1440 fetch CR_RANGE into p_INTEREST_RATE;
1441 close CR_RANGE;
1442 end if;
1443 End if;
1444
1445 If p_block = 'IG' and p_INTEREST_RATE is null then
1446 p_warn_message := 'XTR_542'; -- Cannot find an Interest Rate for this Account
1447 p_interest_rate := null;
1448 End if;
1449
1450 else
1451 close FIND_GLOBAL_RATE;
1452 end if;
1453 end if;
1454
1455 else
1456 close FIND_SPECIFIC_RATE;
1457 end if;
1458 end if;
1459
1460 END FIND_SPECIFIC_GLOBAL_RATE;
1461
1462
1463 --------------------------------------------------------------------------------------------------------------
1464 -- 3800146 This procedure is used by Cash Leveling and ZBA processes to derive info for latest IG transaction
1465 --------------------------------------------------------------------------------------------------------------
1466 PROCEDURE DERIVE_LATEST_TRAN (p_company_code IN VARCHAR2,
1467 p_party_code IN VARCHAR2,
1468 p_currency IN VARCHAR2,
1469 p_transfer_date IN DATE,
1470 p_principal_adjust IN NUMBER,
1471 p_principal_action IN VARCHAR2,
1472 p_interest_rate OUT NOCOPY NUMBER,
1473 p_rounding_type OUT NOCOPY VARCHAR2,
1474 p_day_count_type OUT NOCOPY VARCHAR2,
1475 p_pricing_model OUT NOCOPY VARCHAR2,
1476 p_balance_out OUT NOCOPY NUMBER ) is
1477 -- currently only design as OUT param
1478
1479 ----------------------------------
1480 -- Find latest IG transaction info
1481 ----------------------------------
1482 cursor FIND_LATEST_TRAN is
1483 select INTEREST_RATE,
1484 ROUNDING_TYPE,
1485 DAY_COUNT_TYPE,
1486 PRICING_MODEL,
1487 BALANCE_OUT -- not BALANCE_BF
1488 ,deal_number, transaction_number -- not used
1489 from xtr_intergroup_transfers_v
1490 where company_code = p_company_code
1491 and party_code = p_party_code
1492 and currency = p_currency
1493 and transfer_date <= p_transfer_date
1494 order by TRANSFER_DATE desc, TRANSACTION_NUMBER desc;
1495
1496 l_prv_bal_out NUMBER;
1497 l_deal_no NUMBER;
1498 l_tran_no NUMBER;
1499
1500 BEGIN
1501
1502 p_interest_rate := null;
1503 p_rounding_type := null;
1504 p_day_count_type := null;
1505 p_pricing_model := null;
1506 p_balance_out := null;
1507 l_deal_no := null;
1508 l_tran_no := null;
1509
1510 -------------------------------------------
1511 -- Find latest tran details and BALANCE_BF
1512 -------------------------------------------
1513 open FIND_LATEST_TRAN;
1514 fetch FIND_LATEST_TRAN into p_interest_rate, p_rounding_type, p_day_count_type, p_pricing_model, l_prv_bal_out, l_deal_no, l_tran_no;
1515 if FIND_LATEST_TRAN%NOTFOUND then
1516 l_prv_bal_out := 0;
1517 end if;
1518 close FIND_LATEST_TRAN;
1519
1520 -------------------------------------------
1521 -- Calculate BALANCE_OUT
1522 -------------------------------------------
1523 if p_principal_adjust is NOT NULL then
1524 if p_PRINCIPAL_ACTION = 'PAY' then
1525 p_balance_out := l_prv_bal_out + p_principal_adjust;
1526 elsif p_PRINCIPAL_ACTION = 'REC' then
1527 p_balance_out := l_prv_bal_out - p_principal_adjust;
1528 end if;
1529 else
1530 p_balance_out := l_prv_bal_out;
1531 end if;
1532
1533 --dbms_output.put_line('wrap derive l_DEAL_NO = '||l_deal_no);
1534 --dbms_output.put_line('wrap derive l_TRAN_NO = '||l_tran_no);
1535 --dbms_output.put_line('wrap derive NEW BALANCE OUT = '||p_balance_out);
1536
1537 END DERIVE_LATEST_TRAN;
1538
1539
1540 -------------------------------------------------------------------------------------
1541 -- 3800146 API to default values for by ZBA and Cash Leveling for IG transactions
1542 -------------------------------------------------------------------------------------
1543 PROCEDURE IG_ZBA_CL_DEFAULT (p_company_code IN VARCHAR2,
1544 p_intercompany_code IN VARCHAR2,
1545 p_currency IN VARCHAR2,
1546 p_transfer_date IN DATE,
1547 p_transfer_amount IN NUMBER,
1548 p_action_code IN VARCHAR2,
1549 p_interest_rounding IN VARCHAR2,
1550 p_interest_includes IN VARCHAR2,
1551 p_company_pricing_model IN VARCHAR2,
1552 p_intercompany_pricing_model IN VARCHAR2,
1553 l_interest_rate OUT NOCOPY NUMBER,
1554 l_rounding_type OUT NOCOPY VARCHAR2,
1555 l_day_count_type OUT NOCOPY VARCHAR2,
1556 l_pricing_model OUT NOCOPY VARCHAR2,
1557 l_mirror_pricing_model OUT NOCOPY VARCHAR2)
1558 IS -- pass to IG API
1559
1560 l_balance_out NUMBER;
1561 l_dummy_num NUMBER;
1562 l_dummy_char1 xtr_intergroup_transfers.rounding_type%TYPE;
1563 l_dummy_char2 xtr_intergroup_transfers.day_count_type%TYPE;
1564 l_specific_global NUMBER;
1565 l_dummy_msg VARCHAR2(30);
1566 l_mirror_action VARCHAR2(3);
1567
1568 BEGIN
1569 --******************************************************************************************************
1570 -- Derive latest transaction details
1571 --******************************************************************************************************
1572 l_interest_rate := null;
1573 l_rounding_type := null;
1574 l_day_count_type := null;
1575 l_pricing_model := null;
1576 l_mirror_pricing_model := null;
1577
1578 DERIVE_LATEST_TRAN (p_company_code, -- Main IG
1579 p_intercompany_code,
1580 p_currency,
1581 p_transfer_date,
1582 p_transfer_amount,
1583 p_action_code,
1584 l_interest_rate,
1585 l_rounding_type,
1586 l_day_count_type,
1587 l_pricing_model,
1588 l_balance_out);
1589
1590 --dbms_output.put_line('wrap derive main l_pricing_model = '||l_pricing_model);
1591 --dbms_output.put_line('wrap derive main l_interest_rate = '||l_interest_rate);
1592 --dbms_output.put_line('wrap derive main l_rounding_type = '||l_rounding_type);
1593 --dbms_output.put_line('wrap derive main l_day_count_type = '||l_day_count_type);
1594 --dbms_output.put_line('wrap derive main l_balance_out = '||l_balance_out);
1595 --dbms_output.put_line('--------------------------------------------------------');
1596
1597 ------------------------------------------
1598 -- For new deals only
1599 ------------------------------------------
1600 if l_rounding_type is null then
1601 l_rounding_type := p_interest_rounding; -- from Cash Pool ID
1602 l_day_count_type := p_interest_includes; -- from Cash Pool ID
1603 l_pricing_model := p_company_pricing_model; -- from Cash Pool ID
1604 --dbms_output.put_line ('wrap Default Rounding, Day Count, Pricing');
1605 end if;
1606
1607 -------------------------
1608 -- Find transaction rates
1609 -------------------------
1610 l_specific_global := null;
1611
1612 FIND_SPECIFIC_GLOBAL_RATE (p_company_code,
1613 p_intercompany_code,
1614 p_currency,
1615 l_balance_out,
1616 p_transfer_amount,
1617 p_transfer_date,
1618 null, -- block name
1619 G_rate_ref_code, -- currently only 'IG_PRO1075' is used
1620 l_specific_global, -- currently only design as OUT param
1621 l_dummy_msg); -- no need message for ZBA/CL
1622 if l_specific_global is not null then -- Found specific/global rate
1623 l_interest_rate := l_specific_global;
1624 elsif l_interest_rate is null then -- Latest transaction does not exists
1625 l_interest_rate := 0;
1626 end if;
1627
1628 --dbms_output.put_line('wrap derive NEW Spec/Glob/Tran = '||l_interest_rate);
1629 --dbms_output.put_line('--------------------------------------------------------');
1630
1631 --******************************************************************************************************
1632 -- Find mirror deal's pricing model
1633 --******************************************************************************************************
1634 if XTR_IG_TRANSFERS_PKG.is_company(p_intercompany_code) then
1635 if p_action_code = 'PAY' then
1636 l_mirror_action := 'REC';
1637 else
1638 l_mirror_action := 'PAY';
1639 end if;
1640 DERIVE_LATEST_TRAN (p_intercompany_code, -- Mirror IG
1641 p_company_code,
1642 p_currency,
1643 p_transfer_date,
1644 p_transfer_amount,
1645 l_mirror_action,
1646 l_dummy_num, -- Same as main deal. Don't find rate again
1647 l_dummy_char1, -- Same as main deal. Don't find ROUNDING_TYPE
1648 l_dummy_char2, -- Same as main deal. Don't find DAY_COUNT_TYPE
1649 l_mirror_pricing_model,-- Latest transaction's PRICING_MODEL
1650 l_dummy_num); -- Don't find balance_out again
1651 if l_mirror_pricing_model is null then
1652 l_mirror_pricing_model := p_intercompany_pricing_model; -- from Cash Pool ID
1653 end if;
1654
1655 --dbms_output.put_line('wrap derive MORR l_mirror_pricing = '||l_mirror_pricing_model);
1656 --dbms_output.put_line('--------------------------------------------------------');
1657
1658 end if;
1659
1660 END IG_ZBA_CL_DEFAULT;
1661
1662 -------------------------------------------------------------------------------------
1663 -- 3800146 Check for IG duplicate for ZBA only
1664 -------------------------------------------------------------------------------------
1665 PROCEDURE CHK_ZBA_IG_DUPLICATE (
1666 p_company_code IN VARCHAR2,
1667 p_intercompany_code IN VARCHAR2,
1668 p_currency IN VARCHAR2,
1669 p_transfer_amount IN NUMBER,
1670 p_transfer_date IN DATE,
1671 p_action_code IN VARCHAR2,
1672 p_company_portfolio IN VARCHAR2,
1673 p_company_product_type IN VARCHAR2,
1674 p_intercompany_portfolio IN VARCHAR2,
1675 p_intercompany_product_type IN VARCHAR2,
1676 p_company_account_no IN VARCHAR2,
1677 p_party_account_no IN VARCHAR2,
1678 p_zba_duplicate OUT NOCOPY BOOLEAN) IS
1679
1680 l_deal_no NUMBER;
1681 l_tran_no NUMBER;
1682 l_mirror_action VARCHAR2(10);
1683
1684 -------------------------------------------------
1685 -- 3800146 Local procedure to Check IG Duplicate
1686 -------------------------------------------------
1687 PROCEDURE CHECK_IG_DUPLICATE (l_company_code IN VARCHAR2,
1688 l_party_code IN VARCHAR2,
1689 l_currency IN VARCHAR2,
1690 l_principal_adjust IN NUMBER,
1691 l_transfer_date IN DATE,
1692 l_principal_action IN VARCHAR2,
1693 l_portfolio IN VARCHAR2,
1694 l_product_type IN VARCHAR2,
1695 l_company_account_no IN VARCHAR2,
1696 l_party_account_no IN VARCHAR2,
1697 l_deal_num OUT NOCOPY NUMBER,
1698 l_tran_num OUT NOCOPY NUMBER,
1699 l_duplicate OUT NOCOPY BOOLEAN) IS
1700
1701 cursor find_ig_deal is
1702 select deal_number,
1703 transaction_number
1704 from xtr_intergroup_transfers
1705 where company_code = l_company_code
1706 and party_code = l_party_code
1707 and currency = l_currency
1708 and principal_adjust = l_principal_adjust
1709 and transfer_date = l_transfer_date
1710 and principal_action = l_principal_action
1711 and portfolio = l_portfolio
1712 and product_type = l_product_type
1713 and company_account_no = l_company_account_no
1714 and party_account_no = l_party_account_no
1715 and external_source = 'ZBA'
1716 order by transfer_date desc, transaction_number desc;
1717 /* Bug 4231200 Added the last AND condition. */
1718
1719 cursor chk_reconcile (l_amt_type VARCHAR2) is
1720 select 1
1721 from xtr_deal_date_amounts
1722 where deal_number = l_deal_num
1723 and transaction_number = l_tran_num
1724 and amount_type = l_amt_type
1725 and nvl(cashflow_amount,0) <> 0
1726 and reconciled_reference is null;
1727 /* Bug 4231200 Changed the last condition to null from not null. */
1728
1729 l_dummy NUMBER := null;
1730
1731 BEGIN
1732
1733 l_deal_num := null;
1734 l_tran_num := null;
1735 open find_ig_deal;
1736 fetch find_ig_deal into l_deal_num, l_tran_num;
1737 close find_ig_deal;
1738
1739 if l_deal_num is not null then
1740 open chk_reconcile ('PRINFLW');
1741 fetch chk_reconcile into l_dummy;
1742 if chk_reconcile%FOUND then
1743 l_duplicate := TRUE;
1744 else
1745 l_duplicate := FALSE;
1746 end if;
1747 close chk_reconcile;
1748 else
1749 l_duplicate := FALSE;
1750 end if;
1751
1752 END CHECK_IG_DUPLICATE;
1753
1754 BEGIN
1755 --******************************************************************************************************
1756 -- Checks main deal duplicate
1757 --******************************************************************************************************
1758 CHECK_IG_DUPLICATE (p_company_code,
1759 p_intercompany_code,
1760 p_currency,
1761 p_transfer_amount,
1762 p_transfer_date,
1763 p_action_code,
1764 p_company_portfolio,
1765 p_company_product_type,
1766 p_company_account_no,
1767 p_party_account_no,
1768 l_deal_no,
1769 l_tran_no,
1770 p_zba_duplicate);
1771
1772 if p_zba_duplicate then
1773 fnd_message.set_name ('XTR','XTR_DUPLICATE_ZBA_CL');
1774 fnd_message.set_token ('DEAL_TYPE','IG');
1775 fnd_message.set_token ('DEAL_NUMBER',l_deal_no);
1776 fnd_message.set_token ('TRANSACTION_NUMBER',l_tran_no);
1777 fnd_msg_pub.add;
1778 --dbms_output.put_line('wrap XTR_DUPLICATE_ZBA_CL = C1');
1779
1780 else
1781
1782 --******************************************************************************************************
1783 -- Checks Mirror company duplicate if party is a company
1784 --******************************************************************************************************
1785 if XTR_IG_TRANSFERS_PKG.is_company(p_intercompany_code) then
1786
1787 if p_action_code = 'PAY' then
1788 l_mirror_action := 'REC';
1789 else
1790 l_mirror_action := 'PAY';
1791 end if;
1792 CHECK_IG_DUPLICATE (p_intercompany_code,
1793 p_company_code,
1794 p_currency,
1795 p_transfer_amount,
1796 p_transfer_date,
1797 l_mirror_action,
1798 p_intercompany_portfolio,
1799 p_intercompany_product_type,
1800 p_party_account_no,
1801 p_company_account_no,
1802 l_deal_no,
1803 l_tran_no,
1804 p_zba_duplicate);
1805
1806 if p_zba_duplicate then
1807 fnd_message.set_name ('XTR','XTR_DUPLICATE_ZBA_CL');
1808 fnd_message.set_token ('DEAL_TYPE','IG');
1809 fnd_message.set_token ('DEAL_NUMBER',l_deal_no);
1810 fnd_message.set_token ('TRANSACTION_NUMBER',l_tran_no);
1811 fnd_msg_pub.add;
1812 --dbms_output.put_line('wrap XTR_DUPLICATE_ZBA_CL = C2');
1813 end if;
1814
1815 end if; -- mirror duplicate check
1816
1817 end if; -- main duplicate check
1818
1819 END CHK_ZBA_IG_DUPLICATE;
1820
1821
1822 -------------------------------------------------
1823 -- 3800146 Check for IAC duplicate for ZBA only
1824 -------------------------------------------------
1825 PROCEDURE CHK_ZBA_IAC_DUPLICATE (l_company_code IN VARCHAR2,
1826 l_transfer_amount IN NUMBER,
1827 l_transfer_date IN DATE,
1828 l_from_account_no IN VARCHAR2,
1829 l_to_account_no IN VARCHAR2,
1830 l_portfolio IN VARCHAR2,
1831 l_product_type IN VARCHAR2,
1832 l_duplicate OUT NOCOPY BOOLEAN) IS
1833
1834 l_tran_num NUMBER := null;
1835
1836 cursor chk_reconcile (l_deal_type VARCHAR2) is
1837 select A.transaction_number
1838 from xtr_interacct_transfers A,
1839 xtr_deal_date_amounts B,
1840 xtr_deal_date_amounts C
1841 where A.company_code = l_company_code
1842 and A.transfer_amount = l_transfer_amount
1843 and A.transfer_date = l_transfer_date
1844 and A.portfolio_code = l_portfolio
1845 and A.product_type = l_product_type
1846 and A.account_no_from = l_from_account_no
1847 and A.account_no_to = l_to_account_no
1848 and A.external_source = 'ZBA'
1849 and B.deal_number = 0
1850 and B.transaction_number = A.transaction_number
1851 and B.deal_type = l_deal_type
1852 and C.deal_number = 0
1853 and C.transaction_number = A.transaction_number
1854 and C.deal_type = l_deal_type
1855 and (B.reconciled_reference is null or C.reconciled_reference is null);
1856 /* Bug 4231200. Added the condition to check for external source
1857 and changed the last condition to null from not null. */
1858
1859 BEGIN
1860
1861 --dbms_output.put_line('ZBA duplicate = '||l_tran_num);
1862
1863 open chk_reconcile ('IAC');
1864 fetch chk_reconcile into l_tran_num;
1865 if chk_reconcile%FOUND then
1866 l_duplicate := TRUE;
1867 fnd_message.set_name ('XTR','XTR_DUPLICATE_ZBA_CL');
1868 fnd_message.set_token ('DEAL_TYPE','IAC');
1869 fnd_message.set_token ('DEAL_NUMBER',0);
1870 fnd_message.set_token ('TRANSACTION_NUMBER',l_tran_num);
1871 fnd_msg_pub.add;
1872 --dbms_output.put_line('wrap XTR_DUPLICATE_ZBA_CL = C2');
1873 else
1874 l_duplicate := FALSE;
1875 end if;
1876 close chk_reconcile;
1877
1878 END CHK_ZBA_IAC_DUPLICATE;
1879
1880
1881 -------------------------------------------------------------------------------------------------------------------
1882 -- 3800146 This procedure checks if a party is a company regardless of user access
1883 -------------------------------------------------------------------------------------------------------------------
1884 FUNCTION A_COMP (l_comp IN VARCHAR2) return boolean is
1885 cursor cur_com is
1886 select 1
1887 from XTR_PARTY_INFO
1888 where party_code = l_comp
1889 and party_type = 'C';
1890 l_dummy NUMBER;
1891 BEGIN
1892 open cur_com;
1893 fetch cur_com into l_dummy;
1894 if cur_com%NOTFOUND then
1895 close cur_com;
1896 return FALSE;
1897 end if;
1898 close cur_com;
1899 return TRUE;
1900 END A_COMP;
1901
1902 ----------------------------------------------------------------------------------------------------------
1903 -- 3800146 Derive IG values from Cash Pool
1904 ----------------------------------------------------------------------------------------------------------
1905 PROCEDURE IG_CASHPOOL_DERIVE (
1906 p_cash_pool_id IN NUMBER,
1907 p_company_bank_id IN NUMBER,
1908 p_party_bank_id IN NUMBER,
1909 p_currency IN VARCHAR2,
1910 p_company_code OUT NOCOPY VARCHAR2,
1911 p_company_account_no OUT NOCOPY VARCHAR2,
1912 p_company_rounding_type OUT NOCOPY VARCHAR2,
1913 p_company_day_count_type OUT NOCOPY VARCHAR2,
1914 p_company_pricing_model OUT NOCOPY VARCHAR2,
1915 p_company_product_type OUT NOCOPY VARCHAR2,
1916 p_company_portfolio OUT NOCOPY VARCHAR2,
1917 p_company_fund_limit OUT NOCOPY VARCHAR2,
1918 p_company_inv_limit OUT NOCOPY VARCHAR2,
1919 p_company_dealer OUT NOCOPY VARCHAR2,
1920 p_intercompany_code OUT NOCOPY VARCHAR2,
1921 p_intercompany_account_no OUT NOCOPY VARCHAR2,
1922 p_intercompany_pricing_model OUT NOCOPY VARCHAR2,
1923 p_intercompany_product_type OUT NOCOPY VARCHAR2,
1924 p_intercompany_portfolio OUT NOCOPY VARCHAR2,
1925 p_intercompany_fund_limit OUT NOCOPY VARCHAR2,
1926 p_intercompany_inv_limit OUT NOCOPY VARCHAR2,
1927 p_intercompany_dealer OUT NOCOPY VARCHAR2) IS
1928
1929 cursor get_company (l_cashpool_id NUMBER, l_ccy VARCHAR2) is
1930 select PARTY_CODE
1931 from ce_cashpools
1932 where cashpool_id = l_cashpool_id
1933 and currency_code = l_ccy;
1934
1935 cursor get_intercompany (l_cashpool_id NUMBER, l_party_bank_id NUMBER) is
1936 select PARTY_CODE
1937 from ce_cashpool_sub_accts
1938 where cashpool_id = l_cashpool_id
1939 and account_id = l_party_bank_id;
1940
1941 cursor get_attributes (l_cashpool_id NUMBER, l_intercomp_code VARCHAR2) is
1942 select rounding_type, day_count_type, pricing_model,
1943 product_type, portfolio, fund_limit_code,
1944 invest_limit_code, party_pricing_model, party_product_type,
1945 party_portfolio, party_fund_limit_code, party_invest_limit_code
1946 from xtr_cashpool_attributes
1947 where cashpool_id = l_cashpool_id
1948 and party_code = l_intercomp_code;
1949
1950 cursor get_acct_no (l_bank_id NUMBER, l_ccy VARCHAR2, l_party VARCHAR2) is
1951 select account_number
1952 from xtr_bank_accounts
1953 where party_code = l_party
1954 and currency = l_ccy
1955 and ce_bank_account_id = l_bank_id;
1956
1957 cursor get_user (p_fnd_user in number) is
1958 select dealer_code
1959 from xtr_dealer_codes_v
1960 where user_id = p_fnd_user;
1961
1962 BEGIN
1963
1964 open get_company (p_cash_pool_id, p_currency);
1965 fetch get_company into p_company_code;
1966 close get_company;
1967
1968 open get_intercompany (p_cash_pool_id, p_party_bank_id);
1969 fetch get_intercompany into p_intercompany_code;
1970 close get_intercompany;
1971
1972 open get_attributes(p_cash_pool_id, p_intercompany_code);
1973 fetch get_attributes into p_company_rounding_type, p_company_day_count_type, p_company_pricing_model,
1974 p_company_product_type, p_company_portfolio, p_company_fund_limit,
1975 p_company_inv_limit, p_intercompany_pricing_model, p_intercompany_product_type,
1976 p_intercompany_portfolio, p_intercompany_fund_limit, p_intercompany_inv_limit;
1977 close get_attributes;
1978
1979 open get_acct_no (p_company_bank_id, p_currency, p_company_code);
1980 fetch get_acct_no into p_company_account_no;
1981 close get_acct_no;
1982
1983 open get_acct_no (p_party_bank_id, p_currency, p_intercompany_code);
1984 fetch get_acct_no into p_intercompany_account_no;
1985 close get_acct_no;
1986
1987 open get_user(fnd_global.user_id);
1988 fetch get_user into p_company_dealer;
1989 close get_user;
1990
1991 p_intercompany_dealer := p_company_dealer;
1992
1993 END IG_CASHPOOL_DERIVE;
1994
1995 ----------------------------------------------------------------------------------------------------------
1996 -- 3800146 Derive IAC values from Cash Pool
1997 ----------------------------------------------------------------------------------------------------------
1998 PROCEDURE IAC_CASHPOOL_DERIVE(p_cash_pool_id IN NUMBER,
1999 p_from_bank_id IN NUMBER,
2000 p_to_bank_id IN NUMBER,
2001 -- p_transfer_date IN DATE,
2002 p_company_code OUT NOCOPY VARCHAR2,
2003 p_company_product_type OUT NOCOPY VARCHAR2,
2004 p_company_portfolio OUT NOCOPY VARCHAR2,
2005 p_account_no_from OUT NOCOPY VARCHAR2,
2006 p_account_no_to OUT NOCOPY VARCHAR2,
2007 p_currency OUT NOCOPY VARCHAR2) IS
2008
2009 cursor get_company (l_cashpool_id NUMBER) is
2010 select party_code,
2011 currency_code
2012 from ce_cashpools
2013 where cashpool_id = l_cashpool_id;
2014
2015 cursor get_attributes (l_cashpool_id NUMBER) is
2016 select iac_product_type,
2017 iac_portfolio
2018 from xtr_cashpool_attributes
2019 where cashpool_id = l_cashpool_id
2020 and product_type is null; -- Condition added Bug 4309871
2021
2022 cursor get_acct_no (l_bank_id NUMBER, l_ccy VARCHAR2, l_party VARCHAR2) is
2023 select account_number
2024 from xtr_bank_accounts
2025 where party_code = l_party
2026 and currency = l_ccy
2027 and ce_bank_account_id = l_bank_id;
2028
2029 l_ccy xtr_bank_accounts.currency%TYPE;
2030
2031 BEGIN
2032 open get_company (p_cash_pool_id);
2033 fetch get_company into p_company_code, p_currency;
2034 close get_company;
2035
2036 open get_attributes(p_cash_pool_id);
2037 fetch get_attributes into p_company_product_type, p_company_portfolio;
2038 close get_attributes;
2039
2040 open get_acct_no (p_from_bank_id, p_currency, p_company_code);
2041 fetch get_acct_no into p_account_no_from;
2042 close get_acct_no;
2043
2044 open get_acct_no (p_to_bank_id, p_currency, p_company_code);
2045 fetch get_acct_no into p_account_no_to;
2046 close get_acct_no;
2047
2048 END IAC_CASHPOOL_DERIVE;
2049
2050
2051 -------------------------------------------------------------------------------------------------------------------
2052 -- 3800146 This procedure derives the validate and settlement status of IAC when calling from ZBA and Cash Leveling
2053 -------------------------------------------------------------------------------------------------------------------
2054 PROCEDURE SET_IAC_VALIDATE_SETTLE(p_product IN VARCHAR2,
2055 p_dealer IN VARCHAR2,
2056 p_called_by_flag IN VARCHAR2, -- null for form
2057 p_auth_validate OUT NOCOPY BOOLEAN,
2058 p_auth_settlement OUT NOCOPY BOOLEAN) is
2059
2060 ---------------------------------------------------
2061 -- for IAC, get PRO_PARAM for auto-auth-settled IAC
2062 ---------------------------------------------------
2063 cursor c_param (l_name VARCHAR2) is
2064 select param_value
2065 from xtr_pro_param
2066 where param_name = l_name;
2067
2068 l_validate_deal VARCHAR2(1);
2069 l_validate_iac VARCHAR2(1);
2070 l_iac_auto_settle VARCHAR2(1);
2071
2072 FUNCTION user_authority (l_product VARCHAR2, l_dealer VARCHAR2) return BOOLEAN is
2073 Cursor user_auth is
2074 SELECT VALIDATION_AUTHORIZED
2075 FROM XTR_AUTH_TYPE_SUBTYPE_PROD_V
2076 WHERE USER_NAME = l_DEALER
2077 AND DEAL_TYPE = 'IAC'
2078 AND DEAL_SUBTYPE = 'FIRM'
2079 AND PRODUCT_TYPE = l_PRODUCT;
2080 L_dummy VARCHAR2(1);
2081 BEGIN
2082 Open user_auth;
2083 Fetch user_auth into l_dummy;
2084 If user_auth%FOUND and l_dummy = 'Y' then
2085 Close user_auth;
2086 Return TRUE;
2087 End if;
2088 Close user_auth;
2089 Return FALSE;
2090 END;
2091
2092 BEGIN
2093 open c_param ('DUAL_AUTHORISE');
2094 fetch c_param into l_validate_deal;
2095 close c_param;
2096
2097 open c_param ('DUAL_AUTHORISE_IAC');
2098 fetch c_param into l_validate_iac;
2099 close c_param;
2100
2101 open c_param ('IAC_AUTO_SETTLE');
2102 fetch c_param into l_iac_auto_settle;
2103 close c_param;
2104
2105 ----------------------------
2106 -- Initialise
2107 ----------------------------
2108 p_auth_validate := FALSE;
2109 p_auth_settlement := FALSE;
2110
2111 IF nvl(p_called_by_flag,'N') = 'Z' THEN
2112 ---------------------------------------------
2113 -- Calling from ZBA is always settled for IAC
2114 ---------------------------------------------
2115 p_auth_settlement := TRUE;
2116
2117 ------------------------------------------------------------
2118 -- Calling from ZBA doesnot require user access to validate
2119 ------------------------------------------------------------
2120 if nvl(l_validate_deal,'N') = 'Y' and nvl(l_validate_iac,'N') = 'Y' then
2121 p_auth_validate := TRUE;
2122 end if;
2123
2124 ELSE
2125 ----------------------------------------------------------
2126 -- Calling from Cash Leveling and IAC form is conditional
2127 ----------------------------------------------------------
2128 if nvl(l_validate_iac,'N') = 'Y' then
2129 if nvl(l_iac_auto_settle,'N') = 'Y' and user_authority(p_PRODUCT, p_DEALER) then
2130 p_auth_validate := TRUE;
2131 p_auth_settlement := TRUE;
2132 end if;
2133 else
2134 if nvl(l_iac_auto_settle,'N') = 'Y' then
2135 p_auth_settlement := TRUE;
2136 end if;
2137 end if;
2138
2139 END IF;
2140
2141 END SET_IAC_VALIDATE_SETTLE;
2142
2143
2144 ------------------------------------------------------------------------------------------------------------------------------------------
2145 -- 3800146 Main IG API called by ZBA and Cash Leveling
2146 ------------------------------------------------------------------------------------------------------------------------------------------
2147 PROCEDURE IG_GENERATION(p_cash_pool_id IN NUMBER,
2148 p_company_bank_id IN NUMBER,
2149 p_party_bank_id IN NUMBER,
2150 p_currency IN VARCHAR2,
2151 p_transfer_date IN DATE,
2152 p_transfer_amount IN NUMBER,
2153 p_action_code IN VARCHAR2,
2154 p_accept_limit_error IN VARCHAR2, -- see Override_limit on IG p.40
2155 p_deal_no OUT NOCOPY NUMBER,
2156 p_tran_no OUT NOCOPY NUMBER,
2157 p_mirror_deal_no OUT NOCOPY NUMBER,
2158 p_mirror_tran_no OUT NOCOPY NUMBER,
2159 p_success_flag OUT NOCOPY VARCHAR2,
2160 p_process_flag IN VARCHAR2) is
2161
2162 l_settled VARCHAR2(1);
2163 L_External_IG xtr_deals_interface%rowtype;
2164 user_error BOOLEAN;
2165 mandatory_error BOOLEAN;
2166 validation_error BOOLEAN;
2167 limit_error BOOLEAN;
2168 l_ext_source VARCHAR2(30);
2169
2170 l_company_dealer xtr_intergroup_transfers.dealer_code%TYPE;
2171 l_company_code xtr_intergroup_transfers.company_code%TYPE;
2172 l_company_account_no xtr_intergroup_transfers.company_account_no%TYPE;
2173 l_company_pricing_model xtr_intergroup_transfers.pricing_model%TYPE;
2174 l_company_product_type xtr_intergroup_transfers.product_type%TYPE;
2175 l_company_portfolio xtr_intergroup_transfers.portfolio%TYPE;
2176 l_company_fund_limit xtr_intergroup_transfers.limit_code%TYPE;
2177 l_company_inv_limit xtr_intergroup_transfers.limit_code%TYPE;
2178 l_company_rounding_type xtr_intergroup_transfers.rounding_type%TYPE;
2179 l_company_day_count_type xtr_intergroup_transfers.day_count_type%TYPE;
2180
2181 l_intercompany_code xtr_intergroup_transfers.party_code%TYPE;
2182 l_intercompany_account_no xtr_intergroup_transfers.party_account_no%TYPE;
2183 l_intercompany_pricing_model xtr_intergroup_transfers.pricing_model%TYPE;
2184 l_intercompany_product_type xtr_intergroup_transfers.product_type%TYPE;
2185 l_intercompany_portfolio xtr_intergroup_transfers.portfolio%TYPE;
2186 l_intercompany_fund_limit xtr_intergroup_transfers.limit_code%TYPE;
2187 l_intercompany_inv_limit xtr_intergroup_transfers.limit_code%TYPE;
2188 l_intercompany_dealer xtr_intergroup_transfers.dealer_code%TYPE;
2189
2190 l_interest_rate NUMBER;
2191 l_rounding_type xtr_intergroup_transfers.rounding_type%TYPE;
2192 l_day_count_type xtr_intergroup_transfers.day_count_type%TYPE;
2193 l_pricing_model xtr_intergroup_transfers.pricing_model%TYPE;
2194 l_mirror_pricing_model xtr_intergroup_transfers.pricing_model%TYPE;
2195
2196
2197 /*---------------------------------------------------------------------------------------------------------------------------------------------
2198 1) Get parameters from CE
2199 - derive ROUNDING, INCLUDES, PRICING MODEL, PRODUCT, PORTFOLIO, LIMITS, ACCOUNTS from CASH_POOL_ID so must be valid.
2200 2) Derive actual values before the creation of transaction BEFORE VALIDATION (need to check authorised values, etc)
2201 - RATE, INTEREST ROUNDING, INTEREST INCLUDES, PRICING MODEL (calculated values must come after this)
2202 3) Validate from IG API - do not do standard duplicate check from IG API
2203 4) Duplicate check - must wait for actual derived values.
2204 5) Other calculated value - balance, hce amount, limit utilisation etc must come last.
2205 ---------------------------------------------------------------------------------------------------------------------------------------------*/
2206 BEGIN
2207
2208 p_deal_no := null;
2209 p_tran_no := null;
2210 p_mirror_deal_no := null;
2211 p_mirror_tran_no := null;
2212 p_success_flag := null;
2213
2214 fnd_msg_pub.initialize;
2215
2216 --########################################################################################################################
2217 -- Derive the following with Cash Pool ID before calling default, etc:
2218 IG_CASHPOOL_DERIVE(p_cash_pool_id,
2219 p_company_bank_id,
2220 p_party_bank_id,
2221 p_currency,
2222 l_company_code,
2223 l_company_account_no,
2224 l_company_rounding_type,
2225 l_company_day_count_type,
2226 l_company_pricing_model,
2227 l_company_product_type,
2228 l_company_portfolio,
2229 l_company_fund_limit,
2230 l_company_inv_limit,
2231 l_company_dealer,
2232 l_intercompany_code,
2233 l_intercompany_account_no,
2234 l_intercompany_pricing_model,
2235 l_intercompany_product_type,
2236 l_intercompany_portfolio,
2237 l_intercompany_fund_limit,
2238 l_intercompany_inv_limit,
2239 l_intercompany_dealer);
2240 --########################################################################################################################
2241
2242 --------------------------------------------------------------------------------------------------------------------------
2243 -- 3800146
2244 -- 1) Derive Interest Rounding, Day Count Type and Pricing Model from EXISTING TRANSACTIONS
2245 -- 2) Before Validation and ANY calculation. (eg. if Pricing Model is unauthorised, then Error.)
2246 -- a) Rate b) Rounding c) Day Count d) Pricing Model e) Mirror Pricing
2247 --------------------------------------------------------------------------------------------------------------------------
2248 IG_ZBA_CL_DEFAULT (l_company_code, -- from Pool ID
2249 l_intercompany_code, -- from Pool ID
2250 p_currency,
2251 p_transfer_date,
2252 p_transfer_amount,
2253 p_action_code,
2254 l_company_rounding_type, -- from Pool ID
2255 l_company_day_count_type, -- from Pool ID
2256 l_company_pricing_model, -- from Pool ID
2257 l_intercompany_pricing_model, -- from Pool ID
2258 l_interest_rate, -- OUT S/G or Latest or Zero
2259 l_rounding_type, -- OUT Latest or Pool ID
2260 l_day_count_type, -- OUT Latest or Pool ID
2261 l_pricing_model, -- OUT Latest or Pool ID
2262 l_mirror_pricing_model); -- OUT Latest or Pool ID
2263 --------------------------------------------------------------------------------------------------------------------------
2264
2265 --------------------------------------------------------------
2266 -- Calling from ZBA is always settled
2267 --------------------------------------------------------------
2268 if p_process_flag = 'Z' then
2269 l_settled := 'Y';
2270 l_ext_source := 'ZBA'; -- called by ZBA
2271 elsif p_process_flag = 'L' then
2272 l_settled := 'N'; -- Always unsettled for IG
2273 l_ext_source := 'CL'; -- called by Cash Leveling
2274 else
2275 l_settled := 'N'; -- Always unsettled for IG
2276 end if;
2277
2278 --------------------------------------------------------------
2279 -- Set attributes of xtr_deals_interface
2280 --------------------------------------------------------------
2281 L_External_IG.external_deal_id := '0';
2282 L_External_IG.deal_type := 'IG';
2283 L_External_IG.date_a := p_transfer_date;
2284 L_External_IG.company_code := l_company_code;
2285 L_External_IG.cparty_code := l_intercompany_code;
2286 L_External_IG.currency_a := p_currency;
2287 L_External_IG.account_no_a := l_company_account_no;
2288 L_External_IG.account_no_b := l_intercompany_account_no;
2289 L_External_IG.action_code := p_action_code;
2290 L_External_IG.amount_a := p_transfer_amount;
2291 L_External_IG.rate_a := l_interest_rate; -- DERIVED
2292 L_External_IG.product_type := l_company_product_type;
2293 L_External_IG.portfolio_code := l_company_portfolio;
2294 L_External_IG.limit_code := l_company_fund_limit;
2295 L_External_IG.limit_code_b := l_company_inv_limit;
2296 L_External_IG.override_limit := p_accept_limit_error;
2297 L_External_IG.comments := null;
2298 L_External_IG.attribute_category := null;
2299 L_External_IG.attribute1 := null;
2300 L_External_IG.attribute2 := null;
2301 L_External_IG.attribute3 := null;
2302 L_External_IG.attribute4 := null;
2303 L_External_IG.attribute5 := null;
2304 L_External_IG.attribute6 := null;
2305 L_External_IG.attribute7 := null;
2306 L_External_IG.attribute8 := null;
2307 L_External_IG.attribute9 := null;
2308 L_External_IG.attribute10 := null;
2309 L_External_IG.attribute11 := null;
2310 L_External_IG.attribute12 := null;
2311 L_External_IG.attribute13 := null;
2312 L_External_IG.attribute14 := null;
2313 L_External_IG.attribute15 := null;
2314 L_External_IG.Rounding_Type := l_rounding_type; -- DERIVED
2315 L_External_IG.Day_Count_Type := l_day_count_type; -- DERIVED
2316 L_External_IG.pricing_model := l_pricing_model; -- DERIVED
2317 L_External_IG.Original_Amount := 0; -- This will be calculated in IG API's CALC_DETAILS
2318 L_External_IG.Deal_Linking_Code := null;
2319 L_External_IG.Dealer_Code := l_company_dealer;
2320 L_External_IG.External_Source := l_ext_source ;
2321 L_External_IG.mirror_limit_code_fund := l_intercompany_fund_limit;
2322 L_External_IG.mirror_limit_code_invest := l_intercompany_inv_limit;
2323 L_External_IG.mirror_portfolio_code := l_intercompany_portfolio;
2324 L_External_IG.mirror_product_type := l_intercompany_product_type;
2325 L_External_IG.mirror_pricing_model := l_mirror_pricing_model; -- DERIVED
2326 L_External_IG.mirror_dealer_code := l_intercompany_dealer;
2327 L_External_IG.Settlement_Flag := l_settled;
2328 --dbms_output.put_line('wrap pass Pricing = '||L_External_IG.pricing_model);
2329 --dbms_output.put_line('wrap pass Interest Rate = '||L_External_IG.rate_a);
2330 --dbms_output.put_line('wrap pass Rounding_Type = '||L_External_IG.Rounding_Type);
2331 --dbms_output.put_line('wrap pass Day_Count_Type = '||L_External_IG.Day_Count_Type);
2332 --dbms_output.put_line('wrap pass Product_type = '||L_External_IG.product_type);
2333 --dbms_output.put_line('wrap pass Mirror Pricing = '||L_External_IG.Mirror_pricing_model);
2334 --dbms_output.put_line('-----------------------------------------------------');
2335
2336 ----------------------------------------------------------
2337 -- Call xtrimigb.pls
2338 ----------------------------------------------------------
2339 user_error := FALSE;
2340 mandatory_error := FALSE;
2341 validation_error := FALSE;
2342 limit_error := FALSE;
2343
2344 XTR_IG_TRANSFERS_PKG.TRANSFER_IG_DEALS(L_External_IG,
2345 null, -- G_ig_source: must be null
2346 user_error,
2347 mandatory_error,
2348 validation_error,
2349 limit_error,
2350 p_deal_no,
2351 p_tran_no, -- new
2352 p_mirror_deal_no, -- new
2353 p_mirror_tran_no); -- new
2354
2355 --dbms_output.put_line('-----------------------------------------------------');
2356 --if user_error then dbms_output.put_line('wrap User error'); end if;
2357 --if mandatory_error then dbms_output.put_line('wrap Mandatory error'); end if;
2358 --if validation_error then dbms_output.put_line('wrap Validation error'); end if;
2359 --if limit_error then dbms_output.put_line('wrap Limit error'); end if;
2360
2361 if user_error or mandatory_error or validation_error or limit_error then
2362
2363 p_success_flag := 'N';
2364 p_deal_no := null;
2365 p_tran_no := null;
2366 p_mirror_deal_no := null;
2367 p_mirror_tran_no := null;
2368
2369 else
2370
2371 commit;
2372 p_success_flag := 'Y';
2373
2374 end if;
2375
2376 END IG_GENERATION;
2377
2378 ------------------------------------------------------------------------------------------------------------------------------------------
2379 -- 3800146 Main IAC API called by ZBA and Cash Leveling
2380 ------------------------------------------------------------------------------------------------------------------------------------------
2381 PROCEDURE IAC_GENERATION(p_cash_pool_id IN NUMBER,
2382 p_from_bank_acct_id IN NUMBER,
2383 p_to_bank_acct_id IN NUMBER,
2384 p_transfer_date IN DATE,
2385 p_transfer_amount IN NUMBER,
2386 p_tran_no OUT NOCOPY NUMBER,
2387 p_success_flag OUT NOCOPY VARCHAR2,
2388 p_process_flag IN VARCHAR2) is
2389
2390 cursor get_user (p_fnd_user in number) is
2391 select dealer_code
2392 from xtr_dealer_codes_v
2393 where user_id = p_fnd_user;
2394
2395 l_Ext_IAC xtr_interacct_transfers%rowtype;
2396 l_dealer_code xtr_interacct_transfers.dealer_code%TYPE;
2397 l_company_code xtr_interacct_transfers.company_code%TYPE;
2398 l_company_product_type xtr_interacct_transfers.product_type%TYPE;
2399 l_company_portfolio xtr_interacct_transfers.portfolio_code%TYPE;
2400 l_account_no_from xtr_interacct_transfers.account_no_from%TYPE;
2401 l_account_no_to xtr_interacct_transfers.account_no_to%TYPE;
2402 l_currency xtr_interacct_transfers.currency%TYPE;
2403 l_auto_validation BOOLEAN;
2404 l_auto_settlement BOOLEAN;
2405 user_error BOOLEAN;
2406 mandatory_error BOOLEAN;
2407 validation_error BOOLEAN;
2408 l_ext_source VARCHAR2(30);
2409 l_sysdate DATE;
2410
2411 BEGIN
2412
2413 p_tran_no := null;
2414 p_success_flag := null;
2415 l_sysdate := trunc(sysdate);
2416
2417 fnd_msg_pub.initialize;
2418
2419 ----------------------------------------------
2420 -- 1) Set the process flag
2421 ----------------------------------------------
2422 if p_process_flag = 'Z' then
2423 l_ext_source := 'ZBA'; -- called by ZBA
2424 elsif p_process_flag = 'L' then
2425 l_ext_source := 'CL'; -- called by Cash Leveling
2426 else
2427 p_success_flag := 'N';
2428 end if;
2429
2430 ----------------------------------------------
2431 -- 2) Derive from Cashpool
2432 ----------------------------------------------
2433 IF nvl(p_success_flag,'Y') = 'Y' then
2434 IAC_CASHPOOL_DERIVE(p_cash_pool_id,
2435 p_from_bank_acct_id,
2436 p_to_bank_acct_id,
2437 -- p_transfer_date,
2438 l_company_code,
2439 l_company_product_type,
2440 l_company_portfolio,
2441 l_account_no_from,
2442 l_account_no_to,
2443 l_currency);
2444 END IF;
2445
2446
2447 -------------------------------------------------------------------
2448 -- 3) Set other attributes of XTR_INTERACCT_TRANSFERS
2449 -------------------------------------------------------------------
2450 l_Ext_IAC.deal_type := 'IAC';
2451 l_Ext_IAC.deal_subtype := 'FIRM';
2452 l_Ext_IAC.status_code := 'CURRENT';
2453 l_Ext_IAC.transfer_date := p_transfer_date;
2454 l_Ext_IAC.transfer_amount := p_transfer_amount;
2455 l_Ext_IAC.company_code := l_company_code;
2456 l_Ext_IAC.currency := l_currency;
2457 l_Ext_IAC.account_no_from := l_account_no_from;
2458 l_Ext_IAC.account_no_to := l_account_no_to;
2459 l_Ext_IAC.product_type := l_company_product_type;
2460 l_Ext_IAC.portfolio_code := l_company_portfolio;
2461 l_Ext_IAC.External_Source := l_ext_source ;
2462
2463 ------------------------------------------------------------------
2464 -- 4) Set DEALER_CODE of XTR_INTERACCT_TRANSFERS
2465 ------------------------------------------------------------------
2466 open get_user(fnd_global.user_id);
2467 fetch get_user into l_Ext_IAC.dealer_code;
2468 close get_user;
2469
2470 ------------------------------------------------------------------
2471 -- 5) Set DEAL_DATE of XTR_INTERACCT_TRANSFERS
2472 ------------------------------------------------------------------
2473 if l_Ext_IAC.Transfer_Date > l_sysdate then
2474 l_Ext_IAC.deal_date := l_sysdate;
2475 else
2476 l_Ext_IAC.deal_date := l_Ext_IAC.Transfer_Date;
2477 end if;
2478
2479 ------------------------------------------------------------------
2480 -- 6) Set Validation + Settlement flag of XTR_INTERACCT_TRANSFERS
2481 ------------------------------------------------------------------
2482 SET_IAC_VALIDATE_SETTLE(l_Ext_IAC.product_type,
2483 l_Ext_IAC.dealer_code,
2484 p_process_flag,
2485 l_auto_validation,
2486 l_auto_settlement);
2487
2488 --dbms_output.put_line('wrap pass Product_type = '||l_Ext_IAC.product_type);
2489 --dbms_output.put_line('----------------------------------------------------------');
2490
2491 ----------------------------------------------------------
2492 -- Call xtrimigb.pls
2493 ----------------------------------------------------------
2494 user_error := FALSE;
2495 mandatory_error := FALSE;
2496 validation_error := FALSE;
2497
2498 XTR_IAC_TRANSFERS_PKG.TRANSFER_IAC_DEALS(l_Ext_IAC,
2499 l_auto_validation,
2500 l_auto_settlement,
2501 user_error,
2502 mandatory_error,
2503 validation_error,
2504 p_tran_no); -- new
2505 --dbms_output.put_line('-----------------------------------------------------');
2506
2507 --if user_error then dbms_output.put_line('wrap User error'); end if;
2508 --if mandatory_error then dbms_output.put_line('wrap Mandatory error'); end if;
2509 --if validation_error then dbms_output.put_line('wrap Validation error'); end if;
2510
2511 if user_error or mandatory_error or validation_error then
2512
2513 p_success_flag := 'N';
2514 p_tran_no := null;
2515
2516 else
2517
2518 commit;
2519 p_success_flag := 'Y';
2520
2521 end if;
2522
2523 END IAC_GENERATION;
2524
2525 END XTR_WRAPPER_API_P;