[Home] [Help]
PACKAGE BODY: APPS.POS_SBD_PKG
Source
1 PACKAGE BODY POS_SBD_PKG as
2 /*$Header: POSSBDB.pls 120.17 2006/07/25 21:21:16 gdwivedi noship $ */
3
4
5 g_log_module_name varchar2(30) := 'POS_SBD_PKG';
6
7 PROCEDURE buyer_remove_account (
8 p_account_request_id IN NUMBER
9 , p_object_version_number IN NUMBER
10 , x_status out nocopy VARCHAR2
11 , x_exception_msg out nocopy VARCHAR2
12 )
13 IS
14 l_step NUMBER;
15 BEGIN
16
17 l_step := 0;
18 x_status := 'E';
19
20 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
21 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
22 ' Begin buyer_remove_account ');
23 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
24 ' p_account_request_id ' || p_account_request_id);
25 END IF;
26
27 POS_SBD_PKG.remove_account
28 (
29 p_account_request_id,
30 p_object_version_number,
31 x_status,
32 x_exception_msg
33 );
34
35 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
36 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
37 ' End buyer_remove_account ');
38 END IF;
39
40 EXCEPTION
41 WHEN OTHERS THEN
42 raise_application_error(-20011, 'Failure at step ' || l_step || Sqlerrm, true);
43 END buyer_remove_account;
44
45
46 PROCEDURE supplier_remove_account (
47 p_account_request_id IN NUMBER
48 , p_object_version_number IN NUMBER
49 , x_status out nocopy VARCHAR2
50 , x_exception_msg out nocopy VARCHAR2
51 )
52 IS
53 l_step NUMBER;
54 BEGIN
55 l_step := 0;
56 x_status := 'E';
57
58 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
59 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
60 ' Begin supplier_remove_account ');
61 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
62 ' p_account_request_id ' || p_account_request_id);
63 END IF;
64
65 POS_SBD_PKG.remove_account
66 (
67 p_account_request_id,
68 p_object_version_number,
69 x_status,
70 x_exception_msg
71 );
72
73 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
74 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
75 ' End supplier_remove_account ');
76 END IF;
77
78 EXCEPTION
79 WHEN OTHERS THEN
80 raise_application_error(-20012, 'Failure at step ' || l_step || Sqlerrm, true);
81 END supplier_remove_account;
82
83 PROCEDURE remove_account (
84 p_account_request_id IN NUMBER
85 , p_object_version_number IN NUMBER
86 , x_status out nocopy VARCHAR2
87 , x_exception_msg out nocopy VARCHAR2
88 )
89 IS
90 l_step NUMBER;
91 l_status IBY_TEMP_EXT_BANK_ACCTS.status%TYPE;
92 l_temp_ext_bank_account_id NUMBER;
93
94 -- Account Request details
95 CURSOR l_acct_status_cur IS
96 SELECT iby.status, iby.temp_ext_bank_acct_id, req.object_version_number
97 FROM iby_temp_ext_bank_accts iby, pos_acnt_gen_req req
98 WHERE req.account_request_id = p_account_request_id
99 AND iby.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id for update nowait;
100
101 -- Assignments Impacted
102 CURSOR l_assign_req_cur IS
103 SELECT req.assignment_request_id, summ.assignment_id, summ.priority,
104 req.request_type, req.mapping_id, req.party_site_id,
105 req.address_request_id, req.object_version_number
106 FROM pos_acnt_addr_req req, pos_acnt_addr_summ_req summ
107 where req.assignment_request_id = summ.assignment_request_id
108 AND summ.account_request_id = p_account_request_id for update nowait;
109
110 l_assign_req_rec l_assign_req_cur%ROWTYPE;
111
112 l_assignment_request_id NUMBER;
113 l_assignment_id NUMBER;
114 l_priority NUMBER;
115 l_needToDelete boolean;
116 l_object_version_number number;
117
118 -- Assignment Details
119 CURSOR l_assign_req_detail_below_cur IS
120 SELECT assignment_id, priority, start_date, end_date,
121 ext_bank_account_id, account_request_id, assignment_status
122 FROM pos_acnt_addr_summ_req
123 where assignment_request_id = l_assignment_request_id
124 and priority > l_priority for update nowait;
125 l_assign_req_det_below_rec l_assign_req_detail_below_cur%ROWTYPE;
126
127 CURSOR l_assign_req_detail_above_cur IS
128 SELECT assignment_id, priority
129 FROM pos_acnt_addr_summ_req
130 where assignment_request_id = l_assignment_request_id
131 and priority < l_priority;
132 l_assign_req_detail_above_rec l_assign_req_detail_above_cur%ROWTYPE;
133
134 CURSOR l_need_to_del_cur is
135 select summ.assignment_id from pos_acnt_addr_summ_req summ
136 where assignment_request_id = l_assignment_request_id
137 and assignment_status = 'CURRENT'
138 and not exists(select 1 from pos_acnt_addr_summ_req
139 where assignment_request_id = l_assignment_request_id
140 and assignment_status <> 'CURRENT');
141 l_need_to_del_rec l_need_to_del_cur%ROWTYPE;
142 BEGIN
143
144 l_step := 0;
145 x_status := 'E';
146
147 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
148 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
149 ' Begin remove_account ');
150 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
151 ' p_account_request_id ' || p_account_request_id);
152 END IF;
153
154 l_step := 1;
155 open l_acct_status_cur;
156 fetch l_acct_status_cur into l_status, l_temp_ext_bank_account_id, l_object_version_number;
157 -- Wrong Acount ID Issue: Verify that the account request is valid
158 if l_acct_status_cur%NOTFOUND then
159 close l_acct_status_cur;
160 x_exception_msg := 'The Bank account does not exist.';
161 raise_application_error(-20013, x_exception_msg, TRUE);
162 else
163 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
164 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
165 ' l_status ' || l_status);
166 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
167 ' l_temp_ext_bank_account_id ' || l_temp_ext_bank_account_id);
168 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
169 ' l_object_version_number ' || l_object_version_number);
170 END IF;
171
172 end if;
173
174 close l_acct_status_cur;
175
176 l_step := 2;
177
178 -- Concurrency Issue: Verify no one else has updated the account
179 if l_object_version_number <> p_object_version_number then
180 x_exception_msg := 'The bank account has been updated.';
181 raise_application_error(-20014, x_exception_msg, TRUE);
182 end if;
183
184 l_step := 3;
185
186 -- Verify that its a NEW request
187 if l_status <> 'NEW' then
188 x_exception_msg := 'The bank account cannot be deleted. Account Status is ' || l_status;
189 raise_application_error(-20015, x_exception_msg , TRUE);
190 end if;
191
192 -- Delete the row in the IBY temp table
193 l_step := 4;
194
195 POS_SBD_IBY_PKG.remove_iby_temp_account
196 (
197 p_iby_temp_ext_bank_account_id => l_temp_ext_bank_account_id
198 , x_status => x_status
199 , x_exception_msg => x_exception_msg
200 );
201
202 -- Update the priorities in POS_ACNT_ADDR_SUMM_REQ
203 -- Delete the row in the POS_ACNT_ADDR_SUMM_REQ
204 l_step := 5;
205
206 for l_assign_req_rec in l_assign_req_cur loop
207
208 l_step := 6;
209
210 l_assignment_request_id := l_assign_req_rec.assignment_request_id;
211 l_assignment_id := l_assign_req_rec.assignment_id;
212 l_priority := l_assign_req_rec.priority;
213 l_needToDelete := true;
214
215 for l_assign_req_det_below_rec in l_assign_req_detail_below_cur loop
216 l_step := 7;
217
218 -- Update the priority in POS_ACNT_ADDR_SUMM_REQ
219 POS_SBD_PKG.supplier_update_assignment(
220 p_assignment_id => l_assign_req_det_below_rec.assignment_id
221 , p_assignment_request_id => l_assign_req_rec.assignment_request_id
222 , p_object_version_number => l_assign_req_rec.object_version_number
223 , p_account_request_id => l_assign_req_det_below_rec.account_request_id
224 , p_ext_bank_account_id => l_assign_req_det_below_rec.ext_bank_account_id
225 , p_request_type => l_assign_req_rec.request_type
226 , p_mapping_id => l_assign_req_rec.mapping_id
227 , p_party_site_id => l_assign_req_rec.party_site_id
228 , p_address_request_id => l_assign_req_rec.address_request_id
229 , p_priority => l_assign_req_det_below_rec.priority - 1
230 , p_start_date => l_assign_req_det_below_rec.start_date
231 , p_end_date => l_assign_req_det_below_rec.end_date
232 , x_status => x_status
233 , x_exception_msg => x_exception_msg
234 );
235
236 l_needToDelete := false;
237 end loop;
238
239 l_step := 8;
240 -- Delete the row in POS_ACNT_ADDR_SUMM_REQ
241 l_assignment_id := l_assign_req_rec.assignment_id;
242
243 POS_SBD_TBL_PKG.del_row_pos_acnt_summ_req (
244 p_assignment_id => l_assignment_id
245 , x_status => x_status
246 , x_exception_msg => x_exception_msg
247 );
248
249 l_step := 9;
250 -- Delete the row in POS_ACNT_ADDR_REQ if needed
251 if l_needToDelete = true then
252
253 -- Check if there are any other rows left.
254 l_step := 10;
255 open l_assign_req_detail_above_cur;
256 fetch l_assign_req_detail_above_cur into l_assign_req_detail_above_rec;
257 if l_assign_req_detail_above_cur%NOTFOUND then
258 l_step := 11;
259 POS_SBD_TBL_PKG.del_row_pos_acnt_addr_req (
260 p_assignment_request_id => l_assignment_request_id
261 , x_status => x_status
262 , x_exception_msg => x_exception_msg
263 );
264 end if;
265 close l_assign_req_detail_above_cur;
266 end if;
267
268 l_needToDelete := false;
269 for l_need_to_del_rec in l_need_to_del_cur loop
270 l_assignment_id := l_need_to_del_rec.assignment_id;
271 POS_SBD_TBL_PKG.del_row_pos_acnt_summ_req (
272 p_assignment_id => l_assignment_id
273 , x_status => x_status
274 , x_exception_msg => x_exception_msg
275 );
276 l_needToDelete := true;
277 end loop;
278
279 if l_needToDelete = true then
280 POS_SBD_TBL_PKG.del_row_pos_acnt_addr_req (
281 p_assignment_request_id => l_assignment_request_id
282 , x_status => x_status
283 , x_exception_msg => x_exception_msg
284 );
285 end if;
286 end loop;
287
288 l_step := 10;
289
290 POS_SBD_TBL_PKG.del_row_pos_acnt_gen_req (
291 p_account_request_id => p_account_request_id
292 , x_status => x_status
293 , x_exception_msg => x_exception_msg
294 );
295
296 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
297 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
298 ' End remove_account ');
299 END IF;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 raise_application_error(-20016, 'Failure at step ' || l_step || Sqlerrm, true);
304 END remove_account;
305
306 PROCEDURE supplier_create_account (
307 p_mapping_id in NUMBER
308 , p_request_type in varchar2
309 , p_address_request_id in number
310 , p_party_site_id in number
311 , p_BANK_ID in NUMBER
312 , p_BANK_NAME in VARCHAR2
313 , p_BANK_NAME_ALT in varchar2
314 , p_BANK_NUMBER in VARCHAR2
315 , p_BANK_INSTITUTION in varchar2
316 , p_BANK_ADDRESS1 in VARCHAR2
317 , p_BANK_ADDRESS2 in VARCHAR2
318 , p_BANK_ADDRESS3 in VARCHAR2
319 , p_BANK_ADDRESS4 in VARCHAR2
320 , p_BANK_CITY in VARCHAR2
321 , p_BANK_COUNTY in VARCHAR2
322 , p_BANK_STATE VARCHAR2
323 , p_BANK_ZIP in VARCHAR2
324 , p_BANK_PROVINCE in VARCHAR2
325 , p_BANK_COUNTRY in VARCHAR2
326 , p_BRANCH_ID in NUMBER
327 , p_BRANCH_NAME in VARCHAR2
328 , p_BRANCH_NAME_ALT in varchar2
329 , p_BRANCH_NUMBER in VARCHAR2
330 , p_BRANCH_TYPE in varchar2
331 , p_RFC_IDENTIFIER in varchar2
332 , p_BIC in varchar2
333 , p_BRANCH_ADDRESS1 in VARCHAR2
334 , p_BRANCH_ADDRESS2 in VARCHAR2
335 , p_BRANCH_ADDRESS3 in VARCHAR2
336 , p_BRANCH_ADDRESS4 in VARCHAR2
337 , p_BRANCH_CITY in VARCHAR2
338 , p_BRANCH_COUNTY in VARCHAR2
339 , p_BRANCH_STATE VARCHAR2
340 , p_BRANCH_ZIP in VARCHAR2
341 , p_BRANCH_PROVINCE in VARCHAR2
342 , p_BRANCH_COUNTRY in VARCHAR2
343 , p_EXT_BANK_ACCOUNT_ID in number
344 , p_bank_account_number in varchar2
345 , p_bank_account_name in varchar2
346 , p_bank_account_name_alt in varchar2
347 , p_check_digits in varchar2
348 , p_iban in varchar2
349 , p_currency_code in varchar2
350 , p_country_code in varchar2
351 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
352 , p_bank_account_type in varchar2
353 , p_account_description in varchar2
354 , p_end_date in date
355 , p_start_date in date
356 , p_agency_location_code in varchar2
357 , p_account_suffix in varchar2
358 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
359 , P_EXCHANGE_RATE_AGREEMENT_TYPE in VARCHAR2
360 , p_EXCHANGE_RATE in NUMBER
361 , p_NOTES_FROM_SUPPLIER in VARCHAR2
362 , x_account_request_id out nocopy NUMBER
363 , x_status out nocopy VARCHAR2
364 , x_exception_msg out nocopy VARCHAR2
365 )
366 IS
367
368 l_step NUMBER;
369 l_temp_ext_bank_account_id number;
370
371 l_party_id HZ_PARTIES.party_id%TYPE;
372
373 CURSOR l_dup_account_req_cur is
374 select account_request_id from pos_acnt_gen_req req, iby_temp_ext_bank_accts iby
375 WHERE iby.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id
376 AND req.mapping_id = p_mapping_id
377 AND ( (iby.bank_id = p_bank_id and p_bank_id is not null and iby.bank_id is not null) OR
378 (iby.bank_number = p_bank_number and p_bank_number is not null and iby.bank_number is not null)
379 )
380 AND ( (iby.branch_id = p_branch_id and p_branch_id is not null and iby.branch_id is not null) OR
381 (iby.branch_number = p_branch_number and p_branch_number is not null
382 and iby.branch_number is not null)
383 )
384 AND (iby.bank_account_num = p_bank_account_number OR iby.bank_account_name = p_bank_account_name)
385 AND iby.currency_code = p_currency_code
386 AND iby.country_code = p_country_code;
387
388 l_dup_account_request_id POS_ACNT_GEN_REQ.account_request_id%TYPE;
389
390 CURSOR l_party_id_cur is
391 select party_id, vendor_id from pos_supplier_mappings
392 where mapping_id = p_mapping_id;
393 l_vendor_id po_vendors.vendor_id%TYPE;
394
395 l_itemtype wf_items.item_type%TYPE;
396 l_itemkey wf_items.item_key%TYPE;
397
398 BEGIN
399 l_step := 0;
400 x_status := 'E';
401 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
402 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
403 ' Begin supplier_create_account ');
404 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
405 ' p_mapping_id ' || p_mapping_id);
406 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
407 ' p_request_type ' || p_request_type);
408 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
409 ' p_address_request_id ' || p_address_request_id);
410 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
411 ' p_party_site_id ' || p_party_site_id);
412 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
413 ' p_bank_id ' || p_bank_id);
414 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
415 ' p_branch_id ' || p_branch_id);
419 ' p_branch_name ' || p_branch_name);
416 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
417 ' p_bank_name ' || p_bank_name);
418 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
420 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
421 ' p_bank_number ' || p_bank_number);
422 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
423 ' p_branch_number ' || p_branch_number);
424 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
425 ' p_bank_account_number ' || p_bank_account_number);
426 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
427 ' p_bank_account_name ' || p_bank_account_name);
428 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
429 ' p_currency_code ' || p_currency_code);
430 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
431 ' p_country_code ' || p_country_code);
432 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
433 ' p_iban ' || p_iban);
434 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
435 ' p_check_digits ' || p_check_digits);
436 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
437 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
438 END IF;
439
440 -- Check if there exists a similar account request.
441 open l_dup_account_req_cur;
442 fetch l_dup_account_req_cur into l_dup_account_request_id;
443 if l_dup_account_req_cur%FOUND then
444 x_exception_msg := 'Similar Bank Account Request already exists';
445 raise_application_error(-20017, 'Similar Bank Account Request already exists', true);
446 end if;
447 close l_dup_account_req_cur;
448
449 l_step := 1;
450 -- Find the party id for the mapping id.
451
452 open l_party_id_cur;
453 fetch l_party_id_cur into l_party_id, l_vendor_id;
454 if l_party_id_cur%NOTFOUND then
455 l_party_id := null;
456 end if;
457 close l_party_id_cur;
458
459 l_step := 2;
460
461 -- Create a row in IBY_TEMP_EXT_BANK_ACCTS
462 POS_SBD_IBY_PKG.create_iby_temp_account (
463 p_party_id => l_party_id
464 , p_status => 'NEW'
465 , p_owner_primary_flag => 'Y'
466 , p_payment_factor_flag => 'N'
467 , p_BANK_ID => p_bank_id
468 , p_BANK_NAME => p_bank_name
469 , p_BANK_NAME_ALT => p_bank_name_alt
470 , p_BANK_NUMBER => p_bank_number
471 , p_BANK_INSTITUTION => p_bank_institution
472 , p_BANK_ADDRESS1 => p_bank_address1
473 , p_BANK_ADDRESS2 => p_bank_address2
474 , p_BANK_ADDRESS3 => p_bank_address3
475 , p_BANK_ADDRESS4 => p_bank_address4
476 , p_BANK_CITY => p_bank_city
477 , p_BANK_COUNTY => p_bank_county
478 , p_BANK_STATE => p_bank_state
479 , p_BANK_ZIP => p_bank_zip
480 , p_BANK_PROVINCE => p_bank_province
481 , p_BANK_COUNTRY => p_country_code
482 , p_BRANCH_ID => p_branch_id
483 , p_BRANCH_NAME => p_branch_name
484 , p_BRANCH_NAME_ALT => p_branch_name_alt
485 , p_BRANCH_NUMBER => p_branch_number
486 , p_BRANCH_TYPE => p_branch_type
487 , p_RFC_IDENTIFIER => p_rfc_identifier
488 , p_BIC => p_bic
489 , p_BRANCH_ADDRESS1 => p_branch_address1
490 , p_BRANCH_ADDRESS2 => p_branch_address2
491 , p_BRANCH_ADDRESS3 => p_branch_address3
492 , p_BRANCH_ADDRESS4 => p_branch_address4
493 , p_BRANCH_CITY => p_branch_city
494 , p_BRANCH_COUNTY => p_branch_county
495 , p_BRANCH_STATE => p_branch_state
496 , p_BRANCH_ZIP => p_branch_zip
497 , p_BRANCH_PROVINCE => p_branch_province
498 , p_BRANCH_COUNTRY => p_country_code
499 , p_EXT_BANK_ACCOUNT_ID => p_ext_bank_account_id
500 , p_bank_account_number => p_bank_account_number
501 , p_bank_account_name => p_bank_account_name
502 , p_bank_account_name_alt => p_bank_account_name_alt
503 , p_check_digits => p_check_digits
504 , p_iban => p_iban
505 , p_currency_code => p_currency_code
506 , p_country_code => p_country_code
507 , p_FOREIGN_PAYMENT_USE_FLAG => p_FOREIGN_PAYMENT_USE_FLAG
508 , p_bank_account_type => p_bank_account_type
509 , p_account_description => p_account_description
510 , p_end_date => p_end_date
511 , p_start_date => nvl(p_start_date, sysdate)
512 , p_agency_location_code => p_agency_location_code
513 , p_account_suffix => p_account_suffix
514 , p_EXCHANGE_RATE_AGREEMENT_NUM => p_EXCHANGE_RATE_AGREEMENT_NUM
515 , P_EXCHANGE_RATE_AGREEMENT_TYPE => P_EXCHANGE_RATE_AGREEMENT_TYPE
516 , p_EXCHANGE_RATE => p_EXCHANGE_RATE
517 , p_NOTES => p_NOTES_FROM_SUPPLIER
518 , p_NOTE_ALT => null -- Note to Buyer
519 , x_temp_ext_bank_account_id => l_temp_ext_bank_account_id
520 , x_status => x_status
521 , x_exception_msg => x_exception_msg
522 );
523
524 l_step := 3;
525
526 -- Create a row in POS_ACNT_GEN_REQ
527 POS_SBD_TBL_PKG.insert_row_pos_acnt_gen_req (
528 p_mapping_id => p_mapping_id
529 , p_temp_ext_bank_account_id => l_temp_ext_bank_account_id
530 , p_ext_bank_account_id => p_ext_bank_account_id
531 , x_account_request_id => x_account_request_id
532 , x_status => x_status
533 , x_exception_msg => x_exception_msg
534 );
535
536 l_step := 4;
537
538 -- Assign the account to the supplier.
539 POS_SBD_PKG.supplier_update_assignment(
540 p_assignment_id => null
541 , p_assignment_request_id => null
542 , p_object_version_number => null
546 , p_mapping_id => p_mapping_id
543 , p_account_request_id => x_account_request_id
544 , p_ext_bank_account_id => p_ext_bank_account_id
545 , p_request_type => p_request_type
547 , p_party_site_id => p_party_site_id
548 , p_address_request_id => p_address_request_id
549 , p_priority => null
550 , p_start_date => sysdate
551 , p_end_date => null
552 , x_status => x_status
553 , x_exception_msg => x_exception_msg
554 );
555
556 -- Send all the notifications.
557 if l_vendor_id is not null then
558
559 pos_spm_wf_pkg1.notify_account_create
560 (p_vendor_id => l_vendor_id,
561 p_bank_name => p_bank_name,
562 p_bank_account_number => p_bank_account_number,
563 x_itemtype => l_itemtype,
564 x_itemkey => l_itemkey);
565 end if;
566
567 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
568 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
569 ' End supplier_create_account ');
570 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
571 ' x_account_request_id ' || x_account_request_id);
572 END IF;
573 EXCEPTION
574 WHEN OTHERS THEN
575 X_STATUS :='E';
576 raise_application_error(-20018,'Failure at step ' || l_step || Sqlerrm, true);
577 END supplier_create_account;
578
579 /* This procedure edits an account as a supplier
580 *
581 */
582
583 procedure supplier_update_account (
584 p_mapping_id in NUMBER
585 , p_account_request_id in number
586 , p_object_version_number in number
587 , p_BANK_ID in NUMBER
588 , p_BANK_NAME in VARCHAR2
589 , p_BANK_NAME_ALT in varchar2
590 , p_BANK_NUMBER in VARCHAR2
591 , p_BANK_INSTITUTION in varchar2
592 , p_BANK_ADDRESS1 in VARCHAR2
593 , p_BANK_ADDRESS2 in VARCHAR2
594 , p_BANK_ADDRESS3 in VARCHAR2
595 , p_BANK_ADDRESS4 in VARCHAR2
596 , p_BANK_CITY in VARCHAR2
597 , p_BANK_COUNTY in VARCHAR2
598 , p_BANK_STATE VARCHAR2
599 , p_BANK_ZIP in VARCHAR2
600 , p_BANK_PROVINCE in VARCHAR2
601 , p_BANK_COUNTRY in VARCHAR2
602 , p_BRANCH_ID in NUMBER
603 , p_BRANCH_NAME in VARCHAR2
604 , p_BRANCH_NAME_ALT in varchar2
605 , p_BRANCH_NUMBER in VARCHAR2
606 , p_BRANCH_TYPE in varchar2
607 , p_RFC_IDENTIFIER in varchar2
608 , p_BIC in varchar2
609 , p_BRANCH_ADDRESS1 in VARCHAR2
610 , p_BRANCH_ADDRESS2 in VARCHAR2
611 , p_BRANCH_ADDRESS3 in VARCHAR2
612 , p_BRANCH_ADDRESS4 in VARCHAR2
613 , p_BRANCH_CITY in VARCHAR2
614 , p_BRANCH_COUNTY in VARCHAR2
615 , p_BRANCH_STATE VARCHAR2
616 , p_BRANCH_ZIP in VARCHAR2
617 , p_BRANCH_PROVINCE in VARCHAR2
618 , p_BRANCH_COUNTRY in VARCHAR2
619 , p_EXT_BANK_ACCOUNT_ID in number
620 , p_bank_account_number in varchar2
621 , p_bank_account_name in varchar2
622 , p_bank_account_name_alt in varchar2
623 , p_check_digits in varchar2
624 , p_iban in varchar2
625 , p_currency_code in varchar2
626 , p_country_code in varchar2
627 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
628 , p_bank_account_type in varchar2
629 , p_account_description in varchar2
630 , p_end_date in date
631 , p_start_date in date
632 , p_agency_location_code in varchar2
633 , p_account_suffix in varchar2
634 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
635 , P_EXCHANGE_RATE_AGREEMENT_TYPE in VARCHAR2
636 , p_EXCHANGE_RATE in NUMBER
637 , p_NOTES_FROM_SUPPLIER in VARCHAR2
638 , x_account_request_id out nocopy NUMBER
639 , x_status out nocopy VARCHAR2
640 , x_exception_msg out nocopy VARCHAR2
641 )
642 IS
643 l_step number;
644 l_party_id HZ_PARTIES.party_id%TYPE;
645 l_status iby_temp_ext_bank_accts.status%TYPE;
646 l_vendor_id po_vendors.vendor_id%TYPE;
647
648 CURSOR l_party_id_cur is
649 select party_id, vendor_id from pos_supplier_mappings
650 where mapping_id = p_mapping_id;
651
652 l_temp_ext_bank_account_id pos_acnt_gen_req.temp_ext_bank_acct_id%TYPE;
653 l_object_version_number pos_acnt_gen_req.object_version_number%TYPE;
654
655 CURSOR l_account_request_id_cur is
656 select req.temp_ext_bank_acct_id, iby.note_alt, req.object_version_number, iby.status
657 from pos_acnt_gen_req req, iby_temp_ext_bank_accts iby
658 where req.account_request_id = p_account_request_id
659 and req.temp_ext_bank_acct_id = iby.temp_ext_bank_acct_id for update nowait;
660
661 l_itemtype wf_items.item_type%TYPE;
662 l_itemkey wf_items.item_key%TYPE;
663 l_account_request_id_rec l_account_request_id_cur%ROWTYPE;
664 BEGIN
665
666 l_step := 0;
667 x_status := 'E';
668
669 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
670 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
671 ' Begin supplier_update_account ');
672 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
673 ' p_mapping_id ' || p_mapping_id);
674 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
675 ' p_bank_id ' || p_bank_id);
676 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
677 ' p_branch_id ' || p_branch_id);
678 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
679 ' p_bank_name ' || p_bank_name);
683 ' p_bank_number ' || p_bank_number);
680 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
681 ' p_branch_name ' || p_branch_name);
682 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
684 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
685 ' p_branch_number ' || p_branch_number);
686 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
687 ' p_bank_account_number ' || p_bank_account_number);
688 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
689 ' p_bank_account_name ' || p_bank_account_name);
690 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
691 ' p_currency_code ' || p_currency_code);
692 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
693 ' p_country_code ' || p_country_code);
694 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
695 ' p_iban ' || p_iban);
696 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
697 ' p_check_digits ' || p_check_digits);
698 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
699 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
700 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
701 ' p_account_request_id ' || p_account_request_id);
702
703 END IF;
704
705
706 open l_party_id_cur;
707 fetch l_party_id_cur into l_party_id, l_vendor_id;
708 if l_party_id_cur%NOTFOUND then
709 l_party_id := null;
710 end if;
711 close l_party_id_cur;
712
713 l_step := 1;
714
715 if p_account_request_id is not null then
716 -- Update the existing request.
717 l_step := 2;
718
719 open l_account_request_id_cur;
720 fetch l_account_request_id_cur into l_account_request_id_rec;
721
722 l_object_version_number := l_account_request_id_rec.object_version_number;
723 l_status := l_account_request_id_rec.status;
724
725 -- Verify no one else has changed the row.
726 if l_object_version_number <> p_object_version_number then
727 close l_account_request_id_cur;
728 x_exception_msg := 'The bank account has been updated.';
729 raise_application_error(-20019, x_exception_msg, TRUE);
730 end if;
731
732 if l_status = 'VERIFICATION_FAILED' then
733 l_status := 'CORRECTED';
734 end if;
735
736 l_step := 3;
737
738 -- Update the row in IBY_TEMP_EXT_BANK_ACCTS
739 POS_SBD_IBY_PKG.update_iby_temp_account (
740 p_temp_ext_bank_acct_id => l_account_request_id_rec.temp_ext_bank_acct_id
741 , p_party_id => l_party_id
742 , p_status => l_status
743 , p_owner_primary_flag => 'Y'
744 , p_payment_factor_flag => 'N'
745 , p_BANK_ID => p_bank_id
746 , p_BANK_NAME => p_bank_name
747 , p_BANK_NAME_ALT => p_bank_name_alt
748 , p_BANK_NUMBER => p_bank_number
749 , p_BANK_INSTITUTION => p_bank_institution
750 , p_BANK_ADDRESS1 => p_bank_address1
751 , p_BANK_ADDRESS2 => p_bank_address2
752 , p_BANK_ADDRESS3 => p_bank_address3
753 , p_BANK_ADDRESS4 => p_bank_address4
754 , p_BANK_CITY => p_bank_city
755 , p_BANK_COUNTY => p_bank_county
756 , p_BANK_STATE => p_bank_state
757 , p_BANK_ZIP => p_bank_zip
758 , p_BANK_PROVINCE => p_bank_province
759 , p_BANK_COUNTRY => p_country_code
760 , p_BRANCH_ID => p_branch_id
761 , p_BRANCH_NAME => p_branch_name
762 , p_BRANCH_NAME_ALT => p_branch_name_alt
763 , p_BRANCH_NUMBER => p_branch_number
764 , p_BRANCH_TYPE => p_branch_type
765 , p_RFC_IDENTIFIER => p_rfc_identifier
766 , p_BIC => p_bic
767 , p_BRANCH_ADDRESS1 => p_branch_address1
768 , p_BRANCH_ADDRESS2 => p_branch_address2
769 , p_BRANCH_ADDRESS3 => p_branch_address3
770 , p_BRANCH_ADDRESS4 => p_branch_address4
771 , p_BRANCH_CITY => p_branch_city
772 , p_BRANCH_COUNTY => p_branch_county
773 , p_BRANCH_STATE => p_branch_state
774 , p_BRANCH_ZIP => p_branch_zip
775 , p_BRANCH_PROVINCE => p_branch_province
776 , p_BRANCH_COUNTRY => p_country_code
777 , p_EXT_BANK_ACCOUNT_ID => p_ext_bank_account_id
778 , p_bank_account_number => p_bank_account_number
779 , p_bank_account_name => p_bank_account_name
780 , p_bank_account_name_alt => p_bank_account_name_alt
781 , p_check_digits => p_check_digits
782 , p_iban => p_iban
783 , p_currency_code => p_currency_code
784 , p_country_code => p_country_code
785 , p_FOREIGN_PAYMENT_USE_FLAG => p_FOREIGN_PAYMENT_USE_FLAG
786 , p_bank_account_type => p_bank_account_type
787 , p_account_description => p_account_description
788 , p_end_date => p_end_date
789 , p_start_date => p_start_date
790 , p_agency_location_code => p_agency_location_code
791 , p_account_suffix => p_account_suffix
792 , p_EXCHANGE_RATE_AGREEMENT_NUM => p_EXCHANGE_RATE_AGREEMENT_NUM
793 , P_EXCHANGE_RATE_AGREEMENT_TYPE => P_EXCHANGE_RATE_AGREEMENT_TYPE
794 , p_EXCHANGE_RATE => p_EXCHANGE_RATE
795 , p_NOTES => p_NOTES_FROM_SUPPLIER
796 , p_NOTE_ALT => l_account_request_id_rec.note_alt
797 , x_status => x_status
798 , x_exception_msg => x_exception_msg
799 );
800
801 close l_account_request_id_cur;
802
803 else
804 l_step := 4;
805
806 if p_ext_bank_account_id is not null then
810 end if;
807 l_status := 'CHANGE_PENDING';
808 else
809 l_status := 'NEW';
811
812 -- Create a row in IBY_TEMP_EXT_BANK_ACCTS
813 POS_SBD_IBY_PKG.create_iby_temp_account (
814 p_party_id => l_party_id
815 , p_status => l_status
816 , p_owner_primary_flag => 'Y'
817 , p_payment_factor_flag => 'N'
818 , p_BANK_ID => p_bank_id
819 , p_BANK_NAME => p_bank_name
820 , p_BANK_NAME_ALT => p_bank_name_alt
821 , p_BANK_NUMBER => p_bank_number
822 , p_BANK_INSTITUTION => p_bank_institution
823 , p_BANK_ADDRESS1 => p_bank_address1
824 , p_BANK_ADDRESS2 => p_bank_address2
825 , p_BANK_ADDRESS3 => p_bank_address3
826 , p_BANK_ADDRESS4 => p_bank_address4
827 , p_BANK_CITY => p_bank_city
828 , p_BANK_COUNTY => p_bank_county
829 , p_BANK_STATE => p_bank_state
830 , p_BANK_ZIP => p_bank_zip
831 , p_BANK_PROVINCE => p_bank_province
832 , p_BANK_COUNTRY => p_country_code
833 , p_BRANCH_ID => p_branch_id
834 , p_BRANCH_NAME => p_branch_name
835 , p_BRANCH_NAME_ALT => p_branch_name_alt
836 , p_BRANCH_NUMBER => p_branch_number
837 , p_BRANCH_TYPE => p_branch_type
838 , p_RFC_IDENTIFIER => p_rfc_identifier
839 , p_BIC => p_bic
840 , p_BRANCH_ADDRESS1 => p_branch_address1
841 , p_BRANCH_ADDRESS2 => p_branch_address2
842 , p_BRANCH_ADDRESS3 => p_branch_address3
843 , p_BRANCH_ADDRESS4 => p_branch_address4
844 , p_BRANCH_CITY => p_branch_city
845 , p_BRANCH_COUNTY => p_branch_county
846 , p_BRANCH_STATE => p_branch_state
847 , p_BRANCH_ZIP => p_branch_zip
848 , p_BRANCH_PROVINCE => p_branch_province
849 , p_BRANCH_COUNTRY => p_country_code
850 , p_EXT_BANK_ACCOUNT_ID => p_ext_bank_account_id
851 , p_bank_account_number => p_bank_account_number
852 , p_bank_account_name => p_bank_account_name
853 , p_bank_account_name_alt => p_bank_account_name_alt
854 , p_check_digits => p_check_digits
855 , p_iban => p_iban
856 , p_currency_code => p_currency_code
857 , p_country_code => p_country_code
858 , p_FOREIGN_PAYMENT_USE_FLAG => p_FOREIGN_PAYMENT_USE_FLAG
859 , p_bank_account_type => p_bank_account_type
860 , p_account_description => p_account_description
861 , p_end_date => p_end_date
862 , p_start_date => p_start_date
863 , p_agency_location_code => p_agency_location_code
864 , p_account_suffix => p_account_suffix
865 , p_EXCHANGE_RATE_AGREEMENT_NUM => p_EXCHANGE_RATE_AGREEMENT_NUM
866 , P_EXCHANGE_RATE_AGREEMENT_TYPE => P_EXCHANGE_RATE_AGREEMENT_TYPE
867 , p_EXCHANGE_RATE => p_EXCHANGE_RATE
868 , p_NOTES => p_NOTES_FROM_SUPPLIER
869 , p_NOTE_ALT => null
870 , x_temp_ext_bank_account_id => l_temp_ext_bank_account_id
871 , x_status => x_status
872 , x_exception_msg => x_exception_msg
873 );
874
875 l_step := 5;
876
877 -- Create a row in POS_ACNT_GEN_REQ
878 POS_SBD_TBL_PKG.insert_row_pos_acnt_gen_req (
879 p_mapping_id => p_mapping_id
880 , p_temp_ext_bank_account_id => l_temp_ext_bank_account_id
881 , p_ext_bank_account_id => p_ext_bank_account_id
882 , x_account_request_id => x_account_request_id
883 , x_status => x_status
884 , x_exception_msg => x_exception_msg
885 );
886
887 -- update all the records in POS_ACNT_ADDR_SUMM_REQ with the account id
888 update pos_acnt_addr_summ_req
889 set account_request_id = x_account_request_id
890 where ext_bank_account_id = p_ext_bank_account_id;
891
892 end if;
893
894 -- Send all the notifications.
895 if l_vendor_id is not null then
896
897 pos_spm_wf_pkg1.notify_account_update
898 (p_vendor_id => l_vendor_id,
899 p_bank_name => p_bank_name,
900 p_bank_account_number => p_bank_account_number,
901 p_currency_code => p_currency_code,
902 p_bank_account_name => p_bank_account_name,
903 x_itemtype => l_itemtype,
904 x_itemkey => l_itemkey);
905 end if;
906
907
908 l_step := 6;
909
910 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
911 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
912 ' End supplier_update_account ');
913 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
914 ' x_account_request_id ' || x_account_request_id);
915 END IF;
916
917 EXCEPTION
918 WHEN OTHERS THEN
919 raise_application_error(-20020, 'Failure at step ' || l_step || Sqlerrm, true);
920 END supplier_update_account;
921
922
923 PROCEDURE buyer_prenote_account (
924 p_party_id in NUMBER
925 , p_account_request_id in number
926 , p_object_version_number in number
927 , p_vendor_site_id in number
928 , p_BANK_ID in NUMBER
929 , p_BANK_NAME in VARCHAR2
930 , p_BANK_NAME_ALT in varchar2
931 , p_BANK_NUMBER in VARCHAR2
932 , p_BANK_INSTITUTION in varchar2
933 , p_BANK_ADDRESS1 in VARCHAR2
934 , p_BANK_ADDRESS2 in VARCHAR2
935 , p_BANK_ADDRESS3 in VARCHAR2
936 , p_BANK_ADDRESS4 in VARCHAR2
937 , p_BANK_CITY in VARCHAR2
938 , p_BANK_COUNTY in VARCHAR2
939 , p_BANK_STATE VARCHAR2
940 , p_BANK_ZIP in VARCHAR2
941 , p_BANK_PROVINCE in VARCHAR2
945 , p_BRANCH_NAME_ALT in varchar2
942 , p_BANK_COUNTRY in VARCHAR2
943 , p_BRANCH_ID in NUMBER
944 , p_BRANCH_NAME in VARCHAR2
946 , p_BRANCH_NUMBER in VARCHAR2
947 , p_BRANCH_TYPE in varchar2
948 , p_RFC_IDENTIFIER in varchar2
949 , p_BIC in varchar2
950 , p_BRANCH_ADDRESS1 in VARCHAR2
951 , p_BRANCH_ADDRESS2 in VARCHAR2
952 , p_BRANCH_ADDRESS3 in VARCHAR2
953 , p_BRANCH_ADDRESS4 in VARCHAR2
954 , p_BRANCH_CITY in VARCHAR2
955 , p_BRANCH_COUNTY in VARCHAR2
956 , p_BRANCH_STATE VARCHAR2
957 , p_BRANCH_ZIP in VARCHAR2
958 , p_BRANCH_PROVINCE in VARCHAR2
959 , p_BRANCH_COUNTRY in VARCHAR2
960 , p_EXT_BANK_ACCOUNT_ID in number
961 , p_bank_account_number in varchar2
962 , p_bank_account_name in varchar2
963 , p_bank_account_name_alt in varchar2
964 , p_check_digits in varchar2
965 , p_iban in varchar2
966 , p_currency_code in varchar2
967 , p_country_code in varchar2
968 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
969 , p_bank_account_type in varchar2
970 , p_account_description in varchar2
971 , p_end_date in date
972 , p_start_date in date
973 , p_agency_location_code in varchar2
974 , p_account_suffix in varchar2
975 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
976 , P_EXCHANGE_RATE_AGREEMENT_TYPE in VARCHAR2
977 , p_EXCHANGE_RATE in NUMBER
978 , p_NOTES_FROM_BUYER in VARCHAR2
979 , x_status out nocopy VARCHAR2
980 , x_exception_msg out nocopy VARCHAR2
981 )
982 IS
983
984 l_temp_ext_bank_account_id number;
985
986 l_notes_from_supplier iby_temp_ext_bank_accts.note%TYPE;
987 CURSOR l_account_request_cur is
988 select req.temp_ext_bank_acct_id, req.object_version_number, iby.note, iby.status,
989 iby.ext_bank_account_id, iby.bank_id, iby.branch_id
990 from pos_acnt_gen_req req, iby_temp_ext_bank_accts iby
991 where account_request_id = p_account_request_id
992 and iby.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id;
993 l_account_request_rec l_account_request_cur%ROWTYPE;
994
995 l_object_version_number POS_ACNT_GEN_REQ.object_version_number%TYPE;
996 l_step number;
997 l_account_status iby_temp_ext_bank_accts.status%TYPE;
998 l_bank_id iby_temp_ext_bank_accts.bank_id%TYPE;
999 l_branch_id iby_temp_ext_bank_accts.branch_id%TYPE;
1000 l_ext_bank_account_id iby_temp_ext_bank_accts.ext_bank_account_id%TYPE;
1001
1002 l_itemtype wf_items.item_type%TYPE;
1003 l_itemkey wf_items.item_key%TYPE;
1004 CURSOR l_vendor_cur is
1005 select vendor_id from pos_supplier_mappings where party_id = p_party_id;
1006 l_vendor_id po_vendors.vendor_id%TYPE;
1007
1008 BEGIN
1009
1010 l_step := 0;
1011 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1012 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1013 ' Begin buyer_prenote_account ');
1014 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1015 ' p_party_id ' || p_party_id);
1016 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1017 ' p_bank_id ' || p_bank_id);
1018 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1019 ' p_branch_id ' || p_branch_id);
1020 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1021 ' p_bank_name ' || p_bank_name);
1022 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1023 ' p_branch_name ' || p_branch_name);
1024 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1025 ' p_bank_number ' || p_bank_number);
1026 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1027 ' p_branch_number ' || p_branch_number);
1028 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1029 ' p_bank_account_number ' || p_bank_account_number);
1030 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1031 ' p_bank_account_name ' || p_bank_account_name);
1032 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1033 ' p_currency_code ' || p_currency_code);
1034 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1035 ' p_country_code ' || p_country_code);
1036 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1037 ' p_iban ' || p_iban);
1038 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1039 ' p_check_digits ' || p_check_digits);
1040 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1041 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
1042 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1043 ' p_account_request_id ' || p_account_request_id);
1044
1045 END IF;
1046
1047
1048 -- Update the iby request tables.
1049 open l_account_request_cur;
1050 fetch l_account_request_cur into l_account_request_rec;
1051 close l_account_request_cur;
1052
1053 l_account_status := l_account_request_rec.status;
1054 l_temp_ext_bank_account_id := l_account_request_rec.temp_ext_bank_acct_id;
1055 l_object_version_number := l_account_request_rec.object_version_number;
1056 l_notes_from_supplier := l_account_request_rec.note;
1057
1058 if l_account_status <> 'NEW' then
1059 l_bank_id := l_account_request_rec.bank_id;
1060 l_branch_id := l_account_request_rec.branch_id;
1061 l_ext_bank_account_id := l_account_request_rec.ext_bank_account_id;
1062 else
1063 l_bank_id := p_bank_id;
1064 l_branch_id := p_branch_id;
1065 l_ext_bank_account_id := p_ext_bank_account_id;
1066 end if;
1067
1068 l_step := 1;
1069
1073
1070 if (l_object_version_number = p_object_version_number) then
1071
1072 l_step := 2;
1074 -- Update the row in IBY_TEMP_EXT_BANK_ACCTS
1075 POS_SBD_IBY_PKG.update_iby_temp_account (
1076 p_temp_ext_bank_acct_id => l_temp_ext_bank_account_id
1077 , p_party_id => p_party_id
1078 , p_status => 'IN_VERIFICATION'
1079 , p_owner_primary_flag => 'Y'
1080 , p_payment_factor_flag => 'N'
1081 , p_BANK_ID => l_bank_id
1082 , p_BANK_NAME => p_bank_name
1083 , p_BANK_NAME_ALT => p_bank_name_alt
1084 , p_BANK_NUMBER => p_bank_number
1085 , p_BANK_INSTITUTION => p_bank_institution
1086 , p_BANK_ADDRESS1 => p_bank_address1
1087 , p_BANK_ADDRESS2 => p_bank_address2
1088 , p_BANK_ADDRESS3 => p_bank_address3
1089 , p_BANK_ADDRESS4 => p_bank_address4
1090 , p_BANK_CITY => p_bank_city
1091 , p_BANK_COUNTY => p_bank_county
1092 , p_BANK_STATE => p_bank_state
1093 , p_BANK_ZIP => p_bank_zip
1094 , p_BANK_PROVINCE => p_bank_province
1095 , p_BANK_COUNTRY => p_country_code
1096 , p_BRANCH_ID => l_branch_id
1097 , p_BRANCH_NAME => p_branch_name
1098 , p_BRANCH_NAME_ALT => p_branch_name_alt
1099 , p_BRANCH_NUMBER => p_branch_number
1100 , p_BRANCH_TYPE => p_branch_type
1101 , p_RFC_IDENTIFIER => p_rfc_identifier
1102 , p_BIC => p_bic
1103 , p_BRANCH_ADDRESS1 => p_branch_address1
1104 , p_BRANCH_ADDRESS2 => p_branch_address2
1105 , p_BRANCH_ADDRESS3 => p_branch_address3
1106 , p_BRANCH_ADDRESS4 => p_branch_address4
1107 , p_BRANCH_CITY => p_branch_city
1108 , p_BRANCH_COUNTY => p_branch_county
1109 , p_BRANCH_STATE => p_branch_state
1110 , p_BRANCH_ZIP => p_branch_zip
1111 , p_BRANCH_PROVINCE => p_branch_province
1112 , p_BRANCH_COUNTRY => p_country_code
1113 , p_EXT_BANK_ACCOUNT_ID => l_ext_bank_account_id
1114 , p_bank_account_number => p_bank_account_number
1115 , p_bank_account_name => p_bank_account_name
1116 , p_bank_account_name_alt => p_bank_account_name_alt
1117 , p_check_digits => p_check_digits
1118 , p_iban => p_iban
1119 , p_currency_code => p_currency_code
1120 , p_country_code => p_country_code
1121 , p_FOREIGN_PAYMENT_USE_FLAG => p_FOREIGN_PAYMENT_USE_FLAG
1122 , p_bank_account_type => p_bank_account_type
1123 , p_account_description => p_account_description
1124 , p_end_date => p_end_date
1125 , p_start_date => p_start_date
1126 , p_agency_location_code => p_agency_location_code
1127 , p_account_suffix => p_account_suffix
1128 , p_EXCHANGE_RATE_AGREEMENT_NUM => p_EXCHANGE_RATE_AGREEMENT_NUM
1129 , P_EXCHANGE_RATE_AGREEMENT_TYPE => P_EXCHANGE_RATE_AGREEMENT_TYPE
1130 , p_EXCHANGE_RATE => p_EXCHANGE_RATE
1131 , p_NOTES => l_NOTES_FROM_SUPPLIER
1132 , p_NOTE_ALT => p_NOTES_FROM_BUYER -- Note from buyer
1133 , x_status => x_status
1134 , x_exception_msg => x_exception_msg
1135 );
1136
1137
1138 l_step := 3;
1139 POS_SBD_IBY_PKG.prenote_iby_temp_account (
1140 p_temp_ext_bank_account_id => l_temp_ext_bank_account_id
1141 , p_vendor_site_id => p_vendor_site_id
1142 , x_status => x_status
1143 , x_exception_msg => x_exception_msg
1144 );
1145
1146 -- Send the notifications.
1147 open l_vendor_cur;
1148 fetch l_vendor_cur into l_vendor_id;
1149 close l_vendor_cur;
1150 pos_spm_wf_pkg1.notify_sup_on_acct_action
1151 (p_bank_account_number => p_bank_account_number,
1152 p_vendor_id => l_vendor_id,
1153 p_bank_name => p_bank_name,
1154 p_request_status => 'IN_VERIFICATION',
1155 p_note => p_notes_from_buyer,
1156 x_itemtype => l_itemtype,
1157 x_itemkey => l_itemkey
1158 );
1159 end if;
1160
1161 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1162 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1163 ' End buyer_prenote_account ');
1164 END IF;
1165
1166 EXCEPTION
1167
1168 WHEN OTHERS THEN
1169 X_STATUS :='E';
1170 x_exception_msg := 'Failure at step ' || l_step;
1171 raise_application_error(-20030, x_exception_msg || Sqlerrm, true);
1172 END buyer_prenote_account;
1173
1174
1175 PROCEDURE buyer_approve_account (
1176 p_party_id in NUMBER
1177 , p_account_request_id in number
1178 , p_object_version_number in number
1179 , p_BANK_ID in NUMBER
1180 , p_BANK_NAME in VARCHAR2
1181 , p_BANK_NAME_ALT in varchar2
1182 , p_BANK_NUMBER in VARCHAR2
1183 , p_BANK_INSTITUTION in varchar2
1184 , p_BANK_ADDRESS1 in VARCHAR2
1185 , p_BANK_ADDRESS2 in VARCHAR2
1186 , p_BANK_ADDRESS3 in VARCHAR2
1187 , p_BANK_ADDRESS4 in VARCHAR2
1188 , p_BANK_CITY in VARCHAR2
1189 , p_BANK_COUNTY in VARCHAR2
1190 , p_BANK_STATE VARCHAR2
1191 , p_BANK_ZIP in VARCHAR2
1192 , p_BANK_PROVINCE in VARCHAR2
1193 , p_BANK_COUNTRY in VARCHAR2
1194 , p_BRANCH_ID in NUMBER
1195 , p_BRANCH_NAME in VARCHAR2
1196 , p_BRANCH_NAME_ALT in varchar2
1197 , p_BRANCH_NUMBER in VARCHAR2
1198 , p_BRANCH_TYPE in varchar2
1199 , p_RFC_IDENTIFIER in varchar2
1200 , p_BIC in varchar2
1201 , p_BRANCH_ADDRESS1 in VARCHAR2
1205 , p_BRANCH_CITY in VARCHAR2
1202 , p_BRANCH_ADDRESS2 in VARCHAR2
1203 , p_BRANCH_ADDRESS3 in VARCHAR2
1204 , p_BRANCH_ADDRESS4 in VARCHAR2
1206 , p_BRANCH_COUNTY in VARCHAR2
1207 , p_BRANCH_STATE VARCHAR2
1208 , p_BRANCH_ZIP in VARCHAR2
1209 , p_BRANCH_PROVINCE in VARCHAR2
1210 , p_BRANCH_COUNTRY in VARCHAR2
1211 , p_EXT_BANK_ACCOUNT_ID in number
1212 , p_bank_account_number in varchar2
1213 , p_bank_account_name in varchar2
1214 , p_bank_account_name_alt in varchar2
1215 , p_check_digits in varchar2
1216 , p_iban in varchar2
1217 , p_currency_code in varchar2
1218 , p_country_code in varchar2
1219 , p_FOREIGN_PAYMENT_USE_FLAG in varchar2
1220 , p_bank_account_type in varchar2
1221 , p_account_description in varchar2
1222 , p_end_date in date
1223 , p_start_date in date
1224 , p_agency_location_code in varchar2
1225 , p_account_suffix in varchar2
1226 , p_EXCHANGE_RATE_AGREEMENT_NUM in VARCHAR2
1227 , P_EXCHANGE_RATE_AGREEMENT_TYPE in VARCHAR2
1228 , p_EXCHANGE_RATE in NUMBER
1229 , p_NOTES_FROM_BUYER in VARCHAR2
1230 , x_status out nocopy VARCHAR2
1231 , x_exception_msg out nocopy VARCHAR2
1232 )
1233 IS
1234 l_temp_ext_bank_account_id number;
1235
1236 l_notes_from_supplier iby_temp_ext_bank_accts.note%TYPE;
1237 CURSOR l_account_request_id_cur is
1238 select req.temp_ext_bank_acct_id, req.object_version_number, iby.note
1239 from pos_acnt_gen_req req, iby_temp_ext_bank_accts iby
1240 where account_request_id = p_account_request_id
1241 and iby.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id for update nowait;
1242
1243 l_object_version_number POS_ACNT_GEN_REQ.object_version_number%TYPE;
1244
1245 l_step number;
1246
1247 l_itemtype wf_items.item_type%TYPE;
1248 l_itemkey wf_items.item_key%TYPE;
1249 CURSOR l_vendor_cur is
1250 select vendor_id from pos_supplier_mappings where party_id = p_party_id;
1251 l_vendor_id po_vendors.vendor_id%TYPE;
1252
1253 BEGIN
1254
1255 l_step := 0;
1256
1257 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1258 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1259 ' Begin buyer_approve_account ');
1260 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1261 ' p_party_id ' || p_party_id);
1262 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1263 ' p_bank_id ' || p_bank_id);
1264 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1265 ' p_branch_id ' || p_branch_id);
1266 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1267 ' p_bank_name ' || p_bank_name);
1268 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1269 ' p_branch_name ' || p_branch_name);
1270 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1271 ' p_bank_number ' || p_bank_number);
1272 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1273 ' p_branch_number ' || p_branch_number);
1274 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1275 ' p_bank_account_number ' || p_bank_account_number);
1276 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1277 ' p_bank_account_name ' || p_bank_account_name);
1278 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1279 ' p_currency_code ' || p_currency_code);
1280 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1281 ' p_country_code ' || p_country_code);
1282 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1283 ' p_iban ' || p_iban);
1284 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1285 ' p_check_digits ' || p_check_digits);
1286 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1287 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
1288 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1289 ' p_account_request_id ' || p_account_request_id);
1290 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1291 ' p_object_version_number ' || p_object_version_number);
1292 END IF;
1293
1294 -- Update the iby request tables.
1295 open l_account_request_id_cur;
1296 fetch l_account_request_id_cur
1297 into l_temp_ext_bank_account_id, l_object_version_number, l_notes_from_supplier;
1298 close l_account_request_id_cur;
1299
1300 l_step := 1;
1301
1302 if (l_object_version_number = p_object_version_number) then
1303
1304 l_step := 2;
1305
1306 -- Update the row in IBY_TEMP_EXT_BANK_ACCTS
1307 POS_SBD_IBY_PKG.update_iby_temp_account (
1308 p_temp_ext_bank_acct_id => l_temp_ext_bank_account_id
1309 , p_party_id => p_party_id
1310 , p_status => 'APPROVED'
1311 , p_owner_primary_flag => 'Y'
1312 , p_payment_factor_flag => 'N'
1313 , p_BANK_ID => p_bank_id
1314 , p_BANK_NAME => p_bank_name
1315 , p_BANK_NAME_ALT => p_bank_name_alt
1316 , p_BANK_NUMBER => p_bank_number
1317 , p_BANK_INSTITUTION => p_bank_institution
1318 , p_BANK_ADDRESS1 => p_bank_address1
1319 , p_BANK_ADDRESS2 => p_bank_address2
1320 , p_BANK_ADDRESS3 => p_bank_address3
1321 , p_BANK_ADDRESS4 => p_bank_address4
1322 , p_BANK_CITY => p_bank_city
1323 , p_BANK_COUNTY => p_bank_county
1324 , p_BANK_STATE => p_bank_state
1325 , p_BANK_ZIP => p_bank_zip
1326 , p_BANK_PROVINCE => p_bank_province
1327 , p_BANK_COUNTRY => p_country_code
1331 , p_BRANCH_NUMBER => p_branch_number
1328 , p_BRANCH_ID => p_branch_id
1329 , p_BRANCH_NAME => p_branch_name
1330 , p_BRANCH_NAME_ALT => p_branch_name_alt
1332 , p_BRANCH_TYPE => p_branch_type
1333 , p_RFC_IDENTIFIER => p_rfc_identifier
1334 , p_BIC => p_bic
1335 , p_BRANCH_ADDRESS1 => p_branch_address1
1336 , p_BRANCH_ADDRESS2 => p_branch_address2
1337 , p_BRANCH_ADDRESS3 => p_branch_address3
1338 , p_BRANCH_ADDRESS4 => p_branch_address4
1339 , p_BRANCH_CITY => p_branch_city
1340 , p_BRANCH_COUNTY => p_branch_county
1341 , p_BRANCH_STATE => p_branch_state
1342 , p_BRANCH_ZIP => p_branch_zip
1343 , p_BRANCH_PROVINCE => p_branch_province
1344 , p_BRANCH_COUNTRY => p_country_code
1345 , p_EXT_BANK_ACCOUNT_ID => p_ext_bank_account_id
1346 , p_bank_account_number => p_bank_account_number
1347 , p_bank_account_name => p_bank_account_name
1348 , p_bank_account_name_alt => p_bank_account_name_alt
1349 , p_check_digits => p_check_digits
1350 , p_iban => p_iban
1351 , p_currency_code => p_currency_code
1352 , p_country_code => p_country_code
1353 , p_FOREIGN_PAYMENT_USE_FLAG => p_FOREIGN_PAYMENT_USE_FLAG
1354 , p_bank_account_type => p_bank_account_type
1355 , p_account_description => p_account_description
1356 , p_end_date => p_end_date
1357 , p_start_date => p_start_date
1358 , p_agency_location_code => p_agency_location_code
1359 , p_account_suffix => p_account_suffix
1360 , p_EXCHANGE_RATE_AGREEMENT_NUM => p_EXCHANGE_RATE_AGREEMENT_NUM
1361 , P_EXCHANGE_RATE_AGREEMENT_TYPE => P_EXCHANGE_RATE_AGREEMENT_TYPE
1362 , p_EXCHANGE_RATE => p_EXCHANGE_RATE
1363 , p_NOTES => l_NOTES_FROM_SUPPLIER
1364 , p_NOTE_ALT => p_NOTES_FROM_BUYER -- Note from buyer
1365 , x_status => x_status
1366 , x_exception_msg => x_exception_msg
1367 );
1368
1369 l_step := 3;
1370
1371 POS_SBD_IBY_PKG.approve_iby_temp_account (
1372 p_temp_ext_bank_account_id => l_temp_ext_bank_account_id
1373 , x_status => x_status
1374 , x_exception_msg => x_exception_msg
1375 );
1376
1377 l_step := 4;
1378
1379 POS_SBD_IBY_PKG.remove_iby_temp_account
1380 (
1381 p_iby_temp_ext_bank_account_id => l_temp_ext_bank_account_id
1382 , x_status => x_status
1383 , x_exception_msg => x_exception_msg
1384 );
1385
1386 l_step := 5;
1387 POS_SBD_TBL_PKG.del_row_pos_acnt_gen_req (
1388 p_account_request_id => p_account_request_id
1389 , x_status => x_status
1390 , x_exception_msg => x_exception_msg
1391 );
1392
1393 l_step := 7;
1394
1395 update pos_acnt_addr_summ_req
1396 set account_request_id = null,
1397 last_update_date = sysdate,
1398 last_updated_by = fnd_global.user_id,
1399 last_update_login = fnd_global.login_id
1400 where account_request_id = p_account_request_id;
1401
1402 -- Notify the Supplier User
1403 open l_vendor_cur;
1404 fetch l_vendor_cur into l_vendor_id;
1405 close l_vendor_cur;
1406 pos_spm_wf_pkg1.notify_sup_on_acct_action
1407 (p_bank_account_number => p_bank_account_number,
1408 p_vendor_id => l_vendor_id,
1409 p_bank_name => p_bank_name,
1410 p_request_status => 'APPROVED',
1411 p_note => p_NOTES_FROM_BUYER,
1412 x_itemtype => l_itemtype,
1413 x_itemkey => l_itemkey
1414 );
1415
1416 end if;
1417
1418 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1419 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1420 ' End buyer_approve_account ');
1421 END IF;
1422
1423 EXCEPTION
1424
1425 WHEN OTHERS THEN
1426 X_STATUS :='E';
1427 x_exception_msg := 'Failure at step ' || l_step;
1428 raise_application_error(-20021, x_exception_msg || Sqlerrm, true);
1429 END buyer_approve_account;
1430
1431
1432 /* This procedure rejects the assignment request
1433 *
1434 */
1435 PROCEDURE buyer_reject_assignment (
1436 p_party_id in NUMBER
1437 , p_assignment_request_id in number
1438 , p_object_version_number in number
1439 , x_status out nocopy VARCHAR2
1440 , x_exception_msg out nocopy VARCHAR2
1441 )
1442 IS
1443
1444 l_step number;
1445
1446 cursor l_addr_summ_req_cur is
1447 select assignment_id from pos_acnt_addr_summ_req
1448 where assignment_request_id = p_assignment_request_id for update nowait;
1449
1450 l_addr_summ_req_rec l_addr_summ_req_cur%ROWTYPE;
1451
1452 BEGIN
1453
1454 l_step := 0;
1455
1456 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1457 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1458 ' Begin buyer_reject_assignment ');
1459 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1460 ' p_assignment_request_id ' || p_assignment_request_id);
1461 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1462 ' p_party_id ' || p_party_id);
1463 END IF;
1464
1465 -- Remove all the rows in the POS_ACNT_ADDR_SUMM_REQ
1466 for l_addr_summ_req_rec in l_addr_summ_req_cur loop
1467
1468 l_step := 2;
1469 POS_SBD_TBL_PKG.del_row_pos_acnt_summ_req (
1473
1470 p_assignment_id => l_addr_summ_req_rec.assignment_id
1471 , x_status => x_status
1472 , x_exception_msg => x_exception_msg);
1474 end loop;
1475
1476 l_step := 2;
1477 -- Remove the rows in the POS_ACNT_ADDR_REQ
1478 POS_SBD_TBL_PKG.del_row_pos_acnt_addr_req (
1479 p_assignment_request_id => p_assignment_request_id
1480 , x_status => x_status
1481 , x_exception_msg => x_exception_msg
1482 );
1483
1484 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1485 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1486 ' End buyer_reject_assignment ');
1487 END IF;
1488
1489 EXCEPTION
1490
1491 WHEN OTHERS THEN
1492 X_STATUS :='E';
1493 raise_application_error(-20022,'Failure at step ' || l_step || Sqlerrm, true);
1494 END buyer_reject_assignment;
1495
1496
1497 /* This procedure approves the assignment request
1498 *
1499 */
1500 PROCEDURE buyer_approve_assignment (
1501 p_party_id in NUMBER
1502 , p_assignment_request_id in number
1503 , p_object_version_number in number
1504 , x_status out nocopy VARCHAR2
1505 , x_exception_msg out nocopy VARCHAR2
1506 )
1507
1508 IS
1509 l_step number;
1510
1511 l_result_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1512 l_payee_rec IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_Rec_Type;
1513 l_pay_instr_rec IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
1514 l_pay_assign_rec IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
1515
1516 cursor l_acnt_req_cur is
1517 select * from pos_acnt_addr_req
1518 where assignment_request_id = p_assignment_request_id
1519 and object_version_number = p_object_version_number for update nowait;
1520 l_acnt_req_rec l_acnt_req_cur%ROWTYPE;
1521
1522 l_object_version_number number;
1523
1524 l_request_type POS_ACNT_ADDR_REQ.request_type%TYPE;
1525 l_request_status POS_ACNT_ADDR_REQ.request_status%TYPE;
1526
1527 cursor l_acnt_req_summ_cur is
1528 select * from pos_acnt_addr_summ_req
1529 where assignment_request_id = p_assignment_request_id
1530 order by priority for update nowait;
1531 l_acnt_req_summ_rec l_acnt_req_summ_cur%ROWTYPE;
1532
1533 l_assignment_status POS_ACNT_ADDR_SUMM_REQ.assignment_status%TYPE;
1534 l_ext_bank_account_id POS_ACNT_ADDR_SUMM_REQ.ext_bank_account_id%TYPE;
1535 l_assignment_id POS_ACNT_ADDR_SUMM_REQ.assignment_id%TYPE;
1536 l_party_site_id HZ_PARTY_SITES.party_site_id%TYPE;
1537 l_address_request_id POS_ACNT_ADDR_REQ.address_request_id%TYPE;
1538 l_end_date POS_ACNT_ADDR_SUMM_REQ.end_date%TYPE;
1539 l_start_date POS_ACNT_ADDR_SUMM_REQ.start_date%TYPE;
1540 l_priority POS_ACNT_ADDR_SUMM_REQ.priority%TYPE;
1541 l_payee_assignment_id number;
1542 l_msg_count number;
1543
1544 BEGIN
1545 l_step := 0;
1546 x_status := 'E';
1547
1548 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1549 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1550 ' Begin buyer_approve_assignment ');
1551 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1552 ' p_assignment_request_id ' || p_assignment_request_id);
1553 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1554 ' p_party_id ' || p_party_id);
1555 END IF;
1556
1557
1558 open l_acnt_req_cur;
1559 fetch l_acnt_req_cur into l_acnt_req_rec;
1560
1561 if l_acnt_req_cur%NOTFOUND then
1562 close l_acnt_req_cur;
1563 return;
1564 end if;
1565
1566 l_step := 1;
1567
1568 l_request_type := l_acnt_req_rec.request_type;
1569 l_object_version_number := l_acnt_req_rec.object_version_number;
1570 l_party_site_id := l_acnt_req_rec.party_site_id;
1571 l_address_request_id := l_acnt_req_rec.address_request_id;
1572 l_request_status := l_acnt_req_rec.request_status;
1573
1574 close l_acnt_req_cur;
1575
1576
1577 l_step := 2;
1578
1579 for l_acnt_req_summ_rec in l_acnt_req_summ_cur loop
1580
1581 l_step := 3;
1582
1583 l_assignment_status := l_acnt_req_summ_rec.assignment_status;
1584 l_ext_bank_account_id := l_acnt_req_summ_rec.ext_bank_account_id;
1585 l_assignment_id := l_acnt_req_summ_rec.assignment_id;
1586 l_end_date := l_acnt_req_summ_rec.end_date;
1587 l_start_date := l_acnt_req_summ_rec.start_date;
1588 l_priority := l_acnt_req_summ_rec.priority;
1589
1590 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1591 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1592 ' Assignment Status ' || l_assignment_status);
1593 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1594 ' l_assignment_id ' || l_assignment_id);
1595 END IF;
1596
1597 l_payee_rec.Payment_Function := 'PAYABLES_DISB';
1598 l_payee_rec.Party_id := p_party_id;
1599 l_payee_rec.Party_Site_id := l_party_site_id;
1600 l_payee_rec.org_Id := null;
1601 l_payee_rec.Supplier_Site_id := null;
1602 l_payee_rec.Org_Type := null;
1603
1604 -- Instrument Record.
1605 l_pay_instr_rec.Instrument_Type := 'BANKACCOUNT';
1606 l_pay_instr_rec.Instrument_Id := l_ext_bank_account_id;
1607
1608 -- Assignment Record.
1609 l_pay_assign_rec.Instrument := l_pay_instr_rec;
1610 l_pay_assign_rec.Priority := l_priority;
1611 l_pay_assign_rec.Start_Date := l_start_date;
1612 l_pay_assign_rec.End_Date := l_end_date;
1613
1614
1615 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1616 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1617 ' Calling Set_Payee_Instr_Assignment ');
1618 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1619 ' l_payee_rec.Party_id ' || l_payee_rec.Party_id);
1620 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1621 ' l_payee_rec.Payment_Function ' || l_payee_rec.Payment_Function);
1622 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1623 ' l_payee_rec.Party_Site_id ' || l_payee_rec.Party_Site_id);
1624 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1625 ' l_payee_rec.Supplier_Site_id ' || l_payee_rec.Supplier_Site_id);
1626 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1627 ' l_payee_rec.Org_id ' || l_payee_rec.org_id);
1628 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1629 ' l_payee_rec.Org_Type ' || l_payee_rec.Org_Type);
1630 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1631 ' l_pay_instr_rec.Instrument_Type ' || l_pay_instr_rec.Instrument_Type);
1632 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1633 ' l_pay_instr_rec.Instrument_Id ' || l_pay_instr_rec.Instrument_Id);
1634 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1635 ' l_pay_assign_rec.Priority ' || l_pay_assign_rec.Priority);
1636 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1637 ' l_pay_assign_rec.Start_Date ' || l_pay_assign_rec.Start_Date );
1638 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1639 ' l_pay_assign_rec.End_Date ' || l_pay_assign_rec.End_Date);
1640 END IF;
1641
1642 l_step := 4;
1643
1644 IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment(
1645 p_api_version => 1.0,
1646 p_init_msg_list => FND_API.G_FALSE,
1647 p_commit => FND_API.G_FALSE,
1648 x_return_status => x_status,
1649 x_msg_count => l_msg_count,
1650 x_msg_data => x_exception_msg,
1651 p_payee => l_payee_rec,
1652 p_assignment_attribs => l_pay_assign_rec,
1653 x_assign_id => l_payee_assignment_id,
1654 x_response => l_result_rec
1655 );
1656
1657 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1658 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1659 ' After Set_Payee_Instr_Assignment ');
1660 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1661 ' x_return_status ' || x_status);
1662 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1663 ' x_msg_count ' || l_msg_count);
1664 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1665 ' x_msg_data ' || x_exception_msg);
1666 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1667 ' x_assign_id ' || l_payee_assignment_id);
1668 END IF;
1669
1670 if l_payee_assignment_id is null OR x_status is null OR x_status <> 'S' then
1671 l_step := 5;
1672 raise_application_error(-20029, 'IBY Failed to create assignment ' || x_exception_msg, true);
1673 end if;
1674
1675 -- Delete the row in POS_ACNT_ADDR_SUMM_REQ
1676 POS_SBD_TBL_PKG.del_row_pos_acnt_summ_req (
1677 p_assignment_id => l_assignment_id
1678 , x_status => x_status
1679 , x_exception_msg => x_exception_msg
1680 );
1681 end loop;
1682
1683 -- update the record in POS_ACNT_ADDR_REQ to Approved.
1684 l_step := 6;
1685
1686 POS_SBD_TBL_PKG.del_row_pos_acnt_addr_req (
1687 p_assignment_request_id => p_assignment_request_id
1688 , x_status => x_status
1689 , x_exception_msg => x_exception_msg);
1690
1691 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1695
1692 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1693 ' End buyer_approve_assignment ');
1694 END IF;
1696 EXCEPTION
1697
1698 WHEN OTHERS THEN
1699 X_STATUS :='E';
1700 x_exception_msg := 'Failure at step ' || l_step;
1701 raise_application_error(-20023, x_exception_msg || Sqlerrm, true);
1702 END buyer_approve_assignment;
1703
1704 /* This procedure adds an account assignment on supplier's request.
1705 *
1706 */
1707 PROCEDURE supplier_add_account (
1708 p_mapping_id IN NUMBER
1709 , p_account_request_id IN NUMBER
1710 , p_ext_bank_account_id IN NUMBER
1711 , p_party_site_id IN NUMBER
1712 , p_address_request_id IN NUMBER
1713 , x_status out nocopy VARCHAR2
1714 , x_exception_msg out nocopy VARCHAR2
1715 )
1716 IS
1717
1718 l_step number;
1719 l_request_type POS_ACNT_ADDR_REQ.request_type%TYPE;
1720
1721 l_number number;
1722
1723 cursor assign_exists_cur is
1724 select 1 from pos_acnt_addr_summ_req poss, pos_acnt_addr_req req
1725 where poss.assignment_request_id = req.assignment_request_id
1726 and req.mapping_id = p_mapping_id
1727 and ( (poss.account_request_id = p_account_request_id
1728 and poss.account_request_id is not null and p_account_request_id is not null) OR
1729 (ext_bank_account_id = p_ext_bank_account_id
1730 and ext_bank_account_id is not null and p_ext_bank_account_id is not null))
1731 and req.request_status = 'PENDING'
1732 and ( (req.party_site_id is null and req.address_request_id is null and p_party_site_id is null
1733 and p_address_request_id is null) OR
1734 (req.party_site_id = p_party_site_id
1735 and req.party_site_id is not null and p_party_site_id is not null) OR
1736 (req.address_request_id = p_address_request_id
1737 and req.address_request_id is not null and p_address_request_id is not null)
1738 )
1739 and rownum = 1
1740
1741 UNION ALL
1742
1743 select 1 from iby_pmt_instr_uses_all uses, iby_external_payees_all payee, pos_supplier_mappings pmap
1744 where uses.instrument_type = 'BANKACCOUNT'
1745 and payee.ext_payee_id = uses.ext_pmt_party_id
1746 and payee.org_id is null
1747 and payee.supplier_site_id is null
1748 and ((payee.party_site_id = p_party_site_id
1749 and payee.party_site_id is not null and p_party_site_id is not null) OR
1750 (payee.party_site_id is null and p_party_site_id is null and p_address_request_id is null))
1751 and payee.payment_function = 'PAYABLES_DISB'
1752 and (uses.instrument_id = p_ext_bank_account_id
1753 and uses.instrument_id is not null and p_ext_bank_account_id is not null)
1754 and payee.payee_party_id = pmap.party_id
1755 and pmap.mapping_id = p_mapping_id
1756 and rownum = 1;
1757 BEGIN
1758
1759 l_step := 0;
1760 x_status := 'E';
1761
1762 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1763 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1764 ' Begin supplier_add_account ');
1765 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1766 ' p_account_request_id ' || p_account_request_id);
1767 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1768 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
1769 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1770 ' p_request_type ' || l_request_type);
1771 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1772 ' p_mapping_id ' || p_mapping_id);
1773 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1774 ' p_party_site_id ' || p_party_site_id);
1775 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1776 ' p_address_request_id ' || p_address_request_id);
1777 END IF;
1778
1779 -- Check if the account assignment already exists.
1780 -- In that case no need to add the account.
1781 open assign_exists_cur;
1782 fetch assign_exists_cur into l_number;
1783 if assign_exists_cur%FOUND then
1784 x_status := 'S';
1785 close assign_exists_cur;
1786 return;
1787 end if;
1788 close assign_exists_cur;
1789
1790 l_step := 1;
1791
1792 if p_address_request_id is null AND p_party_site_id is null then
1793 l_request_type := 'SUPPLIER';
1794 else
1795 l_request_type := 'ADDRESS';
1796 end if;
1797
1798 POS_SBD_PKG.supplier_update_assignment(
1799 p_assignment_id => null
1800 , p_assignment_request_id => null
1801 , p_object_version_number => null
1802 , p_account_request_id => p_account_request_id
1803 , p_ext_bank_account_id => p_ext_bank_account_id
1804 , p_request_type => l_request_type
1805 , p_mapping_id => p_mapping_id
1806 , p_party_site_id => p_party_site_id
1807 , p_address_request_id => p_address_request_id
1808 , p_priority => null
1809 , p_start_date => sysdate
1810 , p_end_date => null
1811 , x_status => x_status
1812 , x_exception_msg => x_exception_msg
1813 );
1814
1815 l_step := '3';
1816
1817 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1818 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1819 ' End supplier_add_account ');
1820 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1821 ' x_status ' || x_status);
1822 END IF;
1823
1824 EXCEPTION
1825
1826 WHEN OTHERS THEN
1827 X_STATUS :='E';
1828 x_exception_msg := 'Failure at step ' || l_step;
1829 raise_application_error(-20025, x_exception_msg || Sqlerrm, true);
1830 END supplier_add_account;
1831
1832
1833 /* This procedure creates/update the account assignment on supplier's request.
1834 *
1835 */
1836 PROCEDURE supplier_update_assignment (
1840 , p_account_request_id IN NUMBER
1837 p_assignment_id IN NUMBER
1838 , p_assignment_request_id IN NUMBER
1839 , p_object_version_number IN NUMBER
1841 , p_ext_bank_account_id IN NUMBER
1842 , p_request_type IN VARCHAR2
1843 , p_mapping_id IN NUMBER
1844 , p_party_site_id IN NUMBER
1845 , p_address_request_id IN NUMBER
1846 , p_priority IN NUMBER
1847 , p_start_date IN DATE
1848 , p_end_date IN DATE
1849 , x_status out nocopy VARCHAR2
1850 , x_exception_msg out nocopy VARCHAR2
1851 )
1852 IS
1853
1854 l_step NUMBER;
1855 l_assignment_status pos_acnt_addr_summ_req.assignment_status%TYPE;
1856
1857 l_start_date pos_acnt_addr_summ_req.start_date%TYPE;
1858 l_end_date pos_acnt_addr_summ_req.end_date%TYPE;
1859 l_priority pos_acnt_addr_summ_req.priority%TYPE;
1860 l_f_priority pos_acnt_addr_summ_req.priority%TYPE;
1861 l_assignment_request_id pos_acnt_addr_summ_req.assignment_request_id%TYPE;
1862 l_assignment_id pos_acnt_addr_summ_req.assignment_id%TYPE;
1863 l_c_assignment_id pos_acnt_addr_summ_req.assignment_id%TYPE;
1864 l_snapshot_created varchar2(1);
1865
1866 cursor l_supplier_assign_cur is
1867
1868 select uses.order_of_preference, uses.start_date, uses.end_date
1869 from iby_pmt_instr_uses_all uses, iby_external_payees_all payee,
1870 iby_ext_bank_accounts act, pos_supplier_mappings pmap
1871 where uses.instrument_type = 'BANKACCOUNT'
1872 and payee.ext_payee_id = uses.ext_pmt_party_id
1873 and payee.org_id is null
1874 and payee.supplier_site_id is null
1875 and payee.party_site_id is null
1876 and payee.payment_function = 'PAYABLES_DISB'
1877 and uses.instrument_id = act.ext_bank_account_id
1878 and payee.payee_party_id = pmap.party_id
1879 and pmap.mapping_id = p_mapping_id
1880 and sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate)
1881 and act.ext_bank_account_id = p_ext_bank_account_id;
1882
1883 cursor l_exist_assign_req_cur is
1884 select assignment_id from pos_acnt_addr_summ_req req
1885 where assignment_request_id = l_assignment_request_id
1886 and ((ext_bank_account_id = p_ext_bank_account_id and ext_bank_account_id is not null and p_ext_bank_account_id is not null) OR
1887 (account_request_id = p_account_request_id and account_request_id is not null and p_account_request_id is not null and p_ext_bank_account_id is null));
1888
1889 cursor l_address_assign_cur is
1890
1891 select uses.order_of_preference, uses.start_date, uses.end_date
1892 from iby_pmt_instr_uses_all uses, iby_external_payees_all payee,
1893 iby_ext_bank_accounts act, pos_supplier_mappings pmap
1894 where uses.instrument_type = 'BANKACCOUNT'
1895 and payee.ext_payee_id = uses.ext_pmt_party_id
1896 and payee.org_id is null
1897 and payee.supplier_site_id is null
1898 and payee.party_site_id = p_party_site_id
1899 and payee.payment_function = 'PAYABLES_DISB'
1900 and uses.instrument_id = act.ext_bank_account_id
1901 and payee.payee_party_id = pmap.party_id
1902 and pmap.mapping_id = p_mapping_id
1903 and sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate)
1904 and act.ext_bank_account_id = p_ext_bank_account_id;
1905
1906 cursor l_supplier_req_cur is
1907 select assignment_request_id from pos_acnt_addr_req
1908 where request_type = 'SUPPLIER'
1909 and mapping_id = p_mapping_id
1910 and request_status = 'PENDING'
1911 and party_site_id is null
1912 and address_request_id is null;
1913
1914 cursor l_address_req_cur is
1915 select assignment_request_id from pos_acnt_addr_req
1916 where request_type = 'ADDRESS'
1917 and mapping_id = p_mapping_id
1918 and request_status = 'PENDING'
1919 and ( (address_request_id = p_address_request_id and address_request_id is not null and p_address_request_id is not null)
1920 OR (party_site_id = p_party_site_id and party_site_id is not null and p_party_site_id is not null));
1921
1922 cursor l_assign_iby_cur is
1923 select uses.order_of_preference, uses.start_date, uses.end_date, uses.instrument_id, req.account_request_id
1924 from iby_pmt_instr_uses_all uses, iby_external_payees_all payee,
1925 iby_ext_bank_accounts act, pos_supplier_mappings pmap, pos_acnt_gen_req req
1926 where uses.instrument_type = 'BANKACCOUNT'
1927 and payee.ext_payee_id = uses.ext_pmt_party_id
1928 and payee.org_id is null
1929 and ((payee.party_site_id = p_party_site_id
1930 and p_party_site_id is not null and payee.party_site_id is not null) OR
1931 (p_party_site_id is null and payee.party_site_id is null))
1932 and uses.instrument_id = act.ext_bank_account_id
1933 and payee.payee_party_id = pmap.party_id
1934 and payee.payment_function = 'PAYABLES_DISB'
1935 and pmap.mapping_id = p_mapping_id
1936 and payee.supplier_site_id is null
1937 and sysdate between NVL(act.start_date,sysdate) AND NVL(act.end_date,sysdate)
1938 and req.ext_bank_account_id (+) = act.ext_bank_account_id
1939 and req.mapping_id(+) = p_mapping_id;
1940
1941
1942 cursor l_max_priority_cur is
1943 select max(summ.priority)
1944 from pos_acnt_addr_summ_req summ, pos_acnt_addr_req req
1945 where req.mapping_id = p_mapping_id
1946 and req.assignment_request_id = summ.assignment_request_id
1947 and ((req.party_site_id = p_party_site_id and req.party_site_id is
1948 not null and p_party_site_id is not null)
1949 OR (req.party_site_id is null and p_party_site_id is null))
1950 and ((req.address_request_id = p_address_request_id
1951 and req.address_request_id is not null and p_address_request_id is not null)
1952 OR (req.address_request_id is null and p_address_request_id is null))
1953 and req.request_status = 'PENDING';
1954
1955 BEGIN
1956
1957 l_step := 0;
1958
1959 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1960 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1961 ' Begin supplier_update_assignment ');
1965 ' p_ext_bank_account_id ' || p_ext_bank_account_id);
1962 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1963 ' p_account_request_id ' || p_account_request_id);
1964 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1966 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1967 ' p_request_type ' || p_request_type);
1968 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1969 ' p_mapping_id ' || p_mapping_id);
1970 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1971 ' p_party_site_id ' || p_party_site_id);
1972 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1973 ' p_address_request_id ' || p_address_request_id);
1974 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1975 ' p_assignment_id ' || p_assignment_id);
1976 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1977 ' p_assignment_request_id ' || p_assignment_request_id);
1978 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1979 ' p_priority ' || p_priority);
1980 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1981 ' p_start_date ' || p_start_date);
1982 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1983 ' p_end_date ' || p_end_date);
1984 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
1985 ' p_object_version_number ' || p_object_version_number);
1986 END IF;
1987
1988 if p_assignment_request_id is null then
1989 l_step := 1;
1990
1991 if p_request_type = 'SUPPLIER' then
1992 l_step := 2;
1993 open l_supplier_req_cur;
1994 fetch l_supplier_req_cur into l_assignment_request_id;
1995 close l_supplier_req_cur;
1996 else
1997 l_step := 3;
1998 open l_address_req_cur;
1999 fetch l_address_req_cur into l_assignment_request_id;
2000 close l_address_req_cur;
2001 end if;
2002
2003 l_step := 4;
2004
2005 if l_assignment_request_id is null then
2006
2007 l_step := 5;
2008
2009 POS_SBD_TBL_PKG.insert_row_pos_acnt_addr_req (
2010 p_mapping_id => p_mapping_id
2011 , p_request_type => p_request_type
2012 , p_party_site_id => p_party_site_id
2013 , p_address_request_id => p_address_request_id
2014 , x_assignment_request_id => l_assignment_request_id
2015 , x_status => x_status
2016 , x_exception_msg => x_exception_msg
2017 );
2018 l_snapshot_created := 'Y';
2019
2020 l_step := 6;
2021 if (p_party_site_id is not null and p_request_type = 'ADDRESS') OR
2022 p_request_type = 'SUPPLIER' then
2023
2024 for l_assign_iby_rec in l_assign_iby_cur loop
2025 l_step := 7;
2026 POS_SBD_TBL_PKG.insert_row_pos_acnt_summ_req (
2027 p_assignment_request_id => l_assignment_request_id
2028 , p_ext_bank_account_id => l_assign_iby_rec.instrument_id
2029 , p_account_request_id => l_assign_iby_rec.account_request_id
2030 , p_start_date => l_assign_iby_rec.start_date
2031 , p_end_date => l_assign_iby_rec.end_date
2032 , p_priority => l_assign_iby_rec.order_of_preference
2033 , p_assignment_status => 'CURRENT'
2034 , x_assignment_id => l_c_assignment_id
2035 , x_status => x_status
2036 , x_exception_msg => x_exception_msg);
2037 end loop;
2038 end if;
2039 else
2040 l_snapshot_created := 'N';
2041
2042 end if;
2043
2044 else
2045 l_step := 8;
2046 l_assignment_request_id := p_assignment_request_id;
2047 end if;
2048
2049 l_step := 9;
2050
2051 l_f_priority := 1;
2052
2053 if p_priority is not null then
2054
2055 l_f_priority := p_priority;
2056
2057 else
2058 open l_max_priority_cur;
2059 fetch l_max_priority_cur into l_f_priority;
2060 if l_max_priority_cur%NOTFOUND then
2061 l_f_priority := 1;
2062 else
2063 if l_f_priority is null then
2064 l_f_priority := 1;
2065 else
2066 l_f_priority := l_f_priority + 1;
2067 end if;
2068 end if;
2069 close l_max_priority_cur;
2070 end if;
2071
2072 if p_ext_bank_account_id is not null then
2073 l_step := 10;
2074 if p_request_type = 'SUPPLIER' then
2075 l_step := 11;
2076 if p_party_site_id is not null OR p_address_request_id is not null then
2077 return;
2078 end if;
2079
2080 open l_supplier_assign_cur;
2081 fetch l_supplier_assign_cur into l_priority, l_start_date, l_end_date;
2082 l_step := 12;
2083 if l_supplier_assign_cur%NOTFOUND THEN
2084 l_assignment_status := 'NEW';
2085 else
2086 if (
2087 (
2088 (p_start_date is not null and l_start_date is not null and
2089 trim(l_start_date) <> trim(p_start_date)) OR
2090 (p_start_date is not null and l_start_date is null) OR
2091 (p_start_date is null and l_start_date is not null)
2092 ) OR
2093 (
2094 (p_end_date is not null and l_end_date is not null and
2095 trim(l_end_date) <> trim(p_end_date)) OR
2096 (p_end_date is not null and l_end_date is null) OR
2097 (p_end_date is null and l_end_date is not null)
2098 ) OR
2099 (l_priority <> l_f_priority)) then
2100
2101 l_assignment_status := 'UPDATE';
2102 else
2103 l_assignment_status := 'CURRENT';
2104 end if;
2105 end if;
2106
2107 close l_supplier_assign_cur;
2108 end if;
2109
2110 l_step := 13;
2111 if p_request_type = 'ADDRESS' then
2112
2113 l_step := 14;
2114 if p_party_site_id is null AND p_address_request_id is null then
2115 return;
2116 end if;
2117
2118 l_step :=15;
2119 if p_party_site_id is not null then
2120 l_step := 7;
2124 if l_address_assign_cur%NOTFOUND THEN
2121 open l_address_assign_cur;
2122 fetch l_address_assign_cur into l_priority, l_start_date, l_end_date;
2123 l_step := 8;
2125 l_assignment_status := 'NEW';
2126 else
2127
2128 if (
2129 (
2130 (p_start_date is not null and l_start_date is not null and
2131 trim(l_start_date) <> trim(p_start_date) ) OR
2132 (p_start_date is not null and l_start_date is null) OR
2133 (p_start_date is null and l_start_date is not null)
2134 ) OR
2135 (
2136 (p_end_date is not null and l_end_date is not null and
2137 trim(l_end_date) <> trim(p_end_date)) OR
2138 (p_end_date is not null and l_end_date is null) OR
2139 (p_end_date is null and l_end_date is not null)
2140 ) OR
2141 (l_priority <> l_f_priority)) then
2142
2143 l_assignment_status := 'UPDATE';
2144 else
2145 l_assignment_status := 'CURRENT';
2146 end if;
2147 end if;
2148 close l_address_assign_cur;
2149 else
2150 l_step := 9;
2151 if p_address_request_id is not null then
2152 l_assignment_status := 'NEW';
2153 end if;
2154 end if;
2155
2156 end if;
2157 else
2158 l_step := 10;
2159 l_assignment_status := 'NEW';
2160 end if;
2161 l_step := 11;
2162
2163
2164 -- Now find the assignment if not provided.
2165 if p_assignment_id is null then
2166 open l_exist_assign_req_cur;
2167 fetch l_exist_assign_req_cur into l_assignment_id;
2168 close l_exist_assign_req_cur;
2169 else
2170 l_assignment_id := p_assignment_id;
2171 end if;
2172
2173 if l_assignment_id is not null then
2174 l_step := 19;
2175 POS_SBD_TBL_PKG.update_row_pos_acnt_summ_req (
2176 p_assignment_id => l_assignment_id
2177 , p_assignment_request_id => l_assignment_request_id
2178 , p_ext_bank_account_id => p_ext_bank_account_id
2179 , p_account_request_id => p_account_request_id
2180 , p_start_date => p_start_date
2181 , p_end_date => p_end_date
2182 , p_priority => l_f_priority
2183 , p_assignment_status => l_assignment_status
2184 , x_status => x_status
2185 , x_exception_msg => x_exception_msg
2186 );
2187
2188 else
2189 l_step := 20;
2190 POS_SBD_TBL_PKG.insert_row_pos_acnt_summ_req (
2191 p_assignment_request_id => l_assignment_request_id
2192 , p_ext_bank_account_id => p_ext_bank_account_id
2193 , p_account_request_id => p_account_request_id
2194 , p_start_date => p_start_date
2195 , p_end_date => p_end_date
2196 , p_priority => l_f_priority
2197 , p_assignment_status => l_assignment_status
2198 , x_assignment_id => l_assignment_id
2199 , x_status => x_status
2200 , x_exception_msg => x_exception_msg
2201 );
2202
2203 end if;
2204
2205 l_step := 21;
2206
2207 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2208 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2209 ' End supplier_update_assignment ');
2210 END IF;
2211
2212 EXCEPTION
2213
2214 WHEN OTHERS THEN
2215 X_STATUS :='E';
2216 x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
2217 raise_application_error(-20026, x_exception_msg, true);
2218 END supplier_update_assignment;
2219
2220 /*
2221 * The accounts which can be rejected are NEW, CHANGE_PENDING, IN_VERIFICATION and CORRECTED
2222 */
2223 PROCEDURE buyer_reject_account (
2224 p_account_request_id in NUMBER
2225 , p_object_version_number in number
2226 , p_note_from_buyer in varchar2
2227 , x_status out nocopy VARCHAR2
2228 , x_exception_msg out nocopy VARCHAR2
2229 )
2230 IS
2231
2232
2233 cursor l_account_request_cur is
2234 select temp.status, req.object_version_number, req.temp_ext_bank_acct_id, pmap.vendor_id,
2235 temp.bank_account_num, temp.bank_name
2236 from pos_acnt_gen_req req, iby_temp_ext_bank_accts temp, pos_supplier_mappings pmap
2237 where req.account_request_id = p_account_request_id
2238 and temp.temp_ext_bank_acct_id = req.temp_ext_bank_acct_id
2239 and pmap.mapping_id = req.mapping_id;
2240
2241 l_account_request_rec l_account_request_cur%ROWTYPE;
2242
2243 l_step number;
2244 l_status iby_temp_ext_bank_accts.status%TYPE;
2245 l_object_version_number pos_acnt_gen_req.object_version_number%TYPE;
2246 l_temp_ext_bank_acct_id iby_temp_ext_bank_accts.temp_ext_bank_acct_id%TYPE;
2247
2248
2249 l_itemtype wf_items.item_type%TYPE;
2250 l_itemkey wf_items.item_key%TYPE;
2251
2252 l_bank_account_number iby_temp_ext_bank_accts.bank_account_num%TYPE;
2253 l_bank_name iby_temp_ext_bank_accts.bank_name%TYPE;
2254 l_vendor_id po_vendors.vendor_id%TYPE;
2255 l_ntf_status iby_temp_ext_bank_accts.status%TYPE;
2256
2257 BEGIN
2258
2259 x_status := 'E';
2260 l_step := 0;
2261
2262
2263 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2264 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2265 ' Begin buyer_reject_account ');
2266 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2267 ' p_account_request_id ' || p_account_request_id);
2268 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2269 ' p_object_version_number ' || p_object_version_number);
2270 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2271 ' p_note_from_buyer ' || p_note_from_buyer);
2272 END IF;
2273
2274 open l_account_request_cur;
2275 fetch l_account_request_cur into l_account_request_rec;
2276 if l_account_request_cur%NOTFOUND then
2280
2277 close l_account_request_cur;
2278 return;
2279 end if;
2281 l_status := l_account_request_rec.status;
2282 l_object_version_number := l_account_request_rec.object_version_number;
2283 l_temp_ext_bank_acct_id := l_account_request_rec.temp_ext_bank_acct_id;
2284 l_bank_account_number := l_account_request_rec.bank_account_num;
2285 l_bank_name := l_account_request_rec.bank_name;
2286 l_vendor_id := l_account_request_rec.vendor_id;
2287
2288 close l_account_request_cur;
2289
2290 l_step := 1;
2291
2292 if l_status = 'NEW' then
2293 l_step := 2;
2294
2295 POS_SBD_PKG.buyer_remove_account (
2296 p_account_request_id => p_account_request_id
2297 , p_object_version_number => p_object_version_number
2298 , x_status => x_status
2299 , x_exception_msg => x_exception_msg
2300 );
2301 l_ntf_status := 'REJECTED';
2302
2303 end if;
2304
2305 if l_status = 'IN_VERIFICATION' OR l_status = 'CORRECTED' then
2306 l_step := 3;
2307 update iby_temp_ext_bank_accts
2308 set status = 'VERIFICATION_FAILED', note_alt = p_note_from_buyer
2309 where temp_ext_bank_acct_id = l_temp_ext_bank_acct_id;
2310 l_ntf_status := 'VERIFICATION_FAILED';
2311 end if;
2312
2313 if l_status = 'CHANGE_PENDING' then
2314
2315 l_step := 4;
2316 POS_SBD_IBY_PKG.remove_iby_temp_account
2317 (
2318 p_iby_temp_ext_bank_account_id => l_temp_ext_bank_acct_id
2319 , x_status => x_status
2320 , x_exception_msg => x_exception_msg
2321 );
2322
2323 l_step := 5;
2324 POS_SBD_TBL_PKG.del_row_pos_acnt_gen_req (
2325 p_account_request_id => p_account_request_id
2326 , x_status => x_status
2327 , x_exception_msg => x_exception_msg
2328 );
2329
2330 update pos_acnt_addr_summ_req
2331 set account_request_id = null
2332 where account_request_id = p_account_request_id;
2333
2334 l_ntf_status := 'REJECTED';
2335
2336 end if;
2337
2338 -- Send the notifications.
2339 pos_spm_wf_pkg1.notify_sup_on_acct_action
2340 (p_bank_account_number => l_bank_account_number,
2341 p_vendor_id => l_vendor_id,
2342 p_bank_name => l_bank_name,
2343 p_request_status => l_ntf_status,
2344 p_note => p_note_from_buyer,
2345 x_itemtype => l_itemtype,
2346 x_itemkey => l_itemkey
2347 );
2348
2349 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2350 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2351 ' End buyer_reject_account ');
2352 END IF;
2353
2354 x_status := 'S';
2355
2356 EXCEPTION
2357
2358 WHEN OTHERS THEN
2359 X_STATUS :='E';
2360 x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
2361 raise_application_error(-20027, x_exception_msg, true);
2362 END buyer_reject_account;
2363
2364 PROCEDURE update_payment_pref(
2365 p_payment_preference_id IN NUMBER
2366 , p_party_id IN NUMBER
2367 , p_party_site_id IN NUMBER
2368 , p_payment_currency_code IN VARCHAR2
2369 , p_invoice_currency_code IN VARCHAR2
2370 , p_payment_method IN VARCHAR2
2371 , p_notification_method IN VARCHAR2
2372 , p_object_version_number IN NUMBER
2373 , x_status out nocopy VARCHAR2
2374 , x_exception_msg out nocopy VARCHAR2
2375 )
2376 IS
2377
2378 l_step NUMBER;
2379
2380 cursor l_current_payment_cur is
2381 select payment_preference_id from pos_acnt_pay_pref
2382 where party_id = p_party_id
2383 and party_site_id = p_party_site_id;
2384
2385 l_payment_preference_id pos_acnt_pay_pref.payment_preference_id%TYPE;
2386 l_notification_method POS_ACNT_PAY_PREF.notification_method%TYPE;
2387
2388 BEGIN
2389
2390 -- Change the notification preference method
2391 if p_notification_method is not null AND p_notification_method = 'NONE' then
2392 l_notification_method := null;
2393 else
2394 l_notification_method := p_notification_method;
2395 end if;
2396
2397 l_step := 0;
2398 -- If payment_preference_id is null then make an attempt to find one
2399
2400 if p_payment_preference_id is null then
2401 open l_current_payment_cur;
2402 fetch l_current_payment_cur into l_payment_preference_id;
2403 close l_current_payment_cur;
2404 else
2405 l_payment_preference_id := p_payment_preference_id;
2406 end if;
2407
2408 l_step := 1;
2409
2410 if l_payment_preference_id is null then
2411
2412 l_step := 2;
2413
2414 select POS_ACNT_PAY_PREF_S.nextval into l_payment_preference_id from dual;
2415 -- Create a row
2416
2417 l_step:= 3;
2418
2419 insert into POS_ACNT_PAY_PREF (
2420 payment_preference_id
2421 , party_id
2422 , party_site_id
2423 , creation_date
2424 , created_by
2425 , last_update_date
2426 , last_updated_by
2427 , last_update_login
2428 , object_version_number
2429 , payment_method
2430 , notification_method
2431 , payment_currency_code
2432 , invoice_currency_code
2433 )
2434 values
2435 (
2436 l_payment_preference_id
2437 , p_party_id
2438 , p_party_site_id
2439 , sysdate -- creation_date
2440 , fnd_global.user_id -- created_by
2441 , sysdate -- last_update_date
2442 , fnd_global.user_id -- last_updated_by
2443 , fnd_global.login_id -- last_update_login
2444 , 1
2445 , p_payment_method
2446 , l_notification_method
2447 , p_payment_currency_code
2448 , p_invoice_currency_code
2449 );
2450
2451 else
2452
2453 l_step := 4;
2457 , last_updated_by = fnd_global.user_id
2454 -- Update the row
2455 update pos_acnt_pay_pref set
2456 last_update_date = sysdate
2458 , last_update_login = fnd_global.login_id
2459 , payment_currency_code = p_payment_currency_code
2460 , invoice_currency_code = p_invoice_currency_code
2461 , notification_method = p_notification_method
2462 , payment_method = p_payment_method
2463 where payment_preference_id = l_payment_preference_id;
2464
2465 end if;
2466
2467 x_status := 'S';
2468
2469 EXCEPTION
2470
2471 WHEN OTHERS THEN
2472 X_STATUS :='E';
2473 x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
2474 raise_application_error(-20028, x_exception_msg, true);
2475 END update_payment_pref;
2476
2477 /* This procedure removes the account assignment request if all are current.
2478 *
2479 */
2480 PROCEDURE supplier_reset_assignment(
2481 p_mapping_id IN NUMBER
2482 , p_party_site_id IN NUMBER
2483 , p_address_request_id IN NUMBER
2484 , x_status out nocopy VARCHAR2
2485 , x_exception_msg out nocopy VARCHAR2
2486 )
2487 IS
2488
2489 l_step number;
2490 l_assignment_request_id number;
2491 l_assignment_id number;
2492
2493 cursor l_is_all_current is
2494 select addr.assignment_request_id from pos_acnt_addr_req addr
2495 where addr.mapping_id = p_mapping_id
2496 and (
2497 (addr.request_type = 'SUPPLIER' and p_address_request_id is null and p_address_request_id is null) OR
2498 (addr.request_type = 'ADDRESS' and addr.party_site_id = p_party_site_id and p_party_site_id is not null and addr.party_site_id is not null) OR
2499 (addr.request_type = 'ADDRESS' and addr.address_request_id = p_address_request_id and p_address_request_id is not null and addr.address_request_id is not null)
2500 )
2501 and addr.REQUEST_STATUS = 'PENDING'
2502 and not exists (select 1 from pos_acnt_addr_summ_req summ
2503 where addr.assignment_request_id = summ.assignment_request_id
2504 and summ.assignment_status <> ('CURRENT')
2505 and rownum = 1);
2506
2507 cursor l_assignment_id_cur is
2508 select assignment_id from pos_acnt_addr_summ_req
2509 where assignment_request_id = l_assignment_request_id;
2510
2511 l_assignment_id_rec l_assignment_id_cur%ROWTYPE;
2512
2513 BEGIN
2514 l_step := 0;
2515
2516 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2517 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2518 ' Start supplier_reset_assignment ');
2519 END IF;
2520
2521 open l_is_all_current;
2522 fetch l_is_all_current into l_assignment_request_id;
2523 l_step := 1;
2524
2525 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2526 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2527 ' l_assignment_request_id ' || l_assignment_request_id);
2528 END IF;
2529
2530 if l_is_all_current%FOUND then
2531
2532 l_step := 2;
2533 for l_assignment_id_rec in l_assignment_id_cur loop
2534
2535 l_step := 3;
2536 l_assignment_id := l_assignment_id_rec.assignment_id;
2537
2538 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2539 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2540 ' l_assignment_id ' || l_assignment_id);
2541 END IF;
2542
2543 POS_SBD_TBL_PKG.del_row_pos_acnt_summ_req (
2544 p_assignment_id => l_assignment_id
2545 , x_status => x_status
2546 , x_exception_msg => x_exception_msg
2547 );
2548
2549 end loop;
2550 l_step := 4;
2551
2552 POS_SBD_TBL_PKG.del_row_pos_acnt_addr_req (
2553 p_assignment_request_id => l_assignment_request_id
2554 , x_status => x_status
2555 , x_exception_msg => x_exception_msg
2556 );
2557
2558 end if;
2559 close l_is_all_current;
2560
2561 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2562 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2563 ' End supplier_reset_assignment ');
2564 END IF;
2565
2566 EXCEPTION
2567
2568 WHEN OTHERS THEN
2569 X_STATUS :='E';
2570 x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
2571 raise_application_error(-20024, x_exception_msg, true);
2572 END supplier_reset_assignment;
2573
2574
2575 PROCEDURE sbd_handle_address_apv(
2576 p_address_request_id IN NUMBER
2577 , p_party_site_id IN NUMBER
2578 , x_status out nocopy VARCHAR2
2579 , x_exception_msg out nocopy VARCHAR2
2580 )
2581 IS
2582 l_step number;
2583
2584 BEGIN
2585
2586 l_step := 0;
2587 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2588 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2589 ' Start supplier_reset_assignment ');
2590 END IF;
2591
2592 if p_address_request_id is null OR p_party_site_id is null then
2593 raise_application_error(-20032, 'Null values passed to sbd_handle_address_apv', true);
2594 end if;
2595
2596 x_status := 'S';
2597 l_step := 1;
2598
2599 update pos_acnt_addr_req
2600 set party_site_id = p_party_site_id, address_request_id = null
2601 where address_request_id = p_address_request_id;
2602
2603 l_step := 2;
2604
2605 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2606 FND_LOG.string(fnd_log.level_statement, g_log_module_name,
2607 ' End supplier_reset_assignment ');
2608 END IF;
2609
2610 EXCEPTION
2611
2612 WHEN OTHERS THEN
2613 X_STATUS :='E';
2614 x_exception_msg := 'Failure at step ' || l_step || Sqlerrm;
2615 raise_application_error(-20031, x_exception_msg, true);
2618 END POS_SBD_PKG;
2616 END sbd_handle_address_apv;
2617