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