[Home] [Help]
PACKAGE BODY: APPS.FV_CCR_GRP
Source
1 PACKAGE BODY FV_CCR_GRP as
2 /* $Header: FVGACCRB.pls 120.0.12000000.2 2007/09/28 15:03:33 sasukuma ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='FV_CCR_GRP';
5
6 PROCEDURE FV_IS_CCR
7 ( p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2 ,
9 p_object_id IN NUMBER,
10 p_object_type IN VARCHAR2,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 x_ccr_id OUT NOCOPY NUMBER,
15 x_out_status OUT NOCOPY VARCHAR2,
16 x_error_code OUT NOCOPY NUMBER
17 )
18 IS
19 l_api_name CONSTANT VARCHAR2(30) := 'FV_IS_CCR';
20 l_api_version CONSTANT NUMBER := 1.0;
21 BEGIN
22 IF NOT FND_API.Compatible_API_Call (l_api_version,
23 p_api_version,
24 l_api_name,
25 G_PKG_NAME )
26 THEN
27 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
28 END IF;
29
30 -- Initialize message list if p_init_msg_list is set to TRUE.
31 IF FND_API.to_Boolean( nvl(p_init_msg_list,FND_API.G_FALSE) ) THEN
32 FND_MSG_PUB.initialize;
33 END IF;
34 -- Initialize API return status to success
35
36 x_return_status := FND_API.G_RET_STS_SUCCESS;
37
38
39 x_out_status := FND_API.G_FALSE;
40 IF(p_object_type = 'S') THEN
41 SELECT ccr_id
42 INTO x_ccr_id
43 FROM fv_ccr_vendors
44 WHERE nvl(vendor_id,-99) = p_object_id
45 AND plus_four IS NULL;
46 ELSIF(p_object_type = 'B') THEN
47 SELECT ccr_id
48 INTO x_ccr_id
49 FROM fv_ccr_vendors
50 WHERE nvl(bank_branch_id,-99) = p_object_id;
51 ELSIF(p_object_type = 'T') THEN
52 SELECT ccr_id
53 INTO x_ccr_id
54 FROM fv_ccr_orgs fcorg
55 WHERE (nvl(fcorg.pay_site_id,-99)=p_object_id
56 OR nvl(fcorg.main_address_site_id,-99)=p_object_id);
57 ELSIF(p_object_type = 'A') THEN
58 SELECT ccr_id
59 INTO x_ccr_id
60 FROM fv_ccr_orgs fcorg
61 WHERE nvl(bank_account_id,-99) = p_object_id;
62 END IF;
63 x_out_status := FND_API.G_TRUE;
64
65 FND_MSG_PUB.Count_And_Get
66 (
67 p_count => x_msg_count ,
68 p_data => x_msg_data
69 );
70
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74
75
76 WHEN TOO_MANY_ROWS THEN
77 x_error_code := 1;
78 x_out_status := FND_API.G_TRUE;
79 IF(p_object_type IN ('S','B')) THEN
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81 ELSE
82 x_return_status := FND_API.G_RET_STS_ERROR ;
83 END IF;
84
85 FND_MESSAGE.SET_NAME('FV', 'FV_CCR_GRP_TOO_MANY_ROWS');
86 FND_MSG_PUB.ADD;
87
88
89 FND_MSG_PUB.Count_And_Get
90 ( p_count => x_msg_count ,
91 p_data => x_msg_data
92 );
93
94 WHEN FND_API.G_EXC_ERROR THEN
95
96 x_return_status := FND_API.G_RET_STS_ERROR ;
97
98 FND_MSG_PUB.Count_And_Get
99 ( p_count => x_msg_count ,
100 p_data => x_msg_data
101 );
102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
103
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
105 FND_MSG_PUB.Count_And_Get
106 ( p_count => x_msg_count ,
107 p_data => x_msg_data
108 );
109
110 WHEN OTHERS THEN
111
112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
113 IF FND_MSG_PUB.Check_Msg_Level
114 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
115 THEN
116 FND_MSG_PUB.Add_Exc_Msg
117 ( G_PKG_NAME ,
118 l_api_name
119 );
120 END IF;
121 FND_MSG_PUB.Count_And_Get
122 ( p_count => x_msg_count ,
123 p_data => x_msg_data
124 );
125 END FV_IS_CCR;
126
127
128
129
130 PROCEDURE FV_CCR_REG_STATUS
131 ( p_api_version IN NUMBER,
132 p_init_msg_list IN VARCHAR2 ,
133 p_vendor_site_id IN NUMBER,
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER,
136 x_msg_data OUT NOCOPY VARCHAR2,
137 x_ccr_status OUT NOCOPY VARCHAR2,
138 x_error_code OUT NOCOPY NUMBER
139
140 )
141 IS
142 l_api_name CONSTANT VARCHAR2(30) := 'FV_CCR_REG_STATUS';
143 l_api_version CONSTANT NUMBER := 1.0;
144
145
146 BEGIN
147
148 IF NOT FND_API.Compatible_API_Call (l_api_version,
149 p_api_version,
150 l_api_name,
151 G_PKG_NAME )
152 THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155
156 -- Initialize message list if p_init_msg_list is set to TRUE.
157 IF FND_API.to_Boolean( nvl(p_init_msg_list,FND_API.G_FALSE) ) THEN
158 FND_MSG_PUB.initialize;
159 END IF;
160 -- Initialize API return status to success
161
162 x_return_status := FND_API.G_RET_STS_SUCCESS;
163 SELECT fcv.ccr_status
164 INTO x_ccr_status
165 FROM fv_ccr_vendors fcv, fv_ccr_orgs fco
166 WHERE fcv.CCR_ID = fco.CCR_ID
167 AND (nvl(fco.pay_site_id,-99)=p_vendor_site_id
168 OR nvl(fco.main_address_site_id,-99)=p_vendor_site_id);
169
170
171 FND_MSG_PUB.Count_And_Get
172 (
173 p_count => x_msg_count ,
174 p_data => x_msg_data
175 );
176
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN
179 x_error_code := 2;
180 FND_MESSAGE.SET_NAME('FV','FV_SITE_NOT_CCR');
181 FND_MSG_PUB.ADD;
182 FND_MSG_PUB.Count_And_Get
183 (
184 p_count => x_msg_count ,
185 p_data => x_msg_data
186 );
187
188 x_return_status := FND_API.G_RET_STS_ERROR ;
189 WHEN TOO_MANY_ROWS THEN
190 FND_MESSAGE.SET_NAME('FV', 'FV_CCR_GRP_TOO_MANY_ROWS');
191 FND_MSG_PUB.ADD;
192 x_error_code := 1;
193 x_return_status := FND_API.G_RET_STS_ERROR ;
194 FND_MSG_PUB.Count_And_Get
195 ( p_count => x_msg_count ,
196 p_data => x_msg_data
197 );
198 WHEN FND_API.G_EXC_ERROR THEN
199
200 x_return_status := FND_API.G_RET_STS_ERROR ;
201 FND_MSG_PUB.Count_And_Get
202 ( p_count => x_msg_count ,
203 p_data => x_msg_data
204 );
205 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
206
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
208 FND_MSG_PUB.Count_And_Get
209 ( p_count => x_msg_count ,
210 p_data => x_msg_data
211 );
212 WHEN OTHERS THEN
213
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
215 IF FND_MSG_PUB.Check_Msg_Level
216 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
217 THEN
218 FND_MSG_PUB.Add_Exc_Msg
219 ( G_PKG_NAME ,
220 l_api_name
221 );
222 END IF;
223 FND_MSG_PUB.Count_And_Get
224 ( p_count => x_msg_count ,
225 p_data => x_msg_data
226 );
227 END FV_CCR_REG_STATUS;
228
229 PROCEDURE IS_VENDOR_FEDERAL
230 (
231 p_api_version IN NUMBER,
232 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
233 p_vendor_id IN NUMBER,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2,
237 x_federal OUT NOCOPY VARCHAR2,
238 x_error_code OUT NOCOPY NUMBER
239 )
240 IS
241 l_api_name CONSTANT VARCHAR2(30) := 'IS_VENDOR_FEDERAL';
242 l_api_version CONSTANT NUMBER := 1.0;
243 l_error VARCHAR2(1024);
244 l_vendor_type po_vendors.vendor_type_lookup_code%TYPE;
245 BEGIN
246 -- Check for call compatibility.
247 IF NOT fnd_api.compatible_api_call
248 (
249 p_current_version_number => l_api_version,
250 p_caller_version_number => p_api_version,
251 p_api_name => l_api_name,
252 p_pkg_name => g_pkg_name
253 )
254 THEN
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256 END IF;
257
258 -- Initialize API message list if necessary.
259 -- Initialize message list if p_init_msg_list is set to TRUE.
260 IF fnd_api.to_boolean(p_init_msg_list) THEN
261 fnd_msg_pub.initialize;
262 END IF;
263
264 x_return_status := FND_API.G_RET_STS_SUCCESS;
265
266 SELECT vendor_type_lookup_code
267 INTO l_vendor_type
268 FROM po_vendors
269 WHERE vendor_id = p_vendor_id;
270
271 IF (l_vendor_type = 'FEDERAL') THEN
272 x_federal := 'Y';
273 ELSE
274 x_federal := 'N';
275 END IF;
276
277 fnd_msg_pub.count_and_get
278 (
279 p_count => x_msg_count,
280 p_data => x_msg_data
281 );
282
283 EXCEPTION
284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 fnd_msg_pub.count_and_get
287 (
288 p_count => x_msg_count,
289 p_data => x_msg_data
290 );
291 WHEN NO_DATA_FOUND THEN
292 x_error_code := 2;
293 fnd_message.set_name('FV','FV_CCR_INVALID_VENDOR_ID');
294 fnd_msg_pub.add;
295 fnd_msg_pub.count_and_get
296 (
297 p_count => x_msg_count,
298 p_data => x_msg_data
299 );
300 x_return_status := FND_API.G_RET_STS_ERROR ;
301 WHEN FND_API.G_EXC_ERROR THEN
302 x_return_status := FND_API.G_RET_STS_ERROR ;
303 fnd_msg_pub.count_and_get
304 (
305 p_count => x_msg_count,
306 p_data => x_msg_data
307 );
308 WHEN OTHERS THEN
309 l_error := SQLERRM;
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
311 IF fnd_msg_pub.check_msg_level
312 (
313 p_message_level => fnd_msg_pub.g_msg_lvl_unexp_error
314 )
315 THEN
316 fnd_msg_pub.add_exc_msg
317 (
318 p_pkg_name => g_pkg_name,
319 p_procedure_name => l_api_name,
320 p_error_text => l_error
321 );
322 END IF;
323 fnd_msg_pub.count_and_get
324 (
325 p_count => x_msg_count,
326 p_data => x_msg_data
327 );
328 END IS_VENDOR_FEDERAL;
329
330
331 PROCEDURE FV_IS_BANK_ACCOUNT_USES_CCR
332 ( p_api_version IN NUMBER,
333 p_init_msg_list IN VARCHAR2 ,
334 p_vendor_site_id IN NUMBER,
335 x_return_status OUT NOCOPY VARCHAR2,
336 x_msg_count OUT NOCOPY NUMBER,
337 x_msg_data OUT NOCOPY VARCHAR2,
338 x_out_status OUT NOCOPY VARCHAR2,
339 x_error_code OUT NOCOPY NUMBER
340 )
341 IS
342 l_api_name CONSTANT VARCHAR2(30) := 'FV_IS_BANK_ACCOUNT_USES_CCR';
343 l_api_version CONSTANT NUMBER := 1.0;
344 l_bank_account_id NUMBER;
345 BEGIN
346 l_bank_account_id := null;
347 IF NOT FND_API.Compatible_API_Call (l_api_version,
348 p_api_version,
349 l_api_name,
350 G_PKG_NAME )
351 THEN
352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
353 END IF;
354
355 -- Initialize message list if p_init_msg_list is set to TRUE.
356 IF FND_API.to_Boolean( nvl(p_init_msg_list,FND_API.G_FALSE) ) THEN
357 FND_MSG_PUB.initialize;
358 END IF;
359 -- Initialize API return status to success
360
361 x_return_status := FND_API.G_RET_STS_SUCCESS;
362
363
364 x_out_status := FND_API.G_FALSE;
365
366 SELECT bank_account_id
367 INTO l_bank_account_id
368 FROM fv_ccr_orgs
369 WHERE pay_site_id = p_vendor_site_id;
370
371 if(l_bank_account_id IS NULL) THEN
372 x_out_status := FND_API.G_FALSE;
373 else
374 x_out_status := FND_API.G_TRUE;
375 end if;
376
377
378
379
380 FND_MSG_PUB.Count_And_Get
381 (
382 p_count => x_msg_count ,
383 p_data => x_msg_data
384 );
385
386 EXCEPTION
387 WHEN NO_DATA_FOUND THEN
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 x_error_code := 2;
390
391
392 WHEN TOO_MANY_ROWS THEN
393 x_error_code := 1;
394 x_return_status := FND_API.G_RET_STS_SUCCESS;
395 FND_MESSAGE.SET_NAME('FV', 'FV_CCR_GRP_TOO_MANY_ROWS');
396 FND_MSG_PUB.ADD;
397
398
399 FND_MSG_PUB.Count_And_Get
400 ( p_count => x_msg_count ,
401 p_data => x_msg_data
402 );
403
404 WHEN FND_API.G_EXC_ERROR THEN
405
406 x_return_status := FND_API.G_RET_STS_ERROR ;
407
408 FND_MSG_PUB.Count_And_Get
409 ( p_count => x_msg_count ,
410 p_data => x_msg_data
411 );
412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
413
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
415 FND_MSG_PUB.Count_And_Get
416 ( p_count => x_msg_count ,
417 p_data => x_msg_data
418 );
419
420 WHEN OTHERS THEN
421
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
423 IF FND_MSG_PUB.Check_Msg_Level
424 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
425 THEN
426 FND_MSG_PUB.Add_Exc_Msg
427 ( G_PKG_NAME ,
428 l_api_name
429 );
430 END IF;
431 FND_MSG_PUB.Count_And_Get
432 ( p_count => x_msg_count ,
433 p_data => x_msg_data
434 );
435 END FV_IS_BANK_ACCOUNT_USES_CCR;
436 --------------------------------------------------------------------
437 FUNCTION SELECT_THIRD_PARTY
438 (
439 p_vendor_site_id NUMBER
440 ) RETURN VARCHAR2
441 IS
442 l_api_version number := 1.0;
443 l_msg_count number;
444 l_msg_data varchar2(5000);
445 l_ccr_status varchar2(2);
446 l_error_code number;
447 l_return_status varchar2(1);
448
449 BEGIN
450 fv_ccr_grp.fv_ccr_reg_status
451 (
452 p_api_version => l_api_version,
453 p_vendor_site_id => p_vendor_site_id,
454 x_return_status => l_return_status,
455 x_msg_count => l_msg_count,
456 x_msg_data => l_msg_data,
457 x_ccr_status => l_ccr_status,
458 x_error_code => l_error_code
459 );
460
461 IF (l_ccr_status = 'A') THEN
462 RETURN 'Y';
463 ELSIF (l_error_code = 2) THEN
464 RETURN 'Y';
465 ELSE
466 RETURN 'N';
467 END IF;
468 END;
469
470 --------------------------------------------------------------------
471
472 FUNCTION SELECT_BANK_ACCOUNT
473 (
474 p_bank_account_id IN NUMBER,
475 p_vendor_site_id NUMBER
476 ) RETURN NUMBER
477 IS
478 l_api_version number := 1.0;
479 l_msg_count number;
480 l_msg_data varchar2(5000);
481 l_ccr_status varchar2(2);
482 l_error_code number;
483 l_return_status varchar2(1);
484 l_bank_account_id NUMBER;
485
486 BEGIN
487 fv_ccr_grp.fv_ccr_reg_status
488 (
489 p_api_version => l_api_version,
490 p_vendor_site_id => p_vendor_site_id,
491 x_return_status => l_return_status,
492 x_msg_count => l_msg_count,
493 x_msg_data => l_msg_data,
494 x_ccr_status => l_ccr_status,
495 x_error_code => l_error_code
496 );
497
498 IF (l_ccr_status = 'A') THEN
499 BEGIN
500 SELECT bank_account_id
501 INTO l_bank_account_id
502 FROM fv_ccr_orgs fco
503 WHERE fco.pay_site_id = p_vendor_site_id;
504
505 IF (l_bank_account_id IS NOT NULL) THEN
506 RETURN l_bank_account_id;
507 ELSE
508 RETURN p_bank_account_id;
509 END IF;
510
511 EXCEPTION
512 WHEN NO_DATA_FOUND THEN
513 RETURN p_bank_account_id;
514 WHEN TOO_MANY_ROWS THEN
515 RETURN p_bank_account_id;
516 END;
517 ELSE
518 RETURN p_bank_account_id;
519 END IF;
520 NULL;
521 END;
522
523 -------------------------------------------
524
525 END FV_CCR_GRP;