[Home] [Help]
PACKAGE BODY: APPS.FV_CCR_UTIL_PVT
Source
1 PACKAGE BODY FV_CCR_UTIL_PVT as
2 /* $Header: FVCCRCRB.pls 120.20.12010000.2 2008/08/04 11:10:16 gnrajago ship $*/
3 G_PKG_NAME CONSTANT VARCHAR2(30):='FV_CCR_UTIL_PVT';
4
5 function existing_org_context
6 RETURN VARCHAR2
7 IS
8 l_ret_value VARCHAR2(10);
9 l_module_name VARCHAR2(60);
10 BEGIN
11
12 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.existing_org_context';
13 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
14
15 -- Retrieve CLIENT_INFO org_id value in the performant method recommended by ATG
16 SELECT DECODE(
17 SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
18 NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10)
19 )
20 INTO l_ret_value
21 FROM dual;
22
23 return l_ret_value;
24 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'END');
25 EXCEPTION
26 WHEN OTHERS THEN
27 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,sqlerrm);
28 END existing_org_context;
29
30 PROCEDURE insert_for_report
31 (
32 p_duns IN VARCHAR2,
33 p_record_type IN NUMBER,
34 p_reference1 IN VARCHAR2,
35 p_reference2 IN VARCHAR2,
36 p_reference3 IN VARCHAR2,
37 p_reference4 IN VARCHAR2,
38 p_reference5 IN VARCHAR2
39 )
40 IS
41 l_module_name VARCHAR2(60);
42 BEGIN
43 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.insert_for_report';
44 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
45
46 INSERT INTO fv_ccr_process_report
47 (duns_info,record_type,reference1,reference2,reference3,reference4,reference5)
48 VALUES
49 (p_duns,p_record_type,p_reference1,p_reference2,p_reference3,p_reference4,
50 p_reference5);
51
52 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'END');
53 EXCEPTION
54 WHEN OTHERS THEN
55 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,sqlerrm);
56 END;
57
58
59 /* Procedure to add supplier party id as owner party id to the bank account created */
60 procedure add_owner_party
61 (
62 p_account_owner_party_id IN NUMBER,
63 p_bank_account_id IN NUMBER
64 )
65 IS
66 l_api_version CONSTANT NUMBER:= 1.0;
67 l_return_status VARCHAR2(1);
68 l_msg_count NUMBER;
69 l_msg_data VARCHAR2(2000);
70 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
71 l_joint_acct_owner_id NUMBER;
72 BEGIN
73 IBY_EXT_BANKACCT_PUB.add_joint_account_owner(p_api_version => 1.0,
74 p_init_msg_list => FND_API.G_TRUE,
75 p_bank_account_id => p_bank_account_id,
76 p_acct_owner_party_id => p_account_owner_party_id,
77 x_joint_acct_owner_id=>l_joint_acct_owner_id,
78 x_return_status=>l_return_status,
79 x_msg_count => l_msg_count,
80 x_msg_data => l_msg_data,
81 x_response => l_response);
82 IF (l_joint_acct_owner_id IS NOT NULL) THEN
83 IBY_EXT_BANKACCT_PUB.change_primary_acct_owner (p_api_version => 1.0,
84 p_init_msg_list => FND_API.G_TRUE,
85 p_bank_acct_id => p_bank_account_id,
86 p_acct_owner_party_id => p_account_owner_party_id,
87 x_return_status=>l_return_status,
88 x_msg_count => l_msg_count,
89 x_msg_data => l_msg_data,
90 x_response => l_response);
91 END IF;
92
93 END;
94
95 /* Procedure to get the payee id for the supplier site */
96 procedure get_payee_id
97 (
98 p_payee_context IN IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_rec_type,
99 x_payee_id OUT NOCOPY iby_external_payees_all.ext_payee_id%TYPE
100 )
101 IS
102 CURSOR c_payee
103 (ci_party_id IN p_payee_context.Party_Id%TYPE,
104 ci_party_site_id IN p_payee_context.Party_Site_id%TYPE,
105 ci_supplier_site_id IN p_payee_context.Supplier_Site_id%TYPE,
106 ci_org_type IN p_payee_context.Org_Type%TYPE,
107 ci_org_id IN p_payee_context.Org_Id%TYPE,
108 ci_pmt_function IN p_payee_context.Payment_Function%TYPE)
109 IS
110 SELECT ext_payee_id
111 FROM iby_external_payees_all payee
112 WHERE payee.PAYEE_PARTY_ID = ci_party_id
113 AND payee.PAYMENT_FUNCTION = ci_pmt_function
114 AND ((ci_party_site_id is NULL and payee.PARTY_SITE_ID is NULL) OR
115 (payee.PARTY_SITE_ID = ci_party_site_id))
116 AND ((ci_supplier_site_id is NULL and payee.SUPPLIER_SITE_ID is NULL) OR
117 (payee.SUPPLIER_SITE_ID = ci_supplier_site_id))
118 AND ((ci_org_id is NULL and payee.ORG_ID is NULL) OR
119 (payee.ORG_ID = ci_org_id AND payee.ORG_TYPE = ci_org_type));
120 BEGIN
121 IF (c_payee%ISOPEN) THEN
122 CLOSE c_payee;
123 END IF;
124
125 OPEN c_payee(p_payee_context.Party_Id,
126 p_payee_context.Party_Site_id,
127 p_payee_context.Supplier_Site_id,
128 p_payee_context.Org_Type,
129 p_payee_context.Org_Id,
130 p_payee_context.Payment_Function );
131 FETCH c_payee INTO x_payee_id;
132 IF c_payee%NOTFOUND THEN x_payee_id := NULL; END IF;
133 CLOSE c_payee;
134 END;
135
136
137
138 PROCEDURE get_vendor_name
139 (
140 p_vendor_id IN NUMBER,
141 x_vendor_name OUT NOCOPY VARCHAR2,
142 x_num_1099 OUT NOCOPY VARCHAR2,
143 x_org_type_lkup OUT NOCOPY VARCHAR2
144 )
145 IS
146 l_vendor_name VARCHAR2(120);
147 l_module_name VARCHAR2(60);
148 BEGIN
149 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.get_vendor_name';
150 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
151
152 --SELECT vendor_name,num_1099 INTO x_vendor_name,x_num_1099
153 --FROM po_vendors WHERE vendor_id = p_vendor_id;
154
155 SELECT hzp.party_name, NVL(pav.num_1099, pav.individual_1099),
156 pav.organization_type_lookup_code
157 INTO x_vendor_name,x_num_1099, x_org_type_lkup
158 FROM hz_parties hzp, ap_suppliers pav
159 WHERE hzp.party_id = pav.party_id
160 AND pav.vendor_id = p_vendor_id;
161
162 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'END');
163 EXCEPTION
164 WHEN OTHERS THEN
165 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,sqlerrm);
166 x_vendor_name := null;
167 x_num_1099 := null;
168 END ;
169
170 PROCEDURE update_vendor_org_type
171 (
172 p_vendor_id IN NUMBER,
173 p_vend_org_type IN VARCHAR2,
174 x_status OUT NOCOPY VARCHAR2,
175 x_exception_msg OUT NOCOPY VARCHAR2
176 )
177 IS
178
179 BEGIN
180 fnd_file.put_line(fnd_file.log, 'p_vendor_id: '||p_vendor_id);
181 fnd_file.put_line(fnd_file.log, 'p_vend_org_type: '||p_vend_org_type);
182
183 x_status := 'E';
184
185 UPDATE ap_suppliers
186 SET organization_type_lookup_code = p_vend_org_type
187 WHERE vendor_id = p_vendor_id;
188
189 IF(SQL%NOTFOUND) THEN
190 x_exception_msg := 'No supplier found with vendor id: '||p_vendor_id;
191 x_status := 'E';
192 ELSE
193 x_status := 'S';
194 END IF;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 x_exception_msg := 'Exception when updating vendor organization type';
199 x_status := 'U';
200
201 END update_vendor_org_type;
202
203 PROCEDURE duplicate_vendor_site_code
204 (
205 p_vendor_id IN NUMBER,
206 p_site_code IN VARCHAR2,
207 p_org_id IN NUMBER,
208 x_dup_site_code OUT NOCOPY VARCHAR2,
209 x_vendor_site_id OUT NOCOPY NUMBER,
210 x_duns OUT NOCOPY VARCHAR2
211 )
212 IS
213 l_module_name VARCHAR2(60);
214
215
216 CURSOR site_code_dup_csr(p_supp_id NUMBER,p_site_name VARCHAR2,p_org NUMBER) IS
217 --SELECT vendor_site_id,duns_number FROM
218 --po_vendor_sites_all
219 --WHERE vendor_id=p_supp_id
220 --AND vendor_site_code=p_site_name
221 --AND org_id=p_org;
222 SELECT pavs.vendor_site_id, hps.duns_number_c
223 FROM ap_supplier_sites_all pavs, hz_party_sites hps
224 WHERE pavs.vendor_id = p_supp_id
225 AND pavs.vendor_site_code = p_site_name
226 AND pavs.org_id = p_org
227 AND pavs.party_site_id = hps.party_site_id;
228
229 BEGIN
230 x_dup_site_code:='F';
231 l_module_name:='fv.plsql.FV_CCR_UTIL_PVT.duplicate_vendor_site_code';
232 OPEN site_code_dup_csr(p_vendor_id,p_site_code,p_org_id);
233 FETCH site_code_dup_csr INTO x_vendor_site_id,x_duns;
234 IF(site_code_dup_csr%FOUND) THEN
235 x_dup_site_code:='T';
236 END IF;
237
238 EXCEPTION
239 WHEN OTHERS THEN
240 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,sqlerrm);
241
242 END;
243
244
245
246 PROCEDURE get_vendor_site_code
247 (
248 p_vendor_site_id IN NUMBER,
249 x_site_code OUT NOCOPY VARCHAR2
250 )
251 IS
252 l_module_name VARCHAR2(60);
253 BEGIN
254 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.get_vendor_site_code';
255 SELECT vendor_site_code INTO x_site_code
256 --FROM po_vendor_sites_all
257 FROM ap_supplier_sites_all
258 WHERE vendor_site_id = p_vendor_site_id;
259 EXCEPTION
260 WHEN OTHERS THEN
261 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,sqlerrm);
262 END;
263
264 PROCEDURE get_vendor_id
265 (
266 p_taxpayer_number IN VARCHAR2,
267 p_legal_bus_name IN VARCHAR2,
268 p_duns IN VARCHAR2,
269 p_fed_check_flag IN VARCHAR2,
270 x_vendor_id OUT NOCOPY NUMBER,
271 x_vendor_name OUT NOCOPY VARCHAR2,
272 x_supp_num_exist OUT NOCOPY VARCHAR2
273 )
274 IS
275 l_supp_num varchar2(30);
276 l_module_name VARCHAR2(60);
277 CURSOR vendor_taxp_csr(p_taxpayer varchar2) IS
278 --SELECT vendor_id,vendor_name FROM
279 --po_vendors WHERE num_1099 = p_taxpayer;
280 SELECT pav.vendor_id, hzp.party_name
281 FROM hz_parties hzp, ap_suppliers pav
282 WHERE (pav.num_1099 = p_taxpayer OR
283 pav.individual_1099 = p_taxpayer)
284 AND hzp.party_id = pav.party_id;
285
286 CURSOR vendor_lbn_csr(p_legal_bus varchar2) IS
287 --SELECT vendor_id,vendor_name FROM
288 --po_vendors WHERE vendor_name = p_legal_bus;
289 SELECT pav.vendor_id, hzp.party_name
290 FROM hz_parties hzp, ap_suppliers pav
291 WHERE hzp.party_name = p_legal_bus
292 AND hzp.party_id = pav.party_id;
293
294 CURSOR vendor_supp_num_csr(p_supp_num varchar2) IS
295 SELECT segment1
296 --FROM po_vendors
297 FROM ap_suppliers
298 where segment1 = p_supp_num;
299
300
301 BEGIN
302
303 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.get_vendor_id';
304 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
305
306 fv_utility.log_mesg('p_taxpayer_number: '||p_taxpayer_number);
307 fv_utility.log_mesg('p_legal_bus_name: '||p_legal_bus_name);
308 fv_utility.log_mesg('p_duns: '||p_duns);
309 fv_utility.log_mesg('p_fed_check_flag: '||p_fed_check_flag);
310
311 IF(p_taxpayer_number IS NOT NULL AND length(p_taxpayer_number) = 9) THEN
312 OPEN vendor_taxp_csr(p_taxpayer_number);
313 fv_utility.log_mesg('Here1');
314 FETCH vendor_taxp_csr INTO x_vendor_id,x_vendor_name;
315 fv_utility.log_mesg('Here2');
316 IF(vendor_taxp_csr%NOTFOUND) THEN
317 x_vendor_id := null;
318 x_vendor_name := null;
319 fv_utility.log_mesg('Here3');
320 IF (p_fed_check_flag='N') THEN
321 OPEN vendor_supp_num_csr(p_taxpayer_number);
322 fv_utility.log_mesg('Here4');
323 FETCH vendor_supp_num_csr INTO l_supp_num;
324 fv_utility.log_mesg('Here5');
325 IF(vendor_supp_num_csr%NOTFOUND) THEN
326 x_supp_num_exist := 'N';
327 ELSE x_supp_num_exist := 'Y';
328 END IF;
329 CLOSE vendor_supp_num_csr;
330 END IF;
331 END IF;
332 CLOSE vendor_taxp_csr;
333 ELSE
334 OPEN vendor_lbn_csr(p_legal_bus_name);
335 fv_utility.log_mesg('Here6');
336 FETCH vendor_lbn_csr INTO x_vendor_id,x_vendor_name;
337 fv_utility.log_mesg('Here7');
338 IF(vendor_lbn_csr%NOTFOUND) THEN
339 x_vendor_id := null;
340 x_vendor_name := null;
341 IF(p_fed_check_flag='N') THEN
342 OPEN vendor_supp_num_csr(p_duns);
343 fv_utility.log_mesg('Here8');
344 FETCH vendor_supp_num_csr INTO l_supp_num;
345 fv_utility.log_mesg('Here9');
346 IF(vendor_supp_num_csr%NOTFOUND) THEN
347 x_supp_num_exist := 'N';
348 ELSE x_supp_num_exist := 'Y';
349 END IF;
350 CLOSE vendor_supp_num_csr;
351 END IF;
352 END IF;
353 CLOSE vendor_lbn_csr;
354 fv_utility.log_mesg('Here10');
355 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'END');
356 END IF;
357 EXCEPTION
358 WHEN OTHERS THEN
359 fv_utility.log_mesg(' Exception in get_vendor_id.');
360 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,sqlerrm);
361 x_vendor_id := null;
362 x_vendor_name := null;
363 END;
364
365 /*commented for R12 upgrade
366 PROCEDURE get_bank_account_id
367 (
368 p_bank_name IN VARCHAR2,
369 p_bank_branch_name IN VARCHAR2,
370 p_routing_num IN VARCHAR2,
371 p_bank_account_number IN VARCHAR2,
372 p_org_id IN NUMBER,
373 p_base_currency IN VARCHAR2,
374 x_bank_account_id OUT NOCOPY NUMBER,
375 x_bank_branch_id IN OUT NOCOPY NUMBER
376
377 )
378 IS
379 l_bank_branch_id NUMBER;
380 l_module_name VARCHAR2(60);
381 BEGIN
382
383 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.get_bank_account_id';
384 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
385
386 SELECT bank_account_id,aba.bank_branch_id
387 INTO x_bank_account_id,l_bank_branch_id
388 FROM ap_bank_branches abb,ap_bank_accounts_all aba
389 WHERE abb.bank_name = p_bank_name
390 AND abb.bank_branch_name =p_bank_branch_name
391 AND abb.bank_num = p_routing_num
392 AND aba.bank_account_num = p_bank_account_number
393 AND aba.org_id = p_org_id
394 AND aba.account_type = 'SUPPLIER'
395 AND nvl(aba.currency_code,'~^') = nvl(p_base_currency,'~^')
396 AND aba.bank_branch_id = abb.bank_branch_id;
397 IF(l_bank_branch_id IS NULL) THEN
398 l_bank_branch_id := x_bank_branch_id;
399 END IF;
400 x_bank_branch_id := l_bank_branch_id;
401
402 IF(x_bank_branch_id =0) THEN
403 x_bank_branch_id := null;
404 END IF;
405
406 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'END');
407 EXCEPTION
408 WHEN OTHERS THEN
409 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,sqlerrm);
410 x_bank_account_id := null;
411 END;
412 */
413
414 PROCEDURE get_bank_branch_information
415 (
416 p_routing_num IN VARCHAR2,
417 p_bank_branch_id IN NUMBER,
418 x_bank_name OUT NOCOPY VARCHAR2,
419 x_bank_branch_name OUT NOCOPY VARCHAR2,
420 x_bank_branch_id OUT NOCOPY NUMBER
421 )
422 IS
423 l_routing_num VARCHAR2(25);
424 l_bank_branch_name VARCHAR2(60);
425 l_bank_name VARCHAR2(60);
426 l_module_name VARCHAR2(60);
427 CURSOR bank_branch_csr(p_routing VARCHAR2) IS
428 SELECT branch_party_id, bank_name, bank_branch_name
429 FROM ce_bank_branches_v
430 WHERE branch_number = p_routing
431 AND branch_number IS NOT NULL;
432 BEGIN
433
434 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.get_bank_branch_information';
435 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
436
437 IF(nvl(p_bank_branch_id,0)<>0) THEN
438 BEGIN
439 SELECT bank_name,bank_branch_name,branch_number
440 INTO l_bank_name,l_bank_branch_name,
441 l_routing_num FROM ce_bank_branches_v
442 WHERE branch_party_id = p_bank_branch_id;
443 EXCEPTION
444 WHEN OTHERS THEN
445 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
446 'Bank Branch exception for'|| p_bank_branch_id);
447 l_bank_name := null;
448 l_bank_branch_name := null;
449 l_routing_num := null;
450 END;
451 END IF;
452 If(l_routing_num IS NOT NULL AND p_routing_num IS NOT NULL
453 AND l_routing_num = p_routing_num) THEN
454 x_bank_branch_id := p_bank_branch_id;
455 x_bank_name := l_bank_name;
456 x_bank_branch_name := l_bank_branch_name;
457 ELSIF(p_routing_num IS NOT NULL) THEN
458 BEGIN
459 OPEN bank_branch_csr(p_routing_num);
460 fetch bank_branch_csr INTO x_bank_branch_id,
461 x_bank_name,x_bank_branch_name;
462 IF(bank_branch_csr%NOTFOUND) THEN
463 x_bank_branch_id := null;
464 x_bank_name := null;
465 x_bank_branch_name := null;
466 END IF;
467 CLOSE bank_branch_csr;
468 EXCEPTION
469 WHEN OTHERS THEN
470 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,sqlerrm);
471 x_bank_branch_id := null;
472 x_bank_name := null;
473 x_bank_branch_name := null;
474 CLOSE bank_branch_csr;
475 END;
476 ELSE
477 x_bank_branch_id := null;
478 x_bank_name := null;
479 x_bank_branch_name := null;
480 END IF;
481 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'END');
482 END;
483
484 PROCEDURE get_bank_account_information
485 (
486 p_bank_branch_id IN NUMBER,
487 p_bank_account_number IN VARCHAR2,
488 p_bank_account_id IN NUMBER,
489 p_account_type IN VARCHAR2,
490 p_base_currency IN VARCHAR2,
491 p_country_code IN VARCHAR2,
492 x_bank_account_id OUT NOCOPY NUMBER,
493 x_update_account OUT NOCOPY VARCHAR2
494 )
495 IS
496 CURSOR bank_acct_csr(p_branch_id NUMBER,p_acct_number VARCHAR2,
497 p_currency VARCHAR2) IS
498 SELECT ext_bank_account_id,bank_account_type
499 FROM iby_ext_bank_accounts
500 WHERE branch_id = p_branch_id
501 AND bank_account_num = p_acct_number
502 AND currency_code = p_currency
503 AND country_code = p_country_code;
504
505 l_bank_branch_id NUMBER;
506 l_bank_acct_num VARCHAR2(30);
507 l_bank_account_type VARCHAR2(25);
508 l_module_name VARCHAR2(60);
509 BEGIN
510 x_update_account:='N';
511 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.get_bank_account_information';
512 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
513
514 BEGIN
515
516 IF(nvl(p_bank_account_id,0)<>0) THEN
517 SELECT bank_account_num,branch_id,bank_account_type
518 INTO l_bank_acct_num,l_bank_branch_id,l_bank_account_type
519 FROM iby_ext_bank_accounts
520 WHERE ext_bank_account_id=p_bank_account_id;
521 ELSE
522 l_bank_branch_id := null;
523 l_bank_acct_num:= null;
524 END IF;
525 EXCEPTION
526 WHEN OTHERS THEN
527 l_bank_branch_id := null;
528 l_bank_acct_num:= null;
529 END;
530
531 IF(l_bank_branch_id IS NOT NULL AND l_bank_acct_num IS NOT NULL
532 AND l_bank_branch_id = p_bank_branch_id AND l_bank_acct_num=p_bank_account_number) THEN
533 x_bank_account_id:=p_bank_account_id;
534 IF(nvl(l_bank_Account_type,'') <> nvl(p_account_type,'')) THEN
535 x_update_account:='Y';
536 END IF;
537 ELSIF(nvl(p_bank_branch_id,0)<>0) THEN
538 OPEN bank_acct_csr(p_bank_branch_id,p_bank_account_number,p_base_currency);
539 FETCH bank_acct_csr INTO x_bank_account_id,l_bank_account_type;
540 IF(bank_acct_csr%NOTFOUND) THEN
541 x_bank_account_id := null;
542 ELSIF(nvl(l_bank_Account_type,'') <> nvl(p_account_type,'')) THEN
543 x_update_account:='Y';
544 END IF;
545 CLOSE bank_acct_csr;
546 ELSE
547 x_bank_account_id := null;
548 END IF;
549
550 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
551 EXCEPTION
552 WHEN OTHERS THEN
553 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,sqlerrm);
554 x_bank_account_id := null;
555 END;
556
557
558 procedure get_federal_indicator
559 (
560 p_vendor_id IN NUMBER,
561 p_taxpayer_number IN VARCHAR2,
562 p_legal_bus_name IN VARCHAR2,
563 x_federal OUT NOCOPY VARCHAR2
564 )
565 IS
566 l_api_version CONSTANT NUMBER := 1.0;
567 l_init_msg_list VARCHAR2(1);
568 l_return_status VARCHAR2(1);
569 l_msg_count NUMBER;
570 l_msg_data VARCHAR2(200);
571 l_error_code NUMBER;
572 l_module_name VARCHAR2(60);
573 l_msg_text VARCHAR2(500);
574 l_duns VARCHAR2(9);
575 l_vendor_id NUMBER;
576 l_vendor_name VARCHAR2(120);
577 l_supp_num_exist VARCHAR2(1);
578 BEGIN
579 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.get_federal_indicator';
580 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
581
582 l_init_msg_list := fnd_api.g_true;
583
584
585 l_duns:='';
586 IF(nvl(p_vendor_id,0)=0) THEN
587 get_vendor_id(p_taxpayer_number,p_legal_bus_name,l_duns,'Y',
588 l_vendor_id,l_vendor_name,l_supp_num_exist);
589 ELSE
590 l_vendor_id:=p_vendor_id;
591 END IF;
592
593
594
595 IF(nvl(l_vendor_id,0) <> 0) THEN
596 FV_CCR_GRP.is_vendor_federal
597 (
598 p_api_version => l_api_version,
599 p_init_msg_list => l_init_msg_list,
600 p_vendor_id => l_vendor_id,
601 x_return_status => l_return_status,
602 x_msg_count => l_msg_count,
603 x_msg_data => l_msg_data,
604 x_federal => x_federal,
605 x_error_code => l_error_code
606 );
607 if(nvl(l_return_status,'E')<>FND_API.G_RET_STS_SUCCESS) THEN
608 if(l_msg_count=1) THEN
609 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,l_module_name,
610 'Get Federal Indicator:' || l_msg_data);
611 else
612 for I IN 0 .. l_msg_count
613 loop
614 l_msg_data := fnd_msg_pub.get(p_encoded=>'F');
615 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,l_module_name,
616 'Get Federal Indicator:' || l_msg_data);
617 end loop;
618 end if;
619 x_federal:= 'N';
620
621 end if;
622 ELSE
623 x_federal := 'N';
624 END IF;
625 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'END');
626 EXCEPTION
627 WHEN OTHERS THEN
628 x_federal:= 'N';
629 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION,l_module_name,'Get Federal Indicator:'
630 || ' Exception');
631 END;
632
633 /* Procedure used to create or update vendor
634 create or update vendor site and update bank_acct_uses table*/
635
636 PROCEDURE fv_process_vendor
637 (
638 p_ccr_id IN NUMBER ,
639 p_prev_ccr_id IN VARCHAR2 ,
640 p_update_type IN VARCHAR2 ,
641 x_return_status OUT NOCOPY VARCHAR2 ,
642 x_msg_count OUT NOCOPY NUMBER ,
643 x_msg_data OUT NOCOPY VARCHAR2 ,
644 p_bank_branch_id IN NUMBER ,
645 p_vendor_id IN NUMBER,
646 p_pay_site_id IN NUMBER,
647 p_main_add_site_id IN NUMBER,
648 p_enabled_flag IN VARCHAR2,
649 p_main_address_flag IN VARCHAR2,
650 p_taxpayer_number IN VARCHAR2,
651 p_legal_bus_name IN VARCHAR2,
652 p_duns IN VARCHAR2,
653 p_plus4 IN VARCHAR2,
654 p_main_address_line1 IN VARCHAR2,
655 p_main_address_line2 IN VARCHAR2,
656 p_main_address_city IN VARCHAR2,
657 p_main_address_state IN VARCHAR2,
658 p_main_address_zip IN VARCHAR2,
659 p_main_address_country IN VARCHAR2,
660 p_pay_address_line1 IN VARCHAR2,
661 p_pay_address_line2 IN VARCHAR2,
662 p_pay_address_line3 IN VARCHAR2,
663 p_pay_address_city IN VARCHAR2,
664 p_pay_address_state IN VARCHAR2,
665 p_pay_address_zip IN VARCHAR2,
666 p_pay_address_country IN VARCHAR2,
667 p_old_bank_account_id IN NUMBER,
668 p_new_bank_account_id IN NUMBER,
669 p_bank_name IN VARCHAR2,
670 p_bank_branch_name IN VARCHAR2,
671 p_bank_num IN VARCHAR2,
672 p_bank_account_num IN VARCHAR2,
673 p_org_id IN NUMBER,
674 p_update_vendor_flag IN VARCHAR2,
675 p_org_name IN varchar2,
676 p_ccr_status IN varchar2,
677 p_insert_vendor_flag IN VARCHAR2,
678 p_prev_vendor_id IN NUMBER,
679 p_file_date IN DATE,
680 p_bank_conc_req_status IN VARCHAR2,
681 p_header_conc_req_status IN VARCHAR2,
682 p_assgn_conc_req_status IN VARCHAR2,
683 p_base_currency IN VARCHAR2,
684 p_valid_bank_info IN VARCHAR2,
685 p_federal_vendor IN VARCHAR2,
686 p_created_bank_branch_id IN NUMBER,
687 p_created_bank_account_id IN NUMBER,
688 x_vendor_id OUT NOCOPY NUMBER,
689 x_output OUT NOCOPY VARCHAR2,
690 x_react_pay_site_code OUT NOCOPY VARCHAR2,
691 x_react_main_site_code OUT NOCOPY VARCHAR2,
692 x_tp_changed OUT NOCOPY VARCHAR2,
693 x_vendor_name OUT NOCOPY VARCHAR2,
694 p_org_type_lookup IN VARCHAR2,
695 p_remit_poc IN VARCHAR2,
696 p_mail_poc IN VARCHAR2,
697 p_ar_us_phone IN VARCHAR2,
698 p_ar_fax IN VARCHAR2,
699 p_ar_email IN VARCHAR2,
700 p_ar_non_us_phone IN VARCHAR2
701 )
702 IS
703
704
705 l_api_name CONSTANT VARCHAR2(30) := 'FV_PROCESS_VENDORS';
706 l_api_version NUMBER := 1.0;
707 l_old_org_id VARCHAR2(10);
708 l_vendor_name varchar2(120);
709 l_vendor_id NUMBER;
710 l_supplier_number varchar2(30);
711 l_status varchar2(1);
712 l_msg varchar2(300);
713 l_update_bank_flag varchar2(1);
714 l_account_uses_insert_flag varchar2(1);
715 l_account_uses_upd_flag varchar2(1);
716 l_user_id NUMBER;
717 l_new_bank_account_id NUMBER;
718 l_bank_branch_id NUMBER;
719 l_plus4 varchar2(20);
720 l_pay_site_flag varchar2(1);
721 l_pay_site_id NUMBER;
722 l_main_add_site_id NUMBER;
723 l_row_id VARCHAR2(30) := null;
724 l_uses_id NUMBER :=null;
725 l_login_id NUMBER := null;
726 l_supp_num_exist VARCHAR2(1);
727 l_num_1099 VARCHAR2(30);
728 l_msg_text VARCHAR2(500);
729 l_duns_for_report VARCHAR2(13);
730 l_state VARCHAR2(150);
731 l_province VARCHAR2(150);
732 l_hold_unmatched_invoices_flag VARCHAR2(1);
733 l_hold_all_payments_flag VARCHAR2(1);
734 l_module_name VARCHAR2(60);
735 l_site_code VARCHAR2(15);
736 l_excp_msg VARCHAR2(1000);
737 l_dup_site_code VARCHAR2(1);
738 l_duns_site_code VARCHAR2(9);
739 l_site_id_site_code NUMBER;
740 l_uses_reln_exists VARCHAR2(1);
741 l_end_date DATE;
742 l_header_conc_req_status VARCHAR2(1);
743 l_assgn_conc_req_status VARCHAR2(1);
744 e_supp_exception EXCEPTION;
745
746 l_party_site_id NUMBER;
747
748 l_org_type_lookup po_lookup_codes.lookup_code%TYPE;
749
750 l_legal_bus_name ap_suppliers.vendor_name%TYPE;
751 l_count NUMBER;
752
753 BEGIN
754
755 l_legal_bus_name := p_legal_bus_name;
756 -- Bug 6238816. If a vendor with the same name exists,
757 -- then append the duns number to the vendor name.
758 BEGIN
759 l_count := 0;
760
761 SELECT 1
762 INTO l_count
763 FROM ap_suppliers
764 WHERE vendor_name = l_legal_bus_name
765 AND num_1099 <> p_taxpayer_number;
766
767 fv_utility.log_mesg('Another vendor exists with the same
768 name as : '||l_legal_bus_name);
769 fv_utility.log_mesg('Appending duns number to '||l_legal_bus_name||
770 ' to keep the name unique.');
771
772 l_legal_bus_name := SUBSTR(l_legal_bus_name,1,231)||p_duns;
773
774 EXCEPTION
775 WHEN NO_DATA_FOUND THEN
776 NULL;
777 WHEN OTHERS THEN
778 l_msg_text := l_module_name||': When others error: Error Code:'||SQLCODE||' Error Text:'||SQLERRM;
779 FV_UTILITY.LOG_MESG(l_msg_text);
780 x_return_status := FND_API.G_RET_STS_ERROR ;
781 insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
782 RETURN;
783 END;
784
785
786
787 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.fv_process_vendor';
788 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'BEGIN');
789 l_dup_site_code:='F';
790 l_user_id := fnd_global.user_id;
791 l_login_id := fnd_global.login_id;
792 l_account_uses_insert_flag := 'N'; -- Do not insert bank acct uses
793 l_account_uses_upd_flag := 'N'; -- Do not update bank acct uses
794 l_bank_branch_id := p_bank_branch_id;
795
796 IF(nvl(p_bank_conc_req_status,'C')='E') THEN
797 l_assgn_conc_req_status:='E';
798 ELSE
799 l_assgn_conc_req_status:='C';
800 END IF;
801 l_header_conc_req_status := 'C';
802
803 l_duns_for_report := p_duns || nvl(p_plus4,'');
804
805 IF(nvl(p_enabled_flag,'Y') = 'N') THEN
806 l_hold_unmatched_invoices_flag:='N';
807 l_hold_all_payments_flag:='N';
808 ELSE
809 l_hold_unmatched_invoices_flag:=null;
810 l_hold_all_payments_flag:=null;
811 END IF;
812
813 FND_MSG_PUB.initialize;
814
815
816 -- Initialize API return status to success
817 x_return_status := FND_API.G_RET_STS_SUCCESS;
818
819
820 -- Get the site id if they exist
821
822
823 l_pay_site_id := p_pay_site_id;
824 l_main_add_site_id := p_main_add_site_id;
825
826
827 -- Process if the record is new or updated
828
829 IF (p_ccr_status='A') THEN
830
831 -- If bank account id new is null then new account is created and row in acct uses
832 -- should be created also get account id
833
834 if(p_valid_bank_info='Y' AND
835 (p_new_bank_account_id IS NULL OR p_new_bank_account_id = 0)) THEN
836
837 /* If the bank account id does not in ccr extension tables and got created in Java CP
838 then use the created bank account and bank branch id */
839 l_new_bank_account_id := p_created_bank_account_id;
840 l_bank_branch_id := p_created_bank_branch_id;
841
842 IF(l_new_bank_account_id IS NULL) THEN
843 l_new_bank_account_id :=0;
844 l_assgn_conc_req_status:='E';
845 ELSE
846 l_account_uses_insert_flag := 'I';
847 insert_for_report(l_duns_for_report,15,null,'CCR SUPPLIER BANK '
848 || l_duns_for_report,p_bank_branch_name,null,null);
849 END IF;
850 ELSIF(p_valid_bank_info='Y') THEN
851 l_new_bank_account_id := p_new_bank_account_id;
852 l_account_uses_insert_flag := 'U'; -- Update bank acct uses if relation already exist
853 ELSE
854 l_new_bank_account_id:=0;
855 l_bank_branch_id:=null;
856 END IF;
857
858 -- Update bank branch id in extension tables only once per ccr_id.
859
860 IF(p_ccr_id <> p_prev_ccr_id) THEN
861 IF(nvl(l_bank_branch_id,0) = 0) THEN
862 l_bank_branch_id := null;
863 IF(p_federal_vendor='N') THEN
864 l_header_conc_req_status := 'E';
865 END IF;
866 END IF;
867 IF(p_bank_branch_id =0 AND l_bank_branch_id IS NOT NULL) THEN
868 insert_for_report(l_duns_for_report,14,null,p_bank_branch_name,
869 p_bank_num,null,null);
870 END IF;
871 UPDATE fv_ccr_vendors
872 SET bank_branch_id = l_bank_branch_id
873 ,enabled ='Y',last_update_date=sysdate,
874 last_updated_by=l_user_id,last_update_login=l_login_id
875 WHERE ccr_id = p_ccr_id;
876 END IF;
877
878
879 -- If old bank acct id is same as new bank acct id no need to
880 -- update bank acct uses table
881 if(nvl(p_old_bank_account_id,0)<>0 AND
882 p_old_bank_account_id <> l_new_bank_account_id)THEN
883 l_account_uses_upd_flag := 'Y';
884 END IF;
885
886 -- Do not update vendor for duns4 record for which
887 -- p_update_vendor_flag='N'
888
889 IF (p_update_vendor_flag = 'Y') THEN
890
891 -- Get the vendor id if it already exists
892
893 IF(p_vendor_id <> 0) THEN
894
895 /* Changed the reference to US from USA */
896 IF(p_main_address_country = 'US' AND
897 (p_taxpayer_number IS NULL or length(p_taxpayer_number)<>9)) THEN
898 get_vendor_name(p_vendor_id,l_vendor_name,l_num_1099,l_org_type_lookup);
899 FND_MESSAGE.SET_NAME('FV','FV_CCR_TAXPAYER_CHANGE_INVALID');
900 FND_MESSAGE.SET_TOKEN('SUPPLIER',l_vendor_name);
901 l_msg_text := FND_MESSAGE.GET;
902 insert_for_report(p_duns,18,l_msg_text,null,null,null,null);
903 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,l_msg_text);
904 l_vendor_id := p_vendor_id;
905 ELSE
906 get_vendor_name(p_vendor_id,l_vendor_name,l_num_1099,l_org_type_lookup);
907 IF(nvl(l_pay_site_id,0)=0 AND
908 l_num_1099 <> p_taxpayer_number) THEN
909 FND_MESSAGE.SET_NAME('FV','FV_CCR_DUNS_TAXPAYER_ASSIGN');
910 FND_MESSAGE.SET_TOKEN('SUPPLIER',l_vendor_name);
911 l_msg_text := FND_MESSAGE.GET;
912 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
913 l_module_name,l_msg_text);
914 insert_for_report(p_duns,16,l_msg_text,null,
915 null,null,null);
916 ELSIF(l_num_1099 <> p_taxpayer_number) THEN
917 FND_MESSAGE.SET_NAME('FV','FV_CCR_TAXPAYER_NUM_CHANGED');
918 FND_MESSAGE.SET_TOKEN('DUNS',p_duns);
919 l_msg_text := FND_MESSAGE.GET;
920 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
921 l_module_name,l_msg_text);
922 insert_for_report(p_duns,16,l_msg_text,null,
923 null,null,null);
924 x_vendor_name := l_vendor_name;
925 END IF;
926
927 l_vendor_id := p_vendor_id;
928
929
930 IF (p_org_type_lookup IS NOT NULL) AND
931 (NVL(l_org_type_lookup,'-XXX') <> p_org_type_lookup) THEN
932 FND_MESSAGE.SET_NAME('FV','FV_CCR_ORG_LKUP_CHANGED');
933 FND_MESSAGE.SET_TOKEN('OLDORGTYPE',l_org_type_lookup);
934 FND_MESSAGE.SET_TOKEN('NAME', l_vendor_name);
935 FND_MESSAGE.SET_TOKEN('TIN', l_num_1099);
936 FND_MESSAGE.SET_TOKEN('NEWORGTYPE', p_org_type_lookup);
937 FND_MESSAGE.SET_TOKEN('DUNS', p_duns);
938 l_msg_text := FND_MESSAGE.GET;
939
940 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,
941 l_module_name,l_msg_text);
942 insert_for_report(p_duns,16,l_msg_text,null,
943 null,null,null);
944 x_vendor_name := l_vendor_name;
945 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating org type with: '||p_org_type_lookup);
946 update_vendor_org_type(
947 p_vendor_id => l_vendor_id,
948 p_vend_org_type => p_org_type_lookup,
949 x_status => l_status,
950 x_exception_msg => l_msg);
951
952 IF(nvl(l_status,'F')<>'S') THEN
953 RAISE e_supp_exception;
954 END IF;
955
956 END IF;
957
958 BEGIN
959 l_msg := null;
960
961
962 FV_CCR_UTIL_PVT.update_vendor(
963 p_vendor_id=>l_vendor_id,
964 p_taxpayer_id=>p_taxpayer_number,
965 x_status =>l_status,
966 x_exception_msg=>l_msg);
967 --p_calling_source=>'CCRImport');
968
969 IF(nvl(l_status,'F')<>'S') THEN
970 RAISE e_supp_exception;
971 END IF;
972 EXCEPTION
973 WHEN OTHERS THEN
974 l_header_conc_req_status := 'E';
975 IF(l_msg IS NULL) THEN
976 l_excp_msg := FND_MESSAGE.GET;
977 ELSE
978 l_excp_msg := l_msg;
979 END IF;
980 FND_MESSAGE.SET_NAME('FV','FV_CCR_VENDOR_UPDATE');
981 FND_MESSAGE.SET_TOKEN('VENDORNAME',l_vendor_name);
982 l_msg_text := FND_MESSAGE.GET;
983 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,
984 l_module_name,l_msg_text||'Error Code:' ||
985 SQLCODE ||' Error Text:' || SQLERRM);
986 insert_for_report(p_duns,19,l_msg_text||nvl(l_excp_msg,''),null,null
987 ,null,null);
988
989 IF FND_MSG_PUB.Check_Msg_Level
990 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
991 THEN
992 FND_MSG_PUB.Add_Exc_Msg
993 ( G_PKG_NAME ,
994 l_api_name
995 );
996 END IF;
997 END;
998 END IF;
999 BEGIN
1000 UPDATE fv_ccr_vendors
1001 SET vendor_id = p_vendor_id,
1002 last_update_date=sysdate,
1003 last_updated_by =l_user_id,
1004 last_update_login=l_login_id
1005 WHERE DUNS=p_duns;
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 l_header_conc_req_status := 'E';
1009 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,sqlerrm);
1010 END;
1011
1012
1013 ELSE
1014 IF(p_taxpayer_number IS NOT NULL
1015 AND length(p_taxpayer_number)=9 ) THEN
1016 l_supplier_number := p_taxpayer_number;
1017 else
1018 l_supplier_number := p_duns;
1019 END IF;
1020 IF(p_insert_vendor_flag = 'N' AND p_prev_vendor_id <> 0) THEN
1021 l_vendor_id := p_prev_vendor_id;
1022 get_vendor_name(l_vendor_id,l_vendor_name,l_num_1099,l_org_type_lookup);
1023 FND_MESSAGE.SET_NAME('FV','FV_CCR_VENDOR_TAXPAYER_EXIST');
1024 FND_MESSAGE.SET_TOKEN('SUPPLIER',l_vendor_name);
1025 l_msg_text := FND_MESSAGE.GET;
1026 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1027 l_module_name,l_msg_text);
1028 insert_for_report(p_duns,16,l_msg_text,null,null,null,null);
1029 ELSE
1030 --get_vendor_id(p_taxpayer_number,p_legal_bus_name,p_duns,'N',
1031 get_vendor_id(p_taxpayer_number,l_legal_bus_name,p_duns,'N',
1032 l_vendor_id,l_vendor_name,l_supp_num_exist);
1033 fv_utility.log_mesg('l_vendor_id: '||l_vendor_id);
1034 fv_utility.log_mesg('l_vendor_name: '||l_vendor_name);
1035 fv_utility.log_mesg('l_supp_num_exist: '||l_supp_num_exist);
1036
1037 IF(l_vendor_id IS NULL) THEN
1038 l_vendor_id :=0;
1039
1040 IF(l_supp_num_exist IS NOT NULL
1041 AND l_supp_num_exist = 'Y') THEN
1042 FND_MESSAGE.SET_NAME('FV','FV_CCR_VENDOR_NUM_EXIST');
1043 FND_MESSAGE.SET_TOKEN('NUM',l_supplier_number);
1044 l_msg_text:= FND_MESSAGE.GET;
1045 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1046 l_module_name,l_msg_text);
1047 insert_for_report(p_duns,18,l_msg_text,null,
1048 null,null,null);
1049 l_supplier_number := null;
1050
1051 END IF;
1052 ELSE
1053 IF(p_taxpayer_number IS NOT NULL) THEN
1054 FND_MESSAGE.SET_NAME('FV','FV_CCR_VENDOR_TAXPAYER_EXIST');
1055 FND_MESSAGE.SET_TOKEN('SUPPLIER',l_vendor_name);
1056 l_msg_text := FND_MESSAGE.GET;
1057 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT
1058 ,l_module_name,l_msg_text);
1059 insert_for_report(p_duns,16,l_msg_text,null,null
1060 ,null,null);
1061 END IF;
1062 END IF;
1063
1064 END IF;
1065
1066 /*** If vendor id does not exist create the vendor else update the vendor ***/
1067 if(l_vendor_id IS NULL OR l_vendor_id = 0 ) THEN
1068
1069 BEGIN
1070 l_msg := null;
1071 fv_utility.log_mesg('before calling insert_vendor');
1072 FV_CCR_UTIL_PVT.insert_vendor
1073 (
1074 --p_vendor_name => p_legal_bus_name,
1075 p_vendor_name => l_legal_bus_name,
1076 p_taxpayer_id => p_taxpayer_number,
1077 p_supplier_number=>l_supplier_number,
1078 p_org_type_lookup_code => p_org_type_lookup,
1079 x_vendor_id =>l_vendor_id,
1080 x_status=>l_status,
1081 x_exception_msg=>l_msg);
1082
1083 fv_utility.log_mesg('after calling insert_vendor');
1084 fv_utility.log_mesg('l_vendor_id: '||l_vendor_id);
1085 fv_utility.log_mesg('l_status: '||l_status);
1086 fv_utility.log_mesg('l_msg: '||l_msg);
1087
1088
1089 IF(nvl(l_status,'F')<> 'S' OR l_vendor_id IS NULL) THEN
1090 RAISE e_supp_exception;
1091 fv_utility.log_mesg('raised e_supp_exception');
1092 END IF;
1093 --insert_for_report(p_duns,12,null,p_legal_bus_name,
1094 insert_for_report(p_duns,12,null,l_legal_bus_name,
1095 p_taxpayer_number,null,null);
1096 IF(p_vendor_id = 0) THEN
1097 UPDATE fv_ccr_vendors
1098 SET vendor_id =l_vendor_id,
1099 last_update_date=sysdate,
1100 last_updated_by=l_user_id,
1101 last_update_login=l_login_id
1102 WHERE DUNS = p_duns;
1103 END IF;
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 l_header_conc_req_status := 'E';
1107 IF(l_msg IS NULL) THEN
1108 l_excp_msg := FND_MESSAGE.GET;
1109 ELSE
1110 l_excp_msg := l_msg;
1111 END IF;
1112 FND_MESSAGE.SET_NAME('FV','FV_CCR_VENDOR_INSERT');
1113 --FND_MESSAGE.SET_TOKEN('LEGALBUSNAME',p_legal_bus_name);
1114 FND_MESSAGE.SET_TOKEN('LEGALBUSNAME',l_legal_bus_name);
1115 l_msg_text := FND_MESSAGE.GET ;
1116 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
1117 l_msg_text||' Error Code:' || SQLCODE ||' Error Text:'
1118 || SQLERRM);
1119 insert_for_report(p_duns,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
1120 if(l_new_bank_account_id IS NOT NULL AND
1121 l_new_bank_account_id<>0) THEN
1122 UPDATE fv_ccr_orgs
1123 SET bank_account_id = l_new_bank_account_id,
1124 last_update_date=sysdate,
1125 last_updated_by=l_user_id,
1126 last_update_login=l_login_id
1127 WHERE ccr_id = p_ccr_id
1128 AND org_id=p_org_id;
1129 END IF;
1130 IF(p_header_conc_req_status = 'S' AND p_ccr_id <> p_prev_ccr_id) THEN
1131 UPDATE fv_ccr_vendors
1132 SET conc_request_status=l_header_conc_req_status,
1133 last_update_date=sysdate,
1134 last_updated_by=l_user_id,
1135 last_update_login=l_login_id
1136 WHERE ccr_id=p_ccr_id;
1137 END IF;
1138 RAISE;
1139 END;
1140 ELSE
1141 BEGIN
1142 UPDATE fv_ccr_vendors
1143 SET vendor_id =l_vendor_id,
1144 last_update_date=sysdate,
1145 last_updated_by=l_user_id,
1146 last_update_login=l_login_id
1147 WHERE DUNS = p_duns;
1148
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 l_header_conc_req_status := 'E';
1152 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,sqlerrm);
1153 END;
1154 END IF;
1155
1156 END IF;
1157
1158
1159
1160 ELSE
1161 --This is the case where DUNS4 is newly inserted and
1162 --does not have vendor_id update it with DUNS/DUNS4
1163 --vendor id
1164 IF(nvl(p_vendor_id,0)<>0) THEN
1165 l_vendor_id:=p_vendor_id;
1166 ELSE
1167 l_vendor_id := p_prev_vendor_id;
1168 END IF;
1169 END IF;
1170 x_vendor_id := l_vendor_id;
1171
1172 IF(nvl(l_vendor_id,0)<>0 AND l_vendor_name IS NULL) THEN
1173 get_vendor_name(l_vendor_id,l_vendor_name,l_num_1099,l_org_type_lookup);
1174 END IF;
1175
1176 BEGIN
1177 IF(p_main_address_country = 'CAN') THEN
1178 l_state := null;
1179 l_province := p_main_address_state;
1180 ELSE
1181 l_state := p_main_address_state;
1182 l_province:=null;
1183 END IF;
1184
1185 l_main_add_site_id := p_main_add_site_id;
1186 IF(p_main_address_flag = 'Y' and nvl(l_vendor_id,0)<>0 and
1187 nvl(p_main_add_site_id,0) = 0) THEN
1188
1189 l_plus4 := 'LOC'||p_duns;
1190 duplicate_vendor_site_code(l_vendor_id,l_plus4,p_org_id,l_dup_site_code,
1191 l_main_add_site_id,l_duns_site_code);
1192 IF(nvl(l_dup_site_code,'F') = 'T') THEN
1193 IF(l_duns_site_code IS NOT NULL AND l_duns_site_code<>p_duns) THEN
1194 l_main_add_site_id:=0;
1195 END IF;
1196 END IF;
1197 END IF;
1198
1199 IF(p_main_address_flag = 'Y' AND nvl(l_main_add_site_id,0) = 0
1200 AND nvl(l_vendor_id,0) <>0 ) THEN
1201
1202
1203 l_plus4 := 'LOC'||p_duns;
1204
1205 IF(nvl(l_dup_site_code,'F') = 'T') THEN
1206 l_assgn_conc_req_status := 'E';
1207 FND_MESSAGE.SET_NAME('FV','FV_CCR_SITE_CODE_EXISTS');
1208 FND_MESSAGE.SET_TOKEN('ORGNAME',p_org_name);
1209 FND_MESSAGE.SET_TOKEN('DEFAULTCODE',l_plus4);
1210 l_msg_text := FND_MESSAGE.GET;
1211 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT
1212 ,l_module_name,l_msg_text);
1213 insert_for_report(p_duns,16,l_msg_text,null,null
1214 ,null,null);
1215 ELSE
1216 l_msg := null;
1217
1218 FV_CCR_UTIL_PVT.insert_vendor_site(
1219 p_vendor_site_code=>l_plus4,
1220 p_vendor_id=>l_vendor_id ,
1221 p_org_id =>p_org_id,
1222 p_address_line1=>p_main_address_line1,
1223 p_address_line2=>p_main_address_line2,
1224 p_address_line3=>null,
1225 p_address_line4=>null,
1226 p_city=>p_main_address_city,
1227 p_state=>l_state,
1228 p_zip=>p_main_address_zip,
1229 p_province=>l_province,
1230 p_country=>p_main_address_country,
1231 p_duns_number=>p_duns,
1232 p_pay_site_flag=> NULL,
1233 p_hold_unvalidated_inv_flag=>'N',
1234 p_hold_all_payments_flag=>'N',
1235 p_us_phone => NULL,
1236 p_fax => NULL,
1237 p_email => NULL,
1238 p_non_us_phone => NULL,
1239 p_purchasing_site_flag => 'Y',
1240 x_vendor_site_id=>l_main_add_site_id,
1241 x_party_site_id => l_party_site_id,
1242 x_status=>l_status,
1243 x_exception_msg=>l_msg);
1244
1245 IF(nvl(l_status,'F')<> 'S' OR l_main_add_site_id IS NULL) THEN
1246 RAISE e_supp_exception;
1247 END IF;
1248
1249 -- Added for bug 6238518
1250 IF (l_status = 'S') THEN
1251 IF NVL(p_main_address_flag,'N') = 'Y' THEN
1252 fv_utility.log_mesg('Updating mail poc for main site id:'||l_party_site_id);
1253 UPDATE hz_party_sites
1254 SET addressee = p_mail_poc
1255 WHERE party_site_id = l_party_site_id;
1256 END IF;
1257 END IF;
1258
1259
1260 --IF(l_vendor_name IS NULL) THEN l_vendor_name := p_legal_bus_name; END IF;
1261 IF(l_vendor_name IS NULL) THEN l_vendor_name := l_legal_bus_name; END IF;
1262 insert_for_report(l_duns_for_report,13,null,l_plus4,l_vendor_name,'M',null);
1263 END IF;
1264 ELSIF (p_main_address_flag = 'Y' AND nvl(l_main_add_site_id,0) <> 0) THEN
1265
1266 l_msg := null;
1267
1268 FV_CCR_UTIL_PVT.update_vendor_site(
1269 p_vendor_site_code =>null,
1270 p_vendor_site_id=>l_main_add_site_id,
1271 p_org_id => p_org_id,
1272 p_address_line1=>p_main_address_line1,
1273 p_address_line2=>p_main_address_line2,
1274 p_address_line3=>null,
1275 p_address_line4=>null,
1276 p_city=>p_main_address_city,
1277 p_state=>l_state,
1278 p_zip=>p_main_address_zip,
1279 p_province=>l_province, --To be populated for canadian vendors.
1280 p_country=>p_main_address_country,
1281 p_duns_number=>p_duns,
1282 p_pay_site_flag => NULL,
1283 p_hold_unvalidated_inv_flag=>l_hold_unmatched_invoices_flag,
1284 p_hold_all_payments_flag=>l_hold_all_payments_flag,
1285 p_us_phone => NULL,
1286 p_fax => NULL,
1287 p_email => NULL,
1288 p_non_us_phone => NULL,
1289 p_purchasing_site_flag => 'Y',
1290 x_party_site_id => l_party_site_id,
1291 x_status=>l_status,
1292 x_exception_msg=>l_msg);
1293 --p_calling_source=>'CCRImport');
1294
1295 IF(nvl(l_status,'F') <> 'S' ) THEN
1296 RAISE e_supp_exception;
1297 END IF;
1298
1299 -- Added for bug 6238518
1300 IF (l_status = 'S') THEN
1301 IF NVL(p_main_address_flag,'N') = 'Y' THEN
1302 fv_utility.log_mesg('Updating mail poc for main site id: '||l_party_site_id);
1303 UPDATE hz_party_sites
1304 SET addressee = p_mail_poc
1305 WHERE party_site_id = l_party_site_id;
1306 END IF;
1307 END IF;
1308
1309 IF(p_enabled_flag = 'N') THEN
1310 get_vendor_site_code(p_main_add_site_id,x_react_main_site_code);
1311 END IF;
1312
1313
1314 END IF;
1315 EXCEPTION
1316 WHEN OTHERS THEN
1317 l_assgn_conc_req_status := 'E';
1318 IF(l_msg IS NULL) THEN
1319 l_excp_msg := FND_MESSAGE.GET;
1320 ELSE
1321 l_excp_msg := l_msg;
1322 END IF;
1323 IF(nvl(l_main_add_site_id,0) = 0) THEN
1324 FND_MESSAGE.SET_NAME('FV','FV_CCR_MAIN_SITE_INSERT');
1325 FND_MESSAGE.SET_TOKEN('DUNS',l_duns_for_report);
1326 l_msg_text := FND_MESSAGE.GET;
1327 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,l_msg_text||
1328 ' Error Code:' || SQLCODE ||' Error Text:' || SQLERRM);
1329 insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
1330 ELSE
1331 FND_MESSAGE.SET_NAME('FV','FV_CCR_MAIN_SITE_UPDATE');
1332 get_vendor_site_code(p_main_add_site_id,l_site_code);
1333 FND_MESSAGE.SET_TOKEN('VENDORSITECODE',l_site_code);
1334 l_msg_text := FND_MESSAGE.GET;
1335 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,l_msg_text||
1336 ' Error Code:' || SQLCODE ||' Error Text:' || SQLERRM);
1337 insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
1338 END IF;
1339
1340 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1341 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1342 END IF;
1343 END;
1344
1345
1346 -- l_plus4 will serve as site code for vendor site
1347
1348 IF(p_plus4 IS NOT NULL) THEN
1349 l_plus4 := p_duns || p_plus4;
1350 ELSE
1351 l_plus4:= p_duns;
1352 END IF;
1353 IF(p_pay_address_country = 'CAN') THEN
1354 l_state := null;
1355 l_province := p_pay_address_state;
1356 ELSE
1357 l_state := p_pay_address_state;
1358 l_province:=null;
1359 END IF;
1360
1361 IF(nvl(p_pay_site_id,0) = 0 AND nvl(l_vendor_id,0) <> 0)THEN
1362 BEGIN
1363 duplicate_vendor_site_code(l_vendor_id,l_plus4,p_org_id,l_dup_site_code,
1364 l_site_id_site_code,l_duns_site_code);
1365 IF(nvl(l_dup_site_code,'F') = 'T') THEN
1366 l_assgn_conc_req_status := 'E';
1367 FND_MESSAGE.SET_NAME('FV','FV_CCR_SITE_CODE_EXISTS');
1368 FND_MESSAGE.SET_TOKEN('ORGNAME',p_org_name);
1369 FND_MESSAGE.SET_TOKEN('DEFAULTCODE',l_plus4);
1370 l_msg_text := FND_MESSAGE.GET;
1371 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT
1372 ,l_module_name,l_msg_text);
1373 insert_for_report(p_duns,16,l_msg_text,null,null
1374 ,null,null);
1375 l_account_uses_insert_flag := 'N';
1376 l_account_uses_upd_flag := 'N';
1377 ELSE
1378 l_pay_site_flag := 'Y';
1379 l_msg := null;
1380
1381 FV_CCR_UTIL_PVT.insert_vendor_site(
1382 p_vendor_site_code=>l_plus4,
1383 p_vendor_id=>l_vendor_id ,
1384 p_org_id =>p_org_id,
1385 p_address_line1=>p_pay_address_line1,
1386 p_address_line2=>p_pay_address_line2,
1387 p_address_line3=>p_pay_address_line3,
1388 p_address_line4=>null,
1389 p_city=>p_pay_address_city,
1390 p_state=>l_state,
1391 p_zip=>p_pay_address_zip,
1392 p_province=>l_province,
1393 p_country=>p_pay_address_country,
1394 p_duns_number=>p_duns,
1395 p_pay_site_flag=>l_pay_site_flag,
1396 p_hold_unvalidated_inv_flag=>'N',
1397 p_hold_all_payments_flag=>'N',
1398 p_us_phone => p_ar_us_phone,
1399 p_fax => p_ar_fax,
1400 p_email => p_ar_email,
1401 p_non_us_phone => p_ar_non_us_phone,
1402 --p_purchasing_site_flag => NULL,
1403 p_purchasing_site_flag => 'Y',
1404 x_vendor_site_id=>l_pay_site_id,
1405 x_party_site_id => l_party_site_id,
1406 x_status=>l_status,
1407 x_exception_msg=>l_msg);
1408
1409 IF(nvl(l_status,'F')<>'S' OR l_pay_site_id IS NULL) THEN
1410 RAISE e_supp_exception;
1411 END IF;
1412
1413 -- Added for bug 6238518
1414 IF (l_status = 'S') THEN
1415 IF NVL(l_pay_site_flag,'N') = 'Y' THEN
1416 fv_utility.log_mesg('Updating remit poc for site id: '||l_party_site_id);
1417 UPDATE hz_party_sites
1418 SET addressee = p_remit_poc
1419 WHERE party_site_id = l_party_site_id;
1420 END IF;
1421 END IF;
1422
1423 l_account_uses_insert_flag := 'I';
1424 l_account_uses_upd_flag := 'N';
1425 --IF(l_vendor_name IS NULL) THEN l_vendor_name := p_legal_bus_name; END IF;
1426 IF(l_vendor_name IS NULL) THEN l_vendor_name := l_legal_bus_name; END IF;
1427 insert_for_report(l_duns_for_report,13,null,l_plus4,l_vendor_name,'P',null);
1428 END IF;
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 l_assgn_conc_req_status := 'E';
1432 IF(l_msg IS NULL) THEN
1433 l_excp_msg := FND_MESSAGE.GET;
1434 ELSE
1435 l_excp_msg := l_msg;
1436 END IF;
1437 FND_MESSAGE.SET_NAME('FV','FV_CCR_PAY_SITE_INSERT');
1438 FND_MESSAGE.SET_TOKEN('DUNS',l_plus4);
1439 l_msg_text := FND_MESSAGE.GET;
1440 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,l_msg_text||
1441 ' Error Code:' || SQLCODE ||' Error Text:' || SQLERRM);
1442 insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
1443 l_account_uses_insert_flag := 'N';
1444 l_account_uses_upd_flag := 'N';
1445
1446 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1447 THEN
1448 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1449 END IF;
1450 END;
1451 ELSIF(nvl(p_pay_site_id,0)<>0) THEN
1452 BEGIN
1453
1454
1455 IF(p_enabled_flag = 'N') THEN
1456 get_vendor_site_code(p_pay_site_id,x_react_pay_site_code);
1457 END IF;
1458
1459 IF(l_account_uses_insert_flag <> 'I' AND l_account_uses_insert_flag<>'N') THEN
1460 l_account_uses_insert_flag := 'U';
1461 END IF;
1462 l_msg := null;
1463
1464 FV_CCR_UTIL_PVT.update_vendor_site(
1465 p_vendor_site_code =>null,
1466 p_vendor_site_id=>p_pay_site_id,
1467 p_org_id => p_org_id,
1468 p_address_line1=>p_pay_address_line1,
1469 p_address_line2=>p_pay_address_line2,
1470 p_address_line3=>p_pay_address_line3,
1471 p_address_line4=>null,
1472 p_city=>p_pay_address_city,
1473 p_state=>l_state,
1474 p_zip=>p_pay_address_zip,
1475 p_province=>l_province,
1476 p_country=>p_pay_address_country,
1477 p_duns_number=>p_duns,
1478 --p_pay_site_flag => NULL, mod for bug 6348043
1479 p_pay_site_flag => 'Y',
1480 p_hold_unvalidated_inv_flag=>l_hold_unmatched_invoices_flag,
1481 p_hold_all_payments_flag=>l_hold_all_payments_flag,
1482 p_us_phone => p_ar_us_phone,
1483 p_fax => p_ar_fax,
1484 p_email => p_ar_email,
1485 p_non_us_phone => p_ar_non_us_phone,
1486 --p_purchasing_site_flag => NULL,
1487 p_purchasing_site_flag => 'Y',
1488 x_party_site_id => l_party_site_id,
1489 x_status=>l_status,
1490 x_exception_msg=>l_msg);
1491 --p_calling_source=>'CCRImport'); Bug#4476059
1492
1493
1494 IF(nvl(l_status,'F')<>'S') THEN
1495 RAISE e_supp_exception;
1496 END IF;
1497
1498 -- Added for bug 6238518
1499 IF (l_status = 'S') THEN
1500 fv_utility.log_mesg('Updating remit poc for site id: '||l_party_site_id);
1501 UPDATE hz_party_sites
1502 SET addressee = p_remit_poc
1503 WHERE party_site_id = l_party_site_id;
1504 END IF;
1505
1506
1507 EXCEPTION
1508 WHEN OTHERS THEN
1509 l_assgn_conc_req_status := 'E';
1510 IF(l_msg IS NULL) THEN
1511 l_excp_msg:=FND_MESSAGE.GET;
1512 ELSE
1513 l_excp_msg:=l_msg;
1514 END IF;
1515 get_vendor_site_code(p_pay_site_id,l_site_code);
1516 FND_MESSAGE.SET_NAME('FV','FV_CCR_PAY_SITE_UPDATE');
1517 FND_MESSAGE.SET_TOKEN('VENDORSITECODE',l_site_code);
1518 l_msg_text := FND_MESSAGE.GET;
1519 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,l_msg_text||
1520 ' Error Code:' || SQLCODE ||' Error Text:' || SQLERRM);
1521 insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
1522
1523 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1524 THEN
1525 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1526 END IF;
1527 END;
1528
1529 END IF;
1530
1531
1532 BEGIN
1533
1534 process_bank_account_uses
1535 (
1536 l_account_uses_upd_flag,
1537 l_vendor_id,
1538 p_federal_vendor,
1539 p_valid_bank_info,
1540 p_old_bank_account_id,
1541 l_pay_site_id,
1542 p_file_date,
1543 l_new_bank_account_id,
1544 l_account_uses_insert_flag,
1545 p_org_id );
1546
1547 EXCEPTION
1548 WHEN OTHERS THEN
1549 l_assgn_conc_req_status := 'E';
1550 FND_CLIENT_INFO.set_org_context(l_old_org_id);
1551 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,l_msg_text||
1552 ' Error Code:' || SQLCODE ||' Error Text:' || SQLERRM);
1553 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1554 THEN
1555 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1556 END IF;
1557 END;
1558 IF(l_pay_site_id = 0) THEN l_pay_site_id := null; END IF;
1559 IF(l_main_add_site_id = 0) THEN l_main_add_site_id := null; END IF;
1560 IF(l_new_bank_account_id=0) THEN l_new_bank_account_id := null; END IF;
1561 IF(p_header_conc_req_status = 'S' AND p_ccr_id <> p_prev_ccr_id) THEN
1562 UPDATE fv_ccr_vendors
1563 SET conc_request_status=l_header_conc_req_status,
1564 last_update_date=sysdate,
1565 last_updated_by=l_user_id,
1566 last_update_login=l_login_id
1567 WHERE ccr_id=p_ccr_id;
1568 END IF;
1569 IF(p_assgn_conc_req_status <> 'S') THEN
1570 l_assgn_conc_req_status:=p_assgn_conc_req_status;
1571 END IF;
1572
1573
1574 UPDATE FV_CCR_ORGS
1575 SET pay_site_id = l_pay_site_id,
1576 main_address_site_id = l_main_add_site_id,
1577 bank_account_id = l_new_bank_account_id,
1578 conc_request_status=l_assgn_conc_req_status,
1579 last_update_date=sysdate,
1580 last_updated_by=l_user_id,
1581 last_update_login=l_login_id
1582 WHERE ccr_id = p_ccr_id
1583 AND org_id = p_org_id;
1584
1585 ELSIF (p_ccr_status='E' or p_ccr_status='D') THEN
1586
1587 IF(nvl(p_pay_site_id,0)<>0) THEN
1588 IF(p_pay_address_country = 'CAN') THEN
1589 l_state := null;
1590 l_province := p_pay_address_state;
1591 ELSE
1592 l_state := p_pay_address_state;
1593 l_province:=null;
1594 END IF;
1595 BEGIN
1596 l_msg := null;
1597
1598
1599 FV_CCR_UTIL_PVT.update_vendor_site(
1600 p_vendor_site_code =>null,
1601 p_vendor_site_id=>p_pay_site_id,
1602 p_org_id => p_org_id,
1603 p_address_line1=>p_pay_address_line1,
1604 p_address_line2=>p_pay_address_line2,
1605 p_address_line3=>p_pay_address_line3,
1606 p_address_line4=>null,
1607 p_city=>p_pay_address_city,
1608 p_state=>l_state,
1609 p_zip=>p_pay_address_zip,
1610 p_province=>l_province,
1611 p_country=>p_pay_address_country,
1612 p_duns_number=>p_duns,
1613 --p_pay_site_flag => NULL, mod for bug 6348043
1614 p_pay_site_flag => NULL,
1615 p_hold_unvalidated_inv_flag=>'Y',
1616 p_hold_all_payments_flag=>'Y',
1617 p_us_phone => p_ar_us_phone,
1618 p_fax => p_ar_fax,
1619 p_email => p_ar_email,
1620 p_non_us_phone => p_ar_non_us_phone,
1621 p_purchasing_site_flag => NULL,
1622 x_party_site_id => l_party_site_id,
1623 x_status=>l_status,
1624 x_exception_msg=>l_msg);
1625
1626
1627 IF(nvl(l_status,'F')<>'S') THEN
1628 RAISE e_supp_exception;
1629 END IF;
1630 get_vendor_site_code(p_pay_site_id,x_react_pay_site_code);
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633 l_assgn_conc_req_status := 'E';
1634 IF(l_msg IS NULL) THEN
1635 l_excp_msg := FND_MESSAGE.GET;
1636 ELSE
1637 l_excp_msg := l_msg;
1638 END IF;
1639 get_vendor_site_code(p_pay_site_id,l_site_code);
1640 FND_MESSAGE.SET_NAME('FV','FV_CCR_PAY_SITE_UPDATE');
1641 FND_MESSAGE.SET_TOKEN('VENDORSITECODE',l_site_code);
1642 l_msg_text := FND_MESSAGE.GET;
1643 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,l_msg_text||
1644 ' Error Code:' || SQLCODE ||' Error Text:' || SQLERRM);
1645 insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
1646 END;
1647 END IF;
1648 IF(nvl(p_main_add_site_id,0) <> 0) THEN
1649 IF(p_main_address_country = 'CAN') THEN
1650 l_state := null;
1651 l_province := p_main_address_state;
1652 ELSE
1653 l_state := p_main_address_state;
1654 l_province:=null;
1655 END IF;
1656 BEGIN
1657 l_msg := null;
1658
1659 FV_CCR_UTIL_PVT.update_vendor_site(
1660 p_vendor_site_code =>null,
1661 p_vendor_site_id=>p_main_add_site_id,
1662 p_org_id => p_org_id,
1663 p_address_line1=>p_main_address_line1,
1664 p_address_line2=>p_main_address_line2,
1665 p_address_line3=>null,
1666 p_address_line4=>null,
1667 p_city=>p_main_address_city,
1668 p_state=>l_state,
1669 p_zip=>p_main_address_zip,
1670 p_province=>l_province,
1671 p_country=>p_main_address_country,
1672 p_duns_number=>p_duns,
1673 p_pay_site_flag => NULL,
1674 p_hold_unvalidated_inv_flag=>'Y',
1675 p_hold_all_payments_flag=>'Y',
1676 p_us_phone => NULL,
1677 p_fax => NULL,
1678 p_email => NULL,
1679 p_non_us_phone => NULL,
1680 p_purchasing_site_flag => NULL,
1681 x_party_site_id => l_party_site_id,
1682 x_status=>l_status,
1683 x_exception_msg=>l_msg);
1684
1685 IF(nvl(l_status,'F')<>'S') THEN
1686 RAISE e_supp_exception;
1687 END IF;
1688 get_vendor_site_code(p_main_add_site_id,x_react_main_site_code);
1689 EXCEPTION
1690 WHEN OTHERS THEN
1691 l_assgn_conc_req_status := 'E';
1692 IF(l_msg IS NULL) THEN
1693 l_excp_msg := FND_MESSAGE.GET;
1694 ELSE
1695 l_excp_msg := l_msg;
1696 END IF;
1697 get_vendor_site_code(p_main_add_site_id,l_site_code);
1698 FND_MESSAGE.SET_NAME('FV','FV_CCR_MAIN_SITE_UPDATE');
1699 FND_MESSAGE.SET_TOKEN('VENDORSITECODE',l_site_code);
1700 l_msg_text := FND_MESSAGE.GET;
1701 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,l_msg_text||
1702 ' Error Code:' || SQLCODE ||' Error Text:' || SQLERRM);
1703 insert_for_report(l_duns_for_report,19,l_msg_text||nvl(l_excp_msg,''),null,null,null,null);
1704 END;
1705 END IF;
1706 IF(p_header_conc_req_status = 'S' AND p_ccr_id <> p_prev_ccr_id) THEN
1707 UPDATE fv_ccr_vendors
1708 SET conc_request_status=l_header_conc_req_status,
1709 last_update_date=sysdate,
1710 last_updated_by=l_user_id,
1711 last_update_login=l_login_id
1712 WHERE ccr_id=p_ccr_id;
1713 END IF;
1714 IF(p_assgn_conc_req_status = 'S') THEN
1715 UPDATE fv_ccr_orgs
1716 SET conc_request_status=l_assgn_conc_req_status,
1717 last_update_date=sysdate,
1718 last_updated_by=l_user_id,
1719 last_update_login=l_login_id
1720 WHERE ccr_id=p_ccr_id
1721 AND org_id=p_org_id;
1722 END IF;
1723
1724 END IF;
1725
1726
1727 -- Standard call to get message count and if count is 1, get message info.
1728 FND_MSG_PUB.Count_And_Get
1729 (
1730 p_count => x_msg_count ,
1731 p_data => x_msg_data
1732 );
1733 EXCEPTION
1734 WHEN FND_API.G_EXC_ERROR THEN
1735 IF(x_vendor_id IS NULL) THEN
1736 x_vendor_id := p_prev_vendor_id;
1737 END IF;
1738
1739 x_return_status := FND_API.G_RET_STS_ERROR ;
1740 FND_MSG_PUB.Count_And_Get
1741 ( p_count => x_msg_count ,
1742 p_data => x_msg_data
1743 );
1744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1745 IF(x_vendor_id IS NULL) THEN
1746 x_vendor_id := p_prev_vendor_id;
1747 END IF;
1748
1749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1750 FND_MSG_PUB.Count_And_Get
1751 ( p_count => x_msg_count ,
1752 p_data => x_msg_data
1753 );
1754 WHEN OTHERS THEN
1755 IF(x_vendor_id IS NULL) THEN
1756 x_vendor_id := p_prev_vendor_id;
1757 END IF;
1758
1759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1760 IF FND_MSG_PUB.Check_Msg_Level
1761 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1762 THEN
1763 FND_MSG_PUB.Add_Exc_Msg
1764 ( G_PKG_NAME ,
1765 l_api_name
1766 );
1767 END IF;
1768 FND_MSG_PUB.Count_And_Get
1769 ( p_count => x_msg_count ,
1770 p_data => x_msg_data
1771 );
1772
1773
1774 END;
1775
1776 FUNCTION get_profile_option(p_name varchar2) RETURN varchar2
1777 as
1778 v_value varchar2(1000) default null;
1779 BEGIN
1780 FND_PROFILE.GET(p_name, v_value);
1781 return v_value;
1782 END get_profile_option;
1783
1784
1785 PROCEDURE delete_plusfour_assignments(p_ccrid number)
1786 as
1787 v_plus_four varchar2(100);
1788 l_module_name VARCHAR2(60);
1789 BEGIN
1790 l_module_name := 'fv.plsql.FV_CCR_UTIL_PVT.delete_plusfour_assignments';
1791
1792 --unassign plus_four's vendor_id (supplier)
1793 update fv_ccr_vendors
1794 set vendor_id = null
1795 where duns = (select duns from fv_ccr_vendors where ccr_id = p_ccrid)
1796 and plus_four is not null;
1797
1798 --delete plus_four assignments
1799 select plus_four into v_plus_four
1800 from fv_ccr_vendors
1801 where ccr_id = p_ccrid;
1802
1803 if v_plus_four is null then
1804 delete from fv_ccr_orgs
1805 where ccr_id in (
1806 select ccr_id
1807 from fv_ccr_vendors
1808 where duns = (select duns from fv_ccr_vendors where ccr_id =
1809 p_ccrid)
1810 and plus_four is not null);
1811 end if;
1812
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,sqlerrm);
1816 END delete_plusfour_assignments;
1817
1818
1819 FUNCTION get_org_paysite_id(p_ccrid NUMBER, p_org_id NUMBER)
1820 RETURN NUMBER
1821 AS
1822 v_id number := null;
1823 v_count number := 0;
1824 v_org_id number := null;
1825 BEGIN
1826
1827 --v_org_id := fv_ccr_util_pvt.get_profile_option('ORG_ID');
1828 IF p_org_id is NOT NULL THEN
1829 for crec in (select pay_site_id
1830 from fv_ccr_orgs o, hr_organization_units ou, ap_supplier_sites_all vs
1831 where o.pay_site_id is not null
1832 and o.pay_site_id = vs.VENDOR_SITE_ID
1833 and vs.ORG_ID = ou.organization_id
1834 and ou.organization_id = nvl(p_org_id,ou.organization_id)
1835 and ccr_id=p_ccrid)
1836 loop
1837 v_count := v_count + 1;
1838 v_id := crec.pay_site_id;
1839 end loop;
1840 END IF;
1841
1842 if v_count > 1 then
1843 return -99;
1844 else
1845 return v_id;
1846 end if;
1847
1848 END get_org_paysite_id;
1849
1850
1851 FUNCTION get_org_mainaddrsite_id(p_ccrid NUMBER, p_org_id NUMBER)
1852 RETURN NUMBER
1853 AS
1854 v_id number := null;
1855 v_count number := 0;
1856 v_org_id number := null;
1857 BEGIN
1858
1859 --v_org_id := fv_ccr_util_pvt.get_profile_option('ORG_ID');
1860 IF p_org_id is NOT NULL THEN
1861 for crec in (select main_address_site_id
1862 from fv_ccr_orgs o, hr_organization_units ou, po_vendor_sites_all vs
1863 where o.main_address_site_id is not null
1864 and o.main_address_site_flag = 'Y'
1865 and o.main_address_site_id = vs.VENDOR_SITE_ID
1866 and vs.ORG_ID = ou.organization_id
1867 and ou.organization_id = nvl(v_org_id,ou.organization_id)
1868 and ccr_id=p_ccrid)
1869 loop
1870 v_count := v_count + 1;
1871 v_id := crec.main_address_site_id;
1872 end loop;
1873 END IF;
1874
1875 if v_count > 1 then
1876 return -99;
1877 else
1878 return v_id;
1879 end if;
1880
1881 END get_org_mainaddrsite_id;
1882
1883 /*-------------------------------------------------------------------
1884 Function get_lookup_desc gets the meaning from fnd_lookups
1885 given a lookup code and type.
1886 This function is used in FvCcrVendorCodesVO which in turn
1887 is used in the View Goods and Services page.
1888 -------------------------------------------------------------------*/
1889 FUNCTION get_lookup_desc (p_lookup_type IN VARCHAR2,
1890 p_lookup_code IN VARCHAR2
1891 ) RETURN VARCHAR2
1892 IS
1893
1894 l_lookup_meaning fnd_lookups.meaning%TYPE;
1895
1896 BEGIN
1897 l_lookup_meaning := NULL;
1898
1899 IF p_lookup_code IS NOT NULL
1900 THEN
1901 SELECT p_lookup_code||' - '||meaning meaning
1902 INTO l_lookup_meaning
1903 FROM fnd_lookup_values
1904 WHERE lookup_type = p_lookup_type
1905 AND lookup_code = p_lookup_code
1906 AND language = userenv('LANG');
1907 END IF;
1908
1909 RETURN l_lookup_meaning;
1910
1911 EXCEPTION
1912 WHEN NO_DATA_FOUND THEN
1913 RETURN p_lookup_code;
1914 WHEN OTHERS THEN
1915 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1916 'fv_ccr_util_pvt.get_lookup_desc',SQLERRM);
1917 RETURN p_lookup_code;
1918
1919 END get_lookup_desc;
1920
1921 /*-------------------------------------------------------------------
1922 Function check_non_user_org_asgnmt checks to see if there are any
1923 org or supplier assignments which do not belong to the organization
1924 that the user has access to.
1925 This function is used in VendorAMImpl.java to check if user should
1926 be allowed to unassign the supplier assigned to a ccr_id
1927 -------------------------------------------------------------------*/
1928 FUNCTION check_non_user_org_asgnmt (p_ccr_id IN NUMBER ) RETURN VARCHAR2
1929 IS
1930
1931 l_asgnmt_exists VARCHAR2(1);
1932 l_count NUMBER ;
1933
1934 BEGIN
1935 l_asgnmt_exists := 'N';
1936 l_count := 0;
1937
1938 SELECT COUNT(*)
1939 INTO l_count
1940 FROM fv_ccr_orgs fco
1941 WHERE fco.ccr_id = p_ccr_id
1942 AND mo_global.check_access(fco.org_id)<>'Y';
1943
1944
1945 IF l_count > 0
1946 THEN
1947 l_asgnmt_exists := 'Y';
1948 ELSE
1949 l_asgnmt_exists := 'N';
1950 END IF;
1951
1952 RETURN l_asgnmt_exists;
1953
1954 END check_non_user_org_asgnmt;
1955
1956 /*-------------------------------------------------------------------
1957 Function check_suppl_tobe_merged checks to see if the supplier
1958 is about to be merged with another supplier.
1959 This function is used in VendorAMImpl.java to check if user should
1960 be allowed to assign the supplier to a ccr_id
1961 -------------------------------------------------------------------*/
1962 FUNCTION check_suppl_tobe_merged (p_vendor_id IN NUMBER ) RETURN VARCHAR2
1963 IS
1964
1965 l_tobe_merged VARCHAR2(1);
1966 l_count NUMBER ;
1967
1968 BEGIN
1969 l_tobe_merged := 'N';
1970 l_count := 0;
1971
1972 -- Check if there is any pending supplier merge.
1973 SELECT COUNT(*)
1974 INTO l_count
1975 FROM ap_duplicate_vendors_all
1976 WHERE duplicate_vendor_id = p_vendor_id
1977 AND process_flag <> 'Y';
1978
1979 IF l_count > 0
1980 THEN
1981 l_tobe_merged := 'Y';
1982 ELSE
1983 l_tobe_merged := 'N';
1984 END IF;
1985
1986 RETURN l_tobe_merged;
1987
1988 END check_suppl_tobe_merged;
1989
1990
1991 PROCEDURE insert_vendor
1992 (
1993 p_vendor_name IN varchar2,
1994 p_taxpayer_id IN varchar2,
1995 p_supplier_number IN varchar2,
1996 p_org_type_lookup_code IN VARCHAR2,
1997 x_vendor_id OUT NOCOPY NUMBER,
1998 x_status OUT NOCOPY VARCHAR2,
1999 x_exception_msg OUT NOCOPY VARCHAR2
2000 )
2001 IS
2002 l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
2003 l_party_id number;
2004 l_version number;
2005 l_msg_count number;
2006 l_msg_data varchar2(400);
2007 l_ret_stat varchar2(50);
2008
2009 BEGIN
2010
2011 l_version := 1.0;
2012 l_vendor_rec.vendor_name := p_vendor_name;
2013 l_vendor_rec.JGZZ_FISCAL_CODE := p_taxpayer_id;
2014 l_vendor_rec.segment1 := p_supplier_number;
2015
2016 l_vendor_rec.ORGANIZATION_TYPE_LOOKUP_CODE := p_org_type_lookup_code;
2017
2018 fv_utility.log_mesg('p_vendor_name: '||p_vendor_name);
2019 fv_utility.log_mesg('p_taxpayer_id: '||p_taxpayer_id);
2020 fv_utility.log_mesg('p_supplier_number: '||p_supplier_number);
2021 fv_utility.log_mesg('p_org_type_lookup_code: '||p_org_type_lookup_code);
2022
2023
2024 AP_VENDOR_PUB_PKG.create_vendor(
2025 p_api_version => l_version,
2026 p_init_msg_list => FND_API.G_TRUE,
2027 p_commit=> FND_API.G_FALSE,
2028 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
2029 x_return_status => l_ret_stat,
2030 x_msg_count=>l_msg_count,
2031 x_msg_data=>l_msg_data,
2032 p_vendor_rec=>l_vendor_rec,
2033 x_vendor_id=>x_vendor_id,
2034 x_party_id=>l_party_id);
2035
2036
2037 fv_utility.log_mesg('l_ret_stat: '||l_ret_stat);
2038 fv_utility.log_mesg('l_msg_count: '||l_msg_count);
2039
2040 if(l_ret_stat <> 'S') THEN
2041 if(l_msg_count = 1) THEN
2042 x_exception_msg := l_msg_data;
2043 else
2044 for i in 1..l_msg_count loop
2045 x_exception_msg := x_exception_msg || fnd_msg_pub.get(p_msg_index=>i,p_encoded=>'F');
2046 end loop;
2047 end if;
2048 end if;
2049
2050 x_status := l_ret_stat;
2051 fv_utility.log_mesg('x_exception_msg: '||x_exception_msg);
2052
2053 exception
2054 when others then
2055 fv_utility.log_mesg('Exception in fv_ccr_util_pvt.insert_vendor.');
2056 fv_utility.log_mesg(sqlcode||': '||sqlerrm);
2057
2058 END insert_vendor;
2059
2060
2061 PROCEDURE update_vendor
2062 (
2063 p_vendor_id IN NUMBER,
2064 p_taxpayer_id IN VARCHAR2,
2065 x_status OUT NOCOPY VARCHAR2,
2066 x_exception_msg OUT NOCOPY VARCHAR2
2067 )
2068 IS
2069 l_party_id number;
2070 BEGIN
2071
2072 x_status := 'E';
2073
2074 SELECT party_id INTO l_party_id
2075 FROM ap_suppliers
2076 WHERE vendor_id = p_vendor_id;
2077
2078 UPDATE hz_parties
2079 SET JGZZ_FISCAL_CODE = p_taxpayer_id
2080 where party_id = l_party_id;
2081
2082 UPDATE ap_suppliers
2083 SET num_1099 = p_taxpayer_id
2084 WHERE vendor_id = p_vendor_id;
2085
2086 x_status := 'S';
2087
2088 IF(SQL%NOTFOUND) THEN
2089 x_exception_msg := 'No party exist for this vendor';
2090 x_status := 'E';
2091 END IF;
2092
2093 EXCEPTION
2094 WHEN OTHERS THEN
2095 x_exception_msg := 'Exception when getting vendor info';
2096 x_status := 'U';
2097 END update_vendor;
2098
2099
2100 PROCEDURE insert_vendor_site
2101 (
2102 p_vendor_site_code IN VARCHAR2,
2103 p_vendor_id IN NUMBER,
2104 p_org_id IN NUMBER,
2105 p_address_line1 IN VARCHAR2,
2106 p_address_line2 IN VARCHAR2,
2107 p_address_line3 IN VARCHAR2,
2108 p_address_line4 IN VARCHAR2,
2109 p_city IN VARCHAR2,
2110 p_state IN VARCHAR2,
2111 p_zip IN VARCHAR2,
2112 p_province IN VARCHAR2,
2113 p_country IN VARCHAR2,
2114 p_duns_number IN VARCHAR2,
2115 p_pay_site_flag IN VARCHAR2,
2116 p_hold_unvalidated_inv_flag IN VARCHAR2,
2117 p_hold_all_payments_flag IN VARCHAR2,
2118 p_us_phone IN VARCHAR2,
2119 p_fax IN VARCHAR2,
2120 p_email IN VARCHAR2,
2121 p_non_us_phone IN VARCHAR2,
2122 p_purchasing_site_flag IN VARCHAR2,
2123 x_vendor_site_id OUT NOCOPY NUMBER,
2124 x_party_site_id OUT NOCOPY NUMBER,
2125 x_status OUT NOCOPY VARCHAR2,
2126 x_exception_msg OUT NOCOPY VARCHAR2
2127 )
2128 IS
2129 l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
2130 l_party_site_id number;
2131 l_version number;
2132 l_ret_stat varchar2(20);
2133 l_msg_count number;
2134 l_msg_data varchar2(400);
2135 l_location_id number;
2136 BEGIN
2137 l_version := 1.0;
2138 l_vendor_site_rec.VENDOR_SITE_CODE := p_vendor_site_code;
2139 l_vendor_site_rec.VENDOR_ID := p_vendor_id;
2140 l_vendor_site_rec.org_id := p_org_id;
2141 l_vendor_site_rec.ADDRESS_LINE1 := p_address_line1;
2142 l_vendor_site_rec.ADDRESS_LINE2 := p_address_line2;
2143 l_vendor_site_rec.ADDRESS_LINE3 := p_address_line3;
2144 l_vendor_site_rec.ADDRESS_LINE4 := p_address_line4;
2145 l_vendor_site_rec.city := p_city;
2146 l_vendor_site_rec.state := p_state;
2147 l_vendor_site_rec.zip := p_zip;
2148 l_vendor_site_rec.province := p_province;
2149 l_vendor_site_rec.country := p_country;
2150 l_vendor_site_rec.duns_number := p_duns_number;
2151 l_vendor_site_rec.HOLD_ALL_PAYMENTS_FLAG := p_hold_all_payments_flag;
2152 l_vendor_site_rec.HOLD_UNMATCHED_INVOICES_FLAG := p_hold_unvalidated_inv_flag;
2153 l_vendor_site_rec.HOLD_FUTURE_PAYMENTS_FLAG := p_hold_unvalidated_inv_flag;
2154 l_vendor_site_rec.PURCHASING_SITE_FLAG := p_purchasing_site_flag;
2155
2156 if(p_pay_site_flag IS NOT NULL) THEN
2157 l_vendor_site_rec.pay_site_flag := p_pay_site_flag;
2158 -- Added for bug 6348043
2159 IF p_us_phone IS NOT NULL THEN
2160 l_vendor_site_rec.area_code := SUBSTR(p_us_phone,1,3);
2161 l_vendor_site_rec.phone := SUBSTR(p_us_phone,4,7);
2162 ELSIF (p_us_phone IS NULL AND p_non_us_phone IS NOT NULL) THEN
2163 l_vendor_site_rec.phone := p_non_us_phone;
2164 END IF;
2165 l_vendor_site_rec.fax_area_code := SUBSTR(p_fax,1,3);
2166 l_vendor_site_rec.fax := SUBSTR(p_fax,4,7);
2167 l_vendor_site_rec.email_address := p_email;
2168 end if;
2169
2170 AP_VENDOR_PUB_PKG.create_vendor_site(
2171 p_api_version => l_version,
2172 p_init_msg_list => FND_API.G_TRUE,
2173 p_commit=> FND_API.G_FALSE,
2174 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
2175 x_return_status => l_ret_stat,
2176 x_msg_count=>l_msg_count,
2177 x_msg_data=>l_msg_data,
2178 p_vendor_site_rec=>l_vendor_site_rec,
2179 x_vendor_site_id=>x_vendor_site_id,
2180 x_party_site_id=>l_party_site_id,
2181 x_location_id => l_location_id);
2182
2183 x_party_site_id := l_party_site_id;
2184
2185 if(l_ret_stat <> 'S') THEN
2186 if(l_msg_count = 1) THEN
2187 x_exception_msg := l_msg_data;
2188 else
2189 for i in 1..l_msg_count loop
2190 x_exception_msg := x_exception_msg || fnd_msg_pub.get(p_msg_index=>i,p_encoded=>'F');
2191 end loop;
2192 end if;
2193 end if;
2194
2195 x_status := l_ret_stat;
2196
2197 EXCEPTION
2198 WHEN OTHERS THEN
2199 x_status := 'U';
2200 x_exception_msg := x_exception_msg ||'Exception when creating vendor site';
2201
2202 END insert_vendor_site;
2203
2204
2205 PROCEDURE update_vendor_site
2206 (
2207 p_vendor_site_code IN VARCHAR2,
2208 p_vendor_site_id IN NUMBER,
2209 p_org_id IN NUMBER,
2210 p_address_line1 IN VARCHAR2,
2211 p_address_line2 IN VARCHAR2,
2212 p_address_line3 IN VARCHAR2,
2213 p_address_line4 IN VARCHAR2,
2214 p_city IN VARCHAR2,
2215 p_state IN VARCHAR2,
2216 p_zip IN VARCHAR2,
2217 p_province IN VARCHAR2,
2218 p_country IN VARCHAR2,
2219 p_duns_number IN VARCHAR2,
2220 p_pay_site_flag IN VARCHAR2,
2221 p_hold_unvalidated_inv_flag IN VARCHAR2,
2222 p_hold_all_payments_flag IN VARCHAR2,
2223 p_us_phone IN VARCHAR2,
2224 p_fax IN VARCHAR2,
2225 p_email IN VARCHAR2,
2226 p_non_us_phone IN VARCHAR2,
2227 p_purchasing_site_flag IN VARCHAR2,
2228 x_party_site_id OUT NOCOPY NUMBER,
2229 x_status OUT NOCOPY VARCHAR2,
2230 x_exception_msg OUT NOCOPY VARCHAR2
2231 )
2232
2233 IS
2234 l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
2235 l_party_site_id number;
2236 l_version number;
2237 l_ret_stat varchar2(20);
2238 l_msg_count number;
2239 l_msg_data varchar2(400);
2240 l_location_id number;
2241 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
2242 l_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
2243 l_object_version_number NUMBER;
2244 BEGIN
2245
2246 l_version := 1.0;
2247 l_vendor_site_rec.VENDOR_SITE_ID := p_vendor_site_id;
2248 l_vendor_site_rec.org_id := p_org_id;
2249 l_vendor_site_rec.HOLD_ALL_PAYMENTS_FLAG := p_hold_all_payments_flag;
2250 l_vendor_site_rec.HOLD_UNMATCHED_INVOICES_FLAG := p_hold_unvalidated_inv_flag;
2251 l_vendor_site_rec.HOLD_FUTURE_PAYMENTS_FLAG := p_hold_unvalidated_inv_flag;
2252 l_vendor_site_rec.PURCHASING_SITE_FLAG := p_purchasing_site_flag;
2253
2254 l_location_rec.ADDRESS1 := p_address_line1;
2255 l_location_rec.ADDRESS2 := p_address_line2;
2256 l_location_rec.ADDRESS3 := p_address_line3;
2257 l_location_rec.ADDRESS4 := p_address_line4;
2258 l_location_rec.city := p_city;
2259 l_location_rec.state := p_state;
2260 l_location_rec.postal_code := p_zip;
2261 l_location_rec.province := p_province;
2262 l_location_rec.country := p_country;
2263
2264 l_party_site_rec.duns_number_c := p_duns_number;
2265
2266 -- Added for bug 6348043
2267 IF(p_pay_site_flag IS NOT NULL) THEN
2268 IF p_us_phone IS NOT NULL THEN
2269 l_vendor_site_rec.area_code := SUBSTR(p_us_phone,1,3);
2270 l_vendor_site_rec.phone := SUBSTR(p_us_phone,4,7);
2271 ELSIF (p_us_phone IS NULL AND p_non_us_phone IS NOT NULL) THEN
2272 l_vendor_site_rec.phone := p_non_us_phone;
2273 END IF;
2274 l_vendor_site_rec.fax_area_code := SUBSTR(p_fax,1,3);
2275 l_vendor_site_rec.fax := SUBSTR(p_fax,4,7);
2276 l_vendor_site_rec.email_address := p_email;
2277 END IF;
2278
2279 x_status := 'S';
2280
2281 --Bug 6519638
2282 l_vendor_site_rec.duns_number := p_duns_number;
2283 l_vendor_site_rec.pay_site_flag := p_pay_site_flag;
2284
2285 AP_VENDOR_PUB_PKG.update_vendor_site(
2286 p_api_version => l_version,
2287 p_init_msg_list => FND_API.G_TRUE,
2288 p_commit=> FND_API.G_FALSE,
2289 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
2290 x_return_status => l_ret_stat,
2291 x_msg_count=>l_msg_count,
2292 x_msg_data=>l_msg_data,
2293 p_vendor_site_rec=>l_vendor_site_rec,
2294 p_vendor_site_id=>p_vendor_site_id,
2295 p_calling_prog=>'CCRImport'); --Bug 6519638
2296
2297
2298 if(l_ret_stat <> 'S') THEN
2299 x_status := l_ret_stat;
2300 if(l_msg_count = 1) THEN
2301 x_exception_msg := l_msg_data;
2302 else
2303 for i in 1..l_msg_count loop
2304 x_exception_msg := x_exception_msg || fnd_msg_pub.get(p_msg_index=>i,p_encoded=>'F');
2305 end loop;
2306 end if;
2307 end if;
2308
2309 -- Get the location and party site information
2310 BEGIN
2311
2312 SELECT party_site_id,location_id
2313 INTO l_party_site_id,l_location_id
2314 FROM ap_supplier_sites_all
2315 WHERE vendor_site_id = p_vendor_site_id;
2316
2317 x_party_site_id := l_party_site_id;
2318
2319 EXCEPTION
2320 WHEN OTHERS THEN
2321 x_status := 'U';
2322 x_exception_msg := x_exception_msg ||'Exception when getting information for vendor site';
2323 END;
2324
2325
2326 -- Update the location information
2327
2328 BEGIN
2329
2330
2331 select object_version_number
2332 into l_object_version_number
2333 from hz_locations
2334 where location_id = l_location_id;
2335
2336
2337 l_location_rec.location_id := l_location_id;
2338
2339 hz_location_v2pub.update_location(
2340 FND_API.G_TRUE,
2341 l_location_rec,
2342 l_object_version_number,
2343 l_ret_stat,
2344 l_msg_count,
2345 l_msg_data);
2346
2347 if(l_ret_stat <> 'S') THEN
2348 x_status := l_ret_stat;
2349 if(l_msg_count = 1) THEN
2350 x_exception_msg := x_exception_msg || l_msg_data;
2351 else
2352 for i in 1..l_msg_count loop
2353 x_exception_msg := x_exception_msg || fnd_msg_pub.get(p_msg_index=>i,p_encoded=>'F');
2354 end loop;
2355 end if;
2356 end if;
2357
2358 EXCEPTION
2359 WHEN OTHERS THEN
2360 x_status := 'U';
2361 x_exception_msg :=x_exception_msg || 'Exception when getting information for location';
2362 END;
2363
2364
2365 -- Update party site information
2366
2367 BEGIN
2368
2369 l_party_site_rec.party_site_id := l_party_site_id;
2370
2371 select object_version_number
2372 into l_object_version_number
2373 from hz_party_sites
2374 where party_site_id = l_party_site_id;
2375
2376
2377 hz_party_site_v2pub.update_party_site(
2378 FND_API.G_TRUE,
2379 l_party_site_rec,
2380 l_object_version_number,
2381 l_ret_stat,
2382 l_msg_count,
2383 l_msg_data);
2384
2385 if(l_ret_stat <> 'S') THEN
2386 x_status := l_ret_stat;
2387 if(l_msg_count = 1) THEN
2388 x_exception_msg := x_exception_msg || l_msg_data;
2389 else
2390 for i in 1..l_msg_count loop
2391 x_exception_msg := x_exception_msg || fnd_msg_pub.get(p_msg_index=>i,p_encoded=>'F');
2392 end loop;
2393 end if;
2394 end if;
2395
2396 EXCEPTION
2397 WHEN OTHERS THEN
2398 x_status := 'U';
2399 x_exception_msg :=x_exception_msg || 'Exception when getting information for party site';
2400 END;
2401
2402 END update_vendor_site;
2403
2404
2405 PROCEDURE create_bank_account
2406 (
2407 p_created_bank_id IN NUMBER,
2408 p_created_bank_branch_id IN NUMBER,
2409 p_bank_name IN VARCHAR2,
2410 p_branch_name IN VARCHAR2,
2411 p_bank_num IN VARCHAR2,
2412 p_eft_user_num IN VARCHAR2,
2413 p_inst_type IN VARCHAR2,
2414 p_bank_branch_type IN VARCHAR2,
2415 p_bank_acct_name IN VARCHAR2,
2416 p_bank_acct_num IN VARCHAR2,
2417 p_currency_code IN VARCHAR2,
2418 p_bank_account_type IN VARCHAR2,
2419 p_country_code IN VARCHAR2,
2420 x_bank_id OUT NOCOPY NUMBER,
2421 x_bank_branch_id OUT NOCOPY NUMBER,
2422 x_bank_account_id OUT NOCOPY NUMBER,
2423 x_return_status OUT NOCOPY VARCHAR2
2424 )
2425 IS
2426 l_api_version CONSTANT NUMBER := 1.0;
2427 l_return_status VARCHAR2(1);
2428 l_msg_count NUMBER;
2429 l_msg_data VARCHAR2(2000);
2430 l_bank_id NUMBER;
2431 l_start_date DATE;
2432 l_end_date DATE;
2433 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2434 l_bank_rec IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
2435 l_bank_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
2436 l_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
2437 l_bank_branch_id NUMBER;
2438 l_bank_account_id NUMBER;
2439 l_object_version_number NUMBER;
2440 BANK_EXCEPTION EXCEPTION;
2441 BEGIN
2442
2443 x_return_status := FND_API.G_RET_STS_SUCCESS;
2444
2445 if(nvl(p_created_bank_branch_id,0)=0) THEN
2446
2447 -- Creation of Bank
2448 -- Check if bank exists
2449
2450 IBY_EXT_BANKACCT_PUB.check_bank_exist(
2451 p_api_version => l_api_version,
2452 p_init_msg_list => FND_API.G_FALSE,
2453 p_country_code=> p_country_code,
2454 p_bank_name =>p_bank_name,
2455 p_bank_number => p_bank_num,
2456 x_return_status => l_return_status,
2457 x_msg_count => l_msg_count,
2458 x_msg_data => l_msg_data,
2459 x_bank_id => l_bank_id,
2460 x_end_date => l_end_date,
2461 x_response => l_response);
2462
2463 if(l_bank_id IS NOT NULL) THEN
2464 if(l_end_date IS NOT NULL) THEN
2465 IBY_EXT_BANKACCT_PUB.set_bank_end_date(
2466 p_api_version => l_api_version,
2467 p_init_msg_list => FND_API.G_FALSE,
2468 p_bank_id => l_bank_id,
2469 p_end_date => null,
2470 x_return_status => l_return_status,
2471 x_msg_count => l_msg_count,
2472 x_msg_data => l_msg_data,
2473 x_response => l_response);
2474
2475 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2476 RAISE BANK_EXCEPTION;
2477 end if;
2478 end if; -- l_end_date
2479 ELSE -- l_bank_id
2480
2481 l_bank_rec.bank_name := p_bank_name;
2482 l_bank_rec.institution_type := p_inst_type;
2483 l_bank_rec.country_code := p_country_code;
2484
2485 IBY_EXT_BANKACCT_PUB.create_ext_bank(
2486 p_api_version => l_api_version,
2487 p_init_msg_list => FND_API.G_FALSE,
2488 p_ext_bank_rec => l_bank_rec,
2489 x_bank_id => l_bank_id,
2490 x_return_status => l_return_status,
2491 x_msg_count => l_msg_count,
2492 x_msg_data => l_msg_data,
2493 x_response => l_response);
2494
2495 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2496 RAISE BANK_EXCEPTION;
2497 end if;
2498 END IF;
2499
2500 if(l_bank_id IS NULL) THEN
2501 RAISE BANK_EXCEPTION;
2502 end if;
2503
2504 -- Creation of Bank Branch
2505 -- Check if Bank Branch Exists
2506
2507 IBY_EXT_BANKACCT_PUB.check_ext_bank_branch_exist(
2508 p_api_version => l_api_version,
2509 p_init_msg_list => FND_API.G_FALSE,
2510 p_bank_id => l_bank_id,
2511 p_branch_name => p_branch_name,
2512 p_branch_number => p_bank_num,
2513 x_return_status => l_return_status,
2514 x_msg_count => l_msg_count,
2515 x_msg_data => l_msg_data,
2516 x_branch_id => l_bank_branch_id,
2517 x_end_date => l_end_date,
2518 x_response => l_response);
2519
2520 if(l_bank_branch_id IS NOT NULL) THEN
2521 if(l_end_date IS NOT NULL) THEN
2522 IBY_EXT_BANKACCT_PUB.set_ext_bank_branch_end_date(
2523 p_api_version => l_api_version,
2524 p_init_msg_list => FND_API.G_FALSE,
2525 p_branch_id => l_bank_branch_id,
2526 p_end_date => null,
2527 x_return_status => l_return_status,
2528 x_msg_count => l_msg_count,
2529 x_msg_data => l_msg_data,
2530 x_response => l_response);
2531
2532 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2533 RAISE BANK_EXCEPTION;
2534 end if;
2535 end if; -- l_end_date
2536
2537 ELSE -- l_bank_branch_id
2538
2539 l_bank_branch_rec.bank_party_id := l_bank_id;
2540 l_bank_branch_rec.branch_name := p_branch_name;
2541 l_bank_branch_rec.branch_number := p_bank_num;
2542 l_bank_branch_rec.branch_type := p_bank_branch_type;
2543
2544 if(p_eft_user_num IS NOT NULL) THEN
2545 l_bank_branch_rec.eft_number := p_eft_user_num;
2546 end if;
2547
2548 IBY_EXT_BANKACCT_PUB.create_ext_bank_branch(
2549 p_api_version => l_api_version,
2550 p_init_msg_list => FND_API.G_FALSE,
2551 p_ext_bank_branch_rec => l_bank_branch_rec,
2552 x_branch_id => l_bank_branch_id,
2553 x_return_status => l_return_status,
2554 x_msg_count => l_msg_count,
2555 x_msg_data => l_msg_data,
2556 x_response => l_response);
2557
2558 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2559 RAISE BANK_EXCEPTION;
2560 end if;
2561 END IF; -- l_bank_branch_id
2562
2563 if(l_bank_branch_id IS NULL) THEN
2564 RAISE BANK_EXCEPTION;
2565 end if;
2566
2567 ELSE
2568 l_bank_id := p_created_bank_id;
2569 l_bank_branch_id := p_created_bank_branch_id;
2570 END IF;
2571
2572 -- Create Bank Account
2573
2574 IBY_EXT_BANKACCT_PUB.check_ext_acct_exist(
2575 p_api_version => l_api_version,
2576 p_init_msg_list => FND_API.G_FALSE,
2577 p_bank_id => l_bank_id,
2578 p_branch_id => l_bank_branch_id,
2579 p_acct_number => p_bank_acct_num,
2580 p_acct_name => p_bank_acct_name,
2581 p_currency => p_currency_code,
2582 p_country_code => p_country_code,
2583 x_acct_id => l_bank_account_id,
2584 x_start_date => l_start_date,
2585 x_end_date => l_end_date,
2586 x_return_status => l_return_status,
2587 x_msg_count => l_msg_count,
2588 x_msg_data => l_msg_data,
2589 -- x_branch_id => l_bank_branch_id,
2590 -- x_end_date => l_end_date,
2591 x_response => l_response);
2592
2593 if(l_bank_account_id IS NOT NULL) THEN
2594 if(l_end_date IS NOT NULL or l_start_date IS NOT NULL) THEN
2595
2596 l_object_version_number := -1;
2597
2598 IBY_EXT_BANKACCT_PUB.set_ext_bank_acct_dates(
2599 p_api_version => l_api_version,
2600 p_init_msg_list => FND_API.G_FALSE,
2601 p_acct_id => l_bank_account_id,
2602 p_start_date => l_start_date,
2603 p_end_date => null,
2604 p_object_version_number => l_object_version_number,
2605 x_return_status => l_return_status,
2606 x_msg_count => l_msg_count,
2607 x_msg_data => l_msg_data,
2608 x_response => l_response);
2609
2610 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2611 RAISE BANK_EXCEPTION;
2612 end if;
2613 end if; -- l_end_date
2614
2615 ELSE -- l_bank_account_id
2616
2617 l_bank_acct_rec.country_code := p_country_code;
2618 l_bank_acct_rec.branch_id := l_bank_branch_id;
2619 l_bank_acct_rec.bank_id := l_bank_id;
2620 l_bank_acct_rec.bank_account_name := p_bank_acct_name;
2621 l_bank_acct_rec.bank_account_num := p_bank_acct_num;
2622 l_bank_acct_rec.currency := p_currency_code;
2623 l_bank_acct_rec.multi_currency_allowed_flag:= 'N';
2624 --Bug 7038108
2625 IF p_bank_account_type = 'C' THEN
2626 l_bank_acct_rec.acct_type := 'Checking';
2627 ELSIF
2628 p_bank_account_type = 'S' THEN
2629 l_bank_acct_rec.acct_type := 'Savings';
2630 END IF;
2631
2632 l_bank_acct_rec.acct_owner_party_id := -99;
2633
2634 if(p_eft_user_num IS NOT NULL) THEN
2635 l_bank_branch_rec.eft_number := p_eft_user_num;
2636 end if;
2637
2638 IBY_EXT_BANKACCT_PUB.create_ext_bank_acct(
2639 p_api_version => l_api_version,
2640 p_init_msg_list => FND_API.G_FALSE,
2641 p_ext_bank_acct_rec => l_bank_acct_rec,
2642 x_acct_id => l_bank_account_id,
2643 x_return_status => l_return_status,
2644 x_msg_count => l_msg_count,
2645 x_msg_data => l_msg_data,
2646 x_response => l_response);
2647
2648 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2649 RAISE BANK_EXCEPTION;
2650 end if;
2651 END IF; -- l_bank_account_id
2652
2653 if(l_bank_account_id IS NULL) THEN
2654 RAISE BANK_EXCEPTION;
2655 end if;
2656
2657 x_bank_id := l_bank_id;
2658 x_bank_branch_id := l_bank_branch_id;
2659 x_bank_account_id := l_bank_account_id;
2660
2661 EXCEPTION
2662 WHEN BANK_EXCEPTION THEN
2663 x_return_status := fnd_api.g_ret_sts_error;
2664
2665 -- Print the messages from l_msg_data in log file
2666 WHEN OTHERS THEN
2667 x_return_status := fnd_api.g_ret_sts_unexp_error;
2668 -- Print SQLERRM in log file
2669
2670 END create_bank_account;
2671
2672 PROCEDURE update_bank_account
2673 (
2674 p_bank_account_id NUMBER,
2675 p_bank_account_type VARCHAR2,
2676 x_return_status OUT NOCOPY VARCHAR2
2677 )
2678 IS
2679 l_bank_account_type iby_ext_bank_accounts.bank_account_type%TYPE;
2680 BEGIN
2681 --Bug 7038108
2682 IF p_bank_account_type = 'C' THEN
2683 l_bank_account_type := 'Checking';
2684 ELSIF
2685 p_bank_account_type = 'S' THEN
2686 l_bank_account_type := 'Savings';
2687 END IF;
2688
2689 UPDATE iby_ext_bank_accounts
2690 set BANK_ACCOUNT_TYPE = l_bank_account_type
2691 where EXT_BANK_ACCOUNT_ID = p_bank_account_id;
2692
2693 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2694 EXCEPTION
2695 WHEN OTHERS THEN
2696 x_return_status := fnd_api.g_ret_sts_unexp_error;
2697 -- Print SQLERRM in log file
2698 fv_utility.log_mesg('When others error in update_bank_account.');
2699 END update_bank_account;
2700
2701
2702 PROCEDURE process_bank_account_uses
2703 (
2704 p_account_uses_upd_flag IN VARCHAR2,
2705 p_vendor_id IN NUMBER,
2706 p_federal_vendor IN VARCHAR2,
2707 p_valid_bank_info IN VARCHAR2,
2708 p_old_bank_account_id IN NUMBER,
2709 p_pay_site_id IN NUMBER,
2710 p_file_date IN DATE,
2711 p_new_bank_account_id IN NUMBER,
2712 p_account_uses_insert_flag IN VARCHAR2,
2713 p_org_id IN NUMBER
2714 )
2715 IS
2716 l_api_version CONSTANT NUMBER:= 1.0;
2717 l_return_status VARCHAR2(1);
2718 l_msg_count NUMBER;
2719 l_msg_data VARCHAR2(2000);
2720 l_user_id NUMBER;
2721 l_login_id NUMBER;
2722 l_uses_reln_exists VARCHAR2(1);
2723 l_assign_id NUMBER;
2724 l_party_id NUMBER;
2725 l_party_site_id NUMBER;
2726 l_payee_id NUMBER;
2727 l_payee_level VARCHAR2(30);
2728 l_vendor_type_lookup_code VARCHAR2(30);
2729 l_end_date DATE;
2730 l_payee IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_Rec_Type;
2731 l_assignment IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
2732 l_assignment_tab IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_tbl_type;
2733 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2734 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
2735
2736 BANK_USES_EXCEPTION EXCEPTION;
2737
2738 BEGIN
2739 l_user_id := fnd_global.user_id;
2740 l_login_id := fnd_global.login_id;
2741
2742 SELECT party_id,vendor_type_lookup_code
2743 INTO l_party_id,l_vendor_type_lookup_code
2744 FROM ap_suppliers
2745 WHERE vendor_id=p_vendor_id;
2746
2747 SELECT party_site_id INTO l_party_site_id
2748 FROM ap_supplier_sites_all
2749 WHERE vendor_site_id = p_pay_site_id;
2750
2751 if(nvl(l_party_id,0) <> 0 and nvl(p_new_bank_account_id,0) <> 0) THEN
2752 add_owner_party(l_party_id,p_new_bank_account_id);
2753 end if;
2754
2755
2756
2757 IF l_vendor_type_lookup_code = 'EMPLOYEE' THEN
2758 l_payee.payment_function := 'EMPLOYEE_EXP';
2759 ELSE
2760 l_payee.payment_function := 'PAYABLES_DISB';
2761 END IF;
2762
2763 l_payee.party_id := l_party_id;
2764 l_payee.Org_Type := 'OPERATING_UNIT';
2765 l_payee.Org_Id := p_org_id;
2766 l_payee.party_site_Id := l_party_site_id;
2767 l_payee.Supplier_Site_Id := p_pay_site_id;
2768
2769
2770 get_payee_id
2771 (
2772 p_payee_context => l_payee,
2773 x_payee_id => l_payee_id
2774 );
2775
2776 IF (l_payee_id IS NULL) THEN
2777 raise BANK_USES_EXCEPTION;
2778 END IF;
2779
2780 IF (p_account_uses_upd_flag = 'Y' AND nvl(p_vendor_id,0)<>0
2781 AND (p_federal_vendor='N' OR p_valid_bank_info='Y')) THEN
2782
2783 l_assignment.Instrument.instrument_Type := 'BANKACCOUNT';
2784 l_assignment.Instrument.instrument_Id := p_old_bank_account_id;
2785 l_assignment.end_date := p_file_date-1;
2786
2787
2788 IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment(
2789 p_api_version => l_api_version,
2790 p_init_msg_list => FND_API.G_FALSE,
2791 p_commit => FND_API.G_FALSE,
2792 x_return_status => l_return_status,
2793 x_msg_count => l_msg_count,
2794 x_msg_data => l_msg_data,
2795 p_payee => l_payee,
2796 p_assignment_attribs => l_assignment,
2797 x_assign_id => l_assign_id,
2798 x_response => l_response);
2799
2800 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2801 RAISE BANK_USES_EXCEPTION;
2802 end if;
2803 END IF;
2804
2805 IF(nvl(p_old_bank_account_id,0)=0 AND nvl(p_vendor_id,0)<>0
2806 AND nvl(p_pay_site_id,0)<>0 AND
2807 (p_valid_bank_info='Y' OR p_federal_vendor='N')) THEN
2808
2809 UPDATE iby_pmt_instr_uses_all
2810 SET END_DATE=nvl(START_DATE,p_file_date-1),
2811 last_update_date=sysdate,
2812 last_updated_by=l_user_id,
2813 last_update_login=l_login_id
2814 WHERE instrument_id <> p_new_bank_account_id
2815 AND EXT_PMT_PARTY_ID = l_payee_id
2816 AND (END_DATE IS NULL OR END_DATE>(p_file_date-1));
2817 END IF;
2818
2819 IF(nvl(p_vendor_id,0)<>0 AND
2820 (p_account_uses_insert_flag = 'I' OR p_account_uses_insert_flag = 'U') AND
2821 (p_valid_bank_info='Y' OR p_federal_vendor='N')) THEN
2822
2823 l_uses_reln_exists := 'N';
2824 IF(p_account_uses_insert_flag = 'U') THEN
2825
2826 IBY_DISBURSEMENT_SETUP_PUB.Get_Payee_Instr_Assignments(
2827 p_api_version => l_api_version,
2828 p_init_msg_list => FND_API.G_FALSE,
2829 x_return_status => l_return_status,
2830 x_msg_count => l_msg_count,
2831 x_msg_data => l_msg_data,
2832 p_payee => l_payee,
2833 x_assignments => l_assignment_tab,
2834 x_response => l_response);
2835
2836 if(l_assignment_tab.count > 0) THEN
2837
2838 FOR i IN l_assignment_tab.FIRST .. l_assignment_tab.LAST
2839 LOOP
2840 IF l_assignment_tab(i).Instrument.Instrument_Id = p_new_bank_account_id
2841 and l_assignment_tab(i).Instrument.instrument_Type = 'BANKACCOUNT' THEN
2842
2843 l_uses_reln_exists := 'Y';
2844 if(l_assignment_tab(i).end_date IS NULL) THEN
2845 l_end_date := NULL;
2846 l_assign_id := l_assignment_tab(i).Assignment_Id;
2847 EXIT;
2848 elsif (l_end_date IS NULL OR trunc(l_assignment_tab(i).end_date) > trunc(l_end_date)) THEN
2849 l_end_date := l_assignment_tab(i).end_date;
2850 l_assign_id := l_assignment_tab(i).Assignment_Id;
2851 end if;
2852 END IF;
2853
2854 END LOOP;
2855
2856 END IF;
2857
2858 if(l_uses_reln_exists = 'Y') THEN
2859
2860 IF(l_end_date IS NOT NULL) THEN
2861 UPDATE iby_pmt_instr_uses_all
2862 SET end_date=null,
2863 start_date=p_file_date,
2864 last_update_date = sysdate,
2865 last_updated_by = l_user_id,
2866 last_update_login=l_user_id
2867 WHERE (l_assign_id IS NOT NULL AND INSTRUMENT_PAYMENT_USE_ID=l_assign_id);
2868 END IF;
2869
2870 end if;
2871 END IF;
2872
2873 IF (l_uses_reln_exists = 'N' OR p_account_uses_insert_flag = 'I') THEN
2874
2875 l_assignment.Instrument.instrument_Type := 'BANKACCOUNT';
2876 l_assignment.Instrument.instrument_Id := p_new_bank_account_id;
2877 l_assignment.end_date := null;
2878
2879 IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment(
2880 p_api_version => l_api_version,
2881 p_init_msg_list => FND_API.G_FALSE,
2882 p_commit => FND_API.G_FALSE,
2883 x_return_status => l_return_status,
2884 x_msg_count => l_msg_count,
2885 x_msg_data => l_msg_data,
2886 p_payee => l_payee,
2887 p_assignment_attribs => l_assignment,
2888 x_assign_id => l_assign_id,
2889 x_response => l_response);
2890
2891
2892 if(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2893 RAISE BANK_USES_EXCEPTION;
2894 end if;
2895 END IF;
2896 END IF;
2897
2898 EXCEPTION
2899 WHEN OTHERS THEN
2900 RAISE ;
2901
2902 END process_bank_account_uses;
2903
2904 END FV_CCR_UTIL_PVT;