[Home] [Help]
PACKAGE BODY: APPS.POS_SBD_IBY_PKG
Source
1 PACKAGE BODY POS_SBD_IBY_PKG as
2 /*$Header: POSIBYB.pls 120.16 2011/01/07 08:52:30 puppulur ship $ */
3
4 PROCEDURE remove_iby_temp_account (
5 p_iby_temp_ext_bank_account_id IN NUMBER
6 , x_status out nocopy VARCHAR2
7 , x_exception_msg out nocopy VARCHAR2
8 )
9
10 IS
11 l_step NUMBER;
12 BEGIN
13 l_step := 0;
14
15 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
16 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
17 ' Begin remove_iby_temp_account ');
18 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
19 ' p_iby_temp_ext_bank_account_id ' || p_iby_temp_ext_bank_account_id);
20 END IF;
21
22 delete from iby_temp_ext_bank_accts
23 where temp_ext_bank_acct_id = p_iby_temp_ext_bank_account_id;
24
25
26 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
27 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
28 ' End remove_iby_temp_account ');
29 END IF;
30
31 l_step := 1;
32
33 x_status :='S';
34 x_exception_msg :=NULL;
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 x_exception_msg := 'Failure at step ' || l_step;
39 raise_application_error(-20060, x_exception_msg, true);
40 END remove_iby_temp_account;
41
42 PROCEDURE create_iby_temp_account (
43 p_party_id in NUMBER
44 , p_status in varchar2
45 , p_owner_primary_flag in varchar2
46 , p_payment_factor_flag in varchar2
47 , p_BANK_ID in NUMBER
48 , p_BANK_NAME in VARCHAR2
49 , p_BANK_NAME_ALT in varchar2
50 , p_BANK_NUMBER in VARCHAR2
51 , p_BANK_INSTITUTION in varchar2
52 , p_BANK_ADDRESS1 in VARCHAR2
53 , p_BANK_ADDRESS2 in VARCHAR2
54 , p_BANK_ADDRESS3 in VARCHAR2
55 , p_BANK_ADDRESS4 in VARCHAR2
56 , p_BANK_CITY in VARCHAR2
57 , p_BANK_COUNTY in VARCHAR2
58 , p_BANK_STATE in VARCHAR2
59 , p_BANK_ZIP in VARCHAR2
60 , p_BANK_PROVINCE in VARCHAR2
61 , p_BANK_COUNTRY in VARCHAR2
62 , p_BRANCH_ID in NUMBER
63 , p_BRANCH_NAME in VARCHAR2
64 , p_BRANCH_NAME_ALT in varchar2
65 , p_BRANCH_NUMBER in VARCHAR2
66 , p_BRANCH_TYPE in varchar2
67 , p_RFC_IDENTIFIER in varchar2
68 , p_BIC in varchar2
69 , p_BRANCH_ADDRESS1 in VARCHAR2
70 , p_BRANCH_ADDRESS2 in VARCHAR2
71 , p_BRANCH_ADDRESS3 in VARCHAR2
72 , p_BRANCH_ADDRESS4 in VARCHAR2
73 , p_BRANCH_CITY in VARCHAR2
74 , p_BRANCH_COUNTY in VARCHAR2
75 , p_BRANCH_STATE in VARCHAR2
76 , p_BRANCH_ZIP in VARCHAR2
77 , p_BRANCH_PROVINCE in VARCHAR2
78 , p_BRANCH_COUNTRY in VARCHAR2
79 , p_EXT_BANK_ACCOUNT_ID in number
80 , p_bank_account_number in varchar2
81 , p_bank_account_name in varchar2
82 , p_bank_account_name_alt in varchar2
83 , p_check_digits in varchar2
84 , p_iban in varchar2
85 , p_currency_code in varchar2
86 , p_country_code in varchar2
87 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
88 , p_bank_account_type in varchar2
89 , p_account_description in varchar2
90 , p_end_date in date
91 , p_start_date in date
92 , p_agency_location_code in varchar2
93 , p_account_suffix in varchar2
94 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
95 , P_EXCHANGE_RATE_AGREEMENT_TYPE in VARCHAR2
96 , p_EXCHANGE_RATE in NUMBER
97 , p_NOTES in VARCHAR2
98 , p_NOTE_ALT in varchar2
99 , x_temp_ext_bank_account_id out nocopy NUMBER
100 , x_status out nocopy VARCHAR2
101 , x_exception_msg out nocopy VARCHAR2
102 )
103 IS
104 l_step NUMBER;
105 l_bank_location_rec hz_location_v2pub.LOCATION_REC_TYPE;
106 l_branch_location_rec hz_location_v2pub.LOCATION_REC_TYPE;
107 l_bank_location_id number;
108 l_branch_location_id number;
109 l_msg_count NUMBER;
110
111 BEGIN
112 l_step := 0;
113
114 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
115 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
116 ' Begin create_iby_temp_account ');
117 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
118 ' p_party_id ' || p_party_id);
119 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
120 ' p_bank_id ' || p_bank_id);
121 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
122 ' p_branch_id ' || p_branch_id);
123 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
124 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
125 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
126 ' p_status ' || p_status);
127 END IF;
128
129 if p_bank_id is null and p_bank_name is not null then
130
131 POS_SBD_IBY_PKG.create_location (
132 p_ADDRESS1 => p_bank_address1
133 , p_ADDRESS2 => p_bank_address2
134 , p_ADDRESS3 => p_bank_address3
135 , p_ADDRESS4 => p_bank_address4
136 , p_CITY => p_bank_city
137 , p_COUNTY => p_bank_county
138 , p_STATE => p_bank_state
139 , p_ZIP => p_bank_zip
140 , p_PROVINCE => p_bank_province
141 , p_COUNTRY => p_country_code
142 , x_location_id => l_bank_location_id
143 , x_status => x_status
144 , x_exception_msg => x_exception_msg);
145
146 end if;
147
148 l_step := 1;
149
150 if p_branch_id is null and p_branch_name is not null then
151
152 POS_SBD_IBY_PKG.create_location (
153 p_ADDRESS1 => p_branch_address1
154 , p_ADDRESS2 => p_branch_address2
155 , p_ADDRESS3 => p_branch_address3
156 , p_ADDRESS4 => p_branch_address4
157 , p_CITY => p_branch_city
158 , p_COUNTY => p_branch_county
159 , p_STATE => p_branch_state
160 , p_ZIP => p_branch_zip
161 , p_PROVINCE => p_branch_province
162 , p_COUNTRY => p_country_code
163 , x_location_id => l_branch_location_id
164 , x_status => x_status
165 , x_exception_msg => x_exception_msg);
166
167 end if;
168
169 l_step := 2;
170
171 select IBY_TEMP_EXT_BANK_ACCTS_S.nextval into x_temp_ext_bank_account_id from dual;
172
173 insert into iby_temp_ext_bank_accts
174 (
175 temp_ext_bank_acct_id
176 , status
177 , account_owner_party_id
178 , owner_primary_flag
179 , payment_factor_flag
180 , request_id
181 , program_application_id
182 , program_id
183 , program_update_date
184 , object_version_number
185 , creation_date
186 , created_by
187 , last_update_date
188 , last_updated_by
189 , last_update_login
190 , BANK_ID
191 , BANK_NAME
192 , BANK_NAME_ALT
193 , BANK_NUMBER
194 , BANK_INSTITUTION_TYPE
195 , BANK_ADDRESS_ID
196 , BRANCH_ID
197 , BRANCH_NAME
198 , BRANCH_NAME_ALT
199 , BRANCH_NUMBER
200 , BRANCH_TYPE
201 , RFC_IDENTIFIER
202 , BIC
203 , BRANCH_ADDRESS_ID
204 , EXT_BANK_ACCOUNT_ID
205 , bank_account_num
206 , bank_account_name
207 , bank_account_name_alt
208 , check_digits
209 , iban
210 , currency_code
211 , FOREIGN_PAYMENT_USE_FLAG
212 , bank_account_type
213 , country_code
214 , description
215 , end_date
216 , start_date
217 , agency_location_code
218 , account_suffix
219 , EXCHANGE_RATE_AGREEMENT_NUM
220 , EXCHANGE_RATE_AGREEMENT_TYPE
221 , EXCHANGE_RATE
222 , NOTE
223 , NOTE_ALT
224 )
225 values
226 (
227 x_temp_ext_bank_account_id
228 , p_status
229 , p_party_id
230 , p_owner_primary_flag
231 , p_payment_factor_flag
232 , null
233 , 177
234 , 177
235 , sysdate
236 , 1
237 , sysdate
238 , fnd_global.user_id
239 , sysdate
240 , fnd_global.user_id
241 , fnd_global.login_id
242 , p_BANK_ID
243 , p_BANK_NAME
244 , p_BANK_NAME_ALT
245 , p_BANK_NUMBER
246 , p_BANK_INSTITUTION
247 , l_bank_location_id
248 , p_BRANCH_ID
249 , p_BRANCH_NAME
250 , p_BRANCH_NAME_ALT
251 , p_BRANCH_NUMBER
252 , p_BRANCH_TYPE
253 , p_RFC_IDENTIFIER
254 , p_BIC
255 , l_branch_location_id
256 , p_EXT_BANK_ACCOUNT_ID
257 , p_bank_account_number
258 , p_bank_account_name
259 , p_bank_account_name_alt
260 , p_check_digits
261 , p_iban
262 , p_currency_code
263 , p_FOREIGN_PAYMENT_USE_FLAG
264 , p_bank_account_type
265 , p_country_code
266 , p_account_description
267 , p_end_date
268 , p_start_date
269 , p_agency_location_code
270 , p_account_suffix
271 , p_EXCHANGE_RATE_AGREEMENT_NUM
272 , p_EXCHANGE_RATE_AGREEMENT_TYPE
273 , p_EXCHANGE_RATE
274 , p_NOTES
275 , p_NOTE_ALT
276 );
277
278 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
279 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
280 ' End create_iby_temp_account ');
281 END IF;
282
283 l_step := 3;
284
285 x_status :='S';
286 x_exception_msg :=NULL;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 x_exception_msg := 'Failure at step ' || l_step;
291 raise_application_error(-20061, x_exception_msg, true);
292 END create_iby_temp_account;
293
294
295 /* This procedure updates the iby temp account on buyer's request.
296 *
297 */
298 PROCEDURE update_iby_temp_account (
299 p_temp_ext_bank_acct_id in number
300 , p_party_id in NUMBER
301 , p_status in varchar2
302 , p_owner_primary_flag in varchar2
303 , p_payment_factor_flag in varchar2
304 , p_BANK_ID in NUMBER
305 , p_BANK_NAME in VARCHAR2
306 , p_BANK_NAME_ALT in varchar2
307 , p_BANK_NUMBER in VARCHAR2
308 , p_BANK_INSTITUTION in varchar2
309 , p_BANK_ADDRESS1 in VARCHAR2
310 , p_BANK_ADDRESS2 in VARCHAR2
311 , p_BANK_ADDRESS3 in VARCHAR2
312 , p_BANK_ADDRESS4 in VARCHAR2
313 , p_BANK_CITY in VARCHAR2
314 , p_BANK_COUNTY in VARCHAR2
315 , p_BANK_STATE in VARCHAR2
316 , p_BANK_ZIP in VARCHAR2
317 , p_BANK_PROVINCE in VARCHAR2
318 , p_BANK_COUNTRY in VARCHAR2
319 , p_BRANCH_ID in NUMBER
320 , p_BRANCH_NAME in VARCHAR2
321 , p_BRANCH_NAME_ALT in varchar2
322 , p_BRANCH_NUMBER in VARCHAR2
323 , p_BRANCH_TYPE in varchar2
324 , p_RFC_IDENTIFIER in varchar2
325 , p_BIC in varchar2
326 , p_BRANCH_ADDRESS1 in VARCHAR2
327 , p_BRANCH_ADDRESS2 in VARCHAR2
328 , p_BRANCH_ADDRESS3 in VARCHAR2
329 , p_BRANCH_ADDRESS4 in VARCHAR2
330 , p_BRANCH_CITY in VARCHAR2
331 , p_BRANCH_COUNTY in VARCHAR2
332 , p_BRANCH_STATE in VARCHAR2
333 , p_BRANCH_ZIP in VARCHAR2
334 , p_BRANCH_PROVINCE in VARCHAR2
335 , p_BRANCH_COUNTRY in VARCHAR2
336 , p_EXT_BANK_ACCOUNT_ID in number
337 , p_bank_account_number in varchar2
338 , p_bank_account_name in varchar2
339 , p_bank_account_name_alt in varchar2
340 , p_check_digits in varchar2
341 , p_iban in varchar2
342 , p_currency_code in varchar2
343 , p_country_code in varchar2
344 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
345 , p_bank_account_type in varchar2
346 , p_account_description in varchar2
347 , p_end_date in date
348 , p_start_date in date
349 , p_agency_location_code in varchar2
350 , p_account_suffix in varchar2
351 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
352 , p_exchange_rate_agreement_type in VARCHAR2
353 , p_EXCHANGE_RATE in NUMBER
354 , p_NOTES in VARCHAR2
355 , p_NOTE_ALT in varchar2
356 , x_status out nocopy VARCHAR2
357 , x_exception_msg out nocopy VARCHAR2
358 )
359 IS
360 l_step NUMBER;
361 l_bank_location_id number;
362 l_branch_location_id number;
363 l_msg_count NUMBER;
364
365 cursor l_bank_branch_loc_cur is
366 select iby.bank_address_id, iby.branch_address_id
367 into l_bank_location_id, l_branch_location_id
368 from iby_temp_ext_bank_accts iby
369 where temp_ext_bank_acct_id = p_temp_ext_bank_acct_id;
370
371 BEGIN
372 l_step := 0;
373
374 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
375 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
376 ' Begin update_iby_temp_account ');
377 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
378 ' p_temp_ext_account_id ' || p_temp_ext_bank_acct_id);
379 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
380 ' p_party_id ' || p_party_id);
381 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
382 ' p_bank_id ' || p_bank_id);
383 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
384 ' p_branch_id ' || p_branch_id);
385 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
386 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
387 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
388 ' p_status ' || p_status);
389 END IF;
390
391 open l_bank_branch_loc_cur;
392 fetch l_bank_branch_loc_cur into l_bank_location_id, l_branch_location_id;
393 close l_bank_branch_loc_cur;
394
395 l_step := 1;
396
397 if p_bank_id is null then
398
399 if l_bank_location_id is not null then
400
401 if p_country_code is null OR p_bank_address1 is null then
402
403 l_bank_location_id := null;
404
405 else
406
407 POS_SBD_IBY_PKG.update_location (
408 p_location_id => l_bank_location_id
409 , p_ADDRESS1 => p_bank_address1
410 , p_ADDRESS2 => p_bank_address2
411 , p_ADDRESS3 => p_bank_address3
412 , p_ADDRESS4 => p_bank_address4
413 , p_CITY => p_bank_city
414 , p_COUNTY => p_bank_county
415 , p_STATE => p_bank_state
416 , p_ZIP => p_bank_zip
417 , p_PROVINCE => p_bank_province
418 , p_COUNTRY => p_country_code
419 , x_status => x_status
420 , x_exception_msg => x_exception_msg);
421 end if;
422
423 else
424
425 if p_country_code is null OR p_bank_address1 is null then
426 l_bank_location_id := null;
427 else
428 POS_SBD_IBY_PKG.create_location (
429 p_ADDRESS1 => p_bank_address1
430 , p_ADDRESS2 => p_bank_address2
431 , p_ADDRESS3 => p_bank_address3
432 , p_ADDRESS4 => p_bank_address4
433 , p_CITY => p_bank_city
434 , p_COUNTY => p_bank_county
435 , p_STATE => p_bank_state
436 , p_ZIP => p_bank_zip
437 , p_PROVINCE => p_bank_province
438 , p_COUNTRY => p_country_code
439 , x_location_id => l_bank_location_id
440 , x_status => x_status
441 , x_exception_msg => x_exception_msg);
442 end if;
443
444 end if;
445 end if;
446
447 l_step := 2;
448
449 if p_branch_id is null then
450
451 if l_branch_location_id is not null then
452
453 if p_country_code is null OR p_branch_address1 is null then
454
455 l_branch_location_id := null;
456
457 else
458
459 POS_SBD_IBY_PKG.update_location (
460 p_location_id => l_branch_location_id
461 , p_ADDRESS1 => p_branch_address1
462 , p_ADDRESS2 => p_branch_address2
463 , p_ADDRESS3 => p_branch_address3
464 , p_ADDRESS4 => p_branch_address4
465 , p_CITY => p_branch_city
466 , p_COUNTY => p_branch_county
467 , p_STATE => p_branch_state
468 , p_ZIP => p_branch_zip
469 , p_PROVINCE => p_branch_province
470 , p_COUNTRY => p_country_code
471 , x_status => x_status
472 , x_exception_msg => x_exception_msg);
473 end if;
474
475 else
476
477 if p_country_code is null OR p_branch_address1 is null then
478
479 l_branch_location_id := null;
480
481 else
482
483 POS_SBD_IBY_PKG.create_location (
484 p_ADDRESS1 => p_branch_address1
485 , p_ADDRESS2 => p_branch_address2
486 , p_ADDRESS3 => p_branch_address3
487 , p_ADDRESS4 => p_branch_address4
488 , p_CITY => p_branch_city
489 , p_COUNTY => p_branch_county
490 , p_STATE => p_branch_state
491 , p_ZIP => p_branch_zip
492 , p_PROVINCE => p_branch_province
493 , p_COUNTRY => p_country_code
494 , x_location_id => l_branch_location_id
495 , x_status => x_status
496 , x_exception_msg => x_exception_msg);
497 end if;
498
499 end if;
500 end if;
501
502 l_step := 3;
503
504 update iby_temp_ext_bank_accts set
505 status = p_status
506 , account_owner_party_id = p_party_id
507 , owner_primary_flag = p_owner_primary_flag
508 , payment_factor_flag = p_payment_factor_flag
509 , last_update_date = sysdate
510 , last_updated_by = fnd_global.user_id
511 , last_update_login = fnd_global.login_id
512 , BANK_ID = p_bank_id
513 , BANK_NAME = p_bank_name
514 , BANK_NAME_ALT = p_bank_name_alt
515 , BANK_NUMBER = p_bank_number
516 , BANK_INSTITUTION_TYPE = p_bank_institution
517 , BANK_ADDRESS_ID = l_bank_location_id
518 , BRANCH_ID = p_branch_id
519 , BRANCH_NAME = p_branch_name
520 , BRANCH_NAME_ALT = p_branch_name_alt
521 , BRANCH_NUMBER = p_branch_number
522 , BRANCH_TYPE = p_branch_type
523 , RFC_IDENTIFIER = p_rfc_identifier
524 , BIC = p_bic
525 , BRANCH_ADDRESS_ID = l_branch_location_id
526 , EXT_BANK_ACCOUNT_ID = nvl(p_ext_bank_account_id, ext_bank_account_id)
527 , bank_account_num = p_bank_account_number
528 , bank_account_name = p_bank_account_name
529 , bank_account_name_alt = p_bank_account_name_alt
530 , check_digits = p_check_digits
531 , iban = p_iban
532 , currency_code = p_currency_code
533 , FOREIGN_PAYMENT_USE_FLAG = p_foreign_payment_use_flag
534 , bank_account_type = p_bank_account_type
535 , country_code = p_country_code
536 , description = p_account_description
537 , end_date = p_end_date
538 , start_date = p_start_date
539 , agency_location_code = p_agency_location_code
540 , account_suffix = p_account_suffix
541 , EXCHANGE_RATE_AGREEMENT_NUM = p_exchange_rate_agreement_num
542 , EXCHANGE_RATE_AGREEMENT_TYPE = p_exchange_rate_agreement_type
543 , EXCHANGE_RATE = p_exchange_rate
544 , NOTE = p_notes
545 , NOTE_ALT = p_note_alt
546 where temp_ext_bank_acct_id = p_temp_ext_bank_acct_id;
547
548 l_step := 4;
549
550 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
551 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
552 ' End update_iby_temp_account ');
553 END IF;
554
555 x_status :='S';
556 x_exception_msg :=NULL;
557
558 EXCEPTION
559 WHEN OTHERS THEN
560 raise_application_error(-20062, 'Failure at step ' || l_step || Sqlerrm, true);
561 END update_iby_temp_account;
562
563 /* This procedure creates the location.
564 *
565 */
566 PROCEDURE create_location (
567 p_ADDRESS1 in VARCHAR2
568 , p_ADDRESS2 in VARCHAR2
569 , p_ADDRESS3 in VARCHAR2
570 , p_ADDRESS4 in VARCHAR2
571 , p_CITY in VARCHAR2
572 , p_COUNTY in VARCHAR2
573 , p_STATE in VARCHAR2
574 , p_ZIP in VARCHAR2
575 , p_PROVINCE in VARCHAR2
576 , p_COUNTRY in VARCHAR2
577 , x_location_id out nocopy number
578 , x_status out nocopy VARCHAR2
579 , x_exception_msg out nocopy VARCHAR2
580 )
581 IS
582
583 l_step number;
584 l_msg_count number;
585 l_location_rec hz_location_v2pub.LOCATION_REC_TYPE;
586
587 BEGIN
588
589 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
590 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
591 ' Begin create_location ');
592 END IF;
593
594 if p_country is not null and p_address1 is not null then
595 l_location_rec.country := p_country;
596 l_location_rec.address1 := p_address1;
597 l_location_rec.address2 := p_address2;
598 l_location_rec.address3 := p_address3;
599 l_location_rec.address4 := p_address4;
600 l_location_rec.city := p_city;
601 l_location_rec.postal_code := p_zip;
602 l_location_rec.state := p_state;
603 l_location_rec.province := p_province;
604 l_location_rec.country := p_country;
605 l_location_rec.county := p_county;
606
607 l_location_rec.created_by_module := 'POS_SUPPLIER_MGMT';
608 l_location_rec.application_id := 177;
609
610 hz_location_v2pub.create_location (
611 p_init_msg_list => fnd_api.g_true,
612 p_location_rec => l_location_rec,
613 x_location_id => x_location_id,
614 x_return_status => x_status,
615 x_msg_count => l_msg_count,
616 x_msg_data => x_exception_msg);
617 else
618 x_status := 'S';
619 end if;
620
621 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
622 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
623 ' End create_location ');
624 END IF;
625
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 raise_application_error(-20063, 'Create location failure', true);
630 END create_location;
631
632
633
634 /* This procedure updates the location.
635 *
636 */
637 PROCEDURE update_location (
638 p_location_id in NUMBER
639 , p_ADDRESS1 in VARCHAR2
640 , p_ADDRESS2 in VARCHAR2
641 , p_ADDRESS3 in VARCHAR2
642 , p_ADDRESS4 in VARCHAR2
643 , p_CITY in VARCHAR2
644 , p_COUNTY in VARCHAR2
645 , p_STATE in VARCHAR2
646 , p_ZIP in VARCHAR2
647 , p_PROVINCE in VARCHAR2
648 , p_COUNTRY in VARCHAR2
649 , x_status out nocopy VARCHAR2
650 , x_exception_msg out nocopy VARCHAR2
651 )
652 IS
653
654 l_step number;
655 l_msg_count number;
656 l_obj_ver HZ_LOCATIONS.object_version_number%TYPE;
657 l_created_by_module HZ_PARTY_SITES.created_by_module%TYPE;
658 l_location_rec hz_location_v2pub.LOCATION_REC_TYPE;
659
660 BEGIN
661
662 l_step := 0;
663
664 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
665 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
666 ' Begin update_location ');
667 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
668 ' p_location_id ' || p_location_id);
669 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
670 ' p_country ' || p_country);
671 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
672 ' p_address1 ' || p_address1);
673 END IF;
674
675 select object_version_number, created_by_module
676 into l_obj_ver, l_created_by_module from hz_locations
677 where location_id = p_location_id;
678
679 l_step := 1;
680
681 l_location_rec.location_id := p_location_id;
682 l_location_rec.country := p_country;
683 l_location_rec.address1 := p_address1;
684
685 if p_address2 is not null then l_location_rec.address2 := p_address2;
686 else l_location_rec.address2 := FND_API.G_MISS_CHAR;
687 end if;
688
689 if p_address3 is not null then l_location_rec.address3 := p_address3;
690 else l_location_rec.address3 := FND_API.G_MISS_CHAR;
691 end if;
692
693 if p_address4 is not null then l_location_rec.address4 := p_address4;
694 else l_location_rec.address4 := FND_API.G_MISS_CHAR;
695 end if;
696
697 l_location_rec.city := p_city;
698 l_location_rec.postal_code := p_zip;
699
700 if p_state is not null then l_location_rec.state := p_state;
701 else l_location_rec.state := FND_API.G_MISS_CHAR;
702 end if;
703
704 if p_province is not null then l_location_rec.province := p_province;
705 else l_location_rec.province := FND_API.G_MISS_CHAR;
706 end if;
707
708 if p_county is not null then l_location_rec.county := p_county;
709 else l_location_rec.county := FND_API.G_MISS_CHAR;
710 end if;
711
712 l_location_rec.created_by_module := l_created_by_module;
713 l_location_rec.application_id := 177;
714 hz_location_v2pub.update_location (
715 p_init_msg_list => fnd_api.g_true,
716 p_location_rec => l_location_rec,
717 p_object_version_number => l_obj_ver,
718 x_return_status => x_status,
719 x_msg_count => l_msg_count,
720 x_msg_data => x_exception_msg
721 );
722
723 l_step := 2;
724
725 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
726 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
727 ' End update_location ');
728 END IF;
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 x_exception_msg := 'Failure at step ' || l_step;
733 raise_application_error(-20065, x_exception_msg, true);
734 END update_location;
735
736
737 PROCEDURE validate_account (
738 p_mapping_id in NUMBER
739 -- Bank
740 , p_BANK_ID in NUMBER
741 , p_BANK_NAME in VARCHAR2
742 , p_BANK_NAME_ALT in varchar2
743 , p_BANK_NUMBER in VARCHAR2
744 , p_BANK_INSTITUTION in varchar2
745 , p_BANK_ADDRESS1 in VARCHAR2
746 , p_BANK_ADDRESS2 in VARCHAR2
747 , p_BANK_ADDRESS3 in VARCHAR2
748 , p_BANK_ADDRESS4 in VARCHAR2
749 , p_BANK_CITY in VARCHAR2
750 , p_BANK_COUNTY in VARCHAR2
751 , p_BANK_STATE VARCHAR2
752 , p_BANK_ZIP in VARCHAR2
753 , p_BANK_PROVINCE in VARCHAR2
754 , p_BANK_COUNTRY in VARCHAR2
755 -- Branch
756 , p_BRANCH_ID in NUMBER
757 , p_BRANCH_NAME in VARCHAR2
758 , p_BRANCH_NAME_ALT in varchar2
759 , p_BRANCH_NUMBER in VARCHAR2
760 , p_BRANCH_TYPE in varchar2
761 , p_RFC_IDENTIFIER in varchar2
762 , p_BIC in varchar2
763 , p_BRANCH_ADDRESS1 in VARCHAR2
764 , p_BRANCH_ADDRESS2 in VARCHAR2
765 , p_BRANCH_ADDRESS3 in VARCHAR2
766 , p_BRANCH_ADDRESS4 in VARCHAR2
767 , p_BRANCH_CITY in VARCHAR2
768 , p_BRANCH_COUNTY in VARCHAR2
769 , p_BRANCH_STATE VARCHAR2
770 , p_BRANCH_ZIP in VARCHAR2
771 , p_BRANCH_PROVINCE in VARCHAR2
772 , p_BRANCH_COUNTRY in VARCHAR2
773 -- Account
774 , p_EXT_BANK_ACCOUNT_ID in number
775 , p_account_request_id in number
776 , p_bank_account_number in varchar2
777 , p_bank_account_name in varchar2
778 , p_bank_account_name_alt in varchar2
779 , p_check_digits in varchar2
780 , p_iban in varchar2
781 , p_currency_code in varchar2
782 , p_country_code in varchar2
783 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
784 , p_bank_account_type in varchar2
785 , p_account_description in varchar2
786 , p_end_date in date
787 , p_start_date in date
788 , p_agency_location_code in varchar2
789 , p_account_suffix in varchar2
790 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
791 , P_EXCHANGE_RATE_AGREEMENT_TYPE in VARCHAR2
792 , p_EXCHANGE_RATE in NUMBER
793 , x_status out nocopy VARCHAR2
794 , x_exception_msg out nocopy VARCHAR2
795 )
796
797 IS
798
799 l_ext_bank_rec IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
800 l_ext_bank_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
801 l_ext_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
802 l_result_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
803 l_msg_count number;
804 l_step number;
805 l_temp_bank_account_num IBY_EXT_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE;
806
807 cursor l_party_id_cur is
808 select party_id from pos_supplier_mappings where mapping_id = p_mapping_id;
809 l_party_id number;
810
811 l_need_validation varchar2(1);
812
813 l_create_flag varchar2(1);
814 BEGIN
815
816 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
817 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
818 ' Begin validate_account ');
819 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
820 ' p_mapping_id ' || p_mapping_id);
821 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
822 ' p_bank_id ' || p_bank_id);
823 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
824 ' p_branch_id ' || p_branch_id);
825 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
826 ' p_bank_name ' || p_bank_name);
827 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
828 ' p_branch_name ' || p_branch_name);
829 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
830 ' p_bank_number ' || p_bank_number);
831 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
832 ' p_branch_number ' || p_branch_number);
833 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
834 ' p_bank_account_number ' || p_bank_account_number);
835 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
836 ' p_bank_account_name ' || p_bank_account_name);
837 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
838 ' p_currency_code ' || p_currency_code);
839 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
840 ' p_country_code ' || p_country_code);
841 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
842 ' p_iban ' || p_iban);
843 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
844 ' p_check_digits ' || p_check_digits);
845 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
846 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
847 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
848 ' p_account_request_ud ' || p_account_request_id);
849 END IF;
850
851 l_step := 0;
852
853 -- Drop all the error message stack.
854 FND_MSG_PUB.initialize;
855
856 open l_party_id_cur;
857 fetch l_party_id_cur into l_party_id;
858 close l_party_id_cur;
859
860 l_step := 1;
861
862 POS_SBD_IBY_PKG.check_for_duplicates (
863 p_mapping_id => p_mapping_id
864 , p_BANK_ID => p_bank_id
865 , p_BANK_NAME => p_bank_name
866 , p_BANK_NUMBER => p_bank_number
867 , p_BRANCH_ID => p_branch_id
868 , p_BRANCH_NAME => p_branch_name
869 , p_BRANCH_NUMBER => p_branch_number
870 , p_EXT_BANK_ACCOUNT_ID => p_ext_bank_account_id
871 , p_bank_account_number => p_bank_account_number
872 , p_bank_account_name => p_bank_account_name
873 , p_currency_code => p_currency_code
874 , p_country_code => p_country_code
875 , p_account_request_id => p_account_request_id
876 , x_need_validation => l_need_validation
877 , x_status => x_status
878 , x_exception_msg => x_exception_msg);
879
880 l_step := 2;
881
882 if x_status <> 'S' then
883 return;
884 end if;
885
886 l_step := 3;
887
888 if l_need_validation = 'Y' then
889
890 l_step := 4;
891 l_ext_bank_rec.bank_id := p_bank_id;
892 l_ext_bank_rec.bank_name := p_bank_name;
893 l_ext_bank_rec.bank_number := p_bank_number;
894 l_ext_bank_rec.institution_type := p_bank_institution;
895 l_ext_bank_rec.country_code := p_country_code;
896 l_ext_bank_rec.bank_alt_name := p_bank_name_alt;
897
898 l_step := 5;
899 l_ext_bank_branch_rec.branch_party_id := p_branch_id;
900 l_ext_bank_branch_rec.branch_name := p_branch_name;
901 l_ext_bank_branch_rec.branch_number := p_branch_number;
902 l_ext_bank_branch_rec.branch_type := p_branch_type;
903 l_ext_bank_branch_rec.alternate_branch_name := p_branch_name_alt;
904 l_ext_bank_branch_rec.bic := p_bic;
905 l_ext_bank_branch_rec.rfc_identifier := p_rfc_identifier;
906
907 l_step := 6;
908 l_ext_bank_acct_rec.bank_account_id := p_EXT_BANK_ACCOUNT_ID;
909 l_ext_bank_acct_rec.country_code := p_country_code;
910 l_ext_bank_acct_rec.branch_id := p_branch_id;
911 l_ext_bank_acct_rec.bank_id := p_bank_id;
912 l_ext_bank_acct_rec.acct_owner_party_id := l_party_id;
913 l_ext_bank_acct_rec.bank_account_name := p_bank_account_name;
914
915 /* BUG 10384712 START */
916
917 IF p_EXT_BANK_ACCOUNT_ID IS NOT NULL THEN
918
919 SELECT BANK_ACCOUNT_NUM INTO l_temp_bank_account_num
920 FROM
921 IBY_EXT_BANK_ACCOUNTS
922 WHERE
923 EXT_BANK_ACCOUNT_ID=p_EXT_BANK_ACCOUNT_ID;
924
925 l_ext_bank_acct_rec.bank_account_num := l_temp_bank_account_num;
926 ELSE
927 l_ext_bank_acct_rec.bank_account_num := p_bank_account_number;
928 END IF;
929
930 /* BUG 10384712 END */
931
932 l_ext_bank_acct_rec.currency := p_currency_code;
933 l_ext_bank_acct_rec.iban := p_iban;
934 l_ext_bank_acct_rec.check_digits := p_check_digits;
935 l_ext_bank_acct_rec.alternate_acct_name := p_bank_account_name_alt;
936 l_ext_bank_acct_rec.acct_type := p_bank_account_type;
937 l_ext_bank_acct_rec.acct_suffix := p_account_suffix;
938 l_ext_bank_acct_rec.agency_location_code := p_agency_location_code;
939 l_ext_bank_acct_rec.foreign_payment_use_flag := p_foreign_payment_use_flag;
940 l_ext_bank_acct_rec.exchange_rate_agreement_num := p_exchange_rate_agreement_num;
941 l_ext_bank_acct_rec.exchange_rate_agreement_type := p_exchange_rate_agreement_type;
942 l_ext_bank_acct_rec.exchange_rate := p_exchange_rate;
943 l_ext_bank_acct_rec.payment_factor_flag := 'Y';
944 l_ext_bank_acct_rec.end_date := p_end_date;
945 l_ext_bank_acct_rec.START_DATE := p_start_date;
946
947 l_step := 7;
948
949 if p_ext_bank_account_id is null then
950 l_create_flag := FND_API.G_TRUE;
951 else
952 l_create_flag := FND_API.G_FALSE;
953 end if;
954
955 IBY_EXT_BANKACCT_VALIDATIONS.iby_validate_account(
956 p_api_version => 1.0,
957 p_init_msg_list => FND_API.G_TRUE,
958 p_create_flag => l_create_flag,
959 p_ext_bank_rec => l_ext_bank_rec,
960 p_ext_bank_branch_rec => l_ext_bank_branch_rec,
961 p_ext_bank_acct_rec => l_ext_bank_acct_rec,
962 x_return_status => x_status,
963 x_msg_count => l_msg_count,
964 x_msg_data => x_exception_msg,
965 x_response => l_result_rec
966 );
967
968 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
969 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
970 ' IBY API Validation Status ' || x_status);
971 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
972 ' IBY API Validation msg count ' || l_msg_count);
973 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
974 ' IBY API Validation exception msg ' || x_exception_msg);
975 END IF;
976
977 l_step := 8;
978
979 end if;
980
981 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
982 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
983 ' End validate_account ');
984 END IF;
985
986 EXCEPTION
987 WHEN OTHERS THEN
988 x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
989 raise_application_error(-20066, x_exception_msg, true);
990 END validate_account;
991
992 PROCEDURE assign_site_to_account (
993 p_temp_ext_bank_account_id in number
994 , p_vendor_site_id in number
995 , x_status out nocopy VARCHAR2
996 , x_exception_msg out nocopy VARCHAR2
997 )
998
999 IS
1000
1001 l_msg_count number;
1002 l_step number;
1003
1004 CURSOR l_temp_account_cur is
1005 select temp.ext_bank_account_id, temp.account_owner_party_id
1006 from IBY_TEMP_EXT_BANK_ACCTS temp
1007 where temp.temp_ext_bank_acct_id = p_temp_ext_bank_account_id;
1008
1009 l_temp_account_rec l_temp_account_cur%ROWTYPE;
1010 l_result_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1011 l_ext_bank_account_id IBY_TEMP_EXT_BANK_ACCTS.ext_bank_account_id%TYPE;
1012 l_payee_rec IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_Rec_Type;
1013 l_pay_instr_rec IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
1014 l_pay_assign_rec IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
1015 l_party_id number;
1016 L_PAYEE_ASSIGNMENT_ID number;
1017
1018 cursor l_site_detail_cur is
1019 select org_id, party_site_id from ap_supplier_sites_all where
1020 vendor_site_id = p_vendor_site_id;
1021 l_party_site_id number;
1022 l_org_id number;
1023
1024 cursor l_max_p_cur is
1025 select max(uses.order_of_preference)
1026 from iby_pmt_instr_uses_all uses, iby_external_payees_all payee,
1027 iby_ext_bank_accounts act, ap_supplier_sites_all pvsa
1028 where uses.instrument_type = 'BANKACCOUNT'
1029 AND sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate)
1030 and payee.ext_payee_id = uses.ext_pmt_party_id
1031 and payee.org_id = pvsa.org_id
1032 and payee.party_site_id = pvsa.party_site_id
1033 and org_type = 'OPERATING_UNIT'
1034 and pvsa.vendor_site_id = payee.supplier_site_id
1035 and payee.supplier_site_id = p_vendor_site_id
1036 and uses.instrument_id = act.ext_bank_account_id
1037 and payee.payee_party_id = l_party_id
1038 and payee.party_site_id is null;
1039
1040 l_priority number;
1041
1042 cursor l_payee_cur is
1043 select payee.object_version_number, payee.ext_payee_id from iby_external_payees_all payee
1044 where payee.ext_payee_id = l_party_id
1045 and payee.org_id is null
1046 and payee.party_site_id is null
1047 and payee.supplier_site_id = p_vendor_site_id;
1048 l_cur_payee_rec l_payee_cur%ROWTYPE;
1049
1050 cursor l_payee_assignment_cur is
1051 select uses.object_version_number
1052 from iby_external_payees_all payee, iby_pmt_instr_uses_all uses
1053 where payee.ext_payee_id = l_party_id
1054 and payee.org_id is null
1055 and payee.party_site_id is null
1056 and payee.supplier_site_id = p_vendor_site_id
1057 and payee.ext_payee_id = uses.ext_pmt_party_id
1058 and uses.instrument_id = l_ext_bank_account_id
1059 and uses.instrument_type = 'BANKACCOUNT';
1060
1061 l_cur_payee_assign_rec l_payee_assignment_cur%ROWTYPE;
1062
1063
1064 BEGIN
1065
1066 l_step := 0;
1067 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1068 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1069 ' Begin assign_site_to_account ');
1070 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1071 ' p_vendor_site_id ' || p_vendor_site_id);
1072 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1073 ' p_temp_ext_bank_account_id ' || p_temp_ext_bank_account_id);
1074 END IF;
1075
1076 l_step := 1;
1077
1078 for l_temp_account_rec in l_temp_account_cur loop
1079
1080 l_ext_bank_account_id := l_temp_account_rec.ext_bank_account_id;
1081 l_party_id := l_temp_account_rec.account_owner_party_id;
1082
1083 l_step := 3;
1084
1085 open l_max_p_cur;
1086 fetch l_max_p_cur into l_priority;
1087 close l_max_p_cur;
1088
1089 l_step := 4;
1090
1091 if l_priority is null then
1092 l_priority := 1;
1093 else
1094 l_priority := l_priority + 1;
1095 end if;
1096
1097 open l_site_detail_cur;
1098 fetch l_site_detail_cur into l_org_id, l_party_site_id;
1099 close l_site_detail_cur;
1100
1101 l_step := 5;
1102 l_payee_rec.Payment_Function := 'PAYABLES_DISB';
1103 l_payee_rec.Party_id := l_party_id;
1104 l_payee_rec.Party_Site_id := l_party_site_id;
1105 l_payee_rec.org_Id := l_org_id;
1106 l_payee_rec.Supplier_Site_id := p_vendor_site_id;
1107 l_payee_rec.Org_Type := 'OPERATING_UNIT';
1108
1109 l_pay_instr_rec.Instrument_Type := 'BANKACCOUNT';
1110 l_pay_instr_rec.Instrument_Id := l_ext_bank_account_id;
1111
1112 l_pay_assign_rec.Instrument := l_pay_instr_rec;
1113 l_pay_assign_rec.Priority := l_priority;
1114 l_pay_assign_rec.Start_Date := sysdate;
1115 l_pay_assign_rec.End_Date := null;
1116
1117 l_step := 6;
1118
1119 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1120 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1121 ' Calling Set_Payee_Instr_Assignment ');
1122 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1123 ' l_payee_rec.Party_id ' || l_payee_rec.Party_id);
1124 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1125 ' l_payee_rec.Payment_Function ' || l_payee_rec.Payment_Function);
1126 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1127 ' l_payee_rec.Supplier_Site_id ' || l_payee_rec.Supplier_Site_id);
1128 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1129 ' l_pay_instr_rec.Instrument_Type ' || l_pay_instr_rec.Instrument_Type);
1130 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1131 ' l_pay_instr_rec.Instrument_Id ' || l_pay_instr_rec.Instrument_Id);
1132 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1133 ' l_pay_assign_rec.Priority ' || l_pay_assign_rec.Priority);
1134 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1135 ' l_pay_assign_rec.Start_Date ' || l_pay_assign_rec.Start_Date );
1136 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1137 ' l_pay_assign_rec.End_Date ' || l_pay_assign_rec.End_Date);
1138 END IF;
1139
1140 IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment(
1141 p_api_version => 1.0,
1142 p_init_msg_list => FND_API.G_FALSE,
1143 p_commit => FND_API.G_FALSE,
1144 x_return_status => x_status,
1145 x_msg_count => l_msg_count,
1146 x_msg_data => x_exception_msg,
1147 p_payee => l_payee_rec,
1148 p_assignment_attribs => l_pay_assign_rec,
1149 x_assign_id => l_payee_assignment_id,
1150 x_response => l_result_rec
1151 );
1152 l_step := 7;
1153
1154 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1155 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1156 ' After Set_Payee_Instr_Assignment ');
1157 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1158 ' x_return_status ' || x_status);
1159 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1160 ' x_msg_count ' || l_msg_count);
1161 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1162 ' x_msg_data ' || x_exception_msg);
1163 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1164 ' x_assign_id ' || l_payee_assignment_id);
1165 END IF;
1166
1167 if l_payee_assignment_id is null OR x_status is null OR x_status <> 'S' then
1168 l_step := 8;
1169 raise_application_error(-20084, 'IBY Failed to create assignment ' || x_exception_msg, true);
1170 end if;
1171
1172 end loop;
1173
1174 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1175 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1176 ' END assign_site_to_account ');
1177 END IF;
1178
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 x_status := 'E';
1182 x_exception_msg := 'Failure at step ' || l_step;
1183 raise_application_error(-20067, x_exception_msg, true);
1184 END assign_site_to_account;
1185
1186
1187 PROCEDURE prenote_iby_temp_account (
1188 p_temp_ext_bank_account_id in number
1189 , p_vendor_site_id in number
1190 , x_status out nocopy VARCHAR2
1191 , x_exception_msg out nocopy VARCHAR2
1192 )
1193
1194 IS
1195 l_step number;
1196 BEGIN
1197
1198 l_step := 0;
1199 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1200 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1201 ' Begin prenote_iby_temp_account ');
1202 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1203 ' p_vendor_site_id ' || p_vendor_site_id);
1204 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1205 ' p_temp_ext_bank_account_id ' || p_temp_ext_bank_account_id);
1206 END IF;
1207
1208 -- Create/Update the account
1209 POS_SBD_IBY_PKG.approve_iby_temp_account (
1210 p_temp_ext_bank_account_id => p_temp_ext_bank_account_id
1211 , x_status => x_status
1212 , x_exception_msg => x_exception_msg
1213 );
1214
1215 l_step := 1;
1216
1217 POS_SBD_IBY_PKG.assign_site_to_account (
1218 p_temp_ext_bank_account_id => p_temp_ext_bank_account_id
1219 , p_vendor_site_id => p_vendor_site_id
1220 , x_status => x_status
1221 , x_exception_msg => x_exception_msg
1222 );
1223
1224 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1225 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1226 ' END prenote_iby_temp_account ');
1227 END IF;
1228
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 x_exception_msg := 'Failure at step ' || l_step;
1232 raise_application_error(-20068, x_exception_msg, true);
1233 END prenote_iby_temp_account;
1234
1235
1236 PROCEDURE approve_iby_temp_account (
1237 p_temp_ext_bank_account_id in number
1238 , x_status out nocopy VARCHAR2
1239 , x_exception_msg out nocopy VARCHAR2
1240 )
1241
1242 IS
1243
1244 l_msg_count number;
1245 l_step number;
1246
1247 l_temp_bank_account_num IBY_EXT_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE;
1248 l_record_type IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1249 l_end_date DATE;
1250 l_start_date DATE;
1251 l_count NUMBER;
1252 l_msg_data varchar(2000);
1253 CURSOR l_temp_account_cur is
1254
1255 select req.account_request_id,
1256 temp.bank_id, temp.bank_name, temp.bank_number,
1257 temp.bank_institution_type, temp.bank_name_alt,
1258 temp.bank_address_id,
1259 temp.branch_id, temp.branch_name, temp.branch_number, temp.bic,
1260 temp.branch_type, temp.branch_name_alt, temp.rfc_identifier,
1261 temp.branch_address_id,
1262 temp.ext_bank_account_id, temp.account_owner_party_id,
1263 temp.country_code, temp.FOREIGN_PAYMENT_USE_FLAG,
1264 temp.bank_account_name, temp.bank_account_num, temp.check_digits,
1265 temp.iban, temp.currency_code,
1266 temp.bank_account_name_alt, temp.bank_account_type,
1267 temp.description, temp.end_date, temp.start_date, temp.agency_location_code,
1268 temp.status, temp.note, temp.note_alt, temp.account_suffix, temp.exchange_rate,
1269 temp.exchange_rate_agreement_num, temp.exchange_rate_agreement_type, temp.payment_factor_flag
1270 from IBY_TEMP_EXT_BANK_ACCTS temp, pos_acnt_gen_req req
1271 where temp.temp_ext_bank_acct_id = p_temp_ext_bank_account_id
1272 and req.temp_ext_bank_acct_id = temp.temp_ext_bank_acct_id;
1273 l_temp_account_rec l_temp_account_cur%ROWTYPE;
1274
1275 l_ext_bank_rec IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
1276 l_ext_bank_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
1277 l_ext_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
1278 l_result_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1279 l_primary_owner_id HZ_PARTIES.party_id%TYPE;
1280 l_non_primary_owner_id HZ_PARTIES.party_id%TYPE;
1281 l_joint_acct_owner_id NUMBER;
1282
1283 l_bank_id IBY_TEMP_EXT_BANK_ACCTS.bank_id%TYPE;
1284 l_branch_id IBY_TEMP_EXT_BANK_ACCTS.branch_id%TYPE;
1285 l_ext_bank_account_id IBY_TEMP_EXT_BANK_ACCTS.ext_bank_account_id%TYPE;
1286 l_account_request_id POS_ACNT_GEN_REQ.account_request_id%TYPE;
1287 l_bank_address_id IBY_TEMP_EXT_BANK_ACCTS.bank_address_id%TYPE;
1288 l_branch_address_id IBY_TEMP_EXT_BANK_ACCTS.branch_address_id%TYPE;
1289 l_bank_party_site_id number;
1290 l_bank_party_site_number varchar2(2000);
1291 l_branch_party_site_id number;
1292 l_branch_party_site_number varchar2(2000);
1293 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
1294
1295 cursor l_cur_act_cur is
1296 select act.object_version_number, ow.account_owner_party_id
1297 from iby_ext_bank_accounts act, iby_account_owners ow
1298 where ow.ext_bank_account_id = act.ext_bank_account_id
1299 and act.ext_bank_account_id = l_ext_bank_account_id
1300 and ow.primary_flag = 'Y'
1301 and NVL(ow.end_date,SYSDATE+10)>SYSDATE
1302 AND sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate);
1303 l_cur_act_rec l_cur_act_cur%ROWTYPE;
1304 BEGIN
1305
1306 x_status := 'S';
1307
1308 l_step := 0;
1309
1310 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1311 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1312 ' BEGIN approve_iby_temp_account ');
1313 END IF;
1314
1315 for l_temp_account_rec in l_temp_account_cur loop
1316
1317 l_bank_id := l_temp_account_rec.bank_id;
1318 l_branch_id := l_temp_account_rec.branch_id;
1319 l_ext_bank_account_id := l_temp_account_rec.ext_bank_account_id;
1320 l_branch_address_id := l_temp_account_rec.branch_address_id;
1321 l_bank_address_id := l_temp_account_rec.bank_address_id;
1322 l_account_request_id := l_temp_account_rec.account_request_id;
1323
1324 l_step := 1;
1325 if (l_bank_id is null and
1326 (l_temp_account_rec.bank_number is not null OR l_temp_account_rec.bank_name is not null))
1327 then
1328
1329 -- Load the bank record.
1330 l_ext_bank_rec.bank_name := l_temp_account_rec.bank_name;
1331 l_ext_bank_rec.bank_number := l_temp_account_rec.bank_number;
1332 l_ext_bank_rec.institution_type := 'BANK';
1333 l_ext_bank_rec.country_code := l_temp_account_rec.country_code;
1334 l_ext_bank_rec.bank_alt_name := l_temp_account_rec.bank_name_alt;
1335
1336 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1337 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1338 ' Calling IBY_EXT_BANKACCT_PUB.create_ext_bank');
1339 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1340 ' l_ext_bank_rec.bank_name ' || l_ext_bank_rec.bank_name);
1341 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1342 ' l_ext_bank_rec.bank_number ' || l_ext_bank_rec.bank_number);
1343 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1344 ' l_ext_bank_rec.institution_type ' || l_ext_bank_rec.institution_type );
1345 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1346 ' l_ext_bank_rec.country_code ' || l_ext_bank_rec.country_code);
1347 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1348 ' l_ext_bank_rec.bank_alt_name ' || l_ext_bank_rec.bank_alt_name);
1349 END IF;
1350
1351 -- Create a bank
1352 IBY_EXT_BANKACCT_PUB.create_ext_bank (
1353 p_api_version => 1.0,
1354 p_init_msg_list => FND_API.G_TRUE,
1355 p_ext_bank_rec => l_ext_bank_rec,
1356 x_bank_id => l_bank_id,
1357 x_return_status => x_status,
1358 x_msg_count => l_msg_count,
1359 x_msg_data => x_exception_msg,
1360 x_response => l_result_rec
1361 );
1362
1363 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1364 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1365 ' After Calling IBY_EXT_BANKACCT_PUB.create_ext_bank');
1366 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1367 ' x_return_status ' || x_status);
1368 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1369 ' x_msg_count ' || l_msg_count);
1370 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1371 ' x_msg_data ' || x_exception_msg);
1372 END IF;
1373
1374 if l_bank_id is null OR x_status <> 'S' then
1375 raise_application_error(-20073, 'IBY Failed to create a bank', true);
1376 end if;
1377
1378 if l_bank_address_id is not null then
1379
1380 l_party_site_rec.party_id := l_bank_id;
1381 l_party_site_rec.party_site_name := null;
1382 l_party_site_rec.status := 'A';
1383 l_party_site_rec.location_id := l_bank_address_id;
1384 l_party_site_rec.identifying_address_flag := 'Y';
1385 l_party_site_rec.created_by_module := 'POS_SUPPLIER_MGMT';
1386 l_party_site_rec.application_id := 177;
1387
1388 hz_party_site_v2pub.create_party_site
1389 (
1390 p_init_msg_list => FND_API.G_FALSE,
1391 p_party_site_rec => l_party_site_rec,
1392 x_party_site_id => l_bank_party_site_id,
1393 x_party_site_number => l_bank_party_site_number,
1394 x_return_status => x_status,
1395 x_msg_count => l_msg_count,
1396 x_msg_data => x_exception_msg);
1397 end if;
1398 end if;
1399
1400 l_step := 2;
1401 if (l_branch_id is null AND
1402 (l_temp_account_rec.branch_number is not null OR l_temp_account_rec.branch_name is not null))
1403 then
1404
1405 l_ext_bank_branch_rec.bank_party_id := l_bank_id;
1406 l_ext_bank_branch_rec.branch_name := l_temp_account_rec.branch_name;
1407 l_ext_bank_branch_rec.branch_number := l_temp_account_rec.branch_number;
1408 l_ext_bank_branch_rec.branch_type := l_temp_account_rec.branch_type;
1409 l_ext_bank_branch_rec.alternate_branch_name := l_temp_account_rec.branch_name_alt;
1410 l_ext_bank_branch_rec.bic := l_temp_account_rec.bic;
1411 l_ext_bank_branch_rec.rfc_identifier := l_temp_account_rec.rfc_identifier;
1412
1413 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1414 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1415 ' Calling IBY_EXT_BANKACCT_PUB.create_ext_bank_branch');
1416 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1417 ' l_ext_bank_branch_rec.bank_party_id ' || l_ext_bank_branch_rec.bank_party_id);
1418 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1419 ' l_ext_bank_branch_rec.branch_name ' || l_ext_bank_branch_rec.branch_name);
1420 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1421 ' l_ext_bank_branch_rec.branch_number ' || l_ext_bank_branch_rec.branch_number);
1422 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1423 ' l_ext_bank_branch_rec.branch_type ' || l_ext_bank_branch_rec.branch_type);
1424 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1425 ' l_ext_bank_branch_rec.alternate_branch_name '
1426 || l_ext_bank_branch_rec.alternate_branch_name);
1427 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1428 ' l_ext_bank_branch_rec.bic ' || l_ext_bank_branch_rec.bic );
1429 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1430 ' l_ext_bank_branch_rec.rfc_identifier ' || l_ext_bank_branch_rec.rfc_identifier);
1431 END IF;
1432
1433 IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
1434 p_api_version => 1.0,
1435 p_init_msg_list => FND_API.G_TRUE,
1436 p_ext_bank_branch_rec => l_ext_bank_branch_rec,
1437 x_branch_id => l_branch_id,
1438 x_return_status => x_status,
1439 x_msg_count => l_msg_count,
1440 x_msg_data => x_exception_msg,
1441 x_response => l_result_rec
1442 );
1443
1444 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1445 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1446 ' After Calling IBY_EXT_BANKACCT_PUB.create_ext_bank_branch');
1447 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1448 ' x_return_status ' || x_status);
1449 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1450 ' x_msg_count ' || l_msg_count);
1451 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1452 ' x_msg_data ' || x_exception_msg);
1453 END IF;
1454
1455 if l_branch_id is null OR x_status <> 'S' then
1456 raise_application_error(-20072, 'IBY Failed to create a branch', true);
1457 end if;
1458
1459 if l_branch_address_id is not null then
1460
1461 l_party_site_rec.party_id := l_branch_id;
1462 l_party_site_rec.party_site_name := null;
1463 l_party_site_rec.status := 'A';
1464 l_party_site_rec.location_id := l_branch_address_id;
1465 l_party_site_rec.identifying_address_flag := 'Y';
1466 l_party_site_rec.created_by_module := 'POS_SUPPLIER_MGMT';
1467 l_party_site_rec.application_id := 177;
1468
1469 hz_party_site_v2pub.create_party_site
1470 (
1471 p_init_msg_list => FND_API.G_FALSE,
1472 p_party_site_rec => l_party_site_rec,
1473 x_party_site_id => l_branch_party_site_id,
1474 x_party_site_number => l_branch_party_site_number,
1475 x_return_status => x_status,
1476 x_msg_count => l_msg_count,
1477 x_msg_data => x_exception_msg);
1478 end if;
1479 end if;
1480
1481 l_step := 3;
1482
1483 -- Find out if it exists
1484 if l_ext_bank_account_id is null then
1485 IBY_EXT_BANKACCT_PUB.check_ext_acct_exist(
1486 p_api_version => 1.0,
1487 p_init_msg_list => FND_API.G_FALSE,
1488 p_bank_id => l_bank_id,
1489 p_branch_id => l_branch_id,
1490 p_acct_number => l_temp_account_rec.bank_account_num,
1491 p_acct_name => l_temp_account_rec.bank_account_name,
1492 p_currency => l_temp_account_rec.currency_code,
1493 p_country_code => l_temp_account_rec.country_code,
1494 x_acct_id => l_ext_bank_account_id,
1495 x_start_date => l_start_date,
1496 x_end_date => l_end_date,
1497 x_return_status => x_status,
1498 x_msg_count => l_msg_count,
1499 x_msg_data => l_msg_data,
1500 x_response => l_record_type);
1501
1502 end if;
1503
1504 -- Find out if we need to add this person as an owner.
1505 if l_ext_bank_account_id is not null then
1506 open l_cur_act_cur;
1507 fetch l_cur_act_cur into l_cur_act_rec;
1508 close l_cur_act_cur;
1509
1510 l_primary_owner_id := l_cur_act_rec.account_owner_party_id;
1511 l_non_primary_owner_id := l_temp_account_rec.ACCOUNT_OWNER_PARTY_ID;
1512
1513 if l_primary_owner_id <> l_temp_account_rec.ACCOUNT_OWNER_PARTY_ID then
1514 IBY_EXT_BANKACCT_PUB.add_joint_account_owner (
1515 p_api_version => 1.0,
1516 p_init_msg_list => FND_API.G_FALSE,
1517 p_bank_account_id => l_ext_bank_account_id,
1518 p_acct_owner_party_id => l_non_primary_owner_id,
1519 x_joint_acct_owner_id => l_joint_acct_owner_id,
1520 x_return_status => x_status,
1521 x_msg_count => l_msg_count,
1522 x_msg_data => l_msg_data,
1523 x_response => l_record_type);
1524 end if;
1525
1526 IF x_status <> 'S' then
1527 raise_application_error(-20074, 'IBY API AJAA Failed' || l_msg_data, true);
1528 END IF;
1529 end if;
1530
1531 l_ext_bank_acct_rec.bank_account_id := l_ext_bank_account_id;
1532 l_ext_bank_acct_rec.country_code := l_temp_account_rec.country_code;
1533 l_ext_bank_acct_rec.branch_id := l_branch_id;
1534 l_ext_bank_acct_rec.bank_id := l_bank_id;
1535 l_ext_bank_acct_rec.acct_owner_party_id := l_temp_account_rec.ACCOUNT_OWNER_PARTY_ID;
1536 l_ext_bank_acct_rec.bank_account_name := l_temp_account_rec.bank_account_name;
1537 l_ext_bank_acct_rec.bank_account_num := l_temp_account_rec.bank_account_num;
1538 l_ext_bank_acct_rec.currency := l_temp_account_rec.currency_code;
1539 l_ext_bank_acct_rec.iban := l_temp_account_rec.iban;
1540 l_ext_bank_acct_rec.check_digits := l_temp_account_rec.check_digits;
1541 l_ext_bank_acct_rec.alternate_acct_name := l_temp_account_rec.bank_account_name_alt;
1542 l_ext_bank_acct_rec.acct_type := l_temp_account_rec.bank_account_type;
1543 l_ext_bank_acct_rec.acct_suffix := l_temp_account_rec.account_suffix;
1544 l_ext_bank_acct_rec.agency_location_code := l_temp_account_rec.agency_location_code;
1545 l_ext_bank_acct_rec.foreign_payment_use_flag := l_temp_account_rec.FOREIGN_PAYMENT_USE_FLAG;
1546 l_ext_bank_acct_rec.payment_factor_flag := l_temp_account_rec.payment_factor_flag;
1547 l_ext_bank_acct_rec.end_date := l_temp_account_rec.end_date;
1548 l_ext_bank_acct_rec.start_date := l_temp_account_rec.start_date;
1549 l_ext_bank_acct_rec.description := l_temp_account_rec.description;
1550
1551
1552 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1553 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1554 ' Calling IBY_EXT_BANKACCT_PUB.create/update_ext_bank_acct');
1555 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1556 ' l_ext_bank_acct_rec.bank_account_id ' || l_ext_bank_acct_rec.bank_account_id);
1557 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1558 ' l_ext_bank_acct_rec.country_code ' || l_ext_bank_acct_rec.country_code);
1559 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1560 ' l_ext_bank_acct_rec.branch_id ' || l_ext_bank_acct_rec.branch_id);
1561 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1562 ' l_ext_bank_acct_rec.bank_id ' || l_ext_bank_acct_rec.bank_id);
1563 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1564 ' l_ext_bank_acct_rec.acct_owner_party_id ' || l_ext_bank_acct_rec.acct_owner_party_id);
1565 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1566 ' l_ext_bank_acct_rec.bank_account_name ' || l_ext_bank_acct_rec.bank_account_name);
1567 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1568 ' l_ext_bank_acct_rec.bank_account_num ' || l_ext_bank_acct_rec.bank_account_num);
1569 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1570 ' l_ext_bank_acct_rec.currency ' || l_ext_bank_acct_rec.currency);
1571 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1572 ' l_ext_bank_acct_rec.iban ' || l_ext_bank_acct_rec.iban);
1573 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1574 ' l_ext_bank_acct_rec.check_digits ' || l_ext_bank_acct_rec.check_digits);
1575 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1576 ' l_ext_bank_acct_rec.alternate_acct_name ' || l_ext_bank_acct_rec.alternate_acct_name);
1577 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1578 ' l_ext_bank_acct_rec.acct_type ' || l_ext_bank_acct_rec.acct_type);
1579 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1580 ' l_ext_bank_acct_rec.acct_suffix ' || l_ext_bank_acct_rec.acct_suffix);
1581 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1582 ' l_ext_bank_acct_rec.agency_location_code ' || l_ext_bank_acct_rec.agency_location_code);
1583 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1584 ' l_ext_bank_acct_rec.foreign_payment_use_flag '
1585 || l_ext_bank_acct_rec.foreign_payment_use_flag);
1586 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1587 ' l_ext_bank_acct_rec.payment_factor_flag ' || l_ext_bank_acct_rec.payment_factor_flag);
1588 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1589 ' l_ext_bank_acct_rec.end_date ' || l_ext_bank_acct_rec.end_date);
1590 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1591 ' l_ext_bank_acct_rec.start_date ' || l_ext_bank_acct_rec.start_date);
1592 END IF;
1593
1594
1595 if l_ext_bank_account_id is null then
1596
1597 l_ext_bank_acct_rec.exchange_rate_agreement_num := l_temp_account_rec.exchange_rate_agreement_num;
1598 l_ext_bank_acct_rec.exchange_rate_agreement_type := l_temp_account_rec.exchange_rate_agreement_type;
1599 l_ext_bank_acct_rec.exchange_rate := l_temp_account_rec.exchange_rate;
1600
1601 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1602 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1603 ' l_ext_bank_acct_rec.exchange_rate_agreement_num '
1604 || l_ext_bank_acct_rec.exchange_rate_agreement_num);
1605 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1606 ' l_ext_bank_acct_rec.exchange_rate_agreement_type '
1607 || l_ext_bank_acct_rec.exchange_rate_agreement_type);
1608 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1609 ' l_ext_bank_acct_rec.exchange_rate ' || l_ext_bank_acct_rec.exchange_rate);
1610 END IF;
1611
1612 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1613 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1614 ' Now Calling IBY_EXT_BANKACCT_PUB.create_ext_bank_acct');
1615 END IF;
1616
1617 IBY_EXT_BANKACCT_PUB.create_ext_bank_acct (
1618 p_api_version => 1.0,
1619 p_init_msg_list => FND_API.G_TRUE,
1620 p_ext_bank_acct_rec => l_ext_bank_acct_rec,
1621 x_acct_id => l_ext_bank_account_id,
1622 x_return_status => x_status,
1623 x_msg_count => l_msg_count,
1624 x_msg_data => x_exception_msg,
1625 x_response => l_result_rec
1626 );
1627
1628 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1629 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1630 ' After Calling IBY_EXT_BANKACCT_PUB.create_ext_bank_acct');
1631 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1632 ' x_return_status ' || x_status);
1633 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1634 ' x_msg_count ' || l_msg_count);
1635 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1636 ' x_msg_data ' || x_exception_msg);
1637 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1638 ' x_acct_id ' || l_ext_bank_account_id);
1639
1640 END IF;
1641
1642 IF x_status <> 'S' OR l_ext_bank_account_id is null then
1643 raise_application_error(-20070, 'IBY API CEBA' || x_exception_msg, true);
1644 END IF;
1645
1646 else
1647
1648 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1649 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1650 ' Now Calling IBY_EXT_BANKACCT_PUB.update_ext_bank_acct');
1651 END IF;
1652
1653 open l_cur_act_cur;
1654 fetch l_cur_act_cur into l_cur_act_rec;
1655 close l_cur_act_cur;
1656
1657 l_ext_bank_acct_rec.object_version_number := l_cur_act_rec.object_version_number;
1658
1659
1660 /* BUG 10384712 START */
1661
1662 IF l_ext_bank_account_id IS NOT NULL THEN
1663
1664 SELECT BANK_ACCOUNT_NUM INTO l_temp_bank_account_num
1665 FROM
1666 IBY_EXT_BANK_ACCOUNTS
1667 WHERE
1668 EXT_BANK_ACCOUNT_ID=l_ext_bank_account_id;
1669
1670 l_ext_bank_acct_rec.bank_account_num := l_temp_bank_account_num;
1671 END IF;
1672
1673 /* BUG 10384712 END */
1674
1675 IBY_EXT_BANKACCT_PUB.update_ext_bank_acct (
1676 p_api_version => 1.0,
1677 p_init_msg_list => FND_API.G_TRUE,
1678 p_ext_bank_acct_rec => l_ext_bank_acct_rec,
1679 x_return_status => x_status,
1680 x_msg_count => l_msg_count,
1681 x_msg_data => x_exception_msg,
1682 x_response => l_result_rec
1683 );
1684
1685 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1686 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1687 ' After Calling IBY_EXT_BANKACCT_PUB.update_ext_bank_acct');
1688 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1689 ' x_return_status ' || x_status);
1690 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1691 ' x_msg_count ' || l_msg_count);
1692 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1693 ' x_msg_data ' || x_exception_msg);
1694 END IF;
1695
1696 IF x_status <> 'S' then
1697 raise_application_error(-20071, 'IBY API UEBA Failed' || x_exception_msg, true);
1698 END IF;
1699
1700 end if;
1701
1702 POS_SBD_IBY_PKG.update_req_with_account (
1703 p_temp_ext_bank_account_id => p_temp_ext_bank_account_id
1704 , p_ext_bank_account_id => l_ext_bank_account_id
1705 , p_account_request_id => l_account_request_id
1706 , p_bank_id => l_bank_id
1707 , p_branch_id => l_branch_id
1708 , x_status => x_status
1709 , x_exception_msg => x_exception_msg
1710 );
1711
1712 end loop;
1713
1714 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1715 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1716 ' End approve_iby_temp_account');
1717 END IF;
1718 EXCEPTION
1719 WHEN OTHERS THEN
1720 x_exception_msg := 'Failure at step ' || l_step;
1721 raise_application_error(-20069, x_exception_msg, true);
1722 END approve_iby_temp_account;
1723
1724 PROCEDURE update_req_with_account (
1725 p_temp_ext_bank_account_id in number
1726 , p_ext_bank_account_id in number
1727 , p_account_request_id in number
1728 , p_bank_id in number
1729 , p_branch_id in number
1730 , x_status out nocopy VARCHAR2
1731 , x_exception_msg out nocopy VARCHAR2
1732 )
1733 IS
1734
1735 l_step number;
1736
1737 BEGIN
1738
1739 l_step := 0;
1740 update pos_acnt_addr_summ_req
1741 set ext_bank_account_id = p_ext_bank_account_id,
1742 last_update_date = sysdate,
1743 last_updated_by = fnd_global.user_id,
1744 last_update_login = fnd_global.login_id,
1745 object_version_number = object_version_number + 1
1746 where account_request_id = p_account_request_id;
1747
1748 l_step := 2;
1749 update pos_acnt_gen_req
1750 set ext_bank_account_id = p_ext_bank_account_id,
1751 last_update_date = sysdate,
1752 last_updated_by = fnd_global.user_id,
1753 last_update_login = fnd_global.login_id,
1754 object_version_number = object_version_number + 1
1755 where account_request_id = p_account_request_id;
1756
1757 l_step := 3;
1758 update iby_temp_ext_bank_accts
1759 set bank_id = p_bank_id,
1760 branch_id = p_branch_id,
1761 last_update_date = sysdate,
1762 last_updated_by = fnd_global.user_id,
1763 last_update_login = fnd_global.login_id,
1764 object_version_number = object_version_number + 1,
1765 ext_bank_account_id = p_ext_bank_account_id
1766 where temp_ext_bank_acct_id = p_temp_ext_bank_account_id;
1767
1768 x_status := 'S';
1769
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 x_exception_msg := 'Failure at step ' || l_step;
1773 raise_application_error(-20078, x_exception_msg, true);
1774 END update_req_with_account;
1775
1776
1777 PROCEDURE check_for_duplicates (
1778 p_mapping_id in NUMBER
1779 , p_BANK_ID in NUMBER
1780 , p_BANK_NAME in VARCHAR2
1781 , p_BANK_NUMBER in VARCHAR2
1782 , p_BRANCH_ID in NUMBER
1783 , p_BRANCH_NAME in VARCHAR2
1784 , p_BRANCH_NUMBER in VARCHAR2
1785 , p_EXT_BANK_ACCOUNT_ID in number
1786 , p_bank_account_number in varchar2
1787 , p_bank_account_name in varchar2
1788 , p_currency_code in varchar2
1789 , p_country_code in varchar2
1790 , p_account_request_id in number
1791 , x_need_validation out nocopy varchar2
1792 , x_status out nocopy VARCHAR2
1793 , x_exception_msg out nocopy VARCHAR2
1794 )
1795
1796 IS
1797
1798 l_step number;
1799 l_bank_id HZ_PARTIES.party_id%TYPE;
1800 l_branch_id HZ_PARTIES.party_id%TYPE;
1801 l_ext_bank_account_id IBY_TEMP_EXT_BANK_ACCTS.EXT_BANK_ACCOUNT_ID%TYPE;
1802 l_record_type IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1803 l_end_date DATE;
1804 l_start_date DATE;
1805 l_msg_count NUMBER;
1806 l_count NUMBER;
1807 l_msg_data varchar(2000);
1808
1809 cursor dup_temp_act_cur is
1810 select count(*) from iby_temp_ext_bank_accts iby, pos_acnt_gen_req pos
1811 where pos.mapping_id = p_mapping_id
1812 and pos.temp_ext_bank_acct_id = iby.temp_ext_bank_acct_id
1813 and (
1814 (iby.currency_code = p_currency_code
1815 and p_currency_code is not null and iby.currency_code is not null) OR
1816 (p_currency_code is null and iby.currency_code is null)
1817 )
1818
1819 and iby.country_code = p_country_code
1820 and iby.status in ('NEW', 'IN_VERIFICATION', 'VERIFICATION_FAILED', 'CORRECTED', 'CHANGE_PENDING')
1821 AND ((iby.bank_id = p_bank_id and p_bank_id is not null and iby.bank_id is not null) OR
1822 (iby.bank_number = p_bank_number and p_bank_number is not null and iby.bank_number is not null) OR
1823 (p_bank_id is null and iby.bank_id is null and p_bank_number is null and iby.bank_number is null)
1824 )
1825 AND (
1826 (iby.branch_id = p_branch_id and p_branch_id is not null and iby.branch_id is not null) OR
1827 (iby.branch_number = p_branch_number and p_branch_number is not null
1828 and iby.branch_number is not null) OR
1829 (p_branch_id is null and iby.branch_id is null
1830 and p_branch_number is null and iby.branch_number is null)
1831 )
1832
1833 AND (
1834 (iby.bank_account_num = p_bank_account_number and p_bank_account_number is not null
1835 and iby.bank_account_num is not null) OR
1836 (iby.bank_account_name = p_bank_account_name and p_bank_account_name is not null
1837 and iby.bank_account_name is not null)
1838 )
1839 AND ((pos.account_request_id <> p_account_request_id and p_account_request_id is not null and
1840 pos.account_request_id is not null) OR (p_account_request_id is null));
1841
1842 cursor dup_cur_act_cur is
1843 select count(*) from iby_ext_bank_accounts act, iby_account_owners o, pos_supplier_mappings pmap
1844 where o.ext_bank_account_id = act.ext_bank_account_id
1845 and (
1846 (act.currency_code = p_currency_code
1847 and act.currency_code is not null and p_currency_code is not null) OR
1848 (act.currency_code is null and p_currency_code is null)
1849 )
1850 and o.account_owner_party_id = pmap.party_id
1851 and pmap.mapping_id = p_mapping_id
1852 and ((act.bank_id = l_bank_id and act.bank_id is not null and l_bank_id is not null) OR
1853 (act.bank_id is null and l_bank_id is null))
1854 and ((act.branch_id = l_branch_id and act.branch_id is not null
1855 and l_branch_id is not null) OR
1856 (act.branch_id is null and l_branch_id is null))
1857 and (
1858 act.bank_account_name = p_bank_account_name
1859 and act.bank_account_name is not null and p_bank_account_name is not null
1860 )
1861 and ((act.ext_bank_account_id <> p_EXT_BANK_ACCOUNT_ID and p_EXT_BANK_ACCOUNT_ID is not null)
1862 OR p_EXT_BANK_ACCOUNT_ID is null)
1863 and act.country_code = p_country_code
1864 and not exists
1865 (
1866 select 1 from IBY_TEMP_EXT_BANK_ACCTS temp
1867 where temp.EXT_BANK_ACCOUNT_ID = act.ext_bank_account_id
1868 and temp.status in ('CORRECTED', 'NEW', 'IN_VERIFICATION', 'VERIFICATION_FAILED', 'CHANGE_PENDING')
1869 and temp.account_owner_party_id = o.account_owner_party_id
1870 )
1871 and ((act.ext_bank_account_id <> p_ext_bank_account_id and p_ext_bank_account_id is not null and
1872 act.ext_bank_account_id is not null) OR (p_ext_bank_account_id is null))
1873 AND sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate);
1874
1875 BEGIN
1876
1877 l_step := 0;
1878
1879 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1880 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1881 ' Begin check_for_duplicates ');
1882 END IF;
1883
1884 x_need_validation := 'Y';
1885 x_status := 'S';
1886
1887 -- If bank id is not specified then check if its duplicate
1888 if p_bank_id is null then
1889 ce_bank_pub.check_bank_exist(
1890 p_country_code => p_country_code,
1891 p_bank_name => p_bank_name,
1892 p_bank_number => p_bank_number,
1893 x_bank_id => l_bank_id,
1894 x_end_date => l_end_date);
1895
1896 if l_bank_id is not null then
1897 x_status := 'E';
1898 fnd_message.set_name('POS', 'POS_SBD_DUP_BANK');
1899 fnd_msg_pub.add;
1900 end if;
1901
1902 else
1903 l_bank_id := p_bank_id;
1904 end if;
1905
1906 -- If branch id is not specified then check if its duplicate.
1907 if p_branch_id is null then
1908
1909 if l_bank_id is not null then
1910
1911 ce_bank_pub.check_branch_exist(
1912 p_bank_id => l_bank_id,
1913 p_branch_name => p_branch_name,
1914 p_branch_number => p_branch_number,
1915 x_branch_id => l_branch_id,
1916 x_end_date => l_end_date);
1917 if l_branch_id is not null then
1918 x_status := 'E';
1919 fnd_message.set_name('POS', 'POS_SBD_DUP_BRANCH');
1920 fnd_msg_pub.add;
1921 end if;
1922
1923 end if;
1924 else
1925 l_branch_id := p_branch_id;
1926 end if;
1927
1928 open dup_temp_act_cur;
1929 fetch dup_temp_act_cur into l_count;
1930 if l_count is null then l_count := 0;
1931 end if;
1932 if l_count <> 0 then
1933 x_status := 'E';
1934 fnd_message.set_name('POS', 'POS_SBD_DUP_ACT2');
1935 fnd_msg_pub.add;
1936 end if;
1937 close dup_temp_act_cur;
1938
1939 l_count := 0;
1940 open dup_cur_act_cur;
1941 fetch dup_cur_act_cur into l_count;
1942 if l_count is null then l_count := 0;
1943 end if;
1944 close dup_cur_act_cur;
1945
1946 if l_count <> 0 then
1947 x_status := 'E';
1948 fnd_message.set_name('POS', 'POS_SBD_DUP_ACT1');
1949 fnd_msg_pub.add;
1950 end if;
1951
1952 if l_bank_id is not null and l_branch_id is not null and
1953 p_EXT_BANK_ACCOUNT_ID is null and x_status = 'S' then
1954 IBY_EXT_BANKACCT_PUB.check_ext_acct_exist(
1955 p_api_version => 1.0,
1956 p_init_msg_list => FND_API.G_FALSE,
1957 p_bank_id => l_bank_id,
1958 p_branch_id => l_branch_id,
1959 p_acct_number => p_bank_account_number,
1960 p_acct_name => p_bank_account_name,
1961 p_currency => p_currency_code,
1962 p_country_code => p_country_code,
1963 x_acct_id => l_ext_bank_account_id,
1964 x_start_date => l_start_date,
1965 x_end_date => l_end_date,
1966 x_return_status => x_status,
1967 x_msg_count => l_msg_count,
1968 x_msg_data => l_msg_data,
1969 x_response => l_record_type);
1970 if l_ext_bank_account_id is not null then
1971 x_need_validation := 'N';
1972 end if;
1973 end if;
1974
1975 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1976 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1977 ' End check_for_duplicates ');
1978 END IF;
1979
1980 EXCEPTION
1981
1982 WHEN OTHERS THEN
1983 X_STATUS :='E';
1984 x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
1985 raise_application_error(-20085, x_exception_msg, true);
1986 END check_for_duplicates;
1987
1988
1989 END POS_SBD_IBY_PKG;