[Home] [Help]
PACKAGE: APPS.IBY_EXT_BANKACCT_PUB
Source
1 PACKAGE IBY_EXT_BANKACCT_PUB AUTHID CURRENT_USER AS
2 /*$Header: ibyxbnks.pls 120.25.12020000.3 2012/10/11 06:22:50 asarada ship $*/
3
4 --
5 -- Declaring Global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_EXT_BANKACCT_PUB';
8
9 --
10 -- module name used for the application debugging framework
11 --
12 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_EXT_BANKACCT_PUB';
13
14
15 -- Masking character
16 G_MASK_CHARACTER CONSTANT VARCHAR2(1) := 'X';
17 -- Default unmask length
18 G_DEF_UNMASK_LENGTH CONSTANT NUMBER := 4;
19 -- Bank number padd character
20 G_BANK_NUMBER_PAD CONSTANT VARCHAR2(1) := ' ';
21
22
23 -------------------------------------------------------------------------
24 -- **Defining all DataStructures required by the APIs**
25 -- The following PL/SQL record/table types are defined
26 -- to store the objects (entities) necessary for the APIs.
27 -------------------------------------------------------------------------
28 --
29 -- Generic Record Types
30 --
31
32 -- External Bank Record
33 TYPE ExtBank_rec_type IS RECORD (
34 bank_id hz_parties.PARTY_ID%TYPE,
35 bank_name hz_parties.PARTY_NAME%TYPE,
36 bank_number hz_organization_profiles.BANK_OR_BRANCH_NUMBER%TYPE,
37 institution_type hz_code_assignments.CLASS_CODE%TYPE,
38 country_code hz_parties.COUNTRY%TYPE,
39 bank_alt_name hz_parties.ORGANIZATION_NAME_PHONETIC%TYPE,
40 bank_short_name hz_parties.KNOWN_AS%TYPE,
41 description hz_parties.MISSION_STATEMENT%TYPE,
42 tax_payer_id hz_organization_profiles.JGZZ_FISCAL_CODE%TYPE,
43 tax_registration_number hz_organization_profiles.TAX_REFERENCE%TYPE,
44 attribute_category hz_parties.ATTRIBUTE_CATEGORY%TYPE,
45 attribute1 hz_parties.ATTRIBUTE1%TYPE,
46 attribute2 hz_parties.ATTRIBUTE2%TYPE,
47 attribute3 hz_parties.ATTRIBUTE3%TYPE,
48 attribute4 hz_parties.ATTRIBUTE4%TYPE,
49 attribute5 hz_parties.ATTRIBUTE5%TYPE,
50 attribute6 hz_parties.ATTRIBUTE6%TYPE,
51 attribute7 hz_parties.ATTRIBUTE7%TYPE,
52 attribute8 hz_parties.ATTRIBUTE8%TYPE,
53 attribute9 hz_parties.ATTRIBUTE9%TYPE,
54 attribute10 hz_parties.ATTRIBUTE10%TYPE,
55 attribute11 hz_parties.ATTRIBUTE11%TYPE,
56 attribute12 hz_parties.ATTRIBUTE12%TYPE,
57 attribute13 hz_parties.ATTRIBUTE13%TYPE,
58 attribute14 hz_parties.ATTRIBUTE14%TYPE,
59 attribute15 hz_parties.ATTRIBUTE15%TYPE,
60 attribute16 hz_parties.ATTRIBUTE16%TYPE,
61 attribute17 hz_parties.ATTRIBUTE17%TYPE,
62 attribute18 hz_parties.ATTRIBUTE18%TYPE,
63 attribute19 hz_parties.ATTRIBUTE19%TYPE,
64 attribute20 hz_parties.ATTRIBUTE20%TYPE,
65 attribute21 hz_parties.ATTRIBUTE21%TYPE,
66 attribute22 hz_parties.ATTRIBUTE22%TYPE,
67 attribute23 hz_parties.ATTRIBUTE23%TYPE,
68 attribute24 hz_parties.ATTRIBUTE24%TYPE,
69 object_version_number hz_parties.OBJECT_VERSION_NUMBER%TYPE
70 );
71
72 -- External Bank Branch Record
73 TYPE ExtBankBranch_rec_type IS RECORD(
74 branch_party_id hz_parties.PARTY_ID%TYPE,
75 bank_party_id hz_parties.PARTY_ID%TYPE,
76 branch_name hz_parties.PARTY_NAME%TYPE,
77 branch_number hz_organization_profiles.BANK_OR_BRANCH_NUMBER%TYPE,
78 branch_type hz_code_assignments.CLASS_CODE%TYPE,
79 alternate_branch_name hz_parties.ORGANIZATION_NAME_PHONETIC%TYPE,
80 description hz_parties.MISSION_STATEMENT%TYPE,
81 bic hz_contact_points.EFT_SWIFT_CODE%TYPE,
82 eft_number hz_contact_points.EFT_USER_NUMBER%TYPE,
83 rfc_identifier hz_code_assignments.CLASS_CODE%TYPE,
84 attribute_category hz_parties.ATTRIBUTE_CATEGORY%TYPE,
85 attribute1 hz_parties.ATTRIBUTE1%TYPE,
86 attribute2 hz_parties.ATTRIBUTE2%TYPE,
87 attribute3 hz_parties.ATTRIBUTE3%TYPE,
88 attribute4 hz_parties.ATTRIBUTE4%TYPE,
89 attribute5 hz_parties.ATTRIBUTE5%TYPE,
90 attribute6 hz_parties.ATTRIBUTE6%TYPE,
91 attribute7 hz_parties.ATTRIBUTE7%TYPE,
92 attribute8 hz_parties.ATTRIBUTE8%TYPE,
93 attribute9 hz_parties.ATTRIBUTE9%TYPE,
94 attribute10 hz_parties.ATTRIBUTE10%TYPE,
95 attribute11 hz_parties.ATTRIBUTE11%TYPE,
96 attribute12 hz_parties.ATTRIBUTE12%TYPE,
97 attribute13 hz_parties.ATTRIBUTE13%TYPE,
98 attribute14 hz_parties.ATTRIBUTE14%TYPE,
99 attribute15 hz_parties.ATTRIBUTE15%TYPE,
100 attribute16 hz_parties.ATTRIBUTE16%TYPE,
101 attribute17 hz_parties.ATTRIBUTE17%TYPE,
102 attribute18 hz_parties.ATTRIBUTE18%TYPE,
103 attribute19 hz_parties.ATTRIBUTE19%TYPE,
104 attribute20 hz_parties.ATTRIBUTE20%TYPE,
105 attribute21 hz_parties.ATTRIBUTE21%TYPE,
106 attribute22 hz_parties.ATTRIBUTE22%TYPE,
107 attribute23 hz_parties.ATTRIBUTE23%TYPE,
108 attribute24 hz_parties.ATTRIBUTE24%TYPE,
109 bch_object_version_number hz_parties.OBJECT_VERSION_NUMBER%TYPE,
110 typ_object_version_number hz_code_assignments.OBJECT_VERSION_NUMBER%TYPE,
111 rfc_object_version_number hz_code_assignments.OBJECT_VERSION_NUMBER%TYPE,
112 eft_object_version_number hz_code_assignments.OBJECT_VERSION_NUMBER%TYPE
113 );
114
115 -- External Bank Account Record
116 TYPE ExtBankAcct_rec_type IS RECORD (
117 bank_account_id iby_ext_bank_accounts.EXT_BANK_ACCOUNT_ID%TYPE,
118 country_code iby_ext_bank_accounts.COUNTRY_CODE%TYPE,
119 branch_id iby_ext_bank_accounts.BRANCH_ID%TYPE,
120 bank_id iby_ext_bank_accounts.BANK_ID%TYPE,
121 acct_owner_party_id iby_account_owners.ACCOUNT_OWNER_PARTY_ID%TYPE,
122 bank_account_name iby_ext_bank_accounts.BANK_ACCOUNT_NAME%TYPE,
123 bank_account_num iby_ext_bank_accounts.BANK_ACCOUNT_NUM%TYPE,
124 currency iby_ext_bank_accounts.CURRENCY_CODE%TYPE,
125 bank_account_num_hash1 iby_ext_bank_accounts.BANK_ACCOUNT_NUM_HASH1%TYPE,
126 bank_account_num_hash2 iby_ext_bank_accounts.BANK_ACCOUNT_NUM_HASH2%TYPE,
127 iban iby_ext_bank_accounts.IBAN%TYPE,
128 check_digits iby_ext_bank_accounts.CHECK_DIGITS%TYPE,
129 multi_currency_allowed_flag varchar2(1),
130 alternate_acct_name iby_ext_bank_accounts.BANK_ACCOUNT_NAME_ALT%TYPE,
131 short_acct_name iby_ext_bank_accounts.SHORT_ACCT_NAME%TYPE,
132 acct_type iby_ext_bank_accounts.BANK_ACCOUNT_TYPE%TYPE,
133 acct_suffix iby_ext_bank_accounts.ACCOUNT_SUFFIX%TYPE,
134 description iby_ext_bank_accounts.DESCRIPTION%TYPE,
135 agency_location_code iby_ext_bank_accounts.AGENCY_LOCATION_CODE%TYPE,
136 foreign_payment_use_flag iby_ext_bank_accounts.FOREIGN_PAYMENT_USE_FLAG%TYPE,
137 exchange_rate_agreement_num iby_ext_bank_accounts.EXCHANGE_RATE_AGREEMENT_NUM%TYPE,
138 exchange_rate_agreement_type iby_ext_bank_accounts.EXCHANGE_RATE_AGREEMENT_TYPE%TYPE,
139 exchange_rate iby_ext_bank_accounts.EXCHANGE_RATE%TYPE,
140 payment_factor_flag iby_ext_bank_accounts.PAYMENT_FACTOR_FLAG%TYPE,
141 status varchar2(1),
142 end_date iby_ext_bank_accounts.END_DATE%TYPE,
143 START_DATE iby_ext_bank_accounts.START_DATE%TYPE,
144 hedging_contract_reference iby_ext_bank_accounts.HEDGING_CONTRACT_REFERENCE%TYPE,
145 attribute_category iby_ext_bank_accounts.ATTRIBUTE_CATEGORY%TYPE,
146 attribute1 iby_ext_bank_accounts.ATTRIBUTE1%TYPE,
147 attribute2 iby_ext_bank_accounts.ATTRIBUTE2%TYPE,
148 attribute3 iby_ext_bank_accounts.ATTRIBUTE3%TYPE,
149 attribute4 iby_ext_bank_accounts.ATTRIBUTE4%TYPE,
150 attribute5 iby_ext_bank_accounts.ATTRIBUTE5%TYPE,
151 attribute6 iby_ext_bank_accounts.ATTRIBUTE6%TYPE,
152 attribute7 iby_ext_bank_accounts.ATTRIBUTE7%TYPE,
153 attribute8 iby_ext_bank_accounts.ATTRIBUTE8%TYPE,
154 attribute9 iby_ext_bank_accounts.ATTRIBUTE9%TYPE,
155 attribute10 iby_ext_bank_accounts.ATTRIBUTE10%TYPE,
156 attribute11 iby_ext_bank_accounts.ATTRIBUTE11%TYPE,
157 attribute12 iby_ext_bank_accounts.ATTRIBUTE12%TYPE,
158 attribute13 iby_ext_bank_accounts.ATTRIBUTE13%TYPE,
159 attribute14 iby_ext_bank_accounts.ATTRIBUTE14%TYPE,
160 attribute15 iby_ext_bank_accounts.ATTRIBUTE15%TYPE,
161 object_version_number iby_ext_bank_accounts.OBJECT_VERSION_NUMBER%TYPE,
162 secondary_account_reference iby_ext_bank_accounts.SECONDARY_ACCOUNT_REFERENCE%TYPE, -- Bug 7408747(Added New Parameter to save Secondary Account Reference),
163 contact_name iby_ext_bank_accounts.CONTACT_NAME%TYPE, -- New columns for CLM Reference Data Management uptake.
164 contact_phone iby_ext_bank_accounts.CONTACT_PHONE%TYPE,
165 contact_email iby_ext_bank_accounts.CONTACT_EMAIL%TYPE,
166 contact_fax iby_ext_bank_accounts.CONTACT_FAX%TYPE
167 );
168
169 -- Intermediary Bank Account Record
170 TYPE IntermediaryAcct_rec_type IS RECORD(
171 intermediary_acct_id iby_intermediary_accts.INTERMEDIARY_ACCT_ID%TYPE,
172 bank_account_id iby_intermediary_accts.BANK_ACCT_ID%TYPE,
173 country_code iby_intermediary_accts.COUNTRY_CODE%TYPE,
174 bank_name iby_intermediary_accts.BANK_NAME%TYPE,
175 city iby_intermediary_accts.CITY%TYPE,
176 bank_code iby_intermediary_accts.BANK_CODE%TYPE,
177 branch_number iby_intermediary_accts.BRANCH_NUMBER%TYPE,
178 bic iby_intermediary_accts.BIC%TYPE,
179 account_number iby_intermediary_accts.ACCOUNT_NUMBER%TYPE,
180 check_digits iby_intermediary_accts.CHECK_DIGITS%TYPE,
181 iban iby_intermediary_accts.IBAN%TYPE,
182 comments iby_intermediary_accts.COMMENTS%TYPE,
183 object_version_number iby_intermediary_accts.OBJECT_VERSION_NUMBER%TYPE
184 );
185
186 /*======================================================================
187 * APIs defined in this package
188 *
189 * 1. create_ext_bank
190 * 2. update_ext_bank
191 * 3. set_ext_bank_end_date
192 * 4. check_ext_bank_exist
193 * 5. create_bank_branch
194 * 6. update_bank_branch
195 * 7. set_bank_branch_end_date
196 * 8. check_ext_bank_branch_exist
197 * 9. create_ext_bank_acct
198 * 10. update_ext_bank_acct
199 * 11. set_ext_bank_acct_dates
200 * 12. check_ext_acct_exist
201 * 13. get_ext_bank_acct
202 * 14. get_ext_bank_acct
203 * 15. create_intermediary_acct
204 * 16. update_intermediary_acct
205 * 17. add_joint_acct_owner
206 * 18. set_joint_acct_owner_end_date
207 * 19. change_primary_acct_owner
208 +====================================================================*/
209
210
211 -- 1. create_ext_bank
212 --
213 -- API name : create_ext_bank
214 -- Type : Public
215 -- Pre-reqs : None
216 -- Function : Creates an external bank
217 -- Current version : 1.0
218 -- Previous version: 1.0
219 -- Initial version : 1.0
220 --
221 PROCEDURE create_ext_bank (
222 p_api_version IN NUMBER,
223 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
224 p_ext_bank_rec IN ExtBank_rec_type,
225 x_bank_id OUT NOCOPY NUMBER,
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
230 );
231
232
233 -- 2. update_ext_bank
234 --
235 -- API name : update_ext_bank
236 -- Type : Public
237 -- Pre-reqs : None
238 -- Function : Updates the external bank
239 -- Current version : 1.0
240 -- Previous version: 1.0
241 -- Initial version : 1.0
242 --
243 PROCEDURE update_ext_bank (
244 p_api_version IN NUMBER,
245 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
246 p_ext_bank_rec IN ExtBank_rec_type,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2,
250 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
251 );
252
253
254 -- 3. set_bank_end_date
255 --
256 -- API name : set_bank_end_date
257 -- Type : Public
258 -- Pre-reqs : None
259 -- Function : Sets the bank end date
260 -- Current version : 1.0
261 -- Previous version: 1.0
262 -- Initial version : 1.0
263 --
264 PROCEDURE set_bank_end_date (
265 p_api_version IN NUMBER,
266 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
267 p_bank_id IN NUMBER,
268 p_end_date IN DATE,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2,
272 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
273 );
274
275
276 -- 4. check_bank_exist
277 --
278 -- API name : check_bank_exist
279 -- Type : Public
280 -- Pre-reqs : None
281 -- Function : Checks if the bank exists; bank name/number
282 -- and country code are used for identity
283 -- Current version : 1.0
284 -- Previous version: 1.0
285 -- Initial version : 1.0
286 --
287 PROCEDURE check_bank_exist (
288 p_api_version IN NUMBER,
289 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
290 p_country_code IN VARCHAR2,
291 p_bank_name IN VARCHAR2,
292 p_bank_number IN VARCHAR2,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_count OUT NOCOPY NUMBER,
295 x_msg_data OUT NOCOPY VARCHAR2,
296 x_bank_id OUT NOCOPY NUMBER,
297 x_end_date OUT NOCOPY DATE,
298 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
299 );
300
301
302 -- 5. create_ext_bank_branch
303 --
304 -- API name : create_ext_bank_branch
305 -- Type : Public
306 -- Pre-reqs : None
307 -- Function : Creates the external bank branch
308 -- Current version : 1.0
309 -- Previous version: 1.0
310 -- Initial version : 1.0
311 --
312 PROCEDURE create_ext_bank_branch (
313 p_api_version IN NUMBER,
314 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
315 p_ext_bank_branch_rec IN ExtBankBranch_rec_type,
316 x_branch_id OUT NOCOPY NUMBER,
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2,
320 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
321 );
322
323
324 -- 6. update_ext_bank_branch
325 --
326 -- API name : update_ext_bank_branch
327 -- Type : Public
328 -- Pre-reqs : None
329 -- Function : Updates the external bank branch
330 -- Current version : 1.0
331 -- Previous version: 1.0
332 -- Initial version : 1.0
333 --
334 PROCEDURE update_ext_bank_branch (
335 p_api_version IN NUMBER,
336 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
337 p_ext_bank_branch_rec IN OUT NOCOPY ExtBankBranch_rec_type,
338 x_return_status OUT NOCOPY VARCHAR2,
339 x_msg_count OUT NOCOPY NUMBER,
340 x_msg_data OUT NOCOPY VARCHAR2,
341 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
342 );
343
344
345 -- 7. set_ext_bank_branch_end_date
346 --
347 -- API name : set_ext_bank_branch_end_date
348 -- Type : Public
349 -- Pre-reqs : None
350 -- Function : Sets the bank branch end date
351 -- Current version : 1.0
352 -- Previous version: 1.0
353 -- Initial version : 1.0
354 --
355 PROCEDURE set_ext_bank_branch_end_date (
356 p_api_version IN NUMBER,
357 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
358 p_branch_id IN NUMBER,
359 p_end_date IN DATE,
360 x_return_status OUT NOCOPY VARCHAR2,
361 x_msg_count OUT NOCOPY NUMBER,
362 x_msg_data OUT NOCOPY VARCHAR2,
363 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
364 );
365
366
367 -- 8. check_ext_bank_branch_exist
368 --
369 -- API name : check_ext_bank_branch_exist
370 -- Type : Public
371 -- Pre-reqs : None
372 -- Function : Checks if the bank branch exists; branch name/number
373 -- and bank id are used for identity
374 -- Current version : 1.0
375 -- Previous version: 1.0
376 -- Initial version : 1.0
377 --
378 PROCEDURE check_ext_bank_branch_exist (
379 p_api_version IN NUMBER,
380 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
381 p_bank_id IN NUMBER,
382 p_branch_name IN VARCHAR2,
383 p_branch_number IN VARCHAR2,
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_msg_count OUT NOCOPY NUMBER,
386 x_msg_data OUT NOCOPY VARCHAR2,
387 x_branch_id OUT NOCOPY NUMBER,
388 x_end_date OUT NOCOPY DATE,
389 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
390 );
391
392
393 -- 9. create_ext_bank_acct
394 --
395 -- API name : create_ext_bank_acct
396 -- Type : Public
397 -- Pre-reqs : None
398 -- Function : Creates an external bank account
399 -- Current version : 1.0
400 -- Previous version: 1.0
401 -- Initial version : 1.0
402 --
403 PROCEDURE create_ext_bank_acct (
404 p_api_version IN NUMBER,
405 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
406 p_ext_bank_acct_rec IN ExtBankAcct_rec_type,
407 x_acct_id OUT NOCOPY NUMBER,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2,
411 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
412 );
413
414 --- Updated for the bug 6461487
415 PROCEDURE create_ext_bank_acct (
416 p_api_version IN NUMBER,
417 p_init_msg_list IN VARCHAR2,
418 p_ext_bank_acct_rec IN ExtBankAcct_rec_type,
419 p_association_level IN VARCHAR2,
420 p_supplier_site_id IN NUMBER,
421 p_party_site_id IN NUMBER,
422 p_org_id IN NUMBER,
423 p_org_type IN VARCHAR2 default NULL, --Bug7136876: new parameter
424 x_acct_id OUT NOCOPY NUMBER,
425 x_return_status OUT NOCOPY VARCHAR2,
426 x_msg_count OUT NOCOPY NUMBER,
427 x_msg_data OUT NOCOPY VARCHAR2,
428 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
429 );
430
431
432 -- 10. update_ext_bank_acct
433 --
434 -- API name : update_ext_bank_acct
435 -- Type : Public
436 -- Pre-reqs : None
437 -- Function : Updates an external bank account
438 -- Current version : 1.0
439 -- Previous version: 1.0
440 -- Initial version : 1.0
441 --
442 PROCEDURE update_ext_bank_acct (
443 p_api_version IN NUMBER,
444 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
445 p_ext_bank_acct_rec IN OUT NOCOPY ExtBankAcct_rec_type,
446 x_return_status OUT NOCOPY VARCHAR2,
447 x_msg_count OUT NOCOPY NUMBER,
448 x_msg_data OUT NOCOPY VARCHAR2,
449 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
450 );
451
452
453 -- 11. get_ext_bank_acct
454 --
455 -- API name : get_ext_bank_acct
456 -- Type : Public
457 -- Pre-reqs : None
458 -- Function : Queries an external bank account
459 -- Current version : 1.0
460 -- Previous version: 1.0
461 -- Initial version : 1.0
462 --
463 PROCEDURE get_ext_bank_acct (
464 p_api_version IN NUMBER,
465 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
466 p_bankacct_id IN NUMBER,
467 x_return_status OUT NOCOPY VARCHAR2,
468 x_msg_count OUT NOCOPY NUMBER,
469 x_msg_data OUT NOCOPY VARCHAR2,
470 x_bankacct OUT NOCOPY ExtBankAcct_rec_type,
471 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
472 );
473
474
475 -- 12. get_ext_bank_acct
476 --
477 -- API name : get_ext_bank_acct
478 -- Type : Public
479 -- Pre-reqs : None
480 -- Function : Queries an external bank account, decrypting secure
481 -- fields
482 -- Current version : 1.0
483 -- Previous version: 1.0
484 -- Initial version : 1.0
485 --
486 PROCEDURE get_ext_bank_acct (
487 p_api_version IN NUMBER,
488 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
489 p_bankacct_id IN NUMBER,
490 p_sec_key IN VARCHAR2,
491 x_return_status OUT NOCOPY VARCHAR2,
492 x_msg_count OUT NOCOPY NUMBER,
493 x_msg_data OUT NOCOPY VARCHAR2,
494 x_bankacct OUT NOCOPY ExtBankAcct_rec_type,
495 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
496 );
497
498
499 -- 13. set_ext_bank_acct_dates
500 --
501 -- API name : set_ext_bank_acct_dates
502 -- Type : Public
503 -- Pre-reqs : None
504 -- Function : Sets the bank account end dates
505 -- Current version : 1.0
506 -- Previous version: 1.0
507 -- Initial version : 1.0
508 --
509 PROCEDURE set_ext_bank_acct_dates (
510 p_api_version IN NUMBER,
511 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
512 p_acct_id IN NUMBER,
513 p_start_date IN DATE,
514 p_end_date IN DATE,
515 p_object_version_number IN OUT NOCOPY NUMBER,
516 x_return_status OUT NOCOPY VARCHAR2,
517 x_msg_count OUT NOCOPY NUMBER,
518 x_msg_data OUT NOCOPY VARCHAR2,
519 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
520 );
521
522
523 -- 14. check_ext_acct_exist
524 --
525 -- API name : check_ext_acct_exist
526 -- Type : Public
527 -- Pre-reqs : None
528 -- Function : Checks if the external account exists; identity
529 -- is determined by bank id, branch id, country and
530 -- currency codes
531 -- Current version : 1.0
532 -- Previous version: 1.0
533 -- Initial version : 1.0
534 --
535 PROCEDURE check_ext_acct_exist(
536 p_api_version IN NUMBER,
537 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
538 p_ext_bank_acct_rec IN ExtBankAcct_rec_type,
539 x_acct_id OUT NOCOPY NUMBER,
540 x_start_date OUT NOCOPY DATE,
541 x_end_date OUT NOCOPY DATE,
542 x_return_status OUT NOCOPY VARCHAR2,
543 x_msg_count OUT NOCOPY NUMBER,
544 x_msg_data OUT NOCOPY VARCHAR2,
545 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
546 );
547
548 -- 14. check_ext_acct_exist
549 --
550 -- API name : check_ext_acct_exist
551 -- Type : Public
552 -- Pre-reqs : None
553 -- Function : Checks if the external account exists; identity
554 -- is determined by bank id, branch id, country and
555 -- currency codes
556 --
557 --
558 -- Input parameters for the procedure is modified to facilitate
559 -- country specific unique bank account validation. But this procedure
560 -- is used by many external products. To ensure that no other code breaks
561 -- due to this change, the original procedure is maintained as overloaded
562 -- procedure. This procedure should be removed as soon as all the external
563 -- products update their code to use the updated procedure.
564 --
565 -- Current version : 1.0
566 -- Previous version: 1.0
567 -- Initial version : 1.0
568 --
569 PROCEDURE check_ext_acct_exist(
570 p_api_version IN NUMBER,
571 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
572 p_bank_id IN varchar2,
573 p_branch_id IN NUMBER,
574 p_acct_number IN VARCHAR2,
575 p_acct_name IN VARCHAR2,
576 p_currency IN VARCHAR2,
577 p_country_code IN VARCHAR2,
578 x_acct_id OUT NOCOPY NUMBER,
579 x_start_date OUT NOCOPY DATE,
580 x_end_date OUT NOCOPY DATE,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2,
584 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
585 );
586
587
588 -- 15. create_intermediary_acct
589 --
590 -- API name : create_intermediary_acct
591 -- Type : Public
592 -- Pre-reqs : None
593 -- Function : Creates an intermediary bank account
594 -- Current version : 1.0
595 -- Previous version: 1.0
596 -- Initial version : 1.0
597 --
598 PROCEDURE create_intermediary_acct (
599 p_api_version IN NUMBER,
600 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
601 p_intermed_acct_rec IN IntermediaryAcct_rec_type,
602 x_intermediary_acct_id OUT NOCOPY NUMBER,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_msg_count OUT NOCOPY NUMBER,
605 x_msg_data OUT NOCOPY VARCHAR2,
606 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
607 );
608
609
610 -- 16. Update_Intermediary_Acct
611 --
612 -- API name : Update_Intermediary_Acct
613 -- Type : Public
614 -- Pre-reqs : None
615 -- Function : Updates an intermediary bank account
616 -- Current version : 1.0
617 -- Previous version: 1.0
618 -- Initial version : 1.0
619 --
620 PROCEDURE update_intermediary_acct (
621 p_api_version IN NUMBER,
622 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
623 p_intermed_acct_rec IN OUT NOCOPY IntermediaryAcct_rec_type,
624 x_return_status OUT NOCOPY VARCHAR2,
625 x_msg_count OUT NOCOPY NUMBER,
626 x_msg_data OUT NOCOPY VARCHAR2,
627 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
628 );
629
630
631 -- 17. add_joint_account_owner
632 --
633 -- API name : add_joint_account_owner
634 -- Type : Public
635 -- Pre-reqs : None
636 -- Function : Associates another owner with a bank account
637 -- Current version : 1.0
638 -- Previous version: 1.0
639 -- Initial version : 1.0
640 --
641 PROCEDURE add_joint_account_owner (
642 p_api_version IN NUMBER,
643 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
644 p_bank_account_id IN NUMBER,
645 p_acct_owner_party_id IN NUMBER,
646 x_joint_acct_owner_id OUT NOCOPY NUMBER,
647 x_return_status OUT NOCOPY VARCHAR2,
648 x_msg_count OUT NOCOPY NUMBER,
649 x_msg_data OUT NOCOPY VARCHAR2,
650 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
651 );
652
653
654 -- 18. set_joint_acct_owner_end_date
655 --
656 -- API name : set_joint_acct_owner_end_date
657 -- Type : Public
658 -- Pre-reqs : None
659 -- Function : Sets the end data for a joint account owner
660 -- Current version : 1.0
661 -- Previous version: 1.0
662 -- Initial version : 1.0
663 --
664 PROCEDURE set_joint_acct_owner_end_date (
665 p_api_version IN NUMBER,
666 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
667 p_acct_owner_id IN NUMBER,
668 p_end_date IN DATE,
669 p_object_version_number IN OUT NOCOPY NUMBER,
670 x_return_status OUT NOCOPY VARCHAR2,
671 x_msg_count OUT NOCOPY NUMBER,
672 x_msg_data OUT NOCOPY VARCHAR2,
673 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
674 );
675
676
677 -- 19. change_primary_acct_owner
678 --
679 -- API name : change_primary_acct_owner
680 -- Type : Public
681 -- Pre-reqs : None
682 -- Function : Changes the
683 -- Current version : 1.0
684 -- Previous version: 1.0
685 -- Initial version : 1.0
686 --
687 PROCEDURE change_primary_acct_owner (
688 p_api_version IN NUMBER,
689 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
690 p_bank_acct_id IN NUMBER,
691 p_acct_owner_party_id IN NUMBER,
692 x_return_status OUT NOCOPY VARCHAR2,
693 x_msg_count OUT NOCOPY NUMBER,
694 x_msg_data OUT NOCOPY VARCHAR2,
695 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
696 );
697
698
699
700 -- 20. check_bank_acct_owner
701 --
702 -- API name : check_bank_acct_owner
703 -- Type : Public
704 -- Pre-reqs : None
705 -- Function : Changes the
706 -- Current version : 1.0
707 -- Previous version: 1.0
708 -- Initial version : 1.0
709 --
710 PROCEDURE check_bank_acct_owner (
711 p_api_version IN NUMBER,
712 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
713 p_bank_acct_id IN NUMBER,
714 p_acct_owner_party_id IN NUMBER,
715 x_return_status OUT NOCOPY VARCHAR2,
716 x_msg_count OUT NOCOPY NUMBER,
717 x_msg_data OUT NOCOPY VARCHAR2,
718 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
719 );
720
721
722 -- 100. Mask_Bank_Number
723 --
724 -- Function : Mask_Bank_Number
725 -- Type : Private
726 -- Purpose : Masks secured bank account attributes using bank
727 -- account mask settings
728 --
729 FUNCTION Mask_Bank_Number( p_bank_number IN VARCHAR2 ) RETURN VARCHAR2;
730
731 -- 101. Mask_Bank_Number
732 --
733 -- Function : Mask_Bank_Number
734 -- Type : Private
735 -- Purpose : Masks secured bank account attributes
736 --
737 FUNCTION Mask_Bank_Number
738 (p_bank_number IN VARCHAR2,
739 p_mask_option IN iby_ext_bank_accounts.ba_mask_setting%TYPE,
740 p_unmask_len IN iby_ext_bank_accounts.ba_unmask_length%TYPE
741 )
742 RETURN VARCHAR2;
743
744 --
745 -- USE: Gets the Bank account encryption mode setting
746 --
747 FUNCTION Get_BA_Encrypt_Mode
748 RETURN iby_sys_security_options.ext_ba_encryption_mode%TYPE;
749
750
751 -- 102. Uncipher_Bank_Number
752 --
753 -- Function : Uncipher_Bank_Number
754 -- Type : Private
755 -- Purpose : Unciphers a bank account/IBAN number
756 --
757 FUNCTION Uncipher_Bank_Number
758 (p_unmask_digits IN VARCHAR2,
759 p_segment_id IN iby_security_segments.sec_segment_id%TYPE,
760 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
761 p_sub_key_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
762 p_segment_cipher IN iby_security_segments.segment_cipher_text%TYPE,
763 p_encoding IN iby_security_segments.encoding_scheme%TYPE,
764 p_mask_option IN iby_ext_bank_accounts.ba_mask_setting%TYPE,
765 p_unmask_len IN iby_ext_bank_accounts.ba_unmask_length%TYPE
766 )
767 RETURN VARCHAR2;
768
769 -- 103. Remask_Accounts
770 --
771 -- Function : Remask_Accounts
772 -- Type : Private
773 -- Purpose : Remasks secured bank account attributes
774 --
775 PROCEDURE Remask_Accounts
776 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
777 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
778 );
779
780 -- 104. Encrypt_Accounts
781 --
782 -- Function : Encrypt_Accounts
783 -- Type : Private
784 -- Purpose : Encrypts all unencrypted accounts
785 --
786 PROCEDURE Encrypt_Accounts
787 (p_commit IN VARCHAR2,
788 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
789 );
790
791 -- 104. Decrypt_Accounts
792 --
793 -- Function : Decrypt_Accounts
794 -- Type : Private
795 -- Purpose : Decrypt all encrypted accounts
796 --
797 PROCEDURE Decrypt_Accounts
798 (p_commit IN VARCHAR2,
799 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
800 );
801
802 -- 105. Compress_Bank_Number
803 --
804 PROCEDURE Compress_Bank_Number
805 (p_bank_number IN VARCHAR2,
806 p_mask_setting IN iby_sys_security_options.ext_ba_mask_setting%TYPE,
807 p_unmask_len IN iby_sys_security_options.ext_ba_unmask_len%TYPE,
808 x_compress_num OUT NOCOPY VARCHAR2,
809 x_unmask_digits OUT NOCOPY VARCHAR2
810 );
811
812
813 --106. FSIO Code.. To Fetch vendor_id given party_id
814 --
815 PROCEDURE vendor_id(p_party_id IN VARCHAR2,
816 x_vendor_id OUT NOCOPY NUMBER);
817
818
819
820 --
821 FUNCTION find_assignment_OU
822 ( p_ext_bank_acct_id IN iby_ext_bank_accounts.ext_bank_account_id%TYPE
823 )
824 RETURN NUMBER;
825 /*Bug 14456128 Introduced new procedure Uncipher_Bank_ACCT_Number for
826 decrypting Bank Account Number */
827 FUNCTION Uncipher_Bank_ACCT_Number(
828 p_ext_bank_acct_id IN iby_ext_bank_accounts.ext_bank_account_id%TYPE,
829 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE )
830 RETURN VARCHAR2;
831
832 END IBY_EXT_BANKACCT_PUB;