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