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