1 PACKAGE BODY PA_CUSTOMERS_CONTACTS_UTILS AS
2 /* $Header: PARPCCUB.pls 120.5 2007/02/06 09:54:26 dthakker ship $ */
3
4
5 -- Global constant
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_CUSTOMERS_CONTACTS_UTILS';
7
8
9 -- API name : Check_Customer_Name_Or_Id
10 -- Type : Utility
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- p_customer_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
14 -- p_customer_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
15 -- p_check_id_flag IN VARCHAR2 Optional Default = 'A'
16 -- x_customer_id OUT NUMBER Required
17 -- x_return_status OUT VARCHAR2 Required
18 -- x_error_msg_code OUT VARCHAR2 Required
19
20 PROCEDURE CHECK_CUSTOMER_NAME_OR_ID
21 ( p_customer_id IN NUMBER := FND_API.G_MISS_NUM
22 ,p_customer_name IN VARCHAR2 := FND_API.G_MISS_CHAR
23 ,p_check_id_flag IN VARCHAR2 := 'A'
24 ,x_customer_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
25 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
26 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
27 )
28 IS
29 l_current_id NUMBER := NULL;
30 l_num_ids NUMBER := 0;
31 l_id_found_flag VARCHAR(1) := 'N';
32
33
34 CURSOR c_ids IS
35 SELECT customer_id
36 FROM pa_customers_v
37 WHERE upper(customer_name) = upper(p_customer_name) and status = 'A';
38 BEGIN
39 if (p_customer_id = FND_API.G_MISS_NUM) OR (p_customer_id is NULL) then
40 if (p_customer_name is not NULL) then
41 SELECT customer_id
42 INTO x_customer_id
43 FROM pa_customers_v
44 WHERE upper(customer_name) = upper(p_customer_name)
45 and status = 'A';
46 else
47 x_customer_id := NULL;
48 end if;
49
50 else
51 if p_check_id_flag = 'Y' then
52 SELECT customer_id
53 INTO x_customer_id
54 FROM pa_customers_v
55 WHERE customer_id = p_customer_id;
56
57 ELSIF (p_check_id_flag='N') THEN
58 x_customer_id := p_customer_id;
59
60
61 ELSIF (p_check_id_flag = 'A') THEN
62 IF (p_customer_name IS NULL) THEN
63 -- Return a null ID since the name is null.
64 x_customer_id := NULL;
65 ELSE
66
67 -- Find the ID which matches the Name passed
68 OPEN c_ids;
69 LOOP
70 FETCH c_ids INTO l_current_id;
71 EXIT WHEN c_ids%NOTFOUND;
72 IF (l_current_id = p_customer_id) THEN
73 l_id_found_flag := 'Y';
74 x_customer_id := p_customer_id;
75 END IF;
76 END LOOP;
77 l_num_ids := c_ids%ROWCOUNT;
78 CLOSE c_ids;
79
80 IF (l_num_ids = 0) THEN
81 -- No IDs for name
82 RAISE NO_DATA_FOUND;
83 ELSIF (l_num_ids = 1) THEN
84 -- Since there is only one ID for the name use it.
85 x_customer_id := l_current_id;
86 ELSIF (l_id_found_flag = 'N') THEN
87 -- More than one ID for the name and none of the IDs matched
88 -- the ID passed in.
89 RAISE TOO_MANY_ROWS;
90 END IF;
91 END IF;
92
93
94
95 else
96 x_customer_id := NULL;
97 end if;
98
99 end if;
100 x_return_status := FND_API.G_RET_STS_SUCCESS;
101 EXCEPTION
102 when NO_DATA_FOUND then
103 x_customer_id := NULL;
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 x_error_msg_code := 'PA_CUSTOMER_ID_INVALID';
106 when TOO_MANY_ROWS then
107 x_customer_id := NULL;
108 x_return_status := FND_API.G_RET_STS_ERROR;
109 x_error_msg_code := 'PA_CUST_NAME_NOT_UNIQUE';
110 when OTHERS then
111 x_customer_id := NULL;
112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
113 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_UTILS', p_procedure_name => 'CHECK_CUSTOMER_NAME_OR_ID');
114 raise;
115 END CHECK_CUSTOMER_NAME_OR_ID;
116
117
118 -- API name : Check_Customer_Number_Or_Id
119 -- Type : Utility
120 -- Pre-reqs : None.
121 -- Parameters :
122 -- p_customer_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
123 -- p_customer_number IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
124 -- p_check_id_flag IN VARCHAR2 Optional Default = 'A'
125 -- x_customer_id OUT NUMBER Required
126 -- x_return_status OUT VARCHAR2 Required
127 -- x_error_msg_code OUT VARCHAR2 Required
128
129 PROCEDURE CHECK_CUSTOMER_NUMBER_OR_ID
130 ( p_customer_id IN NUMBER := FND_API.G_MISS_NUM
131 ,p_customer_number IN VARCHAR2 := FND_API.G_MISS_CHAR
132 ,p_check_id_flag IN VARCHAR2 := 'A'
133 ,x_customer_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
134 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
135 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
136 )
137 IS
138 l_current_id NUMBER := NULL;
139 l_num_ids NUMBER := 0;
140 l_id_found_flag VARCHAR(1) := 'N';
141
142
143 CURSOR c_ids IS
144 SELECT customer_id
145 FROM pa_customers_v
146 WHERE upper(customer_number) = upper(p_customer_number) and status = 'A';
147
148 BEGIN
149 if (p_customer_id = FND_API.G_MISS_NUM) OR (p_customer_id is NULL) then
150 if (p_customer_number is not NULL) then
151 SELECT customer_id
152 INTO x_customer_id
153 FROM pa_customers_v
154 WHERE upper(customer_number) = upper(p_customer_number)
155 and status = 'A';
156 else
157 x_customer_id := NULL;
158 end if;
159
160 else
161 if p_check_id_flag = 'Y' then
162 SELECT customer_id
163 INTO x_customer_id
164 FROM pa_customers_v
165 WHERE customer_id = p_customer_id;
166
167 ELSIF (p_check_id_flag='N') THEN
168 x_customer_id := p_customer_id;
169
170
171 ELSIF (p_check_id_flag = 'A') THEN
172 IF (p_customer_number IS NULL) THEN
173 -- Return a null ID since the name is null.
174 x_customer_id := NULL;
175 ELSE
176
177 -- Find the ID which matches the Name passed
178 OPEN c_ids;
179 LOOP
180 FETCH c_ids INTO l_current_id;
181 EXIT WHEN c_ids%NOTFOUND;
182 IF (l_current_id = p_customer_id) THEN
183 l_id_found_flag := 'Y';
184 x_customer_id := p_customer_id;
185 END IF;
186 END LOOP;
187 l_num_ids := c_ids%ROWCOUNT;
188 CLOSE c_ids;
189
190 IF (l_num_ids = 0) THEN
191 -- No IDs for name
192 RAISE NO_DATA_FOUND;
193 ELSIF (l_num_ids = 1) THEN
194 -- Since there is only one ID for the name use it.
195 x_customer_id := l_current_id;
196 ELSIF (l_id_found_flag = 'N') THEN
197 -- More than one ID for the name and none of the IDs matched
198 -- the ID passed in.
199 RAISE TOO_MANY_ROWS;
200 END IF;
201 END IF;
202 else
203 x_customer_id := NULL;
204 end if;
205 end if;
206
207 x_return_status := FND_API.G_RET_STS_SUCCESS;
208
209 EXCEPTION
210 when NO_DATA_FOUND then
211 x_customer_id := NULL;
212 x_return_status := FND_API.G_RET_STS_ERROR;
213 x_error_msg_code := 'PA_CUSTOMER_ID_INVALID';
214 when TOO_MANY_ROWS then
215 x_customer_id := NULL;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 x_error_msg_code := 'PA_CUST_NUM_NOT_UNIQUE';
218 when OTHERS then
219 x_customer_id := NULL;
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
221 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_UTILS', p_procedure_name => 'CHECK_CUSTOMER_NUMBER_OR_ID');
222 raise;
223
224 END CHECK_CUSTOMER_NUMBER_OR_ID;
225
226
227 -- API name : Check_Bill_Site_Name_Or_Id
228 -- Type : Utility
229 -- Pre-reqs : None.
230 -- Parameters :
231 -- p_customer_id IN NUMBER Required
232 -- p_bill_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
233 -- p_bill_site_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
234 -- p_check_id_flag IN VARCHAR2 Optional Default = 'A'
235 -- x_bill_to_address_id OUT NUMBER Optional
236 -- x_return_status OUT VARCHAR2 Required
237 -- x_error_msg_code OUT VARCHAR2 Required
238
239 PROCEDURE CHECK_BILL_SITE_NAME_OR_ID
240 ( p_customer_id IN NUMBER
241 ,p_bill_to_address_id IN NUMBER := FND_API.G_MISS_NUM
242 ,p_bill_site_name IN VARCHAR2 := FND_API.G_MISS_CHAR
243 ,p_check_id_flag IN VARCHAR2 := 'A'
244 ,x_bill_to_address_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
245 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
246 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
247 )
248 IS
249 l_current_id NUMBER := NULL;
250 l_num_ids NUMBER := 0;
251 l_id_found_flag VARCHAR(1) := 'N';
252
253
254 CURSOR c_ids IS
255 SELECT address_id
256 FROM pa_customer_sites_v
257 WHERE upper(address1) = upper(p_bill_site_name) AND
258 customer_id = p_customer_id AND
259 site_use_code = 'BILL_TO';
260
261 BEGIN
262 if (p_bill_to_address_id = FND_API.G_MISS_NUM) OR (p_bill_to_address_id is NULL) then
263 if (p_bill_site_name is not NULL) then
264 SELECT address_id
265 INTO x_bill_to_address_id
266 FROM pa_customer_sites_v
267 WHERE upper(address1) = upper(p_bill_site_name) AND
268 customer_id = p_customer_id AND
269 site_use_code = 'BILL_TO';
270 else
271 x_bill_to_address_id := NULL;
272 end if;
273 else
274 if p_check_id_flag = 'Y' then
275 SELECT address_id
276 INTO x_bill_to_address_id
277 FROM pa_customer_sites_v
278 WHERE address_id = p_bill_to_address_id AND
279 customer_id = p_customer_id AND
280 site_use_code = 'BILL_TO';
281 ELSIF (p_check_id_flag='N') THEN
282 x_bill_to_address_id := p_bill_to_address_id;
283
284
285 ELSIF (p_check_id_flag = 'A') THEN
286 IF (p_bill_site_name IS NULL) THEN
287 -- Return a null ID since the name is null.
288 x_bill_to_address_id := NULL;
289 ELSE
290
291 -- Find the ID which matches the Name passed
292 OPEN c_ids;
293 LOOP
294 FETCH c_ids INTO l_current_id;
295 EXIT WHEN c_ids%NOTFOUND;
296 IF (l_current_id = p_bill_to_address_id) THEN
297 l_id_found_flag := 'Y';
298 x_bill_to_address_id := p_bill_to_address_id;
299 END IF;
300 END LOOP;
301 l_num_ids := c_ids%ROWCOUNT;
302 CLOSE c_ids;
303
304 IF (l_num_ids = 0) THEN
305 -- No IDs for name
306 RAISE NO_DATA_FOUND;
307 ELSIF (l_num_ids = 1) THEN
308 -- Since there is only one ID for the name use it.
309 X_bill_to_address_id := l_current_id;
310 ELSIF (l_id_found_flag = 'N') THEN
311 -- More than one ID for the name and none of the IDs matched
312 -- the ID passed in.
313 RAISE TOO_MANY_ROWS;
314 END IF;
315 END IF;
316
317 -- else
318 -- x_bill_to_address_id := p_bill_to_address_id;
319 end if;
320 end if;
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322 EXCEPTION
323 when NO_DATA_FOUND then
324 x_bill_to_address_id := NULL;
325 x_return_status := FND_API.G_RET_STS_ERROR;
326 x_error_msg_code := 'PA_BILL_TO_ADDR_ID_INVALID';
327 when TOO_MANY_ROWS then
328 x_bill_to_address_id := NULL;
329 x_return_status := FND_API.G_RET_STS_ERROR;
330 x_error_msg_code := 'PA_BILL_TO_ADDR_NOT_UNIQUE';
331 when OTHERS then
332 x_bill_to_address_id := NULL;
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
334 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_UTILS', p_procedure_name => 'CHECK_BILL_SITE_NAME_OR_ID');
335 raise;
336 END CHECK_BILL_SITE_NAME_OR_ID;
337
338
339 -- API name : Check_Work_Site_Name_Or_Id
340 -- Type : Utility
341 -- Pre-reqs : None.
342 -- Parameters :
343 -- p_customer_id IN NUMBER Required
344 -- p_ship_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
345 -- p_work_site_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
346 -- p_check_id_flag IN VARCHAR2 Optional Default = 'A'
347 -- x_ship_to_address_id OUT NUMBER Optional
348 -- x_return_status OUT VARCHAR2 Required
349 -- x_error_msg_code OUT VARCHAR2 Required
350
351 PROCEDURE CHECK_WORK_SITE_NAME_OR_ID
352 ( p_customer_id IN NUMBER
353 ,p_ship_to_address_id IN NUMBER := FND_API.G_MISS_NUM
354 ,p_work_site_name IN VARCHAR2 := FND_API.G_MISS_CHAR
355 ,p_check_id_flag IN VARCHAR2 := 'A'
356 ,x_ship_to_address_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
357 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
358 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
359 )
360 IS
361 l_current_id NUMBER := NULL;
362 l_num_ids NUMBER := 0;
363 l_id_found_flag VARCHAR(1) := 'N';
364
365
366 CURSOR c_ids IS
367 SELECT address_id
368 FROM pa_customer_sites_v
369 WHERE upper(address1) = upper(p_work_site_name) AND
370 customer_id = p_customer_id AND
371 site_use_code = 'SHIP_TO';
372 BEGIN
373 if (p_ship_to_address_id = FND_API.G_MISS_NUM) OR (p_ship_to_address_id is NULL) then
374 if (p_work_site_name is not NULL) then
375 SELECT address_id
376 INTO x_ship_to_address_id
377 FROM pa_customer_sites_v
378 WHERE upper(address1) = upper(p_work_site_name) AND
379 customer_id = p_customer_id AND
380 site_use_code = 'SHIP_TO';
381 else
382 x_ship_to_address_id := NULL;
383 end if;
384
385 else
386 if p_check_id_flag = 'Y' then
390 WHERE address_id = p_ship_to_address_id AND
387 SELECT address_id
388 INTO x_ship_to_address_id
389 FROM pa_customer_sites_v
391 customer_id = p_customer_id AND
392 site_use_code = 'SHIP_TO';
393
394 ELSIF (p_check_id_flag='N') THEN
395 x_ship_to_address_id := p_ship_to_address_id;
396
397 ELSIF (p_check_id_flag = 'A') THEN
398 IF (p_work_site_name IS NULL) THEN
399 -- Return a null ID since the name is null.
400 x_ship_to_address_id := NULL;
401 ELSE
402
403 -- Find the ID which matches the Name passed
404 OPEN c_ids;
405 LOOP
406 FETCH c_ids INTO l_current_id;
407 EXIT WHEN c_ids%NOTFOUND;
408 IF (l_current_id = p_ship_to_address_id) THEN
409 l_id_found_flag := 'Y';
410 x_ship_to_address_id := p_ship_to_address_id;
411 END IF;
412 END LOOP;
413 l_num_ids := c_ids%ROWCOUNT;
414 CLOSE c_ids;
415
416 IF (l_num_ids = 0) THEN
417 -- No IDs for name
418 RAISE NO_DATA_FOUND;
419 ELSIF (l_num_ids = 1) THEN
420 -- Since there is only one ID for the name use it.
421 x_ship_to_address_id := l_current_id;
422 ELSIF (l_id_found_flag = 'N') THEN
423 -- More than one ID for the name and none of the IDs matched
424 -- the ID passed in.
425 RAISE TOO_MANY_ROWS;
426 END IF;
427 END IF;
428
429 else
430 x_ship_to_address_id := NULL;
431 end if;
432 end if;
433 x_return_status := FND_API.G_RET_STS_SUCCESS;
434 EXCEPTION
435 when NO_DATA_FOUND then
436 x_return_status := FND_API.G_RET_STS_ERROR;
437 x_error_msg_code := 'PA_SHIP_TO_ADDR_ID_INVALID';
438 when TOO_MANY_ROWS then
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 x_error_msg_code := 'PA_SHIP_TO_ADDR_NOT_UNIQUE';
441 when OTHERS then
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443 raise;
444 END CHECK_WORK_SITE_NAME_OR_ID;
445
446
447 -- API name : Check_Contact_Name_Or_Id
448 -- Type : Utility
449 -- Pre-reqs : None.
450 -- Parameters :
451 -- p_customer_id IN NUMBER Required
452 -- p_project_contact_type_code IN VARCHAR2 Required
453 -- p_contact_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
454 -- p_contact_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
455 -- p_check_id_flag IN VARCHAR2 Optional Default = 'A'
456 -- x_contact_id OUT NUMBER Required
457 -- x_return_status OUT VARCHAR2 Required
458 -- x_error_msg_code OUT VARCHAR2 Required
459
460 PROCEDURE CHECK_CONTACT_NAME_OR_ID
461 ( p_customer_id IN NUMBER
462 ,p_project_contact_type_code IN VARCHAR2
463 ,p_contact_id IN NUMBER := FND_API.G_MISS_NUM
464 ,p_contact_name IN VARCHAR2 := FND_API.G_MISS_CHAR
465 ,p_check_id_flag IN VARCHAR2 := 'A'
466 ,p_address_id IN NUMBER := NULL -- Added for Bug 2964227
467 ,x_contact_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
468 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
469 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
470 )
471 IS
472 l_current_id NUMBER := NULL;
473 l_num_ids NUMBER := 0;
474 l_id_found_flag VARCHAR(1) := 'N';
475
476 --commented the filter condition on usage_code for Bug#5160623.
477 CURSOR c_ids IS
478 SELECT contact_id
479 FROM pa_customer_contact_names_v
480 WHERE upper(rtrim(contact_name)) = upper(rtrim(p_contact_name)) AND -- Bug 4015644
481 customer_id = p_customer_id /* AND
482 usage_code = decode(p_project_contact_type_code, 'BILLING', 'BILL_TO',
483 'SHIPPING', 'SHIP_TO', usage_code) */
484 AND (address_id is null OR nvl(p_address_id, address_id)= address_id ); -- Added for Bug 2964227
485
486 -- Bug 2964227 This change is backward compatible. Even if new parmeter is not passed, this will work unaffected.
487
488 BEGIN
489 if (p_contact_id = FND_API.G_MISS_NUM) OR (p_contact_id is NULL) then
490 if (p_contact_name is not NULL) then
491 SELECT contact_id
492 INTO x_contact_id
493 FROM pa_customer_contact_names_v
494 WHERE upper(rtrim(contact_name)) = upper(rtrim(p_contact_name)) AND -- Bug 4015644
495 customer_id = p_customer_id AND
496 usage_code = decode(p_project_contact_type_code, 'BILLING', 'BILL_TO',
497 'SHIPPING', 'SHIP_TO', usage_code)
498 AND (address_id is null OR nvl(p_address_id, address_id)= address_id ); -- Added for Bug 2964227
499 else
500 x_contact_id := NULL;
501 end if;
502
503 else
504 if p_check_id_flag = 'Y' then
505
509 -- FROM pa_customer_contact_names_v
506 -- Commented Following SQL for Performance Bug 4878913 SQL ID : 14907893
507 -- SELECT contact_id
508 -- INTO x_contact_id
510 -- WHERE contact_id = p_contact_id AND
511 -- customer_id = p_customer_id AND
512 -- usage_code = decode(p_project_contact_type_code, 'BILLING', 'BILL_TO',
513 -- 'SHIPPING', 'SHIP_TO', usage_code)
514 -- AND (address_id is null OR nvl(p_address_id, address_id)= address_id ); -- Added for Bug 2964227
515
516 -- Start of NEW SQL for Bug 4878913 SQL ID : 14907893
517 SELECT distinct acct_role.cust_account_role_id contact_id
518 INTO x_contact_id
519 FROM hz_cust_account_roles acct_role
520 ,hz_role_responsibility role_resp
521 WHERE
522 acct_role.cust_account_role_id = role_resp.cust_account_role_id
523 and acct_role.role_type = 'CONTACT'
524 and nvl(acct_role.current_role_state, 'A') = 'A'
525 and acct_role.cust_account_role_id = p_contact_id
526 AND acct_role.cust_account_id = p_customer_id
527 AND role_resp.responsibility_type = decode(p_project_contact_type_code, 'BILLING', 'BILL_TO',
528 'SHIPPING', 'SHIP_TO',role_resp.responsibility_type)
529 AND (acct_role.cust_acct_site_id is null OR nvl(p_address_id,acct_role.cust_acct_site_id)=acct_role.cust_acct_site_id );
530 -- End of NEW SQL for Bug 4878913 SQL ID : 14907893
531
532 -- ELSIF (p_check_id_flag='N') THEN
533 -- x_contact_id := p_contact_id;
534 ELSIF (p_check_id_flag = 'A') THEN
535 IF (p_contact_name IS NULL) THEN
536 -- Return a null ID since the name is null.
537 x_contact_id := NULL;
538 ELSE
539
540 -- Find the ID which matches the Name passed
541 OPEN c_ids;
542 LOOP
543 FETCH c_ids INTO l_current_id;
544 EXIT WHEN c_ids%NOTFOUND;
545 IF (l_current_id = p_contact_id) THEN
546 l_id_found_flag := 'Y';
547 x_contact_id := p_contact_id;
548 END IF;
549 END LOOP;
550 l_num_ids := c_ids%ROWCOUNT;
551 CLOSE c_ids;
552
553 IF (l_num_ids = 0) THEN
554 -- No IDs for name
555 RAISE NO_DATA_FOUND;
556 ELSIF (l_num_ids = 1) THEN
557 -- Since there is only one ID for the name use it.
558 x_contact_id := l_current_id;
559 ELSIF (l_id_found_flag = 'N') THEN
560 -- More than one ID for the name and none of the IDs matched
561 -- the ID passed in.
562 RAISE TOO_MANY_ROWS;
563 END IF;
564 END IF;
565 else
566 x_contact_id := NULL;
567 end if;
568 end if;
569 x_return_status := FND_API.G_RET_STS_SUCCESS;
570 EXCEPTION
571 when NO_DATA_FOUND then
572 x_contact_id := NULL;
573 x_return_status := FND_API.G_RET_STS_ERROR;
574 x_error_msg_code := 'PA_CONTACT_ID_INVALID';
575 when TOO_MANY_ROWS then
576 x_contact_id := NULL;
577 x_return_status := FND_API.G_RET_STS_ERROR;
578 x_error_msg_code := 'PA_CONTACT_NAME_NOT_UNIQUE';
579 when OTHERS then
580 x_contact_id := NULL;
581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
582 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_UTILS', p_procedure_name => 'CHECK_CONTACT_NAME_OR_ID');
583 raise;
584 END CHECK_CONTACT_NAME_OR_ID;
585
586
587 -- API name : Check_Contact_Typ_Name_Or_Code
588 -- Type : Utility
589 -- Pre-reqs : None.
590 -- Parameters :
591 -- p_project_contact_type_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
592 -- p_project_contact_type_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
593 -- p_check_id_flag IN VARCHAR2 Optional Default = 'A'
594 -- x_project_contact_type_code OUT VARCHAR2 Required
595 -- x_return_status OUT VARCHAR2 Required
596 -- x_error_msg_code OUT VARCHAR2 Required
597
598 PROCEDURE CHECK_CONTACT_TYP_NAME_OR_CODE
599 ( p_project_contact_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
600 ,p_project_contact_type_name IN VARCHAR2 := FND_API.G_MISS_CHAR
601 ,p_check_id_flag IN VARCHAR2 := 'A'
602 ,x_project_contact_type_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
603 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
604 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
605 )
606 IS
607 l_current_id VARCHAR2(30) := NULL;
608 l_num_ids NUMBER := 0;
609 l_id_found_flag VARCHAR(1) := 'N';
610
611
612 CURSOR c_ids IS
613 SELECT lookup_code
614 FROM pa_lookups
615 WHERE upper(meaning) = upper(p_project_contact_type_name) AND
616 lookup_type = 'PROJECT CONTACT TYPE' AND
617 trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));
618
619 BEGIN
620 if (p_project_contact_type_code = FND_API.G_MISS_CHAR) OR
621 (p_project_contact_type_code is NULL) then
625 FROM pa_lookups
622 if (p_project_contact_type_name is not NULL) then
623 SELECT lookup_code
624 INTO x_project_contact_type_code
626 WHERE upper(meaning) = upper(p_project_contact_type_name) AND
627 lookup_type = 'PROJECT CONTACT TYPE' AND
628 trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));
629 else
630 x_project_contact_type_code := NULL;
631 end if;
632
633 else
634 if p_check_id_flag = 'Y' then
635 SELECT lookup_code
636 INTO x_project_contact_type_code
637 FROM pa_lookups
638 WHERE lookup_code = p_project_contact_type_code AND
639 lookup_type = 'PROJECT CONTACT TYPE' AND
640 trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));
641
642 ELSIF (p_check_id_flag='N') THEN
643 x_project_contact_type_code := p_project_contact_type_code;
644
645 ELSIF (p_check_id_flag = 'A') THEN
646 IF (p_project_contact_type_name IS NULL) THEN
647 -- Return a null ID since the name is null.
648 x_project_contact_type_code := NULL;
649 ELSE
650
651 -- Find the ID which matches the Name passed
652 OPEN c_ids;
653 LOOP
654 FETCH c_ids INTO l_current_id;
655 EXIT WHEN c_ids%NOTFOUND;
656 IF (l_current_id = p_project_contact_type_code) THEN
657 l_id_found_flag := 'Y';
658 x_project_contact_type_code := p_project_contact_type_code;
659 END IF;
660 END LOOP;
661 l_num_ids := c_ids%ROWCOUNT;
662 CLOSE c_ids;
663
664 IF (l_num_ids = 0) THEN
665 -- No IDs for name
666 RAISE NO_DATA_FOUND;
667 ELSIF (l_num_ids = 1) THEN
668 -- Since there is only one ID for the name use it.
669 x_project_contact_type_code := l_current_id;
670 ELSIF (l_id_found_flag = 'N') THEN
671 -- More than one ID for the name and none of the IDs matched
672 -- the ID passed in.
673 RAISE TOO_MANY_ROWS;
674 END IF;
675 END IF;
676
677
678 else
679 x_project_contact_type_code := NULL;
680 end if;
681 end if;
682 x_return_status := FND_API.G_RET_STS_SUCCESS;
683 EXCEPTION
684 when NO_DATA_FOUND then
685 x_project_contact_type_code := NULL;
686 x_return_status := FND_API.G_RET_STS_ERROR;
687 x_error_msg_code := 'PA_CONTACT_TYP_CODE_INVALID';
688 when TOO_MANY_ROWS then
689 x_project_contact_type_code := NULL;
690 x_return_status := FND_API.G_RET_STS_ERROR;
691 x_error_msg_code := 'PA_CONT_TYP_CODE_NOT_UNIQUE';
692 when OTHERS then
693 x_project_contact_type_code := NULL;
694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
695 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_UTILS', p_procedure_name => 'CHECK_CONTACT_TYP_NAME_OR_CODE');
696 raise;
697 END CHECK_CONTACT_TYP_NAME_OR_CODE;
698
699
700 -- API name : Check_Duplicate_Customer
701 -- Type : Utility
702 -- Pre-reqs : None.
703 -- Parameters :
704 -- p_project_id IN NUMBER Required
705 -- p_customer_id IN NUMBER Required
706 -- x_return_status OUT VARCHAR2 Required
707 -- x_error_msg_code OUT VARCHAR2 Required
708
709 PROCEDURE CHECK_DUPLICATE_CUSTOMER
710 ( p_project_id IN NUMBER
711 ,p_customer_id IN NUMBER
712 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
713 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
714 )
715 IS
716 l_dummy VARCHAR2(1) := NULL;
717 CURSOR C(c_project_id NUMBER, c_customer_id NUMBER) IS
718 SELECT 'x'
719 FROM pa_project_customers
720 WHERE project_id = p_project_id AND
721 customer_id = p_customer_id;
722 BEGIN
723 if (p_customer_id is not null) then
724 open C(p_project_id, p_customer_id);
725 fetch C into l_dummy;
726 if l_dummy is not NULL then
727 x_return_status := FND_API.G_RET_STS_ERROR;
728 x_error_msg_code := 'PA_ALL_DUPLICATE_NAME';
729 else
730 x_return_status := FND_API.G_RET_STS_SUCCESS;
731 end if;
732 close C;
733 else
734 x_return_status := FND_API.G_RET_STS_ERROR;
735 x_error_msg_code := 'PA_CUST_NAME_OR_NUM_REQD';
736 end if;
737 EXCEPTION
738 when OTHERS then
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 raise;
741 END CHECK_DUPLICATE_CUSTOMER;
742
743
744 -- API name : Check_Duplicate_Contact
745 -- Type : Utility
746 -- Pre-reqs : None.
747 -- Parameters :
748 -- p_project_id IN NUMBER Required
749 -- p_customer_id IN NUMBER Required
750 -- p_contact_id IN NUMBER Required
754
751 -- p_project_contact_type_code IN VARCHAR2 Required
752 -- x_return_status OUT VARCHAR2 Required
753 -- x_error_msg_code OUT VARCHAR2 Required
755 PROCEDURE CHECK_DUPLICATE_CONTACT
756 ( p_project_id IN NUMBER
757 ,p_customer_id IN NUMBER
758 ,p_contact_id IN NUMBER
759 ,p_project_contact_type_code IN VARCHAR2
760 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
761 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
762 )
763 IS
764 l_dummy VARCHAR2(1) := NULL;
765 CURSOR C(c_project_id NUMBER, c_customer_id NUMBER, c_contact_id NUMBER,
766 c_project_contact_type_code VARCHAR2) IS
767 SELECT 'x'
768 FROM pa_project_contacts
769 WHERE project_id = c_project_id AND
770 customer_id = c_customer_id AND
771 contact_id = c_contact_id AND
772 project_contact_type_code = c_project_contact_type_code;
773 BEGIN
774 if (p_contact_id is not null) then
775 open C(p_project_id, p_customer_id, p_contact_id, p_project_contact_type_code);
776 fetch C into l_dummy;
777 if l_dummy is not NULL then
778 x_return_status := FND_API.G_RET_STS_ERROR;
779 x_error_msg_code := 'PA_ALL_DUPLICATE_NAME';
780 else
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782 end if;
783 close C;
784 else
785 x_return_status := FND_API.G_RET_STS_ERROR;
786 x_error_msg_code := 'PA_CONTACT_NAME_REQD';
787 end if;
788 EXCEPTION
789 when OTHERS then
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
791 raise;
792 END CHECK_DUPLICATE_CONTACT;
793
794
795 -- API name : Check_Contribution_Percentage
796 -- Type : Utility
797 -- Pre-reqs : None.
798 -- Parameters :
799 -- p_customer_bill_split IN NUMBER Required
800 -- x_return_status OUT VARCHAR2 Required
801 -- x_error_msg_code OUT VARCHAR2 Required
802
803 PROCEDURE CHECK_CONTRIBUTION_PERCENTAGE
804 ( p_customer_bill_split IN NUMBER
805 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
806 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
807 )
808 IS
809 BEGIN
810 if (p_customer_bill_split < 0) OR (p_customer_bill_split > 100) then
811 x_return_status := FND_API.G_RET_STS_ERROR;
812 x_error_msg_code := 'PA_CUST_BILL_SPLIT_INVALID';
813 else
814 x_return_status := FND_API.G_RET_STS_SUCCESS;
815 end if;
816 EXCEPTION
817 when OTHERS then
818 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
819 raise;
820 END CHECK_CONTRIBUTION_PERCENTAGE;
821
822
823 -- API name : Check_Contribution_Total
824 -- Type : Utility
825 -- Pre-reqs : None.
826 -- Parameters :
827 -- p_customer_bill_split IN NUMBER Required
828 -- p_project_id IN NUMBER Required
829 -- p_customer_id IN NUMBER Required
830 -- x_return_status OUT VARCHAR2 Required
831 -- x_error_msg_code OUT VARCHAR2 Required
832
833 PROCEDURE CHECK_CONTRIBUTION_TOTAL
834 ( p_customer_bill_split IN NUMBER
835 ,p_project_id IN NUMBER
836 ,p_customer_id IN NUMBER
837 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
838 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
839 )
840 IS
841 l_total NUMBER;
842 CURSOR C(c_project_id NUMBER, c_customer_id NUMBER) IS
843 SELECT sum(customer_bill_split)
844 FROM pa_project_customers
845 WHERE project_id = c_project_id AND
846 customer_id <> c_customer_id;
847 BEGIN
848 open C(p_project_id, p_customer_id);
849 fetch C into l_total;
850 close C;
851 l_total := l_total + p_customer_bill_split;
852 if l_total > 100 then
853 x_return_status := FND_API.G_RET_STS_ERROR;
854 -- space in the error msg code due to error in akuploading to seed115
855 x_error_msg_code := 'PA_TOT_CUST_BILL_SPLIT_INVLD';
856 else
857 x_return_status := FND_API.G_RET_STS_SUCCESS;
858 end if;
859
860 EXCEPTION
861 when OTHERS then
862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863 raise;
864 END CHECK_CONTRIBUTION_TOTAL;
865
866
867 -- API name : Check_Bill_Work_Sites_Required
868 -- Type : Utility
869 -- Pre-reqs : None.
870 -- Parameters :
871 -- p_customer_bill_split IN NUMBER Required
872 -- p_bill_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
873 -- p_ship_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
874 -- x_return_status OUT VARCHAR2 Required
875 -- x_error_msg_code OUT VARCHAR2 Required
876
877 PROCEDURE CHECK_BILL_WORK_SITES_REQUIRED
878 ( p_customer_bill_split IN NUMBER
879 ,p_bill_to_address_id IN NUMBER := FND_API.G_MISS_NUM
880 ,p_ship_to_address_id IN NUMBER := FND_API.G_MISS_NUM
881 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
885 BEGIN
882 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
883 )
884 IS
886 if p_customer_bill_split > 0 then
887 if ((p_bill_to_address_id = FND_API.G_MISS_NUM) OR (p_bill_to_address_id is NULL)) OR
888 ((p_ship_to_address_id = FND_API.G_MISS_NUM) OR (p_ship_to_address_id is NULL)) then
889 x_return_status := FND_API.G_RET_STS_ERROR;
890 x_error_msg_code := 'PA_BILL_AND_WORK_SITE_REQD';
891 return;
892 end if;
893 end if;
894 x_return_status := FND_API.G_RET_STS_SUCCESS;
895 EXCEPTION
896 when OTHERS then
897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
898 raise;
899 END CHECK_BILL_WORK_SITES_REQUIRED;
900
901
902 -- API name : Check_Cc_Prvdr_Flag_Contrib
903 -- Type : Utility
904 -- Pre-reqs : None.
905 -- Parameters :
906 -- p_project_id IN NUMBER Required
907 -- p_customer_id IN NUMBER Required
908 -- p_customer_bill_split IN NUMBER Required
909 -- x_return_status OUT VARCHAR2 Required
910 -- x_error_msg_code OUT VARCHAR2 Required
911
912 PROCEDURE CHECK_CC_PRVDR_FLAG_CONTRIB
913 ( p_project_id IN NUMBER
914 ,p_customer_id IN NUMBER
915 ,p_customer_bill_split IN NUMBER
916 ,p_action IN VARCHAR2
917 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
918 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
919 )
920 IS
921 l_cc_prvdr_flag VARCHAR2(1);
922
923 CURSOR C1(c_project_id NUMBER) IS
924 SELECT cc_prvdr_flag
925 FROM pa_project_types
926 WHERE project_type =
927 (SELECT project_type
928 FROM pa_projects_all
929 WHERE project_id = c_project_id);
930
931 /* Changed the having condition to the where condition in the following cursor c2
932 to avoid full table scan of pa_project_customers -Bug 2782177 */
933
934 CURSOR C2(c_project_id NUMBER) IS
935 SELECT count(customer_id) count, sum(customer_bill_split) sum
936 FROM pa_project_customers
937 WHERE project_id = c_project_id
938 GROUP BY project_id;
939
940 CURSOR C3(c_project_id NUMBER, c_customer_id NUMBER) IS
941 SELECT '1'
942 FROM pa_project_customers
943 WHERE project_id = c_project_id AND
944 customer_id = c_customer_id;
945
946 l_dummy VARCHAR2(1) := NULL;
947 l_recinfo C2%ROWTYPE;
948 BEGIN
949 open C1(p_project_id);
950 fetch C1 into l_cc_prvdr_flag;
951 close C1;
952
953 open C2(p_project_id);
954 fetch C2 into l_recinfo;
955 close C2;
956
957 if l_cc_prvdr_flag = 'Y' then
958 if p_action = 'INSERT' then
959 if l_recinfo.count > 0 then
960 x_return_status := FND_API.G_RET_STS_ERROR;
961 x_error_msg_code := 'PA_ONLY_ONE_CUST_ALLOWED';
962 return;
963 else
964 if p_customer_bill_split <> 100 then
965 x_return_status := FND_API.G_RET_STS_ERROR;
966 x_error_msg_code := 'PA_PR_INSUF_BILL_SPLIT';
967 return;
968 end if;
969 end if;
970 elsif p_action = 'UPDATE' then
971 if l_recinfo.count > 1 then
972 x_return_status := FND_API.G_RET_STS_ERROR;
973 x_error_msg_code := 'PA_ONLY_ONE_CUST_ALLOWED';
974 return;
975 elsif l_recinfo.count = 1 then
976 open C3(p_project_id, p_customer_id);
977 fetch C3 into l_dummy;
978 close C3;
979
980 if l_dummy is NULL then
981 x_return_status := FND_API.G_RET_STS_ERROR;
982 x_error_msg_code := 'PA_ONLY_ONE_CUST_ALLOWED';
983 return;
984 else
985 if p_customer_bill_split <> 100 then
986 x_return_status := FND_API.G_RET_STS_ERROR;
987 x_error_msg_code := 'PA_PR_INSUF_BILL_SPLIT';
988 return;
989 end if;
990 end if;
991 end if;
992 end if;
993 end if;
994
995 x_return_status := FND_API.G_RET_STS_SUCCESS;
996 EXCEPTION
997 when OTHERS then
998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
999 raise;
1000 END CHECK_CC_PRVDR_FLAG_CONTRIB;
1001
1002
1003 -- API name : Check_Receiver_Proj_Enterable
1004 -- Type : Utility
1005 -- Pre-reqs : None.
1006 -- Parameters :
1007 -- p_project_id IN NUMBER Required
1008 -- p_customer_id IN NUMBER Required
1009 -- p_receiver_task_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1010 -- x_bill_another_project_flag OUT VARCHAR2 Required
1011 -- x_return_status OUT VARCHAR2 Required
1012 -- x_error_msg_code OUT VARCHAR2 Required
1013
1014 PROCEDURE CHECK_RECEIVER_PROJ_ENTERABLE
1015 ( p_project_id IN NUMBER
1016 ,p_customer_id IN NUMBER
1017 ,p_receiver_task_id IN NUMBER := FND_API.G_MISS_NUM
1021 )
1018 ,x_bill_another_project_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1019 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1020 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1022 IS
1023 l_dummy VARCHAR2(1);
1024
1025 CURSOR C1(c_project_id NUMBER) IS
1026 SELECT '1'
1027 FROM
1028 /* Commented for Bug 2499051
1029 pa_projects a,
1030 */
1031 pa_project_types b
1032 WHERE b.project_type_class_code = 'CONTRACT' AND
1033 b.project_type =
1034 (SELECT project_type
1035 FROM pa_projects_all
1036 WHERE project_id = c_project_id) AND
1037 (b.cc_prvdr_flag = 'N' OR b.cc_prvdr_flag is NULL);
1038 CURSOR C2(c_customer_id NUMBER) IS
1039 SELECT '1'
1040 FROM pa_implementations_all
1041 WHERE customer_id = c_customer_id;
1042 CURSOR C3 IS
1043 SELECT '1'
1044 FROM pa_implementations
1045 WHERE cc_ic_billing_prvdr_flag = 'Y';
1046 BEGIN
1047 open C1(p_project_id);
1048 fetch C1 into l_dummy;
1049
1050 open C2(p_customer_id);
1051 fetch C2 into l_dummy;
1052
1053 open C3;
1054 fetch C3 into l_dummy;
1055
1056 if (C1%FOUND AND C2%FOUND AND C3%FOUND) then
1057 if (p_receiver_task_id <> FND_API.G_MISS_NUM) AND (p_receiver_task_id is not NULL) then
1058 x_bill_another_project_flag := 'Y';
1059 else
1060 x_bill_another_project_flag := 'N';
1061 end if;
1062 x_return_status := FND_API.G_RET_STS_SUCCESS;
1063 else
1064 if (p_receiver_task_id <> FND_API.G_MISS_NUM) AND (p_receiver_task_id is not NULL) then
1065 x_return_status := FND_API.G_RET_STS_ERROR;
1066 x_error_msg_code := 'PA_REC_PROJ_NOT_ALLOWED';
1067 else
1068 x_bill_another_project_flag := 'N';
1069 x_return_status := FND_API.G_RET_STS_SUCCESS;
1070 end if;
1071 end if;
1072
1073 close C1;
1074 close C2;
1075 close C3;
1076 EXCEPTION
1077 when OTHERS then
1078 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079 raise;
1080 END CHECK_RECEIVER_PROJ_ENTERABLE;
1081
1082
1083 -- API name : Check_Invoice_Currency_Info
1084 -- Type : Utility
1085 -- Pre-reqs : None.
1086 -- Parameters :
1087 -- p_project_id IN NUMBER Required
1088 -- p_inv_currency_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1089 -- p_inv_rate_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1090 -- p_inv_rate_date IN DATE Optional Default = FND_API.G_MISS_DATE
1091 -- p_inv_exchange_rate IN NUMBER Optional Default = FND_API.G_MISS_NUM
1092 -- p_allow_user_rate_type_flag IN VARCHAR2 Required Default = 'N'
1093 -- x_return_status OUT VARCHAR2 Required
1094 -- x_error_msg_code OUT VARCHAR2 Required
1095
1096 PROCEDURE CHECK_INVOICE_CURRENCY_INFO
1097 ( p_project_id IN NUMBER
1098 ,p_inv_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR
1099 ,p_inv_rate_type IN VARCHAR2 := FND_API.G_MISS_CHAR
1100 ,p_inv_rate_date IN DATE := FND_API.G_MISS_DATE
1101 ,p_inv_exchange_rate IN NUMBER := FND_API.G_MISS_NUM
1102 ,p_allow_user_rate_type_flag IN VARCHAR2 := 'N'
1103 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1104 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1105 )
1106 IS
1107 l_project_currency_code VARCHAR2(10);
1108 l_project_currency_code2 VARCHAR2(10);
1109 l_mcb_flag VARCHAR2(1);
1110 l_cc_prvdr_flag VARCHAR2(1); --bug#5554475
1111 CURSOR C1(c_project_id NUMBER) IS
1112 SELECT project_currency_code, multi_currency_billing_flag
1113 FROM pa_projects_all
1114 WHERE project_id = c_project_id;
1115 BEGIN
1116 -- bug 2070847
1117 open C1(p_project_id);
1118 fetch C1 into l_project_currency_code, l_mcb_flag;
1119 close C1;
1120
1121 if p_inv_currency_code = FND_API.G_MISS_CHAR then
1122 l_project_currency_code2 := NULL;
1123 else
1124 l_project_currency_code2 := p_inv_currency_code;
1125 end if;
1126
1127
1128 -- Start of addition for bug 5554475
1129 select pt.cc_prvdr_flag
1130 into l_cc_prvdr_flag
1131 FROM pa_project_types pt
1132 , pa_projects p
1133 WHERE p.project_type = pt.project_type
1134 AND p.project_id = p_project_id
1135 AND p.org_id = pt.org_id;
1136
1137
1138 If (nvl(p_inv_currency_code,FND_API.G_MISS_CHAR)<> FND_API.G_MISS_CHAR) then
1139 IF ((p_inv_currency_code <> l_project_currency_code) AND ((l_mcb_flag = 'N') and (l_cc_prvdr_flag = 'N'))) then
1140 x_return_status := FND_API.G_RET_STS_ERROR;
1141 x_error_msg_code := 'PA_INV_CURR_NON_MCB_OPTION';
1142 return;
1143 END if;
1144 END if;
1145 -- End of addition for bug 5554475
1146
1147 -- anlee, bug 2461954
1148 if (l_project_currency_code <> nvl(l_project_currency_code2, l_project_currency_code))
1152 if (p_inv_rate_type is NULL) or (p_inv_rate_type = FND_API.G_MISS_CHAR) then
1149 AND ((l_mcb_flag = 'Y') OR (l_cc_prvdr_flag = 'Y')) --bug#5554475
1150 -- anlee end of changes
1151 THEN
1153 x_return_status := FND_API.G_RET_STS_ERROR;
1154 x_error_msg_code := 'PA_RATE_TYPE_REQD';
1155 return;
1156 end if;
1157 END IF; --bug 2131206
1158
1159 if ((upper(p_inv_rate_type) = 'USER') AND ((p_allow_user_rate_type_flag = 'N')
1160 /*OR
1161 (pa_multi_currency.is_user_rate_type_allowed(p_inv_currency_code,
1162 l_project_currency_code, nvl(p_inv_rate_date, sysdate)) = 'N')*/
1163 )) then
1164 x_return_status := FND_API.G_RET_STS_ERROR;
1165 x_error_msg_code := 'PA_USR_RATE_NOT_ALLOWED';
1166 return;
1167 end if;
1168
1169 if upper(p_inv_rate_type) = 'USER' then
1170 if
1171 /* (
1172 (p_inv_rate_date = FND_API.G_MISS_DATE) OR (p_inv_rate_date is NULL)) OR*/
1173 ((p_inv_exchange_rate = FND_API.G_MISS_NUM) OR (p_inv_exchange_rate is NULL)) then
1174 x_return_status := FND_API.G_RET_STS_ERROR;
1175 x_error_msg_code := 'PA_XCH_RATE_AND_DATE_REQD';
1176 return;
1177 end if;
1178 end if;
1179
1180 /* elsif l_project_currency_code = l_project_currency_code2 then
1181 if ((p_inv_rate_type <> FND_API.G_MISS_CHAR) AND (p_inv_rate_type is not NULL)) OR
1182 ((p_inv_rate_date <> FND_API.G_MISS_DATE) AND (p_inv_rate_date is not NULL)) OR
1183 ((p_inv_exchange_rate <>FND_API.G_MISS_NUM) AND (p_inv_exchange_rate is not NULL)) then
1184 x_return_status := FND_API.G_RET_STS_ERROR;
1185 x_error_msg_code := 'PA_RATE_INFO_NOT_ALLOWED';
1186 return;
1187 end if;
1188 end if;
1189 */
1190
1191 x_return_status := FND_API.G_RET_STS_SUCCESS;
1192 EXCEPTION
1193 when OTHERS then
1194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1195 raise;
1196 END CHECK_INVOICE_CURRENCY_INFO;
1197
1198
1199 -- API name : Check_Update_Contrib_Allowed
1200 -- Type : Utility
1201 -- Pre-reqs : None.
1202 -- Parameters :
1203 -- p_project_id IN NUMBER Required
1204 -- p_customer_id IN NUMBER Required
1205 -- x_return_status OUT VARCHAR2 Required
1206 -- x_error_msg_code OUT VARCHAR2 Required
1207 /*Changes for the enhancement 2520222.This code will check
1208 if the assigned customer is having valid funding lines and
1209 user is trying to change existing contribution from non zero to zero
1210 then it will give error.*/
1211
1212 PROCEDURE CHECK_UPDATE_CONTRIB_ALLOWED
1213 ( p_project_id IN NUMBER
1214 ,p_customer_id IN NUMBER
1215 ,p_customer_bill_split IN NUMBER
1216 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1217 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1218 )
1219 IS
1220 c1_exists VARCHAR2(1) := NULL;
1221 c2_exists VARCHAR2(1) := NULL;
1222 l_customer_bill_split NUMBER;
1223
1224 CURSOR C1(c_project_id NUMBER) IS
1225 SELECT 'x'
1226 FROM sys.dual
1227 WHERE exists
1228 (SELECT null
1229 FROM pa_draft_revenues r
1230 WHERE r.project_id = c_project_id) OR
1231 exists
1232 (SELECT null
1233 FROM pa_draft_invoices i
1234 WHERE i.project_id = c_project_id);
1235
1236 /* BUG#2547423. Changed pa_agreements to pa_agreements_all */
1237 /*Commented the below code for enhancement 2520222*/
1238 /*CURSOR C2(c_project_id NUMBER, c_customer_id NUMBER) IS
1239 SELECT 'x'
1240 FROM pa_agreements_all a, pa_project_fundings f
1241 WHERE a.customer_id+0 = c_customer_id AND
1242 a.agreement_id = f.agreement_id AND
1243 f.project_id = c_project_id AND
1244 f.budget_type_code = 'BASELINE';*/
1245
1246 /*Added the below cursor for the enhancement 2520222*/
1247
1248 CURSOR C2(c_project_id NUMBER, c_customer_id NUMBER) IS
1249 SELECT 'x'
1250 FROM pa_agreements_all a,
1251 pa_summary_project_fundings f
1252 WHERE a.customer_id = c_customer_id
1253 AND a.agreement_id = f.agreement_id
1254 AND f.project_id = c_project_id
1255 AND ( f.total_unbaselined_amount <>0
1256 OR f.total_baselined_amount <> 0);
1257
1258
1259 CURSOR C3(c_project_id NUMBER, c_customer_id NUMBER) IS
1260 SELECT customer_bill_split
1261 FROM pa_project_customers
1262 WHERE project_id = c_project_id AND
1263 customer_id = c_customer_id;
1264 BEGIN
1265 open C1(p_project_id);
1266 fetch C1 into c1_exists;
1267 close C1;
1268
1269 open C2(p_project_id, p_customer_id);
1270 fetch C2 into c2_exists;
1271 close C2;
1272
1273 if (c1_exists is not NULL) THEN
1274 /*Commented for bug 2520222 OR (c2_exists is not NULL))*/
1275 open C3(p_project_id, p_customer_id);
1276 fetch C3 into l_customer_bill_split;
1277 close C3;
1278
1279 if p_customer_bill_split <> l_customer_bill_split then
1280 x_return_status := FND_API.G_RET_STS_ERROR;
1284 /*Added for bug 2520222*/
1281 x_error_msg_code := 'PA_UPD_CUST_NOT_ALLOWED';
1282 return;
1283 end if;
1285 elsif (p_customer_bill_split = 0) then
1286 if (c2_exists is not NULL ) then
1287 x_return_status := FND_API.G_RET_STS_ERROR;
1288 x_error_msg_code := 'PA_BILL_CUST_CONTR_ZERO';
1289 return;
1290 end if;
1291 end if;
1292
1293
1294 x_return_status := FND_API.G_RET_STS_SUCCESS;
1295 EXCEPTION
1296 when OTHERS then
1297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1298 raise;
1299 END CHECK_UPDATE_CONTRIB_ALLOWED;
1300
1301
1302 -- API name : Check_Delete_Customer_Allowed
1303 -- Type : Utility
1304 -- Pre-reqs : None.
1305 -- Parameters :
1306 -- p_project_id IN NUMBER Required
1307 -- p_customer_id IN NUMBER Required
1308 -- x_return_status OUT VARCHAR2 Required
1309 -- x_error_msg_code OUT VARCHAR2 Required
1310
1311 PROCEDURE CHECK_DELETE_CUSTOMER_ALLOWED
1312 ( p_project_id IN NUMBER
1313 ,p_customer_id IN NUMBER
1314 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1315 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1316 )
1317 IS
1318 c_exists VARCHAR2(1);
1319
1320 /* BUG#2547423. Changed pa_agreements to pa_agreements_all */
1321 CURSOR C(c_project_id NUMBER, c_customer_id NUMBER) IS
1322 SELECT 'x'
1323 FROM pa_agreements_all a, pa_project_fundings f
1324 WHERE a.customer_id+0 = c_customer_id AND
1325 a.agreement_id = f.agreement_id AND
1326 f.project_id = c_project_id;
1327 BEGIN
1328 open C(p_project_id, p_customer_id);
1329 fetch C into c_exists;
1330 close C;
1331
1332 if c_exists is not NULL then
1333 x_return_status := FND_API.G_RET_STS_ERROR;
1334 x_error_msg_code := 'PA_PR_CANT_DEL_FUND_CUST';
1335 else
1336 x_return_status := FND_API.G_RET_STS_SUCCESS;
1337 end if;
1338 EXCEPTION
1339 when OTHERS then
1340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1341 raise;
1342 END CHECK_DELETE_CUSTOMER_ALLOWED;
1343
1344
1345 -- API name : Get_Org_Id
1346 -- Type : Utility
1347 -- Pre-reqs : None.
1348 -- Parameters : None.
1349 -- Return Type : Number
1350 FUNCTION GET_ORG_ID
1351 RETURN NUMBER
1352 IS
1353 x_org_id NUMBER;
1354 BEGIN
1355 SELECT org_id
1356 INTO x_org_id
1357 FROM pa_implementations;
1358 return x_org_id;
1359 EXCEPTION
1360 when OTHERS then
1361 null;
1362 END GET_ORG_ID;
1363
1364
1365 -- API name : Check_Customer_Name_And_Number
1366 -- Type : Utility
1367 -- Pre-reqs : None.
1368 -- Parameters :
1369 -- p_customer_name IN VARCHAR2 Required
1370 -- p_customer_number IN VARCHAR2 Required
1371 -- x_customer_id OUT NUMBER Required
1372 -- x_return_status OUT VARCHAR2 Required
1373 -- x_error_msg_code OUT VARCHAR2 Required
1374
1375 PROCEDURE CHECK_CUSTOMER_NAME_AND_NUMBER
1376 ( p_customer_name IN VARCHAR2
1377 ,p_customer_number IN VARCHAR2
1378 ,x_customer_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1379 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1380 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1381 )
1382 IS
1383
1384 BEGIN
1385 SELECT customer_id
1386 INTO x_customer_id
1387 FROM pa_customers_v
1388 WHERE upper(customer_number) = upper(p_customer_number) and
1389 upper(customer_name) = upper(p_customer_name)
1390 and status = 'A';
1391
1392 x_return_status := FND_API.G_RET_STS_SUCCESS;
1393
1394 EXCEPTION
1395 when NO_DATA_FOUND then
1396 x_customer_id := NULL;
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 x_error_msg_code := 'PA_CUST_NAME_NUMBER_INVALID';
1399 when TOO_MANY_ROWS then
1400 x_customer_id := NULL;
1401 x_return_status := FND_API.G_RET_STS_ERROR;
1402 x_error_msg_code := 'PA_CUST_NAME_NOT_UNIQUE';
1403 when OTHERS then
1404 x_customer_id := NULL;
1405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1406 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_UTILS', p_procedure_name => 'CHECK_CUSTOMER_NAME_AND_NUMBER');
1407 raise;
1408 END CHECK_CUSTOMER_NAME_AND_NUMBER;
1409
1410
1411 -- API name : Get_Contribution_Total
1412 -- Type : Utility
1413 -- Pre-reqs : None.
1414 -- Parameters : Project_Id
1415 -- Return Type : Number
1416 FUNCTION GET_CONTRIBUTION_TOTAL (p_project_id IN NUMBER)
1417 RETURN NUMBER
1418 IS
1419 l_total NUMBER;
1420 CURSOR C(c_project_id NUMBER) IS
1421 SELECT sum(customer_bill_split)
1422 FROM pa_project_customers
1423 WHERE project_id = c_project_id;
1424 BEGIN
1425 OPEN C(p_project_id);
1426 FETCH C INTO l_total;
1427 CLOSE C;
1428 return l_total;
1429 EXCEPTION
1430 when OTHERS then
1431 null;
1432 END GET_CONTRIBUTION_TOTAL;
1433
1434
1435 --sunkalya federal changes Bug#5511353
1436 -- Procedure : Get_Highest_Contr_Fed_Cust
1437 -- Type : PRIVATE
1438 -- Purpose : Gets the highest contribution federal customer. If contribution is same,
1439 -- then sorts on name and if names are also same, then sorts on
1440 -- customer id. This API is included as a part of
1441 -- federal changes.
1442 -- Note :
1443 -- Assumptions :
1444 -- Parameters Type Required Description and Purpose
1445 -- --------------------------- ------ -------- --------------------------------------------------------
1449 PROCEDURE Get_Highest_Contr_Fed_Cust(
1446 -- p_project_id NUMBER Y Project ID for which highest contribution customer is
1447 -- to be returned
1448 -- x_highst_contr_cust_id NUMBER N Customer ID of the highest contribution customer
1450 p_api_version IN NUMBER := 1.0
1451 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1452 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1453 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1454 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1455 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1456 , p_debug_mode IN VARCHAR2 := 'N'
1457 , p_project_id IN NUMBER
1458 , x_highst_contr_cust_id OUT NOCOPY NUMBER
1459 , x_return_status OUT NOCOPY VARCHAR2
1460 , x_msg_count OUT NOCOPY NUMBER
1461 , x_msg_data OUT NOCOPY VARCHAR2
1462 ) IS
1463
1464 l_msg_count NUMBER := 0;
1465 l_debug_mode VARCHAR2(1);
1466 l_data VARCHAR2(2000);
1467 l_msg_data VARCHAR2(2000);
1468 l_msg_index_out NUMBER;
1469
1470 l_debug_level2 CONSTANT NUMBER := 2;
1471 l_debug_level3 CONSTANT NUMBER := 3;
1472 l_debug_level4 CONSTANT NUMBER := 4;
1473 l_debug_level5 CONSTANT NUMBER := 5;
1474
1475 l_return_cust_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
1476 l_return_cust_name_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE;
1477 l_return_cust_num_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
1478 l_return_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE;
1479 Invalid_Arg_Exc EXCEPTION;
1480
1481 CURSOR
1482 cur_get_ordered_customers
1483 IS
1484 SELECT
1485 HZ_C.cust_account_id ,
1486 HZ_P.party_name ,
1487 HZ_P.party_number,
1488 'Y'
1489 FROM
1490 pa_project_customers proj_cust,
1491 hz_cust_accounts HZ_C,
1492 HZ_PARTIES HZ_P
1493 WHERE
1494 proj_cust.project_id = p_project_id
1495 AND proj_cust.customer_id = hz_c.cust_account_id
1496 AND hz_c.party_id = hz_p.party_id
1497 ORDER BY
1498 proj_cust.customer_bill_split desc,
1499 hz_p.party_name,
1500 HZ_P.party_number ;
1501
1502 BEGIN
1503
1504 x_highst_contr_cust_id := null;
1505 x_msg_count := 0;
1506 x_return_status := FND_API.G_RET_STS_SUCCESS;
1507 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1508
1509
1510 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1511 FND_MSG_PUB.initialize;
1512 END IF;
1513
1514 IF (p_commit = FND_API.G_TRUE) THEN
1515 savepoint get_highest_cont_cust_svpt;
1516 END IF;
1517
1518 IF l_debug_mode = 'Y' THEN
1519
1520 PA_DEBUG.set_curr_function( p_function => 'Get_Highest_Contr_Fed_Cust',
1521 p_debug_mode => l_debug_mode );
1522 END IF;
1523
1524 IF l_debug_mode = 'Y' THEN
1525
1526 Pa_Debug.g_err_stage:= 'Printing Input parameters';
1527 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
1528 l_debug_level3);
1529
1530 Pa_Debug.WRITE(G_PKG_NAME,'p_project_id'||':'||p_project_id,
1531 l_debug_level3);
1532 END IF;
1533
1534 IF l_debug_mode = 'Y' THEN
1535 Pa_Debug.g_err_stage:= 'Validating Input parameters';
1536 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
1537 l_debug_level3);
1538 END IF;
1539
1540 IF ( p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM)
1541 THEN
1542 IF l_debug_mode = 'Y' THEN
1543 Pa_Debug.g_err_stage:= 'PA_CUSTOMERS_CONTACTS_UTILS : Get_Highest_Contr_Fed_Cust :
1544 p_project_id is NULL';
1545 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
1546 l_debug_level3);
1547 END IF;
1548 RAISE Invalid_Arg_Exc;
1549
1550 END IF;
1551
1552 OPEN cur_get_ordered_customers;
1553 FETCH cur_get_ordered_customers BULK COLLECT INTO l_return_cust_id_tbl, l_return_cust_name_tbl, l_return_cust_num_tbl, l_return_flag_tbl;
1554 CLOSE cur_get_ordered_customers;
1555
1556 --If the return table is not NULL
1557 IF nvl(l_return_flag_tbl.LAST,0) > 0 THEN
1558 --Return the first record that has return flag as 'Y'
1559 FOR i IN l_return_flag_tbl.FIRST..l_return_flag_tbl.LAST LOOP
1560 IF l_return_flag_tbl(i) = 'Y' THEN
1561 x_highst_contr_cust_id := l_return_cust_id_tbl(i);
1562 EXIT;
1563 END IF;
1564 END LOOP;
1565 END IF;
1566
1567 EXCEPTION
1568
1569 WHEN FND_API.G_EXC_ERROR THEN
1570
1571 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1572
1573
1574 x_highst_contr_cust_id := null;
1575
1576
1577 IF p_commit = FND_API.G_TRUE THEN
1578 ROLLBACK TO get_highest_cont_cust_svpt;
1579 END IF;
1580
1581 l_msg_count := Fnd_Msg_Pub.count_msg;
1582 IF l_msg_count = 1 AND x_msg_data IS NULL
1583 THEN
1584 Pa_Interface_Utils_Pub.get_messages
1585 ( p_encoded => Fnd_Api.G_FALSE
1586 , p_msg_index => 1
1587 , p_msg_count => l_msg_count
1588 , p_msg_data => l_msg_data
1589 , p_data => l_data
1590 , p_msg_index_out => l_msg_index_out);
1591 x_msg_data := l_data;
1592 x_msg_count := l_msg_count;
1593 ELSE
1594 x_msg_count := l_msg_count;
1595 END IF;
1596
1597 IF l_debug_mode = 'Y' THEN
1598 Pa_Debug.reset_curr_function;
1599 END IF;
1600
1601 WHEN Invalid_Arg_Exc THEN
1602 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1603 x_msg_count := 1;
1604 x_msg_data := ' PA_CUSTOMERS_CONTACTS_UTILS : Get_Highest_Contr_Fed_Cust : NULL parameters passed';
1605
1606
1607 x_highst_contr_cust_id := null;
1608
1609
1610 IF p_commit = FND_API.G_TRUE THEN
1611 ROLLBACK TO get_highest_cont_cust_svpt;
1612 END IF;
1613
1614 Fnd_Msg_Pub.add_exc_msg
1615 ( p_pkg_name => 'PA_CUSTOMERS_CONTACTS_UTILS'
1616 , p_procedure_name => 'Get_Highest_Contr_Fed_Cust'
1617 , p_error_text => x_msg_data);
1618
1619 IF l_debug_mode = 'Y' THEN
1620 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1621 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
1622 l_debug_level5);
1623 Pa_Debug.reset_curr_function;
1624 END IF;
1625 RAISE;
1626
1627 WHEN OTHERS THEN
1628 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1629 x_msg_count := 1;
1630 x_msg_data := SQLERRM;
1631
1632
1633 x_highst_contr_cust_id := null;
1634
1635
1636 IF p_commit = FND_API.G_TRUE THEN
1637 ROLLBACK TO get_highest_cont_cust_svpt;
1638 END IF;
1639
1640 Fnd_Msg_Pub.add_exc_msg
1641 ( p_pkg_name => 'PA_CUSTOMERS_CONTACTS_UTILS'
1642 , p_procedure_name => 'Get_Highest_Contr_Fed_Cust'
1643 , p_error_text => x_msg_data);
1644
1645 IF l_debug_mode = 'Y' THEN
1646 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1647 Pa_Debug.WRITE(G_PKG_NAME,Pa_Debug.g_err_stage,
1648 l_debug_level5);
1649 Pa_Debug.reset_curr_function;
1650 END IF;
1651 RAISE;
1652
1653 END Get_Highest_Contr_Fed_Cust;
1654
1655 --sunkalya federal changes Bug#5511353
1656
1657
1658
1659 END PA_CUSTOMERS_CONTACTS_UTILS;
[Home] [Help]