[Home] [Help]
PACKAGE BODY: APPS.XTR_IAC_TRANSFERS_PKG
Source
1 PACKAGE BODY XTR_IAC_TRANSFERS_PKG AS
2 /* $Header: xtrimiab.pls 120.2 2008/01/25 07:33:44 srsampat ship $ */
3
4
5 -------------------------------------------------------------------------------------------------------------------
6 Procedure Log_IAC_Errors(p_Error_Code In Varchar2,
7 p_Field_Name In Varchar2) is
8 -------------------------------------------------------------------------------------------------------------------
9
10 cursor c_text is
11 select text
12 from xtr_sys_languages_vl
13 where item_name = p_Field_Name;
14
15 p_text xtr_sys_languages_vl.text%TYPE;
16
17 begin
18
19 if p_Error_Code = 'XTR_MANDATORY' then
20 -------------------------------
21 -- Get the dynamic prompt text.
22 -------------------------------
23 open c_text;
24 fetch c_text into p_text;
25 close c_text;
26
27 FND_MESSAGE.Set_Name('XTR','XTR_MANDATORY_FIELD'); -- AW new message
28 FND_MESSAGE.Set_Token('FIELD', p_text);
29
30 else
31 FND_MESSAGE.Set_Name('XTR', p_Error_Code);
32 end if;
33
34 --*****************************************************************************************************************
35 -- Populate message to stack for CE to retrieve
36 --*****************************************************************************************************************
37 fnd_msg_pub.add;
38 --dbms_output.put_line('imIAC Error = '|| p_error_code||' : '||p_field_name);
39
40 end;
41
42 -----------------------------------------------------------------------------------------------------
43 function VALID_CURRENCY(p_curr IN VARCHAR2) return boolean is
44 -----------------------------------------------------------------------------------------------------
45 cursor curs(c_curr IN VARCHAR2) is
46 select 1
47 from XTR_MASTER_CURRENCIES
48 where currency = c_curr;
49
50 l_dummy NUMBER;
51
52 begin
53 open curs (p_curr);
54 fetch curs into l_dummy;
55 if curs%NOTFOUND then
56 close curs;
57 return(FALSE);
58 else
59 close curs;
60 return(TRUE);
61 end if;
62
63 exception
64 when others then
65 if curs%ISOPEN then close curs; end if;
66 return(FALSE);
67 end;
68
69
70
71 -----------------------------------------------------------------------------------------------------
72 function VALID_PARTY_ACCT(p_party IN VARCHAR2,
73 p_party_acct IN VARCHAR2,
74 p_curr IN VARCHAR2) return varchar2 is
75 -----------------------------------------------------------------------------------------------------
76 cursor curs(c_party IN VARCHAR2,
77 c_curr IN VARCHAR2,
78 c_party_acct IN VARCHAR2) is
79 select a.BANK_CODE,
80 a.AUTHORISED
81 from XTR_BANK_ACCOUNTS_V a,
82 XTR_PARTY_INFO b
83 where a.party_code = c_party
84 and a.currency = c_curr
85 and nvl(a.setoff_account_yn,'N') <> 'Y'
86 and a.account_number = c_party_acct
87 and a.bank_code = b.party_code
88 order by nvl(a.authorised,'N') desc;
89
90 l_bank_code xtr_bank_accounts.bank_code%TYPE;
91 l_auth VARCHAR2(1);
92
93 begin
94 open curs (p_party, p_curr, p_party_acct);
95 fetch curs into l_bank_code, l_auth;
96 if curs%NOTFOUND then
97 close curs;
98 FND_MESSAGE.Set_Name('XTR', 'XTR_BANK_CODE_MISSING'); -- The bank account ACCOUNT_NUMBER is missing a bank code.
99 FND_MESSAGE.SET_TOKEN('ACCOUNT_NUMBER', p_party_acct);
100 fnd_msg_pub.add;
101 --dbms_output.put_line('imIAC Error = XTR_BANK_CODE_MISSING = '|| p_party_acct);
102 return(NULL);
103 else
104 close curs;
105 if nvl(l_auth,'N') = 'N' then
106 FND_MESSAGE.Set_Name('XTR', 'XTR_BANK_BAL_ACCT_AUTH'); -- The bank account P_ACCOUNT is not authorised.
107 FND_MESSAGE.SET_TOKEN('P_ACCOUNT', p_party_acct);
108 fnd_msg_pub.add;
109 --dbms_output.put_line('imIAC Error = XTR_BANK_BAL_ACCT_AUTH = '|| p_party_acct);
110 return(NULL);
111 else
112 return(l_bank_code);
113 end if;
114 end if;
115
116 exception
117 when others then
118 if curs%ISOPEN then close curs; end if;
119 return(NULL);
120 end;
121
122
123 -----------------------------------------------------------------------------------------------------
124 function VALID_PRODUCT(p_product IN VARCHAR2) return boolean is
125 -----------------------------------------------------------------------------------------------------
126 cursor curs(c_product in varchar2) is
127 select 1
128 from XTR_AUTH_PRODUCT_TYPES_V
129 where deal_type = C_iac_type
130 and Product_Type = c_product;
131
132 l_dummy NUMBER;
133
134 begin
135 open curs (p_product);
136 fetch curs into l_dummy;
137 if curs%NOTFOUND then
138 close curs;
139 return(FALSE);
140 else
141 close curs;
142 return(TRUE);
143 end if;
144
145 exception
146 when others then
147 if curs%ISOPEN then close curs; end if;
148 return(FALSE);
149 end;
150
151
152 -----------------------------------------------------------------------------------------------------
153 function VALID_PORTFOLIO(p_comp IN VARCHAR2,
154 p_portfolio IN VARCHAR2) return boolean is
155 -----------------------------------------------------------------------------------------------------
156 cursor curs(c_comp in varchar2, c_portfolio in varchar2) is
157 select 1
158 from XTR_PORTFOLIOS_V
159 where company_code = c_comp
160 and nvl(external_portfolio,'N') <> 'Y'
161 and portfolio = c_portfolio;
162
163 l_dummy NUMBER;
164
165 begin
166 open curs (p_comp, p_portfolio);
167 fetch curs into l_dummy;
168 if curs%NOTFOUND then
169 close curs;
170 return(FALSE);
171 else
172 close curs;
173 return(TRUE);
174 end if;
175
176 exception
177 when others then
178 if curs%ISOPEN then close curs; end if;
179 return(FALSE);
180 end;
181
182
183 /*--------------------------------------------------------------------------------*/
184 FUNCTION VALID_DEALER_CODE(p_dealer_code IN VARCHAR2) return BOOLEAN is
185 /*--------------------------------------------------------------------------------*/
186 l_temp NUMBER;
187 BEGIN
188 BEGIN
189 select 1
190 into l_temp
191 from xtr_dealer_codes_v
192 where dealer_code = p_dealer_code
193 and rownum = 1;
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 return(FALSE);
197 END;
198 return(TRUE);
199 END VALID_DEALER_CODE;
200
201
202 -----------------------------------------------------------------------------------------------------
203 function VALID_TRANSFER_AMOUNT(p_value IN NUMBER) return boolean is
204 -----------------------------------------------------------------------------------------------------
205 begin
206 if nvl(p_value,0) < 0 then
207 return(FALSE);
208 else
209 return(TRUE);
210 end if;
211 end;
212
213 -----------------------------------------------------------------------------------------------------
214 function VALID_DEAL_DATE(p_value IN DATE) return boolean is
215 -----------------------------------------------------------------------------------------------------
216 begin
217 if nvl(p_value,sysdate) > G_iac_date then
218 return(FALSE);
219 else
220 return(TRUE);
221 end if;
222 end;
223
224
225 -----------------------------------------------------------------------------------------------------
226 procedure CHECK_MANDATORY_FIELDS(ARec_IAC IN xtr_interacct_transfers%rowtype,
227 p_error OUT NOCOPY BOOLEAN) is
228 -----------------------------------------------------------------------------------------------------
229 begin
230
231 p_error := FALSE;
232
233 if ARec_IAC.company_code is null then
234 Log_IAC_errors('XTR_MANDATORY','CG$CTRL.COMPANY_CODE');
235 p_error := TRUE;
236 end if;
237
238 if ARec_IAC.transfer_date is null then
239 Log_IAC_errors('XTR_MANDATORY','CG$CTRL.AMOUNT_DATE');
240 p_error := TRUE;
241 end if;
242
243 if ARec_IAC.account_no_from is null then
244 Log_IAC_errors('XTR_MANDATORY','TRANS.ACCOUNT_NAME_FROM');
245 p_error := TRUE;
246 end if;
247
248
249 if ARec_IAC.account_no_to is null then
250 Log_IAC_errors('XTR_MANDATORY','TRANS.ACCOUNT_NAME_TO');
251 p_error := TRUE;
252 end if;
253
254 if ARec_IAC.transfer_amount is null then
255 Log_IAC_errors('XTR_MANDATORY','TRANS.PAY_AMOUNT');
256 p_error := TRUE;
257 end if;
258
259 if ARec_IAC.product_type is null then
260 Log_IAC_errors('XTR_MANDATORY','TRANS.PRODUCT_TYPE');
261 p_error := TRUE;
262 end if;
263
264
265 if ARec_IAC.portfolio_code is null then
266 Log_IAC_errors('XTR_MANDATORY','TRANS.PORTFOLIO_CODE_FROM');
267 p_error := TRUE;
268 end if;
269
270 if ARec_IAC.dealer_code is null then
271 Log_IAC_errors('XTR_MANDATORY','CG$CTRL.DEALER_CODE');
272 p_error := TRUE;
273 end if;
274
275 if ARec_IAC.deal_date is null then
276 Log_IAC_errors('XTR_MANDATORY','CG$CTRL.DEAL_DATE');
277 p_error := TRUE;
278 end if;
279
280 end; /* CHECK_MANDATORY_FIELDS */
281
282
283 -----------------------------------------------------------------------------------------------------
284 procedure VALIDATE_DEALS(ARec_IAC IN xtr_interacct_transfers%rowtype,
285 p_Bank_Code_From OUT NOCOPY VARCHAR2,
286 p_Bank_Code_To OUT NOCOPY VARCHAR2,
287 p_error OUT NOCOPY BOOLEAN) is
288 -----------------------------------------------------------------------------------------------------
289 l_zba_duplicate BOOLEAN := FALSE;
290
291 begin
292
293 p_error := FALSE;
294
295 if not VALID_CURRENCY(ARec_IAC.Currency) then
296
297 Log_IAC_errors('XTR_INV_CURR');
298 p_error := TRUE;
299
300 else
301
302 p_Bank_Code_From := null;
303 p_Bank_Code_From := VALID_PARTY_ACCT(ARec_IAC.Company_Code, ARec_IAC.Account_No_From, ARec_IAC.Currency);
304 if p_Bank_Code_From is null then
305 p_error := TRUE;
306 end if;
307
308
309 p_Bank_Code_To := null;
310 p_Bank_Code_To := VALID_PARTY_ACCT(ARec_IAC.Company_Code, ARec_IAC.Account_No_To, ARec_IAC.Currency);
311 if p_Bank_Code_To is null then
312 p_error := TRUE;
313 end if;
314
315 end if; -- not currency error
316
317 if ARec_IAC.Account_No_From = ARec_IAC.Account_No_To then
318 Log_IAC_errors('XTR_1877'); -- The account numbers must be different
319 p_error := TRUE;
320 end if;
321
322 if not VALID_PRODUCT(ARec_IAC.Product_Type) then
323 Log_IAC_errors('XTR_INV_PRODUCT_TYPE');
324 p_error := TRUE;
325 end if;
326
327
328 if not VALID_DEALER_CODE(ARec_IAC.Dealer_Code) then
329 Log_IAC_errors('XTR_INV_DEALER_CODE');
330 p_error := TRUE;
331 end if;
332
333 if not VALID_PORTFOLIO(ARec_IAC.Company_Code, ARec_IAC.Portfolio_Code) then
334 Log_IAC_errors('XTR_INV_PORT_CODE');
335 p_error := TRUE;
336 end if;
337
338
339 if not VALID_TRANSFER_AMOUNT(ARec_IAC.Transfer_Amount) then
340 Log_IAC_errors('XTR_56');
341 p_error := TRUE;
342 end if;
343
344 if not VALID_DEAL_DATE(ARec_IAC.Deal_Date) then
345 Log_IAC_errors('XTR_INV_DEAL_DATE');
346 p_error := TRUE;
347 end if;
348
349
350 --------------------------------------------------------------------------------------------------
351 -- Duplicate check for ZBA
352 --------------------------------------------------------------------------------------------------
353 if not p_error and ARec_IAC.External_Source = C_ZBA then
354 XTR_WRAPPER_API_P.CHK_ZBA_IAC_DUPLICATE (ARec_IAC.company_code, -- p_company_code,
355 ARec_IAC.transfer_amount, -- p_transfer_amount,
356 ARec_IAC.transfer_date, -- p_transfer_date,
357 ARec_IAC.account_no_from, -- p_company_account_no,
358 ARec_IAC.account_no_to, -- p_party_account_no,
359 ARec_IAC.portfolio_code, -- p_company_portfolio,
360 ARec_IAC.product_type, -- p_company_product_type,
361 l_zba_duplicate);
362 if l_zba_duplicate then
363 p_error := TRUE;
364 end if;
365
366 end if;
367
368 end; /* VALIDATE_DEALS */
369
370
371 --********************************************************************************************
372 -- To settle a cashflow *******************************************************************
373 --********************************************************************************************
374 procedure VALIDATE_SETTLE_DDA (p_validate_flag IN BOOLEAN,
375 p_settle_flag IN BOOLEAN,
376 p_actual_settle IN DATE,
377 p_dual_auth_by OUT NOCOPY VARCHAR2,
378 p_dual_auth_on OUT NOCOPY DATE,
379 p_settle OUT NOCOPY VARCHAR2,
380 p_settle_no OUT NOCOPY NUMBER,
381 p_settle_no2 OUT NOCOPY NUMBER,
382 p_settle_auth OUT NOCOPY VARCHAR2,
383 p_settle_date OUT NOCOPY DATE,
384 p_trans_mts OUT NOCOPY VARCHAR2,
385 p_audit_indic OUT NOCOPY VARCHAR2) is
386
387 begin
388
389 -- Bug 6738354 start
390 select XTR_SETTLEMENT_NUMBER_S.NEXTVAL into p_settle_no from DUAL;
391 select XTR_SETTLEMENT_NUMBER_S.NEXTVAL into p_settle_no2 from DUAL;
392 -- Bug 6738354 end
393
394 -------------------------------------
395 -- Dual Authorised Attributes
396 -------------------------------------
397 if p_validate_flag then
398 p_dual_auth_by := G_iac_user;
399 p_dual_auth_on := G_iac_date;
400 else
401 p_dual_auth_by := null;
402 p_dual_auth_on := null;
403 end if;
404
405 -------------------------------------
406 -- Settlement Attributes
407 -------------------------------------
408 if p_settle_flag then
409
410 /* Bug 6738354 start
411 select XTR_SETTLEMENT_NUMBER_S.NEXTVAL into p_settle_no from DUAL;
412 select XTR_SETTLEMENT_NUMBER_S.NEXTVAL into p_settle_no2 from DUAL;
413 Bug 6738354 end */
414 p_settle := 'Y';
415 p_settle_auth := G_iac_user;
416 p_settle_date := p_actual_settle;
417 p_trans_mts := 'Y';
418 p_audit_indic := 'Y';
419
420 else
421 p_settle := 'N';
422 /* Bug 6738354 start
423 p_settle_no := null;
424 p_settle_no2 := null;
425 Bug 6738354 end */
426 p_settle_auth := null;
427 p_settle_date := null;
428 p_trans_mts := null;
429 p_audit_indic := null;
430
431 end if;
432
433 end;
434
435 -----------------------------------------------------------------------------------------------------
436 -- Local procedure to insert Deal Date Rows
437 -----------------------------------------------------------------------------------------------------
438 procedure INS_DEAL_DATE_AMTS (ARec_IAC IN XTR_INTERACCT_TRANSFERS%rowtype,
439 p_From_Bank IN XTR_DEAL_DATE_AMOUNTS.LIMIT_PARTY%TYPE,
440 p_To_Bank IN XTR_DEAL_DATE_AMOUNTS.LIMIT_PARTY%TYPE,
441 p_tran_num IN NUMBER,
442 p_Validated IN BOOLEAN,
443 p_Settled IN BOOLEAN ) is
444 -----------------------------------------------------------------------------------------------------
445
446 l_settle VARCHAR2(1);
447 l_settle_no NUMBER := null;
448 l_settle_no2 NUMBER := null;
449 l_settle_auth xtr_deal_date_amounts.SETTLEMENT_AUTHORISED_BY%TYPE;
450 l_settle_date DATE;
451 l_dual_auth_by xtr_deal_date_amounts.DUAL_AUTHORISATION_BY%TYPE;
452 l_dual_auth_on DATE;
453 l_trans_mts VARCHAR2(1);
454 l_audit_indic VARCHAR2(1);
455 l_dummy_num NUMBER;
456
457 begin
458
459 -------------------------------------------------------------------------
460 -- Validation and settlement
461 -------------------------------------------------------------------------
462 VALIDATE_SETTLE_DDA (p_Validated,
463 p_Settled,
464 ARec_IAC.Transfer_date,
465 l_dual_auth_by,
466 l_dual_auth_on,
467 l_settle,
468 l_settle_no,
469 l_settle_no2,
470 l_settle_auth,
471 l_settle_date,
472 l_trans_mts,
473 l_audit_indic);
474
475 -------------------------------------------------------------------------
476 -- Paying side (bank account from)
477 -------------------------------------------------------------------------
478 insert into XTR_DEAL_DATE_AMOUNTS_V
479 (deal_type, amount_type, date_type, product_type,
480 deal_number, transaction_number, transaction_date,
481 currency, amount, hce_amount, amount_date,
482 cashflow_amount, company_code, account_no, action_code,
483 cparty_code, cparty_account_no, status_code,
484 exp_settle_reqd, deal_subtype, portfolio_code, dealer_code,
485 limit_party, DUAL_AUTHORISATION_BY, DUAL_AUTHORISATION_ON,
486 SETTLE, SETTLEMENT_NUMBER, SETTLEMENT_AUTHORISED_BY, ACTUAL_SETTLEMENT_DATE,
487 TRANS_MTS, AUDIT_INDICATOR)
488 values ( ARec_IAC.deal_type, 'AMOUNT', 'VALUE', ARec_IAC.PRODUCT_TYPE,
489 0, p_tran_num, ARec_IAC.deal_date, -- per TD design
490 ARec_IAC.CURRENCY, ARec_IAC.TRANSFER_AMOUNT, 0, ARec_IAC.Transfer_date,
491 -ARec_IAC.TRANSFER_AMOUNT,ARec_IAC.COMPANY_CODE, ARec_IAC.ACCOUNT_NO_FROM, 'PAY',
492 ARec_IAC.COMPANY_CODE, ARec_IAC.ACCOUNT_NO_TO, ARec_IAC.STATUS_CODE,
493 'Y', ARec_IAC.DEAL_SUBTYPE, ARec_IAC.PORTFOLIO_CODE, ARec_IAC.DEALER_CODE,
494 p_From_Bank, l_dual_auth_by, l_dual_auth_on,
495 l_settle, l_settle_no, l_settle_auth, l_settle_date,
496 l_trans_mts, l_audit_indic);
497
498 -------------------------------------------------------------------------
499 -- Receiving Side (Bank account to)
500 -------------------------------------------------------------------------
501 insert into XTR_DEAL_DATE_AMOUNTS_V
502 (deal_type, amount_type, date_type, product_type,
503 deal_number, transaction_number, transaction_date,
504 currency, amount, hce_amount, amount_date,
505 cashflow_amount, company_code, account_no, action_code,
506 cparty_code, cparty_account_no, status_code,
507 exp_settle_reqd, deal_subtype, portfolio_code, dealer_code,
508 limit_party, DUAL_AUTHORISATION_BY, DUAL_AUTHORISATION_ON,
509 SETTLE, SETTLEMENT_NUMBER, SETTLEMENT_AUTHORISED_BY, ACTUAL_SETTLEMENT_DATE,
510 TRANS_MTS, AUDIT_INDICATOR)
511 values ( ARec_IAC.deal_type, 'AMOUNT', 'VALUE', ARec_IAC.PRODUCT_TYPE,
512 0, p_tran_num, ARec_IAC.deal_date, -- per TD design
513 ARec_IAC.CURRENCY, ARec_IAC.TRANSFER_AMOUNT, 0, ARec_IAC.TRANSFER_DATE,
514 ARec_IAC.TRANSFER_AMOUNT, ARec_IAC.COMPANY_CODE, ARec_IAC.ACCOUNT_NO_TO, 'REC',
515 ARec_IAC.COMPANY_CODE, ARec_IAC.ACCOUNT_NO_FROM, ARec_IAC.STATUS_CODE,
516 'Y', ARec_IAC.DEAL_SUBTYPE, ARec_IAC.PORTFOLIO_CODE, ARec_IAC.DEALER_CODE,
517 p_To_Bank, l_dual_auth_by, l_dual_auth_on,
518 l_settle, l_settle_no2, l_settle_auth, l_settle_date,
519 l_trans_mts, l_audit_indic);
520
521 -------------------------------------------------
522 -- Create Settlement Summary
523 -------------------------------------------------
524 if p_Settled then
525 -------------------------
526 -- Paying
527 -------------------------
528 XTR_SETTLEMENT_SUMMARY_P.INS_SETTLEMENT_SUMMARY(l_settle_no,
529 ARec_IAC.COMPANY_CODE,
530 ARec_IAC.CURRENCY,
531 -ARec_IAC.TRANSFER_AMOUNT, -- PAY amount
532 l_settle_date, -- settlement_date
533 ARec_IAC.ACCOUNT_NO_FROM, -- PAY account
534 ARec_IAC.ACCOUNT_NO_TO, -- REC account
535 null,
536 'A',
537 fnd_global.user_id, -- created_by
538 G_sys_date, -- creation_date
539 ARec_IAC.External_Source,
540 ARec_IAC.COMPANY_CODE, -- cparty code
541 l_dummy_num);
542 -------------------------
543 -- Receiving
544 -------------------------
545 XTR_SETTLEMENT_SUMMARY_P.INS_SETTLEMENT_SUMMARY(l_settle_no2,
546 ARec_IAC.COMPANY_CODE,
547 ARec_IAC.CURRENCY,
548 ARec_IAC.TRANSFER_AMOUNT, -- REC amount
549 l_settle_date, -- settlement_date
550 ARec_IAC.ACCOUNT_NO_TO, -- REC account
551 ARec_IAC.ACCOUNT_NO_FROM, -- PAY account
552 null,
553 'A',
554 fnd_global.user_id, -- created_by
555 G_sys_date, -- creation_date
556 ARec_IAC.External_Source,
557 ARec_IAC.COMPANY_CODE, -- cparty code
558 l_dummy_num);
559 end if;
560
561 end; /* INS_DEAL_DATE_AMTS */
562
563
564 -----------------------------------------------------------------------------------------------------
565 --* Table Handler For Xtr_Interroup_Transfers For Inserting Row
566 procedure CREATE_IAC_DEAL(ARec_IAC IN XTR_INTERACCT_TRANSFERS%rowtype,
567 p_Validated IN BOOLEAN,
568 p_tran_num IN NUMBER) is
569 -----------------------------------------------------------------------------------------------------
570 cursor EXP_TRANS_NUM is
571 select XTR_EXPOSURE_TRANS_S.NEXTVAL
572 from DUAL;
573
574 l_dual_dealer XTR_INTERACCT_TRANSFERS.DEALER_CODE%TYPE;
575 l_dual_date DATE;
576
577 begin
578 /*
579 open EXP_TRANS_NUM;
580 fetch EXP_TRANS_NUM into p_tran_num;
581 close EXP_TRANS_NUM;
582 */
583
584 if p_Validated then
585 l_dual_dealer := ARec_IAC.DEALER_CODE;
586 l_dual_date := G_sys_date;
587 else
588 l_dual_dealer := null;
589 l_dual_date := null;
590 end if;
591
592 Insert into XTR_INTERACCT_TRANSFERS(
593 ACCOUNT_NO_FROM,
594 ACCOUNT_NO_TO,
595 COMPANY_CODE,
596 CURRENCY,
597 DEAL_SUBTYPE,
598 DEAL_TYPE,
599 PORTFOLIO_CODE,
600 PRODUCT_TYPE,
601 STATUS_CODE,
602 TRANSFER_AMOUNT,
603 TRANSFER_DATE,
604 TRANSACTION_NUMBER,
605 CREATION_DATE,
606 CREATED_BY,
607 LAST_UPDATED_BY,
608 LAST_UPDATE_DATE,
609 LAST_UPDATE_LOGIN,
610 DUAL_AUTHORISATION_BY,
611 DUAL_AUTHORISATION_ON,
612 DEAL_DATE,
613 DEALER_CODE,
614 REQUEST_ID,
615 PROGRAM_APPLICATION_ID,
616 PROGRAM_ID,
617 PROGRAM_UPDATE_DATE,
618 EXTERNAL_SOURCE)
619 Values ( ARec_IAC.ACCOUNT_NO_FROM,
620 ARec_IAC.ACCOUNT_NO_TO,
621 ARec_IAC.COMPANY_CODE,
622 ARec_IAC.CURRENCY,
623 ARec_IAC.DEAL_SUBTYPE, -- DEAL_SUBTYPE
624 ARec_IAC.DEAL_TYPE, -- DEAL_TYPE
625 ARec_IAC.PORTFOLIO_CODE,
626 ARec_IAC.PRODUCT_TYPE,
627 ARec_IAC.STATUS_CODE, -- STATUS_CODE
628 ARec_IAC.TRANSFER_AMOUNT,
629 ARec_IAC.TRANSFER_DATE,
630 p_tran_num, -- TRANSACTION_NUMBER
631 G_sys_date, -- CREATION_DATE
632 fnd_global.user_id, -- CREATED_BY
633 fnd_global.user_id, -- LAST_UPDATED_BY
634 G_sys_date, -- LAST_UPDATE_DATE
635 fnd_global.user_id, -- LAST_UPDATE_LOGIN
636 l_dual_dealer, -- DUAL_AUTHORIZATION_BY
637 l_dual_date, -- DUAL_AUTHORIZATION_ON
638 ARec_IAC.DEAL_DATE, -- DEAL DATE
639 ARec_IAC.DEALER_CODE, -- DEALER_CODE
640 fnd_global.conc_request_id,
641 fnd_global.prog_appl_id,
642 fnd_global.conc_program_id,
643 G_sys_date,
644 ARec_IAC.EXTERNAL_SOURCE);
645
646 end; /* CREATE_IAC_DEAL */
647
648 -----------------------------------------------------------------------------------------------------
649 --* Main procedure for Import Deal Record that calls all the validation APIs
650 --* and then finally calls the Insert Table Handler.
651 procedure TRANSFER_IAC_DEALS( ARec_IAC IN XTR_INTERACCT_TRANSFERS%rowtype,
652 p_Validated IN BOOLEAN,
653 p_Settled IN BOOLEAN,
654 user_error OUT NOCOPY BOOLEAN,
655 mandatory_error OUT NOCOPY BOOLEAN,
656 validation_error OUT NOCOPY BOOLEAN,
657 p_tran_num OUT NOCOPY NUMBER) is
658 -----------------------------------------------------------------------------------------------------
659
660 cursor FIND_USER (p_fnd_user in number) is
661 select dealer_code
662 from xtr_dealer_codes_v
663 where user_id = p_fnd_user;
664
665 cursor USER_ACCESS (l_comp VARCHAR2) is
666 select 1
667 from xtr_parties_v
668 where party_type = 'C'
669 and party_code = l_comp;
670
671 cursor EXP_TRANS_NUM is
672 select XTR_EXPOSURE_TRANS_S.NEXTVAL
673 from DUAL;
674
675 l_dummy NUMBER;
676 l_bank_code_from xtr_deal_date_amounts.limit_party%TYPE;
677 l_bank_code_to xtr_deal_date_amounts.limit_party%TYPE;
678
679 begin
680
681 -------------------------
682 -- Initialise Variables
683 -------------------------
684 p_tran_num := null;
685 user_error := FALSE;
686 mandatory_error := FALSE;
687 validation_error := FALSE;
688
689 open FIND_USER(fnd_global.user_id);
690 fetch FIND_USER into G_iac_user;
691 close FIND_USER;
692
693 Select sysdate, trunc(sysdate)
694 into G_sys_date, G_iac_date
695 From Dual;
696
697 ----------------------------------------------------------------------------------------------------
698 --* The following code checks if user has permissions to transfer the deal (company authorization)
699 ----------------------------------------------------------------------------------------------------
700 open USER_ACCESS(ARec_IAC.company_code);
701 fetch USER_ACCESS into l_dummy;
702 if USER_ACCESS%NOTFOUND then
703 Log_IAC_errors('XTR_INV_COMP_CODE');
704 user_error := TRUE;
705 end if;
706 close USER_ACCESS;
707
708 if (user_error <> TRUE) then
709
710 --------------------------------------------------------------------------------
711 --* The following code does mandatory field validation specific to the IAC deals
712 --------------------------------------------------------------------------------
713 CHECK_MANDATORY_FIELDS(ARec_IAC,mandatory_error);
714
715 if (mandatory_error <> TRUE) then
716
717 --------------------------------------------------------------------------------------
718 --* The following code performs the business logic validation specific to the IAC deals
719 --------------------------------------------------------------------------------------
720 VALIDATE_DEALS(ARec_IAC, l_Bank_Code_From, l_Bank_Code_To, validation_error);
721
722 end if; /* Checking Mandatory values */
723
724 end if; /* Checking User Auth */
725
726 /*----------------------------------------------------------------------------------------------*/
727 /* If the process passed all the previous validation, it would be considered a valid deal entry */
728 /*----------------------------------------------------------------------------------------------*/
729 if user_error <> TRUE and mandatory_error <> TRUE and validation_error <> TRUE then
730
731 open EXP_TRANS_NUM;
732 fetch EXP_TRANS_NUM into p_tran_num;
733 close EXP_TRANS_NUM;
734
735
736 INS_DEAL_DATE_AMTS (ARec_IAC,
737 l_Bank_Code_From,
738 l_Bank_code_To,
739 p_tran_num,
740 p_Validated,
741 p_Settled);
742
743 CREATE_IAC_DEAL (ARec_IAC,
744 p_Validated,
745 p_tran_num);
746
747 XTR_MISC_P.MAINT_PROJECTED_BALANCES;
748
749 end if;
750
751 end; /* TRANSFER_IAC_DEALS */
752
753
754 END XTR_IAC_TRANSFERS_PKG;
755