4
1 package body PA_CUSTOMER_INFO as
2 -- $Header: PAXCSINB.pls 120.14 2011/09/22 12:01:54 djambhek ship $
3
5 --
6 -- PROCEDURE
7 -- Get_Customer_Info
8 --
9 --
10 Procedure Get_Customer_Info
11 ( X_project_ID In Number :=NULL,
12 X_Customer_Id In Number,
13 X_Bill_To_Customer_Id In Out NOCOPY Number , /* For Bug 2731449 */ --File.Sql.39 bug 4440895
14 X_Ship_To_Customer_Id In Out NOCOPY Number , /* For Bug 2731449 */ --File.Sql.39 bug 4440895
15 X_Bill_To_Address_Id In Out NOCOPY Number , -- Changed from 'Out' to 'In Out' parameter for Bug 3911782 --File.Sql.39 bug 4440895
16 X_Ship_To_Address_Id In Out NOCOPY Number , -- Changed from 'Out' to 'In Out' parameter for Bug 3911782 --File.Sql.39 bug 4440895
17 X_Bill_To_Contact_Id In Out NOCOPY Number, --File.Sql.39 bug 4440895 -- Changed from 'Out' to 'In Out' parameter aditi for tracking bug
18 X_Ship_To_Contact_Id In Out NOCOPY Number, --File.Sql.39 bug 4440895 -- Changed from 'Out' to 'In Out' parameter aditi for tracking bug
19 X_Err_Code In Out NOCOPY Number, --File.Sql.39 bug 4440895
20 X_Err_Stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
21 X_Err_Stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
22 p_quick_entry_flag In Varchar2, -- If customer id is not passed thru QE then this flag is set to N else Y.
23 p_calling_module IN varchar2 := NULL -- Added for Bug#4770535 for contacts validation
24 ) is
25
26 Pl_Bill_To_Address_Id Number := X_Bill_To_Address_Id; --Bug 3911782 changed from null to value being passed from pa_project_pub.update_project
27 Pl_Ship_To_Address_Id Number := X_Ship_To_Address_Id; --Bug 3911782 changed from null to value being passed from pa_project_pub.update_project
28 Pl_Bill_To_Contact_Id Number := X_Bill_To_Contact_Id; --for tracking bug by aditi changed from null to value being passed from pa_project_pub.update_project
29 Pl_Ship_To_Contact_Id Number := X_Ship_To_Contact_Id; --for tracking bug by aditi changed from null to value being passed from pa_project_pub.update_project
30
31 -- 4363092 TCA changes, replaced RA views with HZ tables
32 --Pl_Status Ra_customers.Status%type;
33 Pl_Status hz_cust_accounts.Status%type;
34 -- 4363092 end
35
36 l_address_id Number;
37 l_contact_id Number;
38 l_count Number := 0;
39 l_site_use_code VARCHAR2(30);
40
41 /* Start For Bug 2731449 */
42 l_cust_acc_rel_code VARCHAR2(1);
43 l_valid_bill_id Number;
44 l_valid_ship_id Number;
45 l_bill_to_customer_id Number;
46 l_ship_to_customer_id Number;
47 /* End For Bug 2731449 */
48
49 l_return_status VARCHAR2(1);
50 l_error_msg_code VARCHAR2(500);
51 l_customer_id Number;
52 l_ic_billing Varchar2(1);
53 l_ov Number;
54 l_ov1 Number;
55 l_return_value VARCHAR2(1); -- Added for Bug 3911782
56
57
58 -- 4363092 TCA changes, replaced RA views with HZ tables
59 /*
60 CURSOR C1 IS
61 SELECT a.Address_id, su.Contact_id, su.site_use_code
62 FROM Ra_Addresses a,
63 Ra_Site_Uses su
64 WHERE a.Address_Id = su.Address_Id
65 AND Nvl(a.Status,'A') = 'A'
66 AND a.Customer_id = X_Customer_Id
67 AND Nvl(su.Status, 'A') = 'A'
68 AND su.primary_flag = 'Y' ;
69 */
70
71 CURSOR C1 IS
72 SELECT acct_site.cust_acct_site_id, su.Contact_id, su.site_use_code
73 FROM hz_cust_acct_sites_all acct_site,
74 hz_cust_site_uses su
75 WHERE
76 acct_site.cust_acct_site_id = su.cust_acct_site_id
77 AND Nvl(acct_site.Status,'A') = 'A'
78 AND acct_site.cust_account_id = X_Customer_Id
79 AND Nvl(su.Status, 'A') = 'A'
80 AND su.primary_flag = 'Y' ;
81
82 -- 4363092 end
83
84 /* Start For Bug 2731449 */
85 -- Cursors added to get the valid contact ids for bill to and ship to customers
86 -- 4363092 TCA changes, replaced RA views with HZ tables
87 /*
88 CURSOR C2 IS
89 SELECT a.Address_id, su.Contact_id, su.site_use_code
90 FROM Ra_Addresses a,
91 Ra_Site_Uses su
92 WHERE a.Address_Id = su.Address_Id
93 AND Nvl(a.Status,'A') = 'A'
94 AND a.Customer_id = X_Bill_To_Customer_Id
95 AND Nvl(su.Status, 'A') = 'A'
96 AND su.primary_flag = 'Y'
97 AND su.site_use_code = 'BILL_TO';
98 */
99
100 CURSOR C2 IS
101 SELECT acct_site.cust_acct_site_id, su.Contact_id, su.site_use_code
102 FROM
103 hz_cust_acct_sites_all acct_site,
104 hz_cust_site_uses su
105 WHERE
106 acct_site.cust_acct_site_id = su.cust_acct_site_id
107 AND Nvl(acct_site.Status,'A') = 'A'
108 AND acct_site.cust_account_id = X_Bill_To_Customer_Id
109 AND Nvl(su.Status, 'A') = 'A'
110 AND su.primary_flag = 'Y'
111 AND su.site_use_code = 'BILL_TO';
112
113 -- 4363092 end
114
115 -- 4363092 TCA changes, replaced RA views with HZ tables
116 /*
117
118 CURSOR C3 IS
119 SELECT a.Address_id, su.Contact_id, su.site_use_code
120 FROM Ra_Addresses a,
121 Ra_Site_Uses su
122 WHERE a.Address_Id = su.Address_Id
123 AND Nvl(a.Status,'A') = 'A'
124 AND a.Customer_id = X_Ship_To_Customer_Id
125 AND Nvl(su.Status, 'A') = 'A'
126 AND su.primary_flag = 'Y'
127 AND su.site_use_code = 'SHIP_TO';
128 */
129
130 CURSOR C3 IS
131 SELECT acct_site.cust_acct_site_id, su.Contact_id, su.site_use_code
132 FROM
133 hz_cust_acct_sites_all acct_site,
134 hz_cust_site_uses su
135 WHERE
136 acct_site.cust_acct_site_id = su.cust_acct_site_id
137 AND Nvl(acct_site.Status,'A') = 'A'
138 AND acct_site.cust_account_id = X_Ship_To_Customer_Id
139 AND Nvl(su.Status, 'A') = 'A'
140 AND su.primary_flag = 'Y'
141 AND su.site_use_code = 'SHIP_TO';
142
143 -- 4363092 end
144
145 CURSOR C4 IS
146 Select cust_acc_rel_code
147 From pa_implementations;
148
149 --Commented the cursors C5 and C6 for bug#5478047
150
151 /* CURSOR C5 IS
152 SELECT related_cust_account_id
153 FROM hz_cust_acct_relate
154 WHERE cust_account_id = X_Customer_Id
155 AND bill_to_flag = 'Y'
156 AND status = 'A'
157 AND related_cust_account_id = X_Bill_To_Customer_Id;
158
159 CURSOR C6 IS
160 SELECT related_cust_account_id
161 FROM hz_cust_acct_relate
162 WHERE cust_account_id = X_Customer_Id
163 AND ship_to_flag = 'Y'
164 AND status = 'A'
165 AND related_cust_account_id = X_Ship_To_Customer_Id; */
166
167 --Added the new cursors C5 and C6 for bug#5478047
168
169 CURSOR C5 IS
170 SELECT cust_account_id
171 FROM hz_cust_acct_relate
172 WHERE related_cust_account_id = X_Customer_Id
173 AND bill_to_flag = 'Y'
174 AND status = 'A'
175 AND cust_account_id = X_Bill_To_Customer_Id; --Bug#5872732
176
177 CURSOR C6 IS
178 SELECT cust_account_id
179 FROM hz_cust_acct_relate
180 WHERE related_cust_account_id = X_Customer_Id
181 AND ship_to_flag = 'Y'
182 AND status = 'A'
183 AND cust_account_id = X_Ship_To_Customer_Id; --Bug#5872732
184
185 Cursor Ic_billing IS
186 Select pt.CC_PRVDR_FLAG
187 From pa_project_types pt, pa_projects pa
188 where pa.project_type=pt.project_type
189 and pa.project_id=x_project_id;
190
191 /* End For Bug 2731449 */
192
193 Begin
194 --dbms_output.put_line('Value of X_Customer_id'||X_Customer_id);
195 X_Err_Code := 0;
196 OPEN Ic_billing;
197 Fetch Ic_billing into l_ic_billing;
198 If l_ic_billing='Y' Then
199 X_Bill_To_Customer_id := X_Customer_id;
200 X_Ship_To_Customer_id := X_Customer_id;
201 /*Changes for 6630834*/
202 /*Changed as a part of 12907893 :START*/
203 /* elsif (X_Customer_id IS NOT NULL) OR (X_Customer_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN */
204 elsif (X_Customer_id IS NOT NULL) AND (X_Customer_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
205 /*Changed as a part of 12907893 :END*/
206 /* Changes for bug 8247716 start here */
207 if(NVL(X_Bill_To_Customer_id,PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) Then
208 X_Bill_To_Customer_id := X_Customer_id;
209 end if;
210 if(NVL(X_Ship_To_Customer_id,PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) Then
211 X_Ship_To_Customer_id := X_Customer_id;
212 end if;
213 /* Changes for bug 8247716 end here */
214 /*Changes for 6630834 end here*/
215 End if;
216 CLOSE Ic_billing; /*Added by avaithia for Bug 3876207*/
217
218 OPEN c4;
219 FETCH C4 into l_cust_acc_rel_code;
220 CLOSE C4;
221
222
223 --fix for bug#2045638 starts
224 --check whether the customer is active or not
225 X_Err_Code := 0;
226 X_Err_Stage := 'Get customer status <' ||to_char(X_Customer_Id) ||'>' ;
227 Begin
228
229 -- 4363092 TCA changes, replaced RA views with HZ tables
230 /*
231 Select Nvl(status,'A')
232 into Pl_Status
233 from Ra_Customers r
234 Where r.customer_id = X_Customer_Id;
235 */
236 Select Nvl(cust_acct.status,'A')
237 into Pl_Status
238 from hz_parties party,
239 hz_cust_accounts cust_acct
240 Where
241 cust_acct.party_id = party.party_id
242 and cust_acct.cust_account_id = X_Customer_Id;
243
244 -- 4363092 end
245
246 If Pl_Status = 'I' then
247 X_Err_Code := 20;
248 X_Err_Stage := 'PA_CUSTOMER_NOT_ACTIVE' ;
249 return;
250 end if;
251 Exception
252 When NO_DATA_FOUND then
253 X_Err_Code := 20;
254 X_Err_Stage := 'PA_CUSTOMER_ID_INVALID' ; --Bug#5183150.Changed the error message PA_CUSTOMER_NOT_EXIST to PA_CUSTOMER_ID_INVALID.
255 return;
256 When OTHERS then
257 X_Err_Code := SQLCODE;
258 return;
259 End;
260
261
262 If l_ic_billing='Y' or l_cust_acc_rel_code='N' THEN
263 X_Bill_To_Customer_id := X_Customer_id;
264 X_Ship_To_Customer_id := X_Customer_id;
265
266 OPEN C1;
267
268 LOOP
269 FETCH C1 INTO l_address_id, l_contact_id, l_site_use_code;
270 EXIT WHEN C1%NOTFOUND;
271
272 if l_site_use_code = 'SHIP_TO' then
273 /* Code changes begin for BUg 3911782 */
274
275 If Pl_Ship_To_Address_Id IS NULL
276 OR Pl_Ship_To_Address_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
277 Pl_Ship_To_Address_Id := l_address_id;
278 else
279 l_return_value := Is_Address_Valid(l_site_use_code => l_site_use_code ,
280 l_Customer_Id => X_Ship_To_Customer_Id,
281 l_Address_Id => Pl_Ship_To_Address_Id);
282 IF l_return_value = 'N' then
283 X_Err_Code := 20;
284 X_Err_Stage := 'PA_SHIP_TO_ADDR_INVALID';
285 return;
286 END IF;
287 End if;
288 -- Pl_Ship_To_Address_Id := l_address_id;
289 /* Code changes end for BUg 3911782 */
290 -- Pl_Ship_To_Contact_Id := l_contact_id; --commented for tracking bug
291 /* Changes begin for tracking bug by aditi */
292 If Pl_Ship_To_Contact_Id IS NULL
293 OR Pl_Ship_To_Contact_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
294 Pl_Ship_To_Contact_Id := l_contact_id;
295 Else
296 l_return_value := Is_Contact_Valid(l_site_use_code => l_site_use_code ,
297 l_Customer_Id => X_Ship_To_Customer_Id,
298 l_Address_Id => Pl_Ship_To_Address_Id,
299 l_Contact_Id => Pl_Ship_To_Contact_Id);
300 IF l_return_value = 'N' then
301 IF p_calling_module = 'AMG' then --added this if condition for Bug#4770535.Throw the error only in case of AMG
302 X_Err_Code := 20;
303 X_Err_Stage := 'PA_SHIP_TO_CONTACT_INVALID';
304 return;
305 ELSE
306 Pl_Ship_To_Contact_Id := NULL; -- added this for Bug#4770535 so that no contacts are copied if the
307 -- contacts are invalid.
308 END IF; --added this if condition for Bug#4770535
309
310 END IF;
311 End if;
312 /* Changes end for tracking bug by aditi **/
313 elsif l_site_use_code = 'BILL_TO' then
314 /* Code changes begin for BUg 3911782 */
315
316 If Pl_Bill_To_Address_Id IS NULL
317 OR Pl_Bill_To_Address_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
318 Pl_Bill_To_Address_Id := l_address_id;
322 --dbms_output.put_line('Value of Pl_Bill_To_Address_Id' ||Pl_Bill_To_Address_Id);
319 else
320 --dbms_output.put_line('Value of l_site_use_code' ||l_site_use_code);
321 --dbms_output.put_line('Value of X_Bill_To_Customer_Id' ||X_Bill_To_Customer_Id);
323
324 l_return_value := Is_Address_Valid(l_site_use_code => l_site_use_code ,
325 l_Customer_Id => X_Bill_To_Customer_Id,
326 l_Address_Id => Pl_Bill_To_Address_Id);
327
328 --dbms_output.put_line('is the error from this place');
329 IF l_return_value = 'N' then
330 X_Err_Code := 20;
331 X_Err_Stage := 'PA_BILL_TO_ADDR_INVALID';
332 return;
333 END IF;
334 End if;
335 -- Pl_Bill_To_Address_Id := l_address_id;
336 /* Code changes end for BUg 3911782 */
337 -- Pl_Bill_To_Contact_Id := l_contact_id; --Commented for tracking bug
338 /* Changes begin for tracking bug by aditi */
339 If Pl_Bill_To_Contact_Id IS NULL
340 OR Pl_Bill_To_Contact_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
341 Pl_Bill_To_Contact_Id := l_contact_id;
342 Else
343 l_return_value := Is_Contact_Valid(l_site_use_code => l_site_use_code ,
344 l_Customer_Id => X_Bill_To_Customer_Id,
345 l_Address_Id => Pl_Bill_To_Address_Id,
346 l_Contact_Id => Pl_Bill_To_Contact_Id);
347 IF l_return_value = 'N' then
348 IF p_calling_module = 'AMG' then --added this if condition for Bug#4770535.Throw the error only in case of AMG
349 X_Err_Code := 20;
350 X_Err_Stage := 'PA_BILL_TO_CONTACT_INVALID';
351 return;
352 ELSE
353 Pl_Bill_To_Contact_Id := NULL; -- added this for Bug#4770535 so that no contacts are copied if the
354 -- contacts are invalid.
355 END IF; --added this if condition for Bug#4770535
356
357 END IF;
358 End if;
359 /* Changes end for tracking bug by aditi **/
360 end if;
361
362 END LOOP;
363
364 CLOSE C1;
365
366 If Pl_Ship_To_Address_Id is null then
367 X_Err_Code := 20;
368 X_Err_Stage := 'PA_NO_SHIP_TO_ADDRESS';
369 return;
370 end if;
371
372 If Pl_Bill_To_Address_Id is null then
373 X_Err_Code := 20;
374 X_Err_Stage := 'PA_NO_BILL_TO_ADDRESS';
375 return;
376 end if;
377
378 -- If Pl_Ship_To_Contact_Id is null then
379 -- X_Err_Code := 30;
380 -- X_Err_Stage := 'PA_NO_SHIP_TO_CONTACT';
381 -- return;
382 -- end if;
383
384 /* Commented out the following if condtion for not showing the warning message if there is no active billing contact
385 for the customer for Bug#4995026 */
386
387 /*If Pl_Bill_To_Contact_Id is null and p_quick_entry_flag = 'Y' then */ -- Bug 2984536. Donot show warning otherwise.
388 /* X_Err_Code := 10; */
389 /* X_Err_Stage := 'PA_NO_BILL_TO_CONTACT';*/
390 /* commented the below line for bug 2977546 */
391 /* return; */
392 /*end if;*/ -- End of commenting for Bug#4995026
393
394 X_Bill_To_Address_Id := Pl_Bill_To_Address_Id ;
395 X_Ship_To_Address_Id := Pl_Ship_To_Address_Id ;
396 X_Bill_To_Contact_Id := Pl_Bill_To_Contact_Id ;
397 X_Ship_To_Contact_Id := Pl_Ship_To_Contact_Id ;
398 X_Bill_To_Customer_Id := X_Customer_Id;
399 X_Ship_To_Customer_Id := X_Customer_Id;
400 Return;
401 END IF;
402
403 /*If customer account relationship flag is Yes or all, validate the passed bill_to_customer_id and ship_to_customer_id */
404
405
406 IF l_cust_acc_rel_code = 'Y' or l_cust_acc_rel_code = 'A' Then
407
408 IF X_Bill_To_Customer_Id IS NOT NULL OR X_Bill_To_Customer_Id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN --Changed if Condition for Bug 6630834
409
410 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
411 ( p_customer_id => X_Bill_To_Customer_Id
412 ,p_check_id_flag => 'Y'
413 ,x_customer_id => l_customer_id
414 ,x_return_status => l_return_status
415 ,x_error_msg_code => l_error_msg_code);
416
417 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
418 X_Err_Code := 20;
419 X_Err_Stage := 'PA_CUSTOMER_ID_INVALID_BILL' ;
420 return;
421 end if;
422
423 /*validate the relation of passed customer_id and bill_to_customer_id */
424
425 l_Bill_To_Customer_id := X_Bill_To_Customer_id;
426 IF X_Bill_To_Customer_Id <> X_Customer_ID THEN
427 OPEN C5;
428 FETCH C5 INTO l_valid_bill_id;
429 Close C5;
430 End if;
431
432 End if;
433
434 IF X_Ship_To_Customer_Id IS NOT NULL OR X_Ship_To_Customer_Id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN --Changed if Condition for Bug 6630834
435
436 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
437 ( p_customer_id => X_Ship_To_Customer_Id
438 ,p_check_id_flag => 'Y'
439 ,x_customer_id => l_customer_id
440 ,x_return_status => l_return_status
444 X_Err_Code := 20;
441 ,x_error_msg_code => l_error_msg_code);
442
443 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
445 X_Err_Stage := 'PA_CUSTOMER_ID_INVALID_SHIP' ;
446 return;
447 end if;
448
449 /*validate the relation of passed customer_id and bill_to_customer_id */
450
451 IF X_ship_To_Customer_Id <> X_Customer_ID THEN
452 OPEN C6;
453 FETCH C6 INTO l_valid_ship_id;
454 Close C6;
455 End if;
456
457 END IF;
458
459 /*If customer account relationship is YES and invalid values are passed for bill to_customer_id and ship_to_customer_id,
460 throw an error message and stop processing */
461
462 IF X_customer_id = X_bill_to_customer_id THEN
463 l_valid_bill_id := 1;
464 END IF;
465
466 IF X_customer_id = X_ship_to_customer_id THEN
467 l_valid_ship_id := 1;
468 END IF;
469
470 IF l_cust_acc_rel_code = 'Y' THEN
471 If X_ship_To_Customer_Id is not null and X_bill_To_Customer_Id is not null then
472 If l_valid_ship_id IS NULL AND l_valid_bill_id is NULL THEN
473 If p_quick_entry_flag = 'Y' THEN
474 X_Err_Code := 20;
475 X_Err_Stage := 'PA_BOTH_CUST_NO_RLTD';
476 return;
477 Else
478 X_ship_To_Customer_Id := X_customer_id;
479 X_bill_To_Customer_Id := X_customer_id;
480 End if;
481 end if;
482 End if;
483
484 If X_ship_To_Customer_Id is not null Then
485 If l_valid_ship_id IS NULL THEN
486 If p_quick_entry_flag = 'Y' THEN
487 X_Err_Code := 20;
488 X_Err_Stage := 'PA_SHIP_TO_NOT_VALID' ;
489 return;
490 Else
491 X_ship_To_Customer_Id := X_customer_id;
492 End if;
493 end if;
494 end if;
495
496 if X_bill_To_Customer_Id is not null then
497 If l_valid_bill_id IS NULL THEN
498 If p_quick_entry_flag = 'Y' THEN
499 X_Err_Code := 20;
500 X_Err_Stage := 'PA_BILL_TO_NOT_VALID' ;
501 return;
502 Else
503 X_bill_To_Customer_Id := X_customer_id;
504 End if;
505 end if;
506 end if;
507
508 END IF;
509
510
511 If X_Bill_To_Customer_Id is null and X_Ship_To_Customer_Id is null then
512 X_bill_to_customer_id :=X_customer_ID;
513 X_ship_to_customer_id :=X_customer_ID;
514 /* Commented the below two lines for bug 2987225 */
515 /* X_Err_Code := 10;
516 X_Err_Stage := 'PA_BOTH_CUST_UPD_REQ' ;
517 */
518 End if;
519
520 If X_Bill_To_Customer_Id Is not null and X_Ship_To_Customer_Id is null then
521 X_ship_to_customer_id :=X_customer_ID;
522 /* Commented the below two lines for bug 2987225 */
523 /* X_Err_Code := 10;
524 X_Err_Stage := 'PA_STO_CUST_UPD_REQ' ;
525 */
526 End if;
527
528 If X_Bill_To_Customer_Id Is null and X_Ship_To_Customer_Id is not null then
529 X_bill_to_customer_id :=X_customer_ID;
530 /* Commented the below two lines for bug 2987225 */
531 /* X_Err_Code := 10;
532 X_Err_Stage := 'PA_BTO_CUST_UPD_REQ' ;
533 */
534 End if;
535
536 OPEN C2;
537 LOOP
538 FETCH C2 INTO l_address_id, l_contact_id, l_site_use_code;
539 EXIT WHEN C2%NOTFOUND;
540
541
542 if l_site_use_code = 'BILL_TO' then
543 /* Code changes begin for BUg 3911782 */
544 If Pl_Bill_To_Address_Id IS NULL
545 OR Pl_Bill_To_Address_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
546 Pl_Bill_To_Address_Id := l_address_id;
547 else
548
549 l_return_value := Is_Address_Valid(l_site_use_code => l_site_use_code ,
550 l_Customer_Id => X_Bill_To_Customer_Id,
551 l_Address_Id => Pl_Bill_To_Address_Id);
552
553
554 IF l_return_value = 'N' then
555 X_Err_Code := 20;
556 X_Err_Stage := 'PA_BILL_TO_ADDR_INVALID';
557 END IF;
558 End if;
559 -- Pl_Bill_To_Address_Id := l_address_id;
560 /* Code changes end for BUg 3911782 */
561 -- Pl_Bill_To_Contact_Id := l_contact_id;--Commented for tracking bug
562 /* Changes begin for tracking bug by aditi */
563 If Pl_Bill_To_Contact_Id IS NULL
564 OR Pl_Bill_To_Contact_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
565 Pl_Bill_To_Contact_Id := l_contact_id;
566 Else
567 l_return_value := Is_Contact_Valid(l_site_use_code => l_site_use_code ,
568 l_Customer_Id => X_Bill_To_Customer_Id,
569 l_Address_Id => Pl_Bill_To_Address_Id,
570 l_Contact_Id => Pl_Bill_To_Contact_Id);
571 IF l_return_value = 'N' then
572 IF p_calling_module = 'AMG' then --added this if condition for Bug#4770535.Throw the error only in case of AMG
573 X_Err_Code := 20;
574 X_Err_Stage := 'PA_BILL_TO_CONTACT_INVALID';
575 return;
576 ELSE
577 Pl_Bill_To_Contact_Id := NULL; -- added this for Bug#4770535 so that no contacts are copied if the
578 --contacts are invalid.
579 END IF; --added this if condition for Bug#4770535
580
581 END IF;
582 End if;
583 /* Changes end for tracking bug by aditi **/
584 end if;
585
586 END LOOP;
587
588 CLOSE C2;
589
590 If Pl_Bill_To_Address_Id is null then
591 X_Err_Code := 20;
592 X_Err_Stage := 'PA_NO_BILL_TO_ADDRESS';
593 return;
594 end if;
595
596
597 OPEN C3;
598 LOOP
599 FETCH C3 INTO l_address_id, l_contact_id, l_site_use_code;
600 EXIT WHEN C3%NOTFOUND;
601
602 if l_site_use_code = 'SHIP_TO' then
603 /* Code changes begin for BUg 3911782 */
604 If Pl_Ship_To_Address_Id IS NULL
605 OR Pl_Ship_To_Address_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
606 Pl_Ship_To_Address_Id := l_address_id;
607 else
608 l_return_value := Is_Address_Valid(l_site_use_code => l_site_use_code ,
609 l_Customer_Id => X_Ship_To_Customer_Id,
610 l_Address_Id => Pl_Ship_To_Address_Id);
611 IF l_return_value = 'N' then
612 X_Err_Code := 20;
613 X_Err_Stage := 'PA_SHIP_TO_ADDR_INVALID';
614 END IF;
615 End if;
616 -- Pl_Ship_To_Address_Id := l_address_id;
617 /* Code changes end for BUg 3911782 */
618 -- Pl_Ship_To_Contact_Id := l_contact_id; --Commented for tracking bug
619 /* Changes begin for tracking bug by aditi */
620 If Pl_Ship_To_Contact_Id IS NULL
621 OR Pl_Ship_To_Contact_Id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM Then
622 Pl_Ship_To_Contact_Id := l_contact_id;
623 Else
624 l_return_value := Is_Contact_Valid(l_site_use_code => l_site_use_code ,
625 l_Customer_Id => X_Ship_To_Customer_Id,
626 l_Address_Id => Pl_Ship_To_Address_Id,
627 l_Contact_Id => Pl_Ship_To_Contact_Id);
628 IF l_return_value = 'N' then
629 IF p_calling_module = 'AMG' then --added this if condition for Bug#4770535. Throw the error only in case of AMG
630 X_Err_Code := 20;
631 X_Err_Stage := 'PA_SHIP_TO_CONTACT_INVALID';
632 return;
633 ELSE
634 Pl_Ship_To_Contact_Id := NULL; -- added this for Bug#4770535 so that no contacts are copied if the
635 -- contacts are invalid.
636 END IF; --added this if condition for Bug#4770535
637
638 END IF;
639 End if;
640 /* Changes end for tracking bug by aditi **/
641 end if;
642
643 END LOOP;
644
645 CLOSE C3;
646
647 If Pl_Ship_To_Address_Id is null then
648 X_Err_Code := 20;
649 X_Err_Stage := 'PA_NO_SHIP_TO_ADDRESS';
650 return;
651 end if;
652
653 X_Bill_To_Address_Id := Pl_Bill_To_Address_Id ;
654 X_Ship_To_Address_Id := Pl_Ship_To_Address_Id ;
655 X_Bill_To_Contact_Id := Pl_Bill_To_Contact_Id ;
656 X_Ship_To_Contact_Id := Pl_Ship_To_Contact_Id ;
657
658 End If;
659
660 Exception
661 When Others Then
662 --In case of any unexpected exceptions
663 --Close the Cursor if it is open and then return
664 --Bug 3876207
665 IF Ic_billing%ISOPEN THEN
666 CLOSE Ic_billing;
667 END IF;
668 X_Err_Code := SQLCODE;
669 return ;
670 End Get_Customer_Info;
671
672
673 -- PROCEDURE
674 -- Create_Customer_Contacts
675 --
676
677 Procedure Create_Customer_Contacts
678 ( X_Project_Id In Number,
679 X_Customer_Id In Number,
680 X_Project_Relation_Code In Varchar2,
681 X_Customer_Bill_Split In Number,
682 X_Bill_To_Customer_Id In Number := NULL , /* For Bug 2731449 */
683 X_Ship_To_Customer_Id In Number := NULL , /* For Bug 2731449 */
684 X_Bill_To_Address_Id In Number,
685 X_Ship_To_Address_Id In Number,
686 X_Bill_To_Contact_Id In Number,
687 X_Ship_To_Contact_Id In Number,
688 X_Inv_Currency_Code In Varchar2,
689 X_Inv_Rate_Type In Varchar2,
690 X_Inv_Rate_Date In Date,
691 X_Inv_Exchange_Rate In Number,
692 X_Allow_Inv_Rate_Type_Fg In Varchar2,
693 X_Bill_Another_Project_Fg In Varchar2,
694 X_Receiver_Task_Id In Number,
695 P_default_top_task_customer In pa_project_customers.DEFAULT_TOP_TASK_CUST_FLAG%TYPE default 'N',
696 X_User In Number,
697 X_Login In Number,
698 X_Err_Code In Out NOCOPY Number, --File.Sql.39 bug 4440895
699 X_Err_Stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
700 X_Err_Stack In Out NOCOPY Varchar2 ) is --File.Sql.39 bug 4440895
701
702 l_dummy VARCHAR2(1); -- Added for Bug 3110489
703 /** Cursor added for Bug 3110489 **/
704 CURSOR Cur_Customer_Exists(c_project_id pa_projects_all.project_id%TYPE,
705 c_customer_id pa_project_customers.customer_id%TYPE)
706 IS
707 Select 'Y' FROM pa_project_customers
708 WHERE project_id = c_project_id
709 AND customer_id = c_customer_id;
710
711 --bug 4054587
712 l_billing VARCHAR2(10) := 'BILLING';
713 l_shipping VARCHAR2(10):= 'SHIPPING';
714
715 l_bill_to_contact_exists VARCHAR2(1); -- Bug 5554475
716 l_ship_to_contact_exists VARCHAR2(1); -- Bug 5554475
717
718 Begin
719
720 X_Err_Code := 0 ;
721
722 /** Code change begins for Bug 3110489 **/
723 OPEN Cur_Customer_Exists(X_Project_Id,X_Customer_Id);
724 FETCH Cur_Customer_Exists INTO l_dummy;
725 IF Cur_Customer_Exists%NOTFOUND then
726 /** Code change ends for Bug 3110489 **/
727
728 Insert Into Pa_Project_Customers
729 ( Project_Id,
730 Customer_Id,
731 Project_Relationship_Code,
732 Customer_Bill_Split,
733 bill_to_customer_id, /* For Bug 2731449 */
734 ship_to_customer_id, /* For Bug 2731449 */
735 Bill_To_Address_Id,
736 Ship_To_Address_Id,
737 Inv_Currency_Code,
738 Inv_Rate_Type,
739 Inv_Rate_Date,
740 Inv_Exchange_Rate,
741 Allow_Inv_User_Rate_Type_Flag,
742 Bill_Another_Project_Flag,
743 Receiver_Task_Id,
744 Creation_Date,
745 Created_By,
746 Last_Update_Date,
747 Last_Update_Login,
748 Last_Updated_By,
749 RECORD_VERSION_NUMBER,
750 DEFAULT_TOP_TASK_CUST_FLAG ) /*The top tak cust added for FPM Development */
751 Values (
752 X_Project_Id,
753 X_Customer_Id,
754 X_Project_Relation_Code,
755 X_Customer_Bill_Split,
756 X_Bill_To_Customer_Id, /* For Bug 2731449 */
757 X_Ship_To_Customer_Id, /* For Bug 2731449 */
758 X_Bill_To_Address_Id,
759 X_Ship_To_Address_Id,
760 X_Inv_Currency_Code,
761 X_Inv_Rate_Type,
762 X_Inv_Rate_Date,
763 X_Inv_Exchange_Rate,
764 X_Allow_Inv_Rate_Type_Fg,
765 X_Bill_Another_Project_Fg,
766 X_Receiver_Task_Id,
767 Sysdate,
768 X_User,
769 Sysdate,
770 X_Login,
771 X_User,
772 1,
773 P_default_top_task_customer) ;
774 End if; -- Added for Bug 3110489
775 --dbms_output.put_line('Value of X_Bill_To_Contact_Id'||X_Bill_To_Contact_Id);
776 --dbms_output.put_line('Value of X_Ship_To_Contact_Id'||X_Ship_To_Contact_Id);
777 --dbms_output.put_line('Value of X_Project_Id'||X_Project_Id);
778 --dbms_output.put_line('Value of X_Customer_Id'||X_Customer_Id);
779 --dbms_output.put_line('Value of X_Bill_To_Customer_Id'||X_Bill_To_Customer_Id);
780 --dbms_output.put_line('Value of X_Bill_To_Contact_Id'||X_Bill_To_Contact_Id);
781 --dbms_output.put_line('Value of l_billing'||l_billing);
782
783
784 --start of addition for bug 5554475
785 BEGIN
786 SELECT 'Y'
787 INTO l_bill_to_contact_exists
788 FROM dual
789 WHERE EXISTS (SELECT 'Y'
790 FROM Pa_Project_Contacts
791 WHERE Project_Id = X_Project_Id
792 AND Customer_Id = X_Customer_Id
793 AND Contact_Id = X_Bill_To_Contact_Id
794 AND PROJECT_CONTACT_TYPE_CODE = l_billing
795 );
796 EXCEPTION WHEN NO_DATA_FOUND THEN
797 l_bill_to_contact_exists := 'N';
798 END;
799
800 BEGIN
801 SELECT 'Y'
802 INTO l_ship_to_contact_exists
803 FROM dual
804 WHERE EXISTS (SELECT 'Y'
805 FROM Pa_Project_Contacts
806 WHERE Project_Id = X_Project_Id
807 AND Customer_Id = X_Customer_Id
808 AND Contact_Id = X_Ship_To_Contact_Id
809 AND PROJECT_CONTACT_TYPE_CODE = l_shipping
810 );
811 EXCEPTION WHEN NO_DATA_FOUND THEN
812 l_ship_to_contact_exists := 'N';
813 END;
814 --end of addition for bug 5554475
815
816
817
818 If X_Bill_To_Contact_Id is not null then
819 If l_bill_to_contact_exists = 'N' then /* for bug 5554475 */
820 Insert Into Pa_Project_Contacts
821 ( Project_Id,
822 Customer_Id,
823 bill_ship_customer_id, /* For Bug 2731449 */
824 Contact_Id,
825 Project_Contact_Type_Code,
826 Creation_Date,
827 Created_By,
828 Last_Update_Date,
829 Last_Update_Login,
830 Last_Updated_By,
831 RECORD_VERSION_NUMBER )
832 Values (
833 X_Project_Id,
834 X_Customer_Id,
835 X_Bill_To_Customer_Id, /* For Bug 2731449 */
836 X_Bill_To_Contact_Id,
837 --'BILLING', --bug 4054587
838 l_billing,
839 Sysdate,
840 X_User,
841 Sysdate,
842 X_Login,
843 X_User,
844 1 );
845 End If;
846 END if;
847
848 If X_Ship_To_Contact_Id is not null then
849 If l_ship_to_contact_exists = 'N' then /* for bug 5554475 */
850 Insert Into Pa_Project_Contacts
851 ( Project_Id,
852 Customer_Id,
853 bill_ship_customer_id, /* For Bug 2731449 */
854 Contact_Id,
855 Project_Contact_Type_Code,
856 Creation_Date,
857 Created_By,
858 Last_Update_Date,
859 Last_Update_Login,
860 Last_Updated_By,
861 RECORD_VERSION_NUMBER )
862 Values (
863 X_Project_Id,
864 X_Customer_Id,
865 X_Ship_To_Customer_Id, /* For Bug 2731449 */
866 X_Ship_To_Contact_Id,
867 --'SHIPPING', --bug 4054587
868 l_shipping,
869 Sysdate,
870 X_User,
871 Sysdate,
872 X_Login,
873 X_User,
874 1 );
875 End If ;
876 END if;
877
878 Exception
879 When Others Then
880 X_Err_Code := SQLCODE ;
881 --dbms_output.put_line('please show the SQL Error '||sqlerrm);
882 End Create_Customer_Contacts ;
883
884 -- API name : Is_Address_Valid
885 -- Type : Function
886 -- Pre-reqs : None
887 -- Return Value : 'Y', 'N'
888 -- Prameters
889 -- l_site_use_code IN VARCHAR2
890 -- l_Customer_Id IN NUMBER
891 -- l_Address_Id IN NUMBER
892 -- History
893 --
894 -- 12-OCT-2004 adarora -Created
895 --
896 -- Notes: This api is called from GET_CUSTOMER_INFO to validate bill_to_address_id and ship_to_address_id
897
898 FUNCTION Is_Address_Valid(l_site_use_code IN VARCHAR2 ,
899 l_Customer_Id IN NUMBER,
900 l_Address_Id IN NUMBER) RETURN VARCHAR2 IS
901 l_return_value VARCHAR2(1) := 'N';
902 BEGIN
903
904 BEGIN
905 -- 4363092 TCA changes, replaced RA views with HZ tables
906 /*
907 SELECT 'Y'
908 INTO l_return_value
909 FROM Ra_Addresses a,
910 Ra_Site_Uses su
911 WHERE a.Address_Id = su.Address_Id
912 AND Nvl(a.Status,'A') = 'A'
913 AND a.Customer_id = l_Customer_Id
914 AND a.Address_Id = l_Address_Id
915 AND Nvl(su.Status, 'A') = 'A'
916 AND su.site_use_code = l_site_use_code;
917 */
918
919 SELECT 'Y'
920 INTO l_return_value
921 FROM
922 hz_cust_acct_sites_all acct_site,
923 hz_cust_site_uses su
924 WHERE
925 acct_site.cust_acct_site_id = su.cust_acct_site_id
926 AND Nvl(acct_site.Status,'A') = 'A'
927 AND acct_site.cust_account_id = l_Customer_Id
928 AND acct_site.cust_acct_site_id = l_Address_Id
929 AND Nvl(su.Status, 'A') = 'A'
930 AND su.site_use_code = l_site_use_code;
931
932 -- 4363092 end
933 l_return_value := 'Y';
934
935 EXCEPTION
936 WHEN NO_DATA_FOUND THEN
937 l_return_value := 'N';
938
939 WHEN OTHERS THEN
940 l_return_value := 'N';
941 END;
942
943 RETURN l_return_value;
944 END Is_Address_Valid;
945
946 -- API name : Is_Contact_Valid
947 -- Type : Function
948 -- Pre-reqs : None
949 -- Return Value : 'Y', 'N'
950 -- Prameters
951 -- l_site_use_code IN VARCHAR2
952 -- l_Customer_Id IN NUMBER
953 -- l_Address_Id IN NUMBER
954 -- l_Contact_Id IN NUMBER
955 -- History
956 --
957 -- 02-FEB-2004 adarora -Created
958 --
959 -- Notes: This api is called from GET_CUSTOMER_INFO to validate bill_to_contact_id and ship_to_contact_id
960
961 FUNCTION Is_Contact_Valid(l_site_use_code IN VARCHAR2 ,
962 l_Customer_Id IN NUMBER,
963 l_Address_Id IN NUMBER,
964 l_Contact_Id IN NUMBER) RETURN VARCHAR2 IS
965 l_return_value VARCHAR2(1) := 'N';
966
967 BEGIN
968 --dbms_output.put_line('Value of l_site_use_code'||l_site_use_code);
969 --dbms_output.put_line('Value of l_Customer_Id'||l_Customer_Id);
970 --dbms_output.put_line('Value of l_Address_Id'||l_Address_Id);
971 --dbms_output.put_line('Value of l_Contact_Id'||l_Contact_Id);
972 l_return_value := 'N';
973 BEGIN
974 /* SELECT 'Y'
975 INTO l_return_value
976 FROM Ra_Addresses a,
977 Ra_Site_Uses su
978 WHERE a.Address_Id = su.Address_Id
979 AND Nvl(a.Status,'A') = 'A'
980 AND a.Customer_id = l_Customer_Id
981 AND a.Address_Id = l_Address_Id
982 AND su.Contact_Id = l_Contact_Id
983 AND Nvl(su.Status, 'A') = 'A'
984 AND su.site_use_code = l_site_use_code; */
985
986 -- 4633405 Start: TCA changes, replaced RA views with HZ tables
987 /*
988 SELECT 'Y'
989 INTO l_return_value
990 FROM
991 ra_contacts c,
992 ra_contact_roles cr
993 WHERE c.customer_id = l_Customer_Id
994 and c.contact_id = l_Contact_Id
995 and c.contact_id = cr.contact_id
996 and cr.usage_code = l_site_use_code
997 and nvl(c.status,'A') = 'A'
998 and c.address_id is null;
999 */
1000 SELECT 'Y'
1001 INTO l_return_value
1002 FROM
1003 hz_cust_account_roles acct_role,
1004 hz_role_responsibility hrrep
1005 WHERE acct_role.cust_account_id = l_Customer_Id
1006 and acct_role.cust_account_role_id = l_Contact_Id
1007 and acct_role.cust_account_role_id = hrrep.cust_account_role_id
1008 and hrrep.responsibility_type = l_site_use_code
1009 and nvl(acct_role.current_role_state,'A') = 'A'
1010 and acct_role.cust_acct_site_id is null
1011 and acct_role.role_type = 'CONTACT';
1012 -- 4633405 End: TCA changes, replaced RA views with HZ tables
1013
1014 l_return_value := 'Y';
1015 --dbms_output.put_line('Value of l_return_value in first loop'||l_return_value);
1016 EXCEPTION
1017 WHEN NO_DATA_FOUND THEN
1018 l_return_value := 'N';
1019
1020 WHEN OTHERS THEN
1021 l_return_value := 'N';
1022 END;
1023
1024 BEGIN
1025
1026 -- 4633405 Start: TCA changes, replaced RA views with HZ tables
1027 /*
1028 SELECT 'Y'
1029 INTO l_return_value
1030 FROM
1031 ra_contacts c ,
1032 ra_contact_roles cr ,
1033 ra_addresses a
1034 WHERE c.customer_id = l_Customer_Id
1035 and c.contact_id = cr.contact_id
1036 and c.contact_id = l_Contact_Id
1037 and cr.usage_code = l_site_use_code
1038 and nvl(c.status,'A') = 'A'
1039 and c.address_id = a.address_id
1040 and c.address_id = l_Address_Id;
1041 */
1042 SELECT 'Y'
1043 INTO l_return_value
1044 FROM
1045 hz_cust_account_roles acct_role ,
1046 hz_role_responsibility hrrep ,
1047 hz_cust_acct_sites_all acct_site
1048 WHERE acct_role.cust_account_id = l_Customer_Id
1049 and acct_role.cust_account_role_id = hrrep.cust_account_role_id
1050 and acct_role.cust_account_role_id = l_Contact_Id
1051 and hrrep.responsibility_type = l_site_use_code
1052 and nvl(acct_role.current_role_state,'A') = 'A'
1053 and acct_role.cust_acct_site_id = acct_site.cust_acct_site_id
1054 and acct_role.cust_acct_site_id = l_Address_Id
1055 and acct_role.role_type = 'CONTACT';
1056 -- 4633405 End: TCA changes, replaced RA views with HZ tables
1057
1058 l_return_value := 'Y';
1059 --dbms_output.put_line('Value of l_return_value in second loop'||l_return_value);
1060
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN
1063
1064 IF l_return_value <> 'Y' Then
1065 l_return_value := 'N';
1066 --dbms_output.put_line('Value of l_return_value in second loop'||l_return_value);
1067 End if;
1068 WHEN OTHERS THEN
1069 l_return_value := 'N';
1070 END;
1071
1072 RETURN l_return_value;
1073 END Is_Contact_Valid;
1074
1075 -- API name : revenue_accrued_or_billed
1076 -- Type : Function
1077 -- Pre-reqs : None
1078 -- Return Value : True, False
1079 -- Prameters
1080 -- l_Project_Id In Number
1081 -- History
1082 --
1083 -- 12-JUN-2005 adarora -Created
1084 --
1085 -- Notes: This api is called from UPDATE_PROJECT to check if customer_bill_split is updateable or nor
1086 -- depending upon whether any invoices or revenues have been chanrged against the passed project.
1087
1088
1089 Function revenue_accrued_or_billed( p_project_Id In Number)
1090 return boolean IS
1091 CURSOR C IS
1092 SELECT 'x'
1093 FROM dual
1094 WHERE exists
1095 (select null
1096 from pa_draft_revenues r
1097 where r.project_id = p_project_Id
1098 )
1099 or exists
1100 (select null
1101 from pa_draft_invoices i
1102 where i.project_id = p_project_Id
1103 );
1104 x_exists varchar2(1):= NULL;
1105 Begin
1106 OPEN C;
1107 FETCH C into x_exists;
1108 CLOSE C;
1109 IF (x_exists IS NOT NULL) THEN
1110 return TRUE;
1111 ELSE
1112 return FALSE;
1113 END IF;
1114 End revenue_accrued_or_billed;
1115
1116 -- API name : check_proj_tot_contribution
1117 -- Type : Function
1118 -- Pre-reqs : None
1119 -- Return Value : Number
1120 -- Prameters
1121 -- l_Project_Id In Number
1122 -- History
1123 --
1124 -- 12-JUN-2005 adarora -Created
1125 --
1126 -- Notes: This api is called from UPDATE_PROJECT to compute the net customer_bill_split
1127 -- for a contract project. It should not exceed 100. if it does, then an error is thrown.
1128
1129 Function check_proj_tot_contribution ( p_project_Id In Number, x_valid_proj_flag OUT NOCOPY BOOLEAN )
1130 -- File.sql.39 Bug 4633405 (For new API)
1131 return number is
1132 CURSOR c_prj IS
1133 select project_type_class_code,project_status_code
1134 from pa_projects_v
1135 where project_id = p_project_Id;
1136
1137 CURSOR C IS
1138 SELECT sum(pc.customer_bill_split)
1139 FROM pa_project_customers pc
1140 WHERE pc.project_id = p_project_Id
1141 GROUP by pc.project_id;
1142
1143
1144 x_percentage NUMBER(15):=0;
1145 l_proj_type_class pa_project_types_all.project_type_class_code%TYPE;
1146 l_project_status_code pa_projects_all.project_status_code%TYPE;
1147 Begin
1148 x_valid_proj_flag := false;
1149 IF p_project_id IS NOT NULL THEN
1150 OPEN c_prj;
1151 FETCH c_prj INTO l_proj_type_class,l_project_status_code;
1152 CLOSE c_prj;
1153 END IF;
1154
1155 /* The check has to be done only for an Approved Contract Type project. */
1156 IF nvl(l_proj_type_class,'NONE') = 'CONTRACT' --AND l_project_status_code = 'APPROVED'
1157 THEN
1158 x_valid_proj_flag := true;
1159 OPEN C;
1160 FETCH C into x_percentage;
1161 CLOSE C;
1162 return x_percentage;
1163 END IF;
1164
1165 IF (x_valid_proj_flag = FALSE) Then
1166 RETURN 0;
1167 END if;
1168
1169 EXCEPTION
1170 when OTHERS then
1171 x_valid_proj_flag := false;
1172
1173 End check_proj_tot_contribution;
1174
1175 -- API name : Check_Receiver_Proj_Enterable
1176 -- Type : Utility
1177 -- Pre-reqs : None.
1178 -- Parameters :
1179 -- p_project_id IN NUMBER Required
1180 -- p_customer_id IN NUMBER Required
1181 -- p_receiver_task_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1182 -- x_bill_another_project_flag OUT VARCHAR2 Required
1183 -- x_return_status OUT VARCHAR2 Required
1184 -- x_error_msg_code OUT VARCHAR2 Required
1185 -- History
1186 --
1187 -- 14-SEP-2005 adarora -Created
1188 --
1189 -- Notes: This api is called from UPDATE_PROJECT to check if the project and task can be specified
1190 -- as receiver project and task for the customer passed.
1191
1192 PROCEDURE CHECK_RECEIVER_PROJ_ENTERABLE
1193 ( p_project_id IN NUMBER
1194 ,p_customer_id IN NUMBER
1195 ,p_receiver_task_id IN NUMBER := FND_API.G_MISS_NUM
1196 ,x_bill_another_project_flag IN OUT NOCOPY VARCHAR2 -- File.sql.39 Bug 4633405 (For new API)
1197 ,x_return_status OUT NOCOPY VARCHAR2 -- File.sql.39 Bug 4633405 (For new API)
1198 ,x_error_msg_code OUT NOCOPY VARCHAR2 -- File.sql.39 Bug 4633405 (For new API)
1199 )
1200 IS
1201 l_dummy VARCHAR2(1);
1202 l_bill_another_project_flag VARCHAR2(1);
1203 CURSOR C1(c_project_id NUMBER) IS
1204 SELECT '1'
1205 FROM
1206 pa_project_types b
1207 WHERE b.project_type_class_code = 'CONTRACT' AND
1208 b.project_type =
1209 (SELECT project_type
1210 FROM pa_projects_all
1211 WHERE project_id = c_project_id) AND
1212 (b.cc_prvdr_flag = 'N' OR b.cc_prvdr_flag is NULL);
1213 CURSOR C2(c_customer_id NUMBER) IS
1214 SELECT '1'
1215 FROM pa_implementations_all
1216 WHERE customer_id = c_customer_id;
1217 CURSOR C3 IS
1218 SELECT '1'
1219 FROM pa_implementations
1220 WHERE cc_ic_billing_prvdr_flag = 'Y';
1221 BEGIN
1222 l_bill_another_project_flag := x_bill_another_project_flag;
1223 open C1(p_project_id);
1224 fetch C1 into l_dummy;
1225
1226 open C2(p_customer_id);
1227 fetch C2 into l_dummy;
1228
1229 open C3;
1230 fetch C3 into l_dummy;
1231
1232 if (C1%FOUND AND C2%FOUND AND C3%FOUND) then
1233 /* Doing this check here so that if standard invoice is generated for the project
1234 then we should not allow to check/uncheck the bill_another_project_flag */
1235 /* Start for bug 3255704 */
1236 if pa_invoice_utils.check_draft_invoice_exists(p_project_id,
1237 p_customer_id) = 0 then
1238 --dbms_output.put_line('No draft invoice');
1239
1240 if (p_receiver_task_id <> FND_API.G_MISS_NUM) AND (p_receiver_task_id is not NULL) then
1241 x_bill_another_project_flag := 'Y';
1242 else
1243 x_bill_another_project_flag := 'N';
1244 end if;
1245 x_return_status := FND_API.G_RET_STS_SUCCESS;
1246 else
1247 --dbms_output.put_line('draft invoice exists ');
1248
1249 x_return_status := FND_API.G_RET_STS_ERROR;
1250 x_error_msg_code := 'PA_REC_PROJ_NOT_ALLOWED';
1251 END if;
1252 else
1253
1254
1255 if (p_receiver_task_id <> FND_API.G_MISS_NUM) AND (p_receiver_task_id is not NULL) then
1256 x_return_status := FND_API.G_RET_STS_ERROR;
1257 x_error_msg_code := 'PA_REC_PROJ_NOT_ALLOWED';
1258 else
1259 x_bill_another_project_flag := 'N';
1260 x_return_status := FND_API.G_RET_STS_SUCCESS;
1261 end if;
1262 end if;
1263 --dbms_output.put_line('Value of x_bill_another_project_flag'||x_bill_another_project_flag);
1264 close C1;
1265 close C2;
1266 close C3;
1267 EXCEPTION
1268 when OTHERS then
1269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1270 raise;
1271 END CHECK_RECEIVER_PROJ_ENTERABLE;
1272
1273 END PA_CUSTOMER_INFO;
[Home] [Help]