1 PACKAGE POS_SBD_IBY_PKG as
2 /*$Header: POSIBYS.pls 120.1 2005/08/31 17:32:35 gdwivedi noship $ */
3
4
5 -- global variable for logging
6 g_log_module_name VARCHAR2(30) := 'POSIBYB';
7
8 /* This procedure removes the iby temp account in temp account request table.
9 *
10 */
11 PROCEDURE remove_iby_temp_account (
12 p_iby_temp_ext_bank_account_id IN NUMBER
13 , x_status out nocopy VARCHAR2
14 , x_exception_msg out nocopy VARCHAR2
15 );
16
17 /* This procedure creates the iby temp account in temp account request table.
18 *
19 */
20
21 PROCEDURE create_iby_temp_account (
22 p_party_id in NUMBER
23 , p_status in varchar2
24 , p_owner_primary_flag in varchar2
25 , p_payment_factor_flag in varchar2
26 , p_BANK_ID in NUMBER
27 , p_BANK_NAME in VARCHAR2
28 , p_BANK_NAME_ALT in varchar2
29 , p_BANK_NUMBER in VARCHAR2
30 , p_BANK_INSTITUTION in varchar2
31 , p_BANK_ADDRESS1 in VARCHAR2
32 , p_BANK_ADDRESS2 in VARCHAR2
33 , p_BANK_ADDRESS3 in VARCHAR2
34 , p_BANK_ADDRESS4 in VARCHAR2
35 , p_BANK_CITY in VARCHAR2
36 , p_BANK_COUNTY in VARCHAR2
37 , p_BANK_STATE in VARCHAR2
38 , p_BANK_ZIP in VARCHAR2
39 , p_BANK_PROVINCE in VARCHAR2
40 , p_BANK_COUNTRY in VARCHAR2
41 , p_BRANCH_ID in NUMBER
42 , p_BRANCH_NAME in VARCHAR2
43 , p_BRANCH_NAME_ALT in varchar2
44 , p_BRANCH_NUMBER in VARCHAR2
45 , p_BRANCH_TYPE in varchar2
46 , p_RFC_IDENTIFIER in varchar2
47 , p_BIC in varchar2
48 , p_BRANCH_ADDRESS1 in VARCHAR2
49 , p_BRANCH_ADDRESS2 in VARCHAR2
50 , p_BRANCH_ADDRESS3 in VARCHAR2
51 , p_BRANCH_ADDRESS4 in VARCHAR2
52 , p_BRANCH_CITY in VARCHAR2
53 , p_BRANCH_COUNTY in VARCHAR2
54 , p_BRANCH_STATE in VARCHAR2
55 , p_BRANCH_ZIP in VARCHAR2
56 , p_BRANCH_PROVINCE in VARCHAR2
57 , p_BRANCH_COUNTRY in VARCHAR2
58 , p_EXT_BANK_ACCOUNT_ID in number
59 , p_bank_account_number in varchar2
60 , p_bank_account_name in varchar2
61 , p_bank_account_name_alt in varchar2
62 , p_check_digits in varchar2
63 , p_iban in varchar2
64 , p_currency_code in varchar2
65 , p_country_code in varchar2
66 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
67 , p_bank_account_type in varchar2
68 , p_account_description in varchar2
69 , p_end_date in date
70 , p_start_date in date
71 , p_agency_location_code in varchar2
72 , p_account_suffix in varchar2
73 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
74 , P_EXCHANGE_RATE_AGREEMENT_TYPE in VARCHAR2
75 , p_EXCHANGE_RATE in NUMBER
76 , p_NOTES in VARCHAR2
77 , p_NOTE_ALT in varchar2
78 , x_temp_ext_bank_account_id out nocopy NUMBER
79 , x_status out nocopy VARCHAR2
80 , x_exception_msg out nocopy VARCHAR2
81 );
82
83 /* This procedure updates the iby temp account in temp account request table.
84 *
85 */
86 PROCEDURE update_iby_temp_account (
87 p_temp_ext_bank_acct_id in number
88 , p_party_id in NUMBER
89 , p_status in varchar2
90 , p_owner_primary_flag in varchar2
91 , p_payment_factor_flag in varchar2
92 , p_BANK_ID in NUMBER
93 , p_BANK_NAME in VARCHAR2
94 , p_BANK_NAME_ALT in varchar2
95 , p_BANK_NUMBER in VARCHAR2
96 , p_BANK_INSTITUTION in varchar2
97 , p_BANK_ADDRESS1 in VARCHAR2
98 , p_BANK_ADDRESS2 in VARCHAR2
99 , p_BANK_ADDRESS3 in VARCHAR2
100 , p_BANK_ADDRESS4 in VARCHAR2
101 , p_BANK_CITY in VARCHAR2
102 , p_BANK_COUNTY in VARCHAR2
103 , p_BANK_STATE in VARCHAR2
104 , p_BANK_ZIP in VARCHAR2
105 , p_BANK_PROVINCE in VARCHAR2
106 , p_BANK_COUNTRY in VARCHAR2
107 , p_BRANCH_ID in NUMBER
108 , p_BRANCH_NAME in VARCHAR2
109 , p_BRANCH_NAME_ALT in varchar2
110 , p_BRANCH_NUMBER in VARCHAR2
111 , p_BRANCH_TYPE in varchar2
112 , p_RFC_IDENTIFIER in varchar2
113 , p_BIC in varchar2
114 , p_BRANCH_ADDRESS1 in VARCHAR2
115 , p_BRANCH_ADDRESS2 in VARCHAR2
116 , p_BRANCH_ADDRESS3 in VARCHAR2
117 , p_BRANCH_ADDRESS4 in VARCHAR2
118 , p_BRANCH_CITY in VARCHAR2
119 , p_BRANCH_COUNTY in VARCHAR2
120 , p_BRANCH_STATE in VARCHAR2
121 , p_BRANCH_ZIP in VARCHAR2
122 , p_BRANCH_PROVINCE in VARCHAR2
123 , p_BRANCH_COUNTRY in VARCHAR2
124 , p_EXT_BANK_ACCOUNT_ID in number
125 , p_bank_account_number in varchar2
126 , p_bank_account_name in varchar2
127 , p_bank_account_name_alt in varchar2
128 , p_check_digits in varchar2
129 , p_iban in varchar2
130 , p_currency_code in varchar2
131 , p_country_code in varchar2
132 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
133 , p_bank_account_type in varchar2
134 , p_account_description in varchar2
135 , p_end_date in date
136 , p_start_date in date
137 , p_agency_location_code in varchar2
138 , p_account_suffix in varchar2
139 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
140 , P_EXCHANGE_RATE_AGREEMENT_TYPE in VARCHAR2
141 , p_EXCHANGE_RATE in NUMBER
142 , p_NOTES in VARCHAR2
143 , p_NOTE_ALT in varchar2
144 , x_status out nocopy VARCHAR2
145 , x_exception_msg out nocopy VARCHAR2
146 );
147
148 /* This procedure updates the location.
149 *
150 */
151 PROCEDURE update_location (
152 p_location_id in NUMBER
153 , p_ADDRESS1 in VARCHAR2
154 , p_ADDRESS2 in VARCHAR2
155 , p_ADDRESS3 in VARCHAR2
156 , p_ADDRESS4 in VARCHAR2
157 , p_CITY in VARCHAR2
158 , p_COUNTY in VARCHAR2
159 , p_STATE in VARCHAR2
160 , p_ZIP in VARCHAR2
161 , p_PROVINCE in VARCHAR2
162 , p_COUNTRY in VARCHAR2
163 , x_status out nocopy VARCHAR2
164 , x_exception_msg out nocopy VARCHAR2
165 );
166
167 /* This procedure creates the location.
168 *
169 */
170 PROCEDURE create_location (
171 p_ADDRESS1 in VARCHAR2
172 , p_ADDRESS2 in VARCHAR2
173 , p_ADDRESS3 in VARCHAR2
174 , p_ADDRESS4 in VARCHAR2
175 , p_CITY in VARCHAR2
176 , p_COUNTY in VARCHAR2
177 , p_STATE in VARCHAR2
178 , p_ZIP in VARCHAR2
179 , p_PROVINCE in VARCHAR2
180 , p_COUNTRY in VARCHAR2
181 , x_location_id out nocopy number
182 , x_status out nocopy VARCHAR2
183 , x_exception_msg out nocopy VARCHAR2
184 );
185
186 /* This procedure validates bank account information
187 *
188 */
189
190 PROCEDURE validate_account (
191 p_mapping_id in NUMBER
192 -- Bank
193 , p_BANK_ID in NUMBER
194 , p_BANK_NAME in VARCHAR2
195 , p_BANK_NAME_ALT in varchar2
196 , p_BANK_NUMBER in VARCHAR2
197 , p_BANK_INSTITUTION in varchar2
198 , p_BANK_ADDRESS1 in VARCHAR2
199 , p_BANK_ADDRESS2 in VARCHAR2
200 , p_BANK_ADDRESS3 in VARCHAR2
201 , p_BANK_ADDRESS4 in VARCHAR2
202 , p_BANK_CITY in VARCHAR2
203 , p_BANK_COUNTY in VARCHAR2
204 , p_BANK_STATE VARCHAR2
205 , p_BANK_ZIP in VARCHAR2
206 , p_BANK_PROVINCE in VARCHAR2
207 , p_BANK_COUNTRY in VARCHAR2
208 -- Branch
209 , p_BRANCH_ID in NUMBER
210 , p_BRANCH_NAME in VARCHAR2
211 , p_BRANCH_NAME_ALT in varchar2
212 , p_BRANCH_NUMBER in VARCHAR2
213 , p_BRANCH_TYPE in varchar2
214 , p_RFC_IDENTIFIER in varchar2
215 , p_BIC in varchar2
216 , p_BRANCH_ADDRESS1 in VARCHAR2
217 , p_BRANCH_ADDRESS2 in VARCHAR2
218 , p_BRANCH_ADDRESS3 in VARCHAR2
219 , p_BRANCH_ADDRESS4 in VARCHAR2
220 , p_BRANCH_CITY in VARCHAR2
221 , p_BRANCH_COUNTY in VARCHAR2
222 , p_BRANCH_STATE VARCHAR2
223 , p_BRANCH_ZIP in VARCHAR2
224 , p_BRANCH_PROVINCE in VARCHAR2
225 , p_BRANCH_COUNTRY in VARCHAR2
226 -- Account
227 , p_EXT_BANK_ACCOUNT_ID in number
228 , p_account_request_id in number
229 , p_bank_account_number in varchar2
230 , p_bank_account_name in varchar2
231 , p_bank_account_name_alt in varchar2
232 , p_check_digits in varchar2
233 , p_iban in varchar2
234 , p_currency_code in varchar2
235 , p_country_code in varchar2
236 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
237 , p_bank_account_type in varchar2
238 , p_account_description in varchar2
239 , p_end_date in date
240 , p_start_date in date
241 , p_agency_location_code in varchar2
242 , p_account_suffix in varchar2
243 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
244 , P_EXCHANGE_RATE_AGREEMENT_TYPE in VARCHAR2
245 , p_EXCHANGE_RATE in NUMBER
246 , x_status out nocopy VARCHAR2
247 , x_exception_msg out nocopy VARCHAR2
248 );
249
250 /* This procedure approves the request in iby temp account table.
251 *
252 */
253 PROCEDURE approve_iby_temp_account (
254 p_temp_ext_bank_account_id in number
255 , x_status out nocopy VARCHAR2
256 , x_exception_msg out nocopy VARCHAR2
257 );
258
259 /* This procedure updates the requests in iby temp account table.
260 * with the approved account information.
261 */
262 PROCEDURE update_req_with_account (
263 p_temp_ext_bank_account_id in number
264 , p_ext_bank_account_id in number
265 , p_account_request_id in number
266 , p_bank_id in number
267 , p_branch_id in number
268 , x_status out nocopy VARCHAR2
269 , x_exception_msg out nocopy VARCHAR2
270 );
271
272
273 /* This procedure prenotes the request in iby temp account table.
274 *
275 */
276 PROCEDURE prenote_iby_temp_account (
277 p_temp_ext_bank_account_id in number
278 , p_vendor_site_id in number
279 , x_status out nocopy VARCHAR2
280 , x_exception_msg out nocopy VARCHAR2
281 );
282
283 /* This procedure prenotes the request in iby temp account table.
284 *
285 */
286 PROCEDURE assign_site_to_account (
287 p_temp_ext_bank_account_id in number
288 , p_vendor_site_id in number
289 , x_status out nocopy VARCHAR2
290 , x_exception_msg out nocopy VARCHAR2
291 );
292
293
294 PROCEDURE check_for_duplicates (
295 p_mapping_id in NUMBER
296 , p_BANK_ID in NUMBER
297 , p_BANK_NAME in VARCHAR2
298 , p_BANK_NUMBER in VARCHAR2
299 , p_BRANCH_ID in NUMBER
300 , p_BRANCH_NAME in VARCHAR2
301 , p_BRANCH_NUMBER in VARCHAR2
302 , p_EXT_BANK_ACCOUNT_ID in number
303 , p_bank_account_number in varchar2
304 , p_bank_account_name in varchar2
305 , p_currency_code in varchar2
306 , p_country_code in varchar2
307 , p_account_request_id in number
308 , x_need_validation out nocopy varchar2
309 , x_status out nocopy VARCHAR2
310 , x_exception_msg out nocopy VARCHAR2
311 );
312
313 END POS_SBD_IBY_PKG;