[Home] [Help]
PACKAGE BODY: APPS.POS_SBD_IBY_PKG
Source
1 PACKAGE BODY POS_SBD_IBY_PKG as
2 /*$Header: POSIBYB.pls 120.15 2006/07/25 21:19:58 gdwivedi noship $ */
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
465 , p_CITY => p_branch_city
462 , p_ADDRESS2 => p_branch_address2
463 , p_ADDRESS3 => p_branch_address3
464 , p_ADDRESS4 => p_branch_address4
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;
623 ' End create_location ');
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,
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
788 , p_agency_location_code in varchar2
785 , p_account_description in varchar2
786 , p_end_date in date
787 , p_start_date in date
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
806 cursor l_party_id_cur is
807 select party_id from pos_supplier_mappings where mapping_id = p_mapping_id;
808 l_party_id number;
809
810 l_need_validation varchar2(1);
811
812 l_create_flag varchar2(1);
813 BEGIN
814
815 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
816 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
817 ' Begin validate_account ');
818 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
819 ' p_mapping_id ' || p_mapping_id);
820 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
821 ' p_bank_id ' || p_bank_id);
822 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
823 ' p_branch_id ' || p_branch_id);
824 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
825 ' p_bank_name ' || p_bank_name);
826 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
827 ' p_branch_name ' || p_branch_name);
828 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
829 ' p_bank_number ' || p_bank_number);
830 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
831 ' p_branch_number ' || p_branch_number);
832 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
833 ' p_bank_account_number ' || p_bank_account_number);
834 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
835 ' p_bank_account_name ' || p_bank_account_name);
836 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
837 ' p_currency_code ' || p_currency_code);
838 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
839 ' p_country_code ' || p_country_code);
840 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
841 ' p_iban ' || p_iban);
842 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
843 ' p_check_digits ' || p_check_digits);
844 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
845 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
846 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
847 ' p_account_request_ud ' || p_account_request_id);
848 END IF;
849
850 l_step := 0;
851
852 -- Drop all the error message stack.
853 FND_MSG_PUB.initialize;
854
855 open l_party_id_cur;
856 fetch l_party_id_cur into l_party_id;
857 close l_party_id_cur;
858
859 l_step := 1;
860
861 POS_SBD_IBY_PKG.check_for_duplicates (
862 p_mapping_id => p_mapping_id
863 , p_BANK_ID => p_bank_id
864 , p_BANK_NAME => p_bank_name
865 , p_BANK_NUMBER => p_bank_number
866 , p_BRANCH_ID => p_branch_id
867 , p_BRANCH_NAME => p_branch_name
868 , p_BRANCH_NUMBER => p_branch_number
869 , p_EXT_BANK_ACCOUNT_ID => p_ext_bank_account_id
870 , p_bank_account_number => p_bank_account_number
871 , p_bank_account_name => p_bank_account_name
872 , p_currency_code => p_currency_code
873 , p_country_code => p_country_code
874 , p_account_request_id => p_account_request_id
875 , x_need_validation => l_need_validation
876 , x_status => x_status
877 , x_exception_msg => x_exception_msg);
878
879 l_step := 2;
880
881 if x_status <> 'S' then
882 return;
883 end if;
884
885 l_step := 3;
886
887 if l_need_validation = 'Y' then
888
889 l_step := 4;
890 l_ext_bank_rec.bank_id := p_bank_id;
891 l_ext_bank_rec.bank_name := p_bank_name;
892 l_ext_bank_rec.bank_number := p_bank_number;
893 l_ext_bank_rec.institution_type := p_bank_institution;
894 l_ext_bank_rec.country_code := p_country_code;
895 l_ext_bank_rec.bank_alt_name := p_bank_name_alt;
896
897 l_step := 5;
898 l_ext_bank_branch_rec.branch_party_id := p_branch_id;
899 l_ext_bank_branch_rec.branch_name := p_branch_name;
900 l_ext_bank_branch_rec.branch_number := p_branch_number;
901 l_ext_bank_branch_rec.branch_type := p_branch_type;
902 l_ext_bank_branch_rec.alternate_branch_name := p_branch_name_alt;
903 l_ext_bank_branch_rec.bic := p_bic;
904 l_ext_bank_branch_rec.rfc_identifier := p_rfc_identifier;
905
906 l_step := 6;
907 l_ext_bank_acct_rec.bank_account_id := p_EXT_BANK_ACCOUNT_ID;
908 l_ext_bank_acct_rec.country_code := p_country_code;
909 l_ext_bank_acct_rec.branch_id := p_branch_id;
910 l_ext_bank_acct_rec.bank_id := p_bank_id;
911 l_ext_bank_acct_rec.acct_owner_party_id := l_party_id;
912 l_ext_bank_acct_rec.bank_account_name := p_bank_account_name;
913 l_ext_bank_acct_rec.bank_account_num := p_bank_account_number;
914 l_ext_bank_acct_rec.currency := p_currency_code;
915 l_ext_bank_acct_rec.iban := p_iban;
916 l_ext_bank_acct_rec.check_digits := p_check_digits;
917 l_ext_bank_acct_rec.alternate_acct_name := p_bank_account_name_alt;
918 l_ext_bank_acct_rec.acct_type := p_bank_account_type;
922 l_ext_bank_acct_rec.exchange_rate_agreement_num := p_exchange_rate_agreement_num;
919 l_ext_bank_acct_rec.acct_suffix := p_account_suffix;
920 l_ext_bank_acct_rec.agency_location_code := p_agency_location_code;
921 l_ext_bank_acct_rec.foreign_payment_use_flag := p_foreign_payment_use_flag;
923 l_ext_bank_acct_rec.exchange_rate_agreement_type := p_exchange_rate_agreement_type;
924 l_ext_bank_acct_rec.exchange_rate := p_exchange_rate;
925 l_ext_bank_acct_rec.payment_factor_flag := 'Y';
926 l_ext_bank_acct_rec.end_date := p_end_date;
927 l_ext_bank_acct_rec.START_DATE := p_start_date;
928
929 l_step := 7;
930
931 if p_ext_bank_account_id is null then
932 l_create_flag := FND_API.G_TRUE;
933 else
934 l_create_flag := FND_API.G_FALSE;
935 end if;
936
937 IBY_EXT_BANKACCT_VALIDATIONS.iby_validate_account(
938 p_api_version => 1.0,
939 p_init_msg_list => FND_API.G_TRUE,
940 p_create_flag => l_create_flag,
941 p_ext_bank_rec => l_ext_bank_rec,
942 p_ext_bank_branch_rec => l_ext_bank_branch_rec,
943 p_ext_bank_acct_rec => l_ext_bank_acct_rec,
944 x_return_status => x_status,
945 x_msg_count => l_msg_count,
946 x_msg_data => x_exception_msg,
947 x_response => l_result_rec
948 );
949
950 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
951 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
952 ' IBY API Validation Status ' || x_status);
953 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
954 ' IBY API Validation msg count ' || l_msg_count);
955 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
956 ' IBY API Validation exception msg ' || x_exception_msg);
957 END IF;
958
959 l_step := 8;
960
961 end if;
962
963 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
964 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
965 ' End validate_account ');
966 END IF;
967
968 EXCEPTION
969 WHEN OTHERS THEN
970 x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
971 raise_application_error(-20066, x_exception_msg, true);
972 END validate_account;
973
974 PROCEDURE assign_site_to_account (
975 p_temp_ext_bank_account_id in number
976 , p_vendor_site_id in number
977 , x_status out nocopy VARCHAR2
978 , x_exception_msg out nocopy VARCHAR2
979 )
980
981 IS
982
983 l_msg_count number;
984 l_step number;
985
986 CURSOR l_temp_account_cur is
987 select temp.ext_bank_account_id, temp.account_owner_party_id
988 from IBY_TEMP_EXT_BANK_ACCTS temp
989 where temp.temp_ext_bank_acct_id = p_temp_ext_bank_account_id;
990
991 l_temp_account_rec l_temp_account_cur%ROWTYPE;
992 l_result_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
993 l_ext_bank_account_id IBY_TEMP_EXT_BANK_ACCTS.ext_bank_account_id%TYPE;
994 l_payee_rec IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_Rec_Type;
995 l_pay_instr_rec IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
996 l_pay_assign_rec IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
997 l_party_id number;
998 L_PAYEE_ASSIGNMENT_ID number;
999
1000 cursor l_site_detail_cur is
1001 select org_id, party_site_id from ap_supplier_sites_all where
1002 vendor_site_id = p_vendor_site_id;
1003 l_party_site_id number;
1004 l_org_id number;
1005
1006 cursor l_max_p_cur is
1007 select max(uses.order_of_preference)
1008 from iby_pmt_instr_uses_all uses, iby_external_payees_all payee,
1009 iby_ext_bank_accounts act, ap_supplier_sites_all pvsa
1010 where uses.instrument_type = 'BANKACCOUNT'
1011 AND sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate)
1012 and payee.ext_payee_id = uses.ext_pmt_party_id
1013 and payee.org_id = pvsa.org_id
1014 and payee.party_site_id = pvsa.party_site_id
1015 and org_type = 'OPERATING_UNIT'
1016 and pvsa.vendor_site_id = payee.supplier_site_id
1017 and payee.supplier_site_id = p_vendor_site_id
1018 and uses.instrument_id = act.ext_bank_account_id
1019 and payee.payee_party_id = l_party_id
1020 and payee.party_site_id is null;
1021
1022 l_priority number;
1023
1024 cursor l_payee_cur is
1025 select payee.object_version_number, payee.ext_payee_id from iby_external_payees_all payee
1026 where payee.ext_payee_id = l_party_id
1027 and payee.org_id is null
1028 and payee.party_site_id is null
1029 and payee.supplier_site_id = p_vendor_site_id;
1030 l_cur_payee_rec l_payee_cur%ROWTYPE;
1031
1032 cursor l_payee_assignment_cur is
1033 select uses.object_version_number
1034 from iby_external_payees_all payee, iby_pmt_instr_uses_all uses
1035 where payee.ext_payee_id = l_party_id
1036 and payee.org_id is null
1037 and payee.party_site_id is null
1038 and payee.supplier_site_id = p_vendor_site_id
1039 and payee.ext_payee_id = uses.ext_pmt_party_id
1040 and uses.instrument_id = l_ext_bank_account_id
1041 and uses.instrument_type = 'BANKACCOUNT';
1042
1043 l_cur_payee_assign_rec l_payee_assignment_cur%ROWTYPE;
1044
1045
1046 BEGIN
1047
1048 l_step := 0;
1049 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1050 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1051 ' Begin assign_site_to_account ');
1052 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1053 ' p_vendor_site_id ' || p_vendor_site_id);
1054 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1055 ' p_temp_ext_bank_account_id ' || p_temp_ext_bank_account_id);
1056 END IF;
1057
1061
1058 l_step := 1;
1059
1060 for l_temp_account_rec in l_temp_account_cur loop
1062 l_ext_bank_account_id := l_temp_account_rec.ext_bank_account_id;
1063 l_party_id := l_temp_account_rec.account_owner_party_id;
1064
1065 l_step := 3;
1066
1067 open l_max_p_cur;
1068 fetch l_max_p_cur into l_priority;
1069 close l_max_p_cur;
1070
1071 l_step := 4;
1072
1073 if l_priority is null then
1074 l_priority := 1;
1075 else
1076 l_priority := l_priority + 1;
1077 end if;
1078
1079 open l_site_detail_cur;
1080 fetch l_site_detail_cur into l_org_id, l_party_site_id;
1081 close l_site_detail_cur;
1082
1083 l_step := 5;
1084 l_payee_rec.Payment_Function := 'PAYABLES_DISB';
1085 l_payee_rec.Party_id := l_party_id;
1086 l_payee_rec.Party_Site_id := l_party_site_id;
1087 l_payee_rec.org_Id := l_org_id;
1088 l_payee_rec.Supplier_Site_id := p_vendor_site_id;
1089 l_payee_rec.Org_Type := 'OPERATING_UNIT';
1090
1091 l_pay_instr_rec.Instrument_Type := 'BANKACCOUNT';
1092 l_pay_instr_rec.Instrument_Id := l_ext_bank_account_id;
1093
1094 l_pay_assign_rec.Instrument := l_pay_instr_rec;
1095 l_pay_assign_rec.Priority := l_priority;
1096 l_pay_assign_rec.Start_Date := sysdate;
1097 l_pay_assign_rec.End_Date := null;
1098
1099 l_step := 6;
1100
1101 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1102 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1103 ' Calling Set_Payee_Instr_Assignment ');
1104 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1105 ' l_payee_rec.Party_id ' || l_payee_rec.Party_id);
1106 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1107 ' l_payee_rec.Payment_Function ' || l_payee_rec.Payment_Function);
1108 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1109 ' l_payee_rec.Supplier_Site_id ' || l_payee_rec.Supplier_Site_id);
1110 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1111 ' l_pay_instr_rec.Instrument_Type ' || l_pay_instr_rec.Instrument_Type);
1112 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1113 ' l_pay_instr_rec.Instrument_Id ' || l_pay_instr_rec.Instrument_Id);
1114 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1115 ' l_pay_assign_rec.Priority ' || l_pay_assign_rec.Priority);
1116 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1117 ' l_pay_assign_rec.Start_Date ' || l_pay_assign_rec.Start_Date );
1118 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1119 ' l_pay_assign_rec.End_Date ' || l_pay_assign_rec.End_Date);
1120 END IF;
1121
1122 IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment(
1123 p_api_version => 1.0,
1124 p_init_msg_list => FND_API.G_FALSE,
1125 p_commit => FND_API.G_FALSE,
1126 x_return_status => x_status,
1127 x_msg_count => l_msg_count,
1128 x_msg_data => x_exception_msg,
1129 p_payee => l_payee_rec,
1130 p_assignment_attribs => l_pay_assign_rec,
1131 x_assign_id => l_payee_assignment_id,
1132 x_response => l_result_rec
1133 );
1134 l_step := 7;
1135
1136 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1137 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1138 ' After Set_Payee_Instr_Assignment ');
1139 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1140 ' x_return_status ' || x_status);
1141 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1142 ' x_msg_count ' || l_msg_count);
1143 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1144 ' x_msg_data ' || x_exception_msg);
1145 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1146 ' x_assign_id ' || l_payee_assignment_id);
1147 END IF;
1148
1149 if l_payee_assignment_id is null OR x_status is null OR x_status <> 'S' then
1150 l_step := 8;
1151 raise_application_error(-20084, 'IBY Failed to create assignment ' || x_exception_msg, true);
1152 end if;
1153
1154 end loop;
1155
1156 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1157 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1158 ' END assign_site_to_account ');
1159 END IF;
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 x_status := 'E';
1164 x_exception_msg := 'Failure at step ' || l_step;
1165 raise_application_error(-20067, x_exception_msg, true);
1166 END assign_site_to_account;
1167
1168
1169 PROCEDURE prenote_iby_temp_account (
1170 p_temp_ext_bank_account_id in number
1171 , p_vendor_site_id in number
1172 , x_status out nocopy VARCHAR2
1173 , x_exception_msg out nocopy VARCHAR2
1174 )
1175
1176 IS
1177 l_step number;
1178 BEGIN
1179
1180 l_step := 0;
1181 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1182 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1183 ' Begin prenote_iby_temp_account ');
1184 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1185 ' p_vendor_site_id ' || p_vendor_site_id);
1186 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1187 ' p_temp_ext_bank_account_id ' || p_temp_ext_bank_account_id);
1188 END IF;
1189
1190 -- Create/Update the account
1191 POS_SBD_IBY_PKG.approve_iby_temp_account (
1192 p_temp_ext_bank_account_id => p_temp_ext_bank_account_id
1193 , x_status => x_status
1194 , x_exception_msg => x_exception_msg
1195 );
1196
1197 l_step := 1;
1198
1199 POS_SBD_IBY_PKG.assign_site_to_account (
1203 , x_exception_msg => x_exception_msg
1200 p_temp_ext_bank_account_id => p_temp_ext_bank_account_id
1201 , p_vendor_site_id => p_vendor_site_id
1202 , x_status => x_status
1204 );
1205
1206 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1207 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1208 ' END prenote_iby_temp_account ');
1209 END IF;
1210
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 x_exception_msg := 'Failure at step ' || l_step;
1214 raise_application_error(-20068, x_exception_msg, true);
1215 END prenote_iby_temp_account;
1216
1217
1218 PROCEDURE approve_iby_temp_account (
1219 p_temp_ext_bank_account_id in number
1220 , x_status out nocopy VARCHAR2
1221 , x_exception_msg out nocopy VARCHAR2
1222 )
1223
1224 IS
1225
1226 l_msg_count number;
1227 l_step number;
1228
1229 l_record_type IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1230 l_end_date DATE;
1231 l_start_date DATE;
1232 l_count NUMBER;
1233 l_msg_data varchar(2000);
1234 CURSOR l_temp_account_cur is
1235
1236 select req.account_request_id,
1237 temp.bank_id, temp.bank_name, temp.bank_number,
1238 temp.bank_institution_type, temp.bank_name_alt,
1239 temp.bank_address_id,
1240 temp.branch_id, temp.branch_name, temp.branch_number, temp.bic,
1241 temp.branch_type, temp.branch_name_alt, temp.rfc_identifier,
1242 temp.branch_address_id,
1243 temp.ext_bank_account_id, temp.account_owner_party_id,
1244 temp.country_code, temp.FOREIGN_PAYMENT_USE_FLAG,
1245 temp.bank_account_name, temp.bank_account_num, temp.check_digits,
1246 temp.iban, temp.currency_code,
1247 temp.bank_account_name_alt, temp.bank_account_type,
1248 temp.description, temp.end_date, temp.start_date, temp.agency_location_code,
1249 temp.status, temp.note, temp.note_alt, temp.account_suffix, temp.exchange_rate,
1250 temp.exchange_rate_agreement_num, temp.exchange_rate_agreement_type, temp.payment_factor_flag
1251 from IBY_TEMP_EXT_BANK_ACCTS temp, pos_acnt_gen_req req
1252 where temp.temp_ext_bank_acct_id = p_temp_ext_bank_account_id
1253 and req.temp_ext_bank_acct_id = temp.temp_ext_bank_acct_id;
1254 l_temp_account_rec l_temp_account_cur%ROWTYPE;
1255
1256 l_ext_bank_rec IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
1257 l_ext_bank_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
1258 l_ext_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
1259 l_result_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1260 l_primary_owner_id HZ_PARTIES.party_id%TYPE;
1261 l_non_primary_owner_id HZ_PARTIES.party_id%TYPE;
1262 l_joint_acct_owner_id NUMBER;
1263
1264 l_bank_id IBY_TEMP_EXT_BANK_ACCTS.bank_id%TYPE;
1265 l_branch_id IBY_TEMP_EXT_BANK_ACCTS.branch_id%TYPE;
1266 l_ext_bank_account_id IBY_TEMP_EXT_BANK_ACCTS.ext_bank_account_id%TYPE;
1267 l_account_request_id POS_ACNT_GEN_REQ.account_request_id%TYPE;
1268 l_bank_address_id IBY_TEMP_EXT_BANK_ACCTS.bank_address_id%TYPE;
1269 l_branch_address_id IBY_TEMP_EXT_BANK_ACCTS.branch_address_id%TYPE;
1270 l_bank_party_site_id number;
1271 l_bank_party_site_number varchar2(2000);
1272 l_branch_party_site_id number;
1273 l_branch_party_site_number varchar2(2000);
1274 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
1275
1276 cursor l_cur_act_cur is
1277 select act.object_version_number, ow.account_owner_party_id
1278 from iby_ext_bank_accounts act, iby_account_owners ow
1279 where ow.ext_bank_account_id = act.ext_bank_account_id
1280 and act.ext_bank_account_id = l_ext_bank_account_id
1281 and ow.primary_flag = 'Y'
1282 and NVL(ow.end_date,SYSDATE+10)>SYSDATE
1283 AND sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate);
1284 l_cur_act_rec l_cur_act_cur%ROWTYPE;
1285 BEGIN
1286
1287 x_status := 'S';
1288
1289 l_step := 0;
1290
1291 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1292 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1293 ' BEGIN approve_iby_temp_account ');
1294 END IF;
1295
1296 for l_temp_account_rec in l_temp_account_cur loop
1297
1298 l_bank_id := l_temp_account_rec.bank_id;
1299 l_branch_id := l_temp_account_rec.branch_id;
1300 l_ext_bank_account_id := l_temp_account_rec.ext_bank_account_id;
1301 l_branch_address_id := l_temp_account_rec.branch_address_id;
1302 l_bank_address_id := l_temp_account_rec.bank_address_id;
1303 l_account_request_id := l_temp_account_rec.account_request_id;
1304
1305 l_step := 1;
1306 if (l_bank_id is null and
1307 (l_temp_account_rec.bank_number is not null OR l_temp_account_rec.bank_name is not null))
1308 then
1309
1310 -- Load the bank record.
1311 l_ext_bank_rec.bank_name := l_temp_account_rec.bank_name;
1312 l_ext_bank_rec.bank_number := l_temp_account_rec.bank_number;
1313 l_ext_bank_rec.institution_type := 'BANK';
1314 l_ext_bank_rec.country_code := l_temp_account_rec.country_code;
1315 l_ext_bank_rec.bank_alt_name := l_temp_account_rec.bank_name_alt;
1316
1317 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1318 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1319 ' Calling IBY_EXT_BANKACCT_PUB.create_ext_bank');
1320 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1321 ' l_ext_bank_rec.bank_name ' || l_ext_bank_rec.bank_name);
1322 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1323 ' l_ext_bank_rec.bank_number ' || l_ext_bank_rec.bank_number);
1324 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1325 ' l_ext_bank_rec.institution_type ' || l_ext_bank_rec.institution_type );
1326 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1327 ' l_ext_bank_rec.country_code ' || l_ext_bank_rec.country_code);
1328 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1332 -- Create a bank
1329 ' l_ext_bank_rec.bank_alt_name ' || l_ext_bank_rec.bank_alt_name);
1330 END IF;
1331
1333 IBY_EXT_BANKACCT_PUB.create_ext_bank (
1334 p_api_version => 1.0,
1335 p_init_msg_list => FND_API.G_TRUE,
1336 p_ext_bank_rec => l_ext_bank_rec,
1337 x_bank_id => l_bank_id,
1338 x_return_status => x_status,
1339 x_msg_count => l_msg_count,
1340 x_msg_data => x_exception_msg,
1341 x_response => l_result_rec
1342 );
1343
1344 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1345 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1346 ' After Calling IBY_EXT_BANKACCT_PUB.create_ext_bank');
1347 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1348 ' x_return_status ' || x_status);
1349 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1350 ' x_msg_count ' || l_msg_count);
1351 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1352 ' x_msg_data ' || x_exception_msg);
1353 END IF;
1354
1355 if l_bank_id is null OR x_status <> 'S' then
1356 raise_application_error(-20073, 'IBY Failed to create a bank', true);
1357 end if;
1358
1359 if l_bank_address_id is not null then
1360
1361 l_party_site_rec.party_id := l_bank_id;
1362 l_party_site_rec.party_site_name := null;
1363 l_party_site_rec.status := 'A';
1364 l_party_site_rec.location_id := l_bank_address_id;
1365 l_party_site_rec.identifying_address_flag := 'Y';
1366 l_party_site_rec.created_by_module := 'POS_SUPPLIER_MGMT';
1367 l_party_site_rec.application_id := 177;
1368
1369 hz_party_site_v2pub.create_party_site
1370 (
1371 p_init_msg_list => FND_API.G_FALSE,
1372 p_party_site_rec => l_party_site_rec,
1373 x_party_site_id => l_bank_party_site_id,
1374 x_party_site_number => l_bank_party_site_number,
1375 x_return_status => x_status,
1376 x_msg_count => l_msg_count,
1377 x_msg_data => x_exception_msg);
1378 end if;
1379 end if;
1380
1381 l_step := 2;
1382 if (l_branch_id is null AND
1383 (l_temp_account_rec.branch_number is not null OR l_temp_account_rec.branch_name is not null))
1384 then
1385
1386 l_ext_bank_branch_rec.bank_party_id := l_bank_id;
1387 l_ext_bank_branch_rec.branch_name := l_temp_account_rec.branch_name;
1388 l_ext_bank_branch_rec.branch_number := l_temp_account_rec.branch_number;
1389 l_ext_bank_branch_rec.branch_type := l_temp_account_rec.branch_type;
1390 l_ext_bank_branch_rec.alternate_branch_name := l_temp_account_rec.branch_name_alt;
1391 l_ext_bank_branch_rec.bic := l_temp_account_rec.bic;
1392 l_ext_bank_branch_rec.rfc_identifier := l_temp_account_rec.rfc_identifier;
1393
1394 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1395 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1396 ' Calling IBY_EXT_BANKACCT_PUB.create_ext_bank_branch');
1397 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1398 ' l_ext_bank_branch_rec.bank_party_id ' || l_ext_bank_branch_rec.bank_party_id);
1399 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1400 ' l_ext_bank_branch_rec.branch_name ' || l_ext_bank_branch_rec.branch_name);
1401 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1402 ' l_ext_bank_branch_rec.branch_number ' || l_ext_bank_branch_rec.branch_number);
1403 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1404 ' l_ext_bank_branch_rec.branch_type ' || l_ext_bank_branch_rec.branch_type);
1405 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1406 ' l_ext_bank_branch_rec.alternate_branch_name '
1407 || l_ext_bank_branch_rec.alternate_branch_name);
1408 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1409 ' l_ext_bank_branch_rec.bic ' || l_ext_bank_branch_rec.bic );
1410 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1411 ' l_ext_bank_branch_rec.rfc_identifier ' || l_ext_bank_branch_rec.rfc_identifier);
1412 END IF;
1413
1414 IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
1415 p_api_version => 1.0,
1416 p_init_msg_list => FND_API.G_TRUE,
1417 p_ext_bank_branch_rec => l_ext_bank_branch_rec,
1418 x_branch_id => l_branch_id,
1419 x_return_status => x_status,
1420 x_msg_count => l_msg_count,
1421 x_msg_data => x_exception_msg,
1422 x_response => l_result_rec
1423 );
1424
1425 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1426 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1427 ' After Calling IBY_EXT_BANKACCT_PUB.create_ext_bank_branch');
1428 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1429 ' x_return_status ' || x_status);
1430 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1431 ' x_msg_count ' || l_msg_count);
1432 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1433 ' x_msg_data ' || x_exception_msg);
1434 END IF;
1435
1436 if l_branch_id is null OR x_status <> 'S' then
1437 raise_application_error(-20072, 'IBY Failed to create a branch', true);
1438 end if;
1439
1440 if l_branch_address_id is not null then
1441
1442 l_party_site_rec.party_id := l_branch_id;
1443 l_party_site_rec.party_site_name := null;
1444 l_party_site_rec.status := 'A';
1445 l_party_site_rec.location_id := l_branch_address_id;
1449
1446 l_party_site_rec.identifying_address_flag := 'Y';
1447 l_party_site_rec.created_by_module := 'POS_SUPPLIER_MGMT';
1448 l_party_site_rec.application_id := 177;
1450 hz_party_site_v2pub.create_party_site
1451 (
1452 p_init_msg_list => FND_API.G_FALSE,
1453 p_party_site_rec => l_party_site_rec,
1454 x_party_site_id => l_branch_party_site_id,
1455 x_party_site_number => l_branch_party_site_number,
1456 x_return_status => x_status,
1457 x_msg_count => l_msg_count,
1458 x_msg_data => x_exception_msg);
1459 end if;
1460 end if;
1461
1462 l_step := 3;
1463
1464 -- Find out if it exists
1465 if l_ext_bank_account_id is null then
1466 IBY_EXT_BANKACCT_PUB.check_ext_acct_exist(
1467 p_api_version => 1.0,
1468 p_init_msg_list => FND_API.G_FALSE,
1469 p_bank_id => l_bank_id,
1470 p_branch_id => l_branch_id,
1471 p_acct_number => l_temp_account_rec.bank_account_num,
1472 p_acct_name => l_temp_account_rec.bank_account_name,
1473 p_currency => l_temp_account_rec.currency_code,
1474 p_country_code => l_temp_account_rec.country_code,
1475 x_acct_id => l_ext_bank_account_id,
1476 x_start_date => l_start_date,
1477 x_end_date => l_end_date,
1478 x_return_status => x_status,
1479 x_msg_count => l_msg_count,
1480 x_msg_data => l_msg_data,
1481 x_response => l_record_type);
1482
1483 end if;
1484
1485 -- Find out if we need to add this person as an owner.
1486 if l_ext_bank_account_id is not null then
1487 open l_cur_act_cur;
1488 fetch l_cur_act_cur into l_cur_act_rec;
1489 close l_cur_act_cur;
1490
1491 l_primary_owner_id := l_cur_act_rec.account_owner_party_id;
1492 l_non_primary_owner_id := l_temp_account_rec.ACCOUNT_OWNER_PARTY_ID;
1493
1494 if l_primary_owner_id <> l_temp_account_rec.ACCOUNT_OWNER_PARTY_ID then
1495 IBY_EXT_BANKACCT_PUB.add_joint_account_owner (
1496 p_api_version => 1.0,
1497 p_init_msg_list => FND_API.G_FALSE,
1498 p_bank_account_id => l_ext_bank_account_id,
1499 p_acct_owner_party_id => l_non_primary_owner_id,
1500 x_joint_acct_owner_id => l_joint_acct_owner_id,
1501 x_return_status => x_status,
1502 x_msg_count => l_msg_count,
1503 x_msg_data => l_msg_data,
1504 x_response => l_record_type);
1505 end if;
1506
1507 IF x_status <> 'S' then
1508 raise_application_error(-20074, 'IBY API AJAA Failed' || l_msg_data, true);
1509 END IF;
1510 end if;
1511
1512 l_ext_bank_acct_rec.bank_account_id := l_ext_bank_account_id;
1513 l_ext_bank_acct_rec.country_code := l_temp_account_rec.country_code;
1514 l_ext_bank_acct_rec.branch_id := l_branch_id;
1515 l_ext_bank_acct_rec.bank_id := l_bank_id;
1516 l_ext_bank_acct_rec.acct_owner_party_id := l_temp_account_rec.ACCOUNT_OWNER_PARTY_ID;
1517 l_ext_bank_acct_rec.bank_account_name := l_temp_account_rec.bank_account_name;
1518 l_ext_bank_acct_rec.bank_account_num := l_temp_account_rec.bank_account_num;
1519 l_ext_bank_acct_rec.currency := l_temp_account_rec.currency_code;
1520 l_ext_bank_acct_rec.iban := l_temp_account_rec.iban;
1521 l_ext_bank_acct_rec.check_digits := l_temp_account_rec.check_digits;
1522 l_ext_bank_acct_rec.alternate_acct_name := l_temp_account_rec.bank_account_name_alt;
1523 l_ext_bank_acct_rec.acct_type := l_temp_account_rec.bank_account_type;
1524 l_ext_bank_acct_rec.acct_suffix := l_temp_account_rec.account_suffix;
1525 l_ext_bank_acct_rec.agency_location_code := l_temp_account_rec.agency_location_code;
1526 l_ext_bank_acct_rec.foreign_payment_use_flag := l_temp_account_rec.FOREIGN_PAYMENT_USE_FLAG;
1527 l_ext_bank_acct_rec.payment_factor_flag := l_temp_account_rec.payment_factor_flag;
1528 l_ext_bank_acct_rec.end_date := l_temp_account_rec.end_date;
1529 l_ext_bank_acct_rec.start_date := l_temp_account_rec.start_date;
1530 l_ext_bank_acct_rec.description := l_temp_account_rec.description;
1531
1532
1533 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1534 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1535 ' Calling IBY_EXT_BANKACCT_PUB.create/update_ext_bank_acct');
1536 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1537 ' l_ext_bank_acct_rec.bank_account_id ' || l_ext_bank_acct_rec.bank_account_id);
1538 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1539 ' l_ext_bank_acct_rec.country_code ' || l_ext_bank_acct_rec.country_code);
1540 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1541 ' l_ext_bank_acct_rec.branch_id ' || l_ext_bank_acct_rec.branch_id);
1542 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1543 ' l_ext_bank_acct_rec.bank_id ' || l_ext_bank_acct_rec.bank_id);
1544 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1545 ' l_ext_bank_acct_rec.acct_owner_party_id ' || l_ext_bank_acct_rec.acct_owner_party_id);
1546 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1547 ' l_ext_bank_acct_rec.bank_account_name ' || l_ext_bank_acct_rec.bank_account_name);
1548 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1549 ' l_ext_bank_acct_rec.bank_account_num ' || l_ext_bank_acct_rec.bank_account_num);
1550 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1551 ' l_ext_bank_acct_rec.currency ' || l_ext_bank_acct_rec.currency);
1552 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1553 ' l_ext_bank_acct_rec.iban ' || l_ext_bank_acct_rec.iban);
1557 ' l_ext_bank_acct_rec.alternate_acct_name ' || l_ext_bank_acct_rec.alternate_acct_name);
1554 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1555 ' l_ext_bank_acct_rec.check_digits ' || l_ext_bank_acct_rec.check_digits);
1556 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1558 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1559 ' l_ext_bank_acct_rec.acct_type ' || l_ext_bank_acct_rec.acct_type);
1560 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1561 ' l_ext_bank_acct_rec.acct_suffix ' || l_ext_bank_acct_rec.acct_suffix);
1562 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1563 ' l_ext_bank_acct_rec.agency_location_code ' || l_ext_bank_acct_rec.agency_location_code);
1564 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1565 ' l_ext_bank_acct_rec.foreign_payment_use_flag '
1566 || l_ext_bank_acct_rec.foreign_payment_use_flag);
1567 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1568 ' l_ext_bank_acct_rec.payment_factor_flag ' || l_ext_bank_acct_rec.payment_factor_flag);
1569 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1570 ' l_ext_bank_acct_rec.end_date ' || l_ext_bank_acct_rec.end_date);
1571 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1572 ' l_ext_bank_acct_rec.start_date ' || l_ext_bank_acct_rec.start_date);
1573 END IF;
1574
1575
1576 if l_ext_bank_account_id is null then
1577
1578 l_ext_bank_acct_rec.exchange_rate_agreement_num := l_temp_account_rec.exchange_rate_agreement_num;
1579 l_ext_bank_acct_rec.exchange_rate_agreement_type := l_temp_account_rec.exchange_rate_agreement_type;
1580 l_ext_bank_acct_rec.exchange_rate := l_temp_account_rec.exchange_rate;
1581
1582 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1583 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1584 ' l_ext_bank_acct_rec.exchange_rate_agreement_num '
1585 || l_ext_bank_acct_rec.exchange_rate_agreement_num);
1586 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1587 ' l_ext_bank_acct_rec.exchange_rate_agreement_type '
1588 || l_ext_bank_acct_rec.exchange_rate_agreement_type);
1589 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1590 ' l_ext_bank_acct_rec.exchange_rate ' || l_ext_bank_acct_rec.exchange_rate);
1591 END IF;
1592
1593 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1594 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1595 ' Now Calling IBY_EXT_BANKACCT_PUB.create_ext_bank_acct');
1596 END IF;
1597
1598 IBY_EXT_BANKACCT_PUB.create_ext_bank_acct (
1599 p_api_version => 1.0,
1600 p_init_msg_list => FND_API.G_TRUE,
1601 p_ext_bank_acct_rec => l_ext_bank_acct_rec,
1602 x_acct_id => l_ext_bank_account_id,
1603 x_return_status => x_status,
1604 x_msg_count => l_msg_count,
1605 x_msg_data => x_exception_msg,
1606 x_response => l_result_rec
1607 );
1608
1609 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1610 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1611 ' After Calling IBY_EXT_BANKACCT_PUB.create_ext_bank_acct');
1612 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1613 ' x_return_status ' || x_status);
1614 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1615 ' x_msg_count ' || l_msg_count);
1616 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1617 ' x_msg_data ' || x_exception_msg);
1618 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1619 ' x_acct_id ' || l_ext_bank_account_id);
1620
1621 END IF;
1622
1623 IF x_status <> 'S' OR l_ext_bank_account_id is null then
1624 raise_application_error(-20070, 'IBY API CEBA' || x_exception_msg, true);
1625 END IF;
1626
1627 else
1628
1629 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1630 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1631 ' Now Calling IBY_EXT_BANKACCT_PUB.update_ext_bank_acct');
1632 END IF;
1633
1634 open l_cur_act_cur;
1635 fetch l_cur_act_cur into l_cur_act_rec;
1636 close l_cur_act_cur;
1637
1638 l_ext_bank_acct_rec.object_version_number := l_cur_act_rec.object_version_number;
1639
1640 IBY_EXT_BANKACCT_PUB.update_ext_bank_acct (
1641 p_api_version => 1.0,
1642 p_init_msg_list => FND_API.G_TRUE,
1643 p_ext_bank_acct_rec => l_ext_bank_acct_rec,
1644 x_return_status => x_status,
1645 x_msg_count => l_msg_count,
1646 x_msg_data => x_exception_msg,
1647 x_response => l_result_rec
1648 );
1649
1650 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1651 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1652 ' After Calling IBY_EXT_BANKACCT_PUB.update_ext_bank_acct');
1653 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1654 ' x_return_status ' || x_status);
1655 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1656 ' x_msg_count ' || l_msg_count);
1657 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1658 ' x_msg_data ' || x_exception_msg);
1659 END IF;
1660
1661 IF x_status <> 'S' then
1662 raise_application_error(-20071, 'IBY API UEBA Failed' || x_exception_msg, true);
1663 END IF;
1664
1665 end if;
1666
1667 POS_SBD_IBY_PKG.update_req_with_account (
1668 p_temp_ext_bank_account_id => p_temp_ext_bank_account_id
1669 , p_ext_bank_account_id => l_ext_bank_account_id
1670 , p_account_request_id => l_account_request_id
1674 , x_exception_msg => x_exception_msg
1671 , p_bank_id => l_bank_id
1672 , p_branch_id => l_branch_id
1673 , x_status => x_status
1675 );
1676
1677 end loop;
1678
1679 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1680 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1681 ' End approve_iby_temp_account');
1682 END IF;
1683 EXCEPTION
1684 WHEN OTHERS THEN
1685 x_exception_msg := 'Failure at step ' || l_step;
1686 raise_application_error(-20069, x_exception_msg, true);
1687 END approve_iby_temp_account;
1688
1689 PROCEDURE update_req_with_account (
1690 p_temp_ext_bank_account_id in number
1691 , p_ext_bank_account_id in number
1692 , p_account_request_id in number
1693 , p_bank_id in number
1694 , p_branch_id in number
1695 , x_status out nocopy VARCHAR2
1696 , x_exception_msg out nocopy VARCHAR2
1697 )
1698 IS
1699
1700 l_step number;
1701
1702 BEGIN
1703
1704 l_step := 0;
1705 update pos_acnt_addr_summ_req
1706 set ext_bank_account_id = p_ext_bank_account_id,
1707 last_update_date = sysdate,
1708 last_updated_by = fnd_global.user_id,
1709 last_update_login = fnd_global.login_id,
1710 object_version_number = object_version_number + 1
1711 where account_request_id = p_account_request_id;
1712
1713 l_step := 2;
1714 update pos_acnt_gen_req
1715 set ext_bank_account_id = p_ext_bank_account_id,
1716 last_update_date = sysdate,
1717 last_updated_by = fnd_global.user_id,
1718 last_update_login = fnd_global.login_id,
1719 object_version_number = object_version_number + 1
1720 where account_request_id = p_account_request_id;
1721
1722 l_step := 3;
1723 update iby_temp_ext_bank_accts
1724 set bank_id = p_bank_id,
1725 branch_id = p_branch_id,
1726 last_update_date = sysdate,
1727 last_updated_by = fnd_global.user_id,
1728 last_update_login = fnd_global.login_id,
1729 object_version_number = object_version_number + 1,
1730 ext_bank_account_id = p_ext_bank_account_id
1731 where temp_ext_bank_acct_id = p_temp_ext_bank_account_id;
1732
1733 x_status := 'S';
1734
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737 x_exception_msg := 'Failure at step ' || l_step;
1738 raise_application_error(-20078, x_exception_msg, true);
1739 END update_req_with_account;
1740
1741
1742 PROCEDURE check_for_duplicates (
1743 p_mapping_id in NUMBER
1744 , p_BANK_ID in NUMBER
1745 , p_BANK_NAME in VARCHAR2
1746 , p_BANK_NUMBER in VARCHAR2
1747 , p_BRANCH_ID in NUMBER
1748 , p_BRANCH_NAME in VARCHAR2
1749 , p_BRANCH_NUMBER in VARCHAR2
1750 , p_EXT_BANK_ACCOUNT_ID in number
1751 , p_bank_account_number in varchar2
1752 , p_bank_account_name in varchar2
1753 , p_currency_code in varchar2
1754 , p_country_code in varchar2
1755 , p_account_request_id in number
1756 , x_need_validation out nocopy varchar2
1757 , x_status out nocopy VARCHAR2
1758 , x_exception_msg out nocopy VARCHAR2
1759 )
1760
1761 IS
1762
1763 l_step number;
1764 l_bank_id HZ_PARTIES.party_id%TYPE;
1765 l_branch_id HZ_PARTIES.party_id%TYPE;
1766 l_ext_bank_account_id IBY_TEMP_EXT_BANK_ACCTS.EXT_BANK_ACCOUNT_ID%TYPE;
1767 l_record_type IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1768 l_end_date DATE;
1769 l_start_date DATE;
1770 l_msg_count NUMBER;
1771 l_count NUMBER;
1772 l_msg_data varchar(2000);
1773
1774 cursor dup_temp_act_cur is
1775 select count(*) from iby_temp_ext_bank_accts iby, pos_acnt_gen_req pos
1776 where pos.mapping_id = p_mapping_id
1777 and pos.temp_ext_bank_acct_id = iby.temp_ext_bank_acct_id
1778 and (
1779 (iby.currency_code = p_currency_code
1780 and p_currency_code is not null and iby.currency_code is not null) OR
1781 (p_currency_code is null and iby.currency_code is null)
1782 )
1783
1784 and iby.country_code = p_country_code
1785 and iby.status in ('NEW', 'IN_VERIFICATION', 'VERIFICATION_FAILED', 'CORRECTED', 'CHANGE_PENDING')
1786 AND ((iby.bank_id = p_bank_id and p_bank_id is not null and iby.bank_id is not null) OR
1787 (iby.bank_number = p_bank_number and p_bank_number is not null and iby.bank_number is not null) OR
1788 (p_bank_id is null and iby.bank_id is null and p_bank_number is null and iby.bank_number is null)
1789 )
1790 AND (
1791 (iby.branch_id = p_branch_id and p_branch_id is not null and iby.branch_id is not null) OR
1792 (iby.branch_number = p_branch_number and p_branch_number is not null
1793 and iby.branch_number is not null) OR
1794 (p_branch_id is null and iby.branch_id is null
1795 and p_branch_number is null and iby.branch_number is null)
1796 )
1797
1798 AND (
1799 (iby.bank_account_num = p_bank_account_number and p_bank_account_number is not null
1800 and iby.bank_account_num is not null) OR
1801 (iby.bank_account_name = p_bank_account_name and p_bank_account_name is not null
1802 and iby.bank_account_name is not null)
1803 )
1804 AND ((pos.account_request_id <> p_account_request_id and p_account_request_id is not null and
1805 pos.account_request_id is not null) OR (p_account_request_id is null));
1806
1807 cursor dup_cur_act_cur is
1808 select count(*) from iby_ext_bank_accounts act, iby_account_owners o, pos_supplier_mappings pmap
1809 where o.ext_bank_account_id = act.ext_bank_account_id
1810 and (
1811 (act.currency_code = p_currency_code
1812 and act.currency_code is not null and p_currency_code is not null) OR
1813 (act.currency_code is null and p_currency_code is null)
1814 )
1815 and o.account_owner_party_id = pmap.party_id
1816 and pmap.mapping_id = p_mapping_id
1817 and ((act.bank_id = l_bank_id and act.bank_id is not null and l_bank_id is not null) OR
1818 (act.bank_id is null and l_bank_id is null))
1819 and ((act.branch_id = l_branch_id and act.branch_id is not null
1820 and l_branch_id is not null) OR
1821 (act.branch_id is null and l_branch_id is null))
1822 and (
1823 act.bank_account_name = p_bank_account_name
1824 and act.bank_account_name is not null and p_bank_account_name is not null
1825 )
1826 and ((act.ext_bank_account_id <> p_EXT_BANK_ACCOUNT_ID and p_EXT_BANK_ACCOUNT_ID is not null)
1827 OR p_EXT_BANK_ACCOUNT_ID is null)
1828 and act.country_code = p_country_code
1829 and not exists
1830 (
1831 select 1 from IBY_TEMP_EXT_BANK_ACCTS temp
1832 where temp.EXT_BANK_ACCOUNT_ID = act.ext_bank_account_id
1833 and temp.status in ('CORRECTED', 'NEW', 'IN_VERIFICATION', 'VERIFICATION_FAILED', 'CHANGE_PENDING')
1834 and temp.account_owner_party_id = o.account_owner_party_id
1835 )
1836 and ((act.ext_bank_account_id <> p_ext_bank_account_id and p_ext_bank_account_id is not null and
1837 act.ext_bank_account_id is not null) OR (p_ext_bank_account_id is null))
1838 AND sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate);
1839
1840 BEGIN
1841
1842 l_step := 0;
1843
1844 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1845 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1846 ' Begin check_for_duplicates ');
1847 END IF;
1848
1849 x_need_validation := 'Y';
1850 x_status := 'S';
1851
1852 -- If bank id is not specified then check if its duplicate
1853 if p_bank_id is null then
1854 ce_bank_pub.check_bank_exist(
1855 p_country_code => p_country_code,
1856 p_bank_name => p_bank_name,
1857 p_bank_number => p_bank_number,
1858 x_bank_id => l_bank_id,
1859 x_end_date => l_end_date);
1860
1861 if l_bank_id is not null then
1862 x_status := 'E';
1863 fnd_message.set_name('POS', 'POS_SBD_DUP_BANK');
1864 fnd_msg_pub.add;
1865 end if;
1866
1867 else
1868 l_bank_id := p_bank_id;
1869 end if;
1870
1871 -- If branch id is not specified then check if its duplicate.
1872 if p_branch_id is null then
1873
1874 if l_bank_id is not null then
1875
1876 ce_bank_pub.check_branch_exist(
1877 p_bank_id => l_bank_id,
1878 p_branch_name => p_branch_name,
1879 p_branch_number => p_branch_number,
1880 x_branch_id => l_branch_id,
1881 x_end_date => l_end_date);
1882 if l_branch_id is not null then
1883 x_status := 'E';
1884 fnd_message.set_name('POS', 'POS_SBD_DUP_BRANCH');
1885 fnd_msg_pub.add;
1886 end if;
1887
1888 end if;
1889 else
1890 l_branch_id := p_branch_id;
1891 end if;
1892
1893 open dup_temp_act_cur;
1894 fetch dup_temp_act_cur into l_count;
1895 if l_count is null then l_count := 0;
1896 end if;
1897 if l_count <> 0 then
1898 x_status := 'E';
1899 fnd_message.set_name('POS', 'POS_SBD_DUP_ACT2');
1900 fnd_msg_pub.add;
1901 end if;
1902 close dup_temp_act_cur;
1903
1904 l_count := 0;
1905 open dup_cur_act_cur;
1906 fetch dup_cur_act_cur into l_count;
1907 if l_count is null then l_count := 0;
1908 end if;
1909 close dup_cur_act_cur;
1910
1911 if l_count <> 0 then
1912 x_status := 'E';
1913 fnd_message.set_name('POS', 'POS_SBD_DUP_ACT1');
1914 fnd_msg_pub.add;
1915 end if;
1916
1917 if l_bank_id is not null and l_branch_id is not null and
1918 p_EXT_BANK_ACCOUNT_ID is null and x_status = 'S' then
1919 IBY_EXT_BANKACCT_PUB.check_ext_acct_exist(
1920 p_api_version => 1.0,
1921 p_init_msg_list => FND_API.G_FALSE,
1922 p_bank_id => l_bank_id,
1923 p_branch_id => l_branch_id,
1924 p_acct_number => p_bank_account_number,
1925 p_acct_name => p_bank_account_name,
1926 p_currency => p_currency_code,
1927 p_country_code => p_country_code,
1928 x_acct_id => l_ext_bank_account_id,
1929 x_start_date => l_start_date,
1930 x_end_date => l_end_date,
1931 x_return_status => x_status,
1932 x_msg_count => l_msg_count,
1933 x_msg_data => l_msg_data,
1934 x_response => l_record_type);
1935 if l_ext_bank_account_id is not null then
1936 x_need_validation := 'N';
1937 end if;
1938 end if;
1939
1940 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1941 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1942 ' End check_for_duplicates ');
1943 END IF;
1944
1945 EXCEPTION
1946
1947 WHEN OTHERS THEN
1948 X_STATUS :='E';
1949 x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
1950 raise_application_error(-20085, x_exception_msg, true);
1951 END check_for_duplicates;
1952
1953
1954 END POS_SBD_IBY_PKG;