[Home] [Help]
PACKAGE BODY: APPS.PA_CUSTOMERS_CONTACTS_PVT
Source
1 PACKAGE BODY PA_CUSTOMERS_CONTACTS_PVT AS
2 /* $Header: PARPCCVB.pls 120.6 2007/02/06 09:55:26 dthakker ship $ */
3
4
5 -- Global constant
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_CUSTOMERS_CONTACTS_PVT';
7
8
9 -- API name : Create_Project_Customer
10 -- Type : Private
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
14 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
15 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
16 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
17 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
18 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
19 -- p_project_id IN NUMBER Required
20 -- p_customer_id IN NUMBER Required
21 -- p_project_relationship_code IN VARCHAR2 Required
22 -- p_customer_bill_split IN NUMBER Required
23 -- p_bill_to_customer_id IN NUMBER Required /* For Bug 2731449 */
24 -- p_ship_to_customer_id IN NUMBER Required /* For Bug 2731449 */
25 -- p_bill_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
26 -- p_ship_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
27 -- p_inv_currency_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
28 -- p_inv_rate_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
29 -- p_inv_rate_date IN DATE Optional Default = FND_API.G_MISS_DATE
30 -- p_inv_exchange_rate IN NUMBER Optional Default = FND_API.G_MISS_NUM
31 -- p_allow_user_rate_type_flag IN VARCHAR2 Required Default = 'N'
32 -- p_receiver_task_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
33 -- x_return_status OUT VARCHAR2 Required
34 -- x_msg_count OUT NUMBER Required
35 -- x_msg_data OUT VARCHAR2 Optional
36
37 PROCEDURE CREATE_PROJECT_CUSTOMER
38 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
39 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
40 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
41 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
42 ,p_debug_mode IN VARCHAR2 := 'N'
43 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
44 ,p_project_id IN NUMBER
45 ,p_customer_id IN NUMBER
46 ,p_project_relationship_code IN VARCHAR2
47 ,p_customer_bill_split IN NUMBER
48 ,p_bill_to_customer_id IN NUMBER /* For Bug 2731449 */
49 ,p_ship_to_customer_id IN NUMBER /* For Bug 2731449 */
50 ,p_bill_to_address_id IN NUMBER := FND_API.G_MISS_NUM
51 ,p_ship_to_address_id IN NUMBER := FND_API.G_MISS_NUM
52 ,p_inv_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR
53 ,p_inv_rate_type IN VARCHAR2 := FND_API.G_MISS_CHAR
54 ,p_inv_rate_date IN DATE := FND_API.G_MISS_DATE
55 ,p_inv_exchange_rate IN NUMBER := FND_API.G_MISS_NUM
56 ,p_allow_user_rate_type_flag IN VARCHAR2 := 'N'
57 ,p_receiver_task_id IN NUMBER := FND_API.G_MISS_NUM
58 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
59 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
60 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
61 ,p_project_party_id IN NUMBER DEFAULT NULL
62 --Billing setup related changes for FP_M development. Tracking bug 3279981
63 ,p_Default_Top_Task_Cust_Flag IN VARCHAR2
64 )
65 IS
66 l_bill_another_project_flag pa_project_customers.bill_another_project_flag%TYPE;
67 l_msg_count NUMBER;
68 l_last_update_date DATE;
69 l_last_updated_by NUMBER(15);
70 l_creation_date DATE;
71 l_created_by NUMBER(15);
72 l_last_update_login NUMBER(15);
73 l_rowid VARCHAR2(250);
74 l_bill_to_address_id NUMBER;
75 l_ship_to_address_id NUMBER;
76 l_inv_currency_code VARCHAR2(250);
77 l_inv_rate_type VARCHAR2(250);
78 l_inv_rate_date DATE;
79 l_inv_exchange_rate NUMBER;
80 l_receiver_task_id NUMBER;
81 l_party_id NUMBER;
82 l_project_party_id NUMBER;
83 l_resource_id NUMBER;
84 l_wf_item_type VARCHAR2(30);
85 l_wf_type VARCHAR2(30);
86 l_wf_party_process VARCHAR2(30);
87 l_assignment_id NUMBER;
88 l_return_status VARCHAR2(1);
89 l_msg_data VARCHAR2(2000);
90 l_end_date_active DATE;
91
92 -- anlee org role changes
93 CURSOR l_check_org_csr IS
94 SELECT PARTY_ID
95 FROM PA_CUSTOMERS_V
96 WHERE CUSTOMER_ID = p_customer_id
97 AND PARTY_TYPE = 'ORGANIZATION';
98
99 l_temp NUMBER;
100 BEGIN
101 if (p_debug_mode = 'Y') then
102 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Create_Project_Customer BEGIN');
103 end if;
104
105 if p_commit = FND_API.G_TRUE then
106 savepoint create_project_customer_pvt;
107 end if;
108
109 if p_validation_level > 0 then
110 if (p_debug_mode = 'Y') then
111 pa_debug.debug('Performing validation...');
112 end if;
113 --dbms_output.put_line('Before calling VALIDATE_CUSTOMER p_customer_id '||p_customer_id);
114 PA_CUSTOMERS_CONTACTS_PVT.VALIDATE_CUSTOMER
115 ( p_validation_level => p_validation_level
116 ,p_calling_module => p_calling_module
117 ,p_debug_mode => p_debug_mode
118 ,p_max_msg_count => p_max_msg_count
119 ,p_action => 'INSERT'
120 ,p_project_id => p_project_id
121 ,p_customer_id => p_customer_id
122 ,p_customer_bill_split => p_customer_bill_split
123 ,p_bill_to_address_id => p_bill_to_address_id /* For Bug 2731449 */
124 ,p_ship_to_address_id => p_ship_to_address_id /* For Bug 2731449 */
125 ,p_inv_currency_code => p_inv_currency_code
126 ,p_inv_rate_type => p_inv_rate_type
127 ,p_inv_rate_date => p_inv_rate_date
128 ,p_inv_exchange_rate => p_inv_exchange_rate
129 ,p_allow_user_rate_type_flag => p_allow_user_rate_type_flag
130 ,p_receiver_task_id => p_receiver_task_id
131 ,x_bill_another_project_flag => l_bill_another_project_flag);
132 --dbms_output.put_line('after calling VALIDATE_CUSTOMER end '||l_msg_count);
133
134 l_msg_count := FND_MSG_PUB.count_msg;
135 if l_msg_count > 0 then
136 x_msg_count := l_msg_count;
137
138 raise FND_API.G_EXC_ERROR;
139 end if;
140 end if;
141
142 -- Populate who column values
143 l_last_update_date := sysdate;
144 l_last_updated_by := fnd_global.user_id;
145 l_creation_date := sysdate;
146 l_created_by := fnd_global.user_id;
147 l_last_update_login := fnd_global.login_id;
148
149 -- Convert default constants to null
150 if p_bill_to_address_id = FND_API.G_MISS_NUM then
151 l_bill_to_address_id := NULL;
152 else
153 l_bill_to_address_id := p_bill_to_address_id;
154 end if;
155
156 if p_ship_to_address_id = FND_API.G_MISS_NUM then
157 l_ship_to_address_id := NULL;
158 else
159 l_ship_to_address_id := p_ship_to_address_id;
160 end if;
161
162 if p_inv_currency_code = FND_API.G_MISS_CHAR then
163 l_inv_currency_code := NULL;
164 else
165 l_inv_currency_code := p_inv_currency_code;
166 end if;
167
168 if p_inv_rate_type = FND_API.G_MISS_CHAR then
169 l_inv_rate_type := NULL;
170 else
171 l_inv_rate_type := p_inv_rate_type;
172 end if;
173
174 if p_inv_rate_date = FND_API.G_MISS_DATE then
175 l_inv_rate_date := NULL;
176 else
177 l_inv_rate_date := p_inv_rate_date;
178 end if;
179
180 if p_inv_exchange_rate = FND_API.G_MISS_NUM OR
181 upper(NVL(l_inv_rate_type,'x')) <> 'USER' --Bug#5554475
182 then
183 l_inv_exchange_rate := NULL;
184 else
185 l_inv_exchange_rate := p_inv_exchange_rate;
186 end if;
187
188 if p_receiver_task_id = FND_API.G_MISS_NUM then
189 l_receiver_task_id := NULL;
190 else
191 l_receiver_task_id := p_receiver_task_id;
192 end if;
193
194 if p_validate_only <> FND_API.G_TRUE then
195
196 PA_PROJECT_CUSTOMERS_PKG.INSERT_ROW
197 ( X_Rowid => l_rowid
198 ,X_Project_Id => p_project_id
199 ,X_Customer_Id => p_customer_id
200 ,X_Last_Update_Date => l_last_update_date
201 ,X_Last_Updated_By => l_last_updated_by
202 ,X_Creation_Date => l_creation_date
203 ,X_Created_By => l_created_by
204 ,X_Last_Update_Login => l_last_update_login
205 ,X_Project_Relationship_Code => p_project_relationship_code
206 ,X_Customer_Bill_Split => p_customer_bill_split
207 ,X_Bill_To_Customer_Id => p_bill_to_customer_id /* For Bug 2731449 */
208 ,X_Ship_To_Customer_Id => p_ship_to_customer_id /* For Bug 2731449 */
209 ,X_Bill_To_Address_Id => l_bill_to_address_id
210 ,X_Ship_To_Address_Id => l_ship_to_address_id
211 ,X_Inv_Currency_Code => l_inv_currency_code
212 ,X_Inv_Rate_Type => l_inv_rate_type
213 ,X_Inv_Rate_Date => l_inv_rate_date
214 ,X_Inv_Exchange_Rate => l_inv_exchange_rate
215 ,X_Allow_Inv_User_Rate_Type_Fg => p_allow_user_rate_type_flag
216 ,X_Bill_Another_Project_Flag => l_bill_another_project_flag
217 ,X_Receiver_Task_Id => l_receiver_task_id
218 ,X_Record_Version_Number => 1
219 --Billing setup related changes for FP_M development. Tracking bug 3279981
220 ,X_Default_Top_Task_Cust_Flag => p_Default_Top_Task_Cust_Flag );
221
222 -- anlee org role changes
223 -- If p_project_party_id is not null, then this API has been
224 -- called from org details flow
225 -- In this case, just update pa_project_customers with this
226 -- project_party_id
227 if p_project_party_id is not null then
228 UPDATE PA_PROJECT_CUSTOMERS
229 SET project_party_id = p_project_party_id
230 WHERE rowid = l_rowid;
231 else
232 -- Not from org details flow
233 -- create a project party if the added customer is an organization
234 l_party_id := null;
235 l_project_party_id := null;
236 OPEN l_check_org_csr;
237 FETCH l_check_org_csr INTO l_party_id;
238 IF l_check_org_csr%NOTFOUND then
239 l_party_id := null;
240 END IF;
241 CLOSE l_check_org_csr;
242
243 if l_party_id is not null then
244 l_temp := null;
245 -- check if the org already exists as a customer org on the project
246 l_temp := PA_PROJECT_PARTIES_UTILS.get_customer_project_party_id
247 ( p_project_id => p_project_id
248 ,p_customer_id => p_customer_id);
249
250 if l_temp is null then
251
252 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
253 p_validate_only => FND_API.G_FALSE
254 , p_object_id => p_project_id
255 , p_OBJECT_TYPE => 'PA_PROJECTS'
256 , p_project_role_id => 100
257 , p_project_role_type => 'CUSTOMER ORG'
258 , p_RESOURCE_TYPE_ID => 112
259 , p_resource_source_id => l_party_id
260 , p_start_date_active => null
261 , p_calling_module => 'FORM'
262 , p_project_id => p_project_id
263 , p_project_end_date => null
264 , p_end_date_active => l_end_date_active
265 , x_project_party_id => l_project_party_id
266 , x_resource_id => l_resource_id
267 , x_wf_item_type => l_wf_item_type
268 , x_wf_type => l_wf_type
269 , x_wf_process => l_wf_party_process
270 , x_assignment_id => l_assignment_id
271 , x_return_status => l_return_status
272 , x_msg_count => l_msg_count
273 , x_msg_data => l_msg_data );
274
275 l_msg_count := FND_MSG_PUB.count_msg;
276 if l_msg_count > 0 then
277 x_msg_count := l_msg_count;
278
279 raise FND_API.G_EXC_ERROR;
280 end if;
281
282 -- Add the new project party ID to the customers row
283 UPDATE PA_PROJECT_CUSTOMERS
284 SET project_party_id = l_project_party_id
285 WHERE rowid = l_rowid;
286 else
287 -- Add the existing project party ID to the customers row
288 UPDATE PA_PROJECT_CUSTOMERS
289 SET project_party_id = l_temp
290 WHERE rowid = l_rowid;
291 end if;
292 end if;
293
294 end if;
295 end if;
296
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 if p_commit = FND_API.G_TRUE then
300 commit work;
301 end if;
302
303 if (p_debug_mode = 'Y') then
304 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Create_Project_Customer END');
305 end if;
306
307 EXCEPTION
308 when FND_API.G_EXC_ERROR then
309 if p_commit = FND_API.G_TRUE then
310 rollback to create_project_customer_pvt;
311 end if;
312 x_return_status := FND_API.G_RET_STS_ERROR;
313 when others then
314 if p_commit = FND_API.G_TRUE then
315 rollback to create_project_customer_pvt;
316 end if;
317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
318 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
319 p_procedure_name => 'Create_Project_Customer',
320 p_error_text => SUBSTRB(SQLERRM,1,240));
321 raise;
322 END CREATE_PROJECT_CUSTOMER;
323
324
325 -- API name : Update_Project_Customer
326 -- Type : Private
327 -- Pre-reqs : None.
328 -- Parameters :
329 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
330 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
331 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
332 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
333 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
334 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
335 -- p_project_id IN NUMBER Required
336 -- p_customer_id IN NUMBER Required
337 -- p_project_relationship_code IN VARCHAR2 Required
338 -- p_customer_bill_split IN NUMBER Required
339 -- p_bill_to_customer_id IN NUMBER Required
340 -- p_ship_to_customer_id IN NUMBER Required
341 -- p_bill_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
345 -- p_inv_rate_date IN DATE Optional Default = FND_API.G_MISS_DATE
342 -- p_ship_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
343 -- p_inv_currency_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
344 -- p_inv_rate_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
346 -- p_inv_exchange_rate IN NUMBER Optional Default = FND_API.G_MISS_NUM
347 -- p_allow_user_rate_type_flag IN VARCHAR2 Required Default = 'N'
348 -- p_receiver_task_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
349 -- p_record_version_number IN NUMBER Required Default = FND_API.G_MISS_NUM
350 -- x_return_status OUT VARCHAR2 Required
351 -- x_msg_count OUT NUMBER Required
352 -- x_msg_data OUT VARCHAR2 Optional
353
354 PROCEDURE UPDATE_PROJECT_CUSTOMER
355 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
356 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
357 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
358 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
359 ,p_debug_mode IN VARCHAR2 := 'N'
360 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
361 ,p_project_id IN NUMBER
362 ,p_customer_id IN NUMBER
363 ,p_project_relationship_code IN VARCHAR2
364 ,p_customer_bill_split IN NUMBER
365 ,p_bill_to_customer_id IN NUMBER /* For Bug 2731449 */
366 ,p_ship_to_customer_id IN NUMBER /* For Bug 2731449 */
367 ,p_bill_to_address_id IN NUMBER := FND_API.G_MISS_NUM
368 ,p_ship_to_address_id IN NUMBER := FND_API.G_MISS_NUM
369 ,p_inv_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR
370 ,p_inv_rate_type IN VARCHAR2 := FND_API.G_MISS_CHAR
371 ,p_inv_rate_date IN DATE := FND_API.G_MISS_DATE
372 ,p_inv_exchange_rate IN NUMBER := FND_API.G_MISS_NUM
373 ,p_allow_user_rate_type_flag IN VARCHAR2 := 'N'
374 ,p_receiver_task_id IN NUMBER := FND_API.G_MISS_NUM
375 ,p_bill_another_project_flag IN VARCHAR2 := 'N' --Added by Aditi for tracking bug 4153629
376 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
377 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
378 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
379 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
380 --Billing setup related changes for FP_M development. Tracking bug 3279981
381 ,p_Default_Top_Task_Cust_Flag IN VARCHAR2
382 )
383 IS
384 l_bill_another_project_flag pa_project_customers.bill_another_project_flag%TYPE;
385 l_msg_count NUMBER;
386 l_msg_data VARCHAR(250);
387 l_last_update_date DATE;
388 l_last_updated_by NUMBER(15);
389 l_last_update_login NUMBER(15);
390 l_rowid VARCHAR2(250);
391 l_dummy VARCHAR2(1);
392 l_bill_to_address_id NUMBER;
393 l_ship_to_address_id NUMBER;
394 l_inv_currency_code VARCHAR2(250);
395 l_inv_rate_type VARCHAR2(250);
396 l_inv_rate_date DATE;
397 l_inv_exchange_rate NUMBER;
398 l_receiver_task_id NUMBER;
399
400 CURSOR C (c_project_id NUMBER, c_customer_id NUMBER) IS
401 SELECT rowid
402 FROM pa_project_customers
403 WHERE project_id = c_project_id AND
404 customer_id = c_customer_id;
405
406 BEGIN
407 --dbms_output.put_line('value of p_project_relationship_code in pvt '||p_project_relationship_code);
408 --dbms_output.put_line('Value of p_validate_only'||p_validate_only);
409 --dbms_output.put_line('Value of p_validation_level'||p_validation_level);
410
411 if (p_debug_mode = 'Y') then
412 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Update_Project_Customer BEGIN');
413 end if;
414
415 if p_commit = FND_API.G_TRUE then
416 savepoint update_project_customer_pvt;
417 end if;
418
419 if (p_debug_mode = 'Y') then
420 pa_debug.debug('Locking record...');
421 end if;
422
423 if p_validate_only <> FND_API.G_TRUE then
424 BEGIN
425 SELECT 'x' INTO l_dummy
426 FROM pa_project_customers
427 WHERE project_id = p_project_id
428 AND customer_id = p_customer_id
429 AND record_version_number = p_record_version_number
430 FOR UPDATE OF record_version_number NOWAIT;
431 EXCEPTION
432 when TIMEOUT_ON_RESOURCE then
433 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
434 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
435 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
436 when NO_DATA_FOUND then
437 if p_calling_module = 'FORM' then
438 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
439 p_msg_name => 'FORM_RECORD_CHANGED');
440 l_msg_data := 'FORM_RECORD_CHANGED';
441 else
442 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
443 p_msg_name => 'PA_XC_RECORD_CHANGED');
447 if SQLCODE = -54 then
444 l_msg_data := 'PA_XC_RECORD_CHANGED';
445 end if;
446 when OTHERS then
448 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
449 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
450 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
451 else
452 raise;
453 end if;
454 END;
455 else
456 --dbms_output.put_line('control wil come till here - print ');
457 --dbms_output.put_line('control wil come till here - print p_project_id '||p_project_id);
458
459 --dbms_output.put_line('control wil come till here - print p_customer_id'||p_customer_id);
460 --dbms_output.put_line('control wil come till here - print p_record_version_number'||p_record_version_number);
461
462 BEGIN
463 SELECT 'x' INTO l_dummy
464 FROM pa_project_customers
465 WHERE project_id = p_project_id
466 AND customer_id = p_customer_id
467 AND record_version_number = p_record_version_number;
468 EXCEPTION
469 when NO_DATA_FOUND then
470 --dbms_output.put_line('comes here');
471 if p_calling_module = 'FORM' then
472 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
473 p_msg_name => 'FORM_RECORD_CHANGED');
474 l_msg_data := 'FORM_RECORD_CHANGED';
475 else
476 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
477 p_msg_name => 'PA_XC_RECORD_CHANGED');
478 l_msg_data := 'PA_XC_RECORD_CHANGED';
479 end if;
480 when OTHERS then
481 --dbms_output.put_line('comes here'||sqlerrm);
482
483 raise;
484 END;
485 end if;
486 --dbms_output.put_line('thuis tooo');
487 l_msg_count := FND_MSG_PUB.count_msg;
488 --dbms_output.put_line('thuis tooo l_msg_count'||l_msg_count);
489
490 if l_msg_count > 0 then
491 x_msg_count := l_msg_count;
492 if x_msg_count = 1 then
493 x_msg_data := l_msg_data;
494 end if;
495 raise FND_API.G_EXC_ERROR;
496 end if;
497 --dbms_output.put_line('Value of p_validate_only'||p_validate_only);
498 --dbms_output.put_line('Value of p_bill_another_project_flag before 11 insert_row '||p_bill_another_project_flag);
499
500
501
502 --dbms_output.put_line('Value of l_bill_another_project_flag before 11 insert_row '||l_bill_another_project_flag);
503
504 if p_validation_level > 0 then
505 if (p_debug_mode = 'Y') then
506 pa_debug.debug('Performing validation...');
507 end if;
508 --dbms_output.put_line('value of p_project_relationship_code in pvt '||p_project_relationship_code);
509 --dbms_output.put_line('value of p_calling_module passed from pvt to validate'||p_calling_module);
510 PA_CUSTOMERS_CONTACTS_PVT.VALIDATE_CUSTOMER
511 ( p_validation_level => p_validation_level
512 ,p_calling_module => p_calling_module
513 ,p_debug_mode => p_debug_mode
514 ,p_max_msg_count => p_max_msg_count
515 ,p_action => 'UPDATE'
516 ,p_project_id => p_project_id
517 ,p_customer_id => p_customer_id
518 ,p_customer_bill_split => p_customer_bill_split
519 ,p_bill_to_address_id => p_bill_to_address_id
520 ,p_ship_to_address_id => p_ship_to_address_id
521 ,p_inv_currency_code => p_inv_currency_code
522 ,p_inv_rate_type => p_inv_rate_type
523 ,p_inv_rate_date => p_inv_rate_date
524 ,p_inv_exchange_rate => p_inv_exchange_rate
525 ,p_allow_user_rate_type_flag => p_allow_user_rate_type_flag
526 ,p_receiver_task_id => p_receiver_task_id
527 ,x_bill_another_project_flag => l_bill_another_project_flag);
528 --dbms_output.put_line('Value of l_bill_another_project_flag before 22222 insert_row '||l_bill_another_project_flag);
529
530 l_msg_count := FND_MSG_PUB.count_msg;
531 --dbms_output.put_line('Value of l_msg_count'||l_msg_count);
532 if l_msg_count > 0 then
533 x_msg_count := l_msg_count;
534
535 raise FND_API.G_EXC_ERROR;
536 end if;
537 end if;
538 --dbms_output.put_line('value of p_project_relationship_code after validate '||p_project_relationship_code);
539
540 -- Populate who column values
541 l_last_update_date := sysdate;
542 l_last_updated_by := fnd_global.user_id;
543 l_last_update_login := fnd_global.login_id;
544
545 -- Convert default constants to null
546 if p_bill_to_address_id = FND_API.G_MISS_NUM then
547 l_bill_to_address_id := NULL;
548 else
549 l_bill_to_address_id := p_bill_to_address_id;
550 end if;
551
552 if p_ship_to_address_id = FND_API.G_MISS_NUM then
553 l_ship_to_address_id := NULL;
554 else
555 l_ship_to_address_id := p_ship_to_address_id;
556 end if;
557
558 if p_inv_currency_code = FND_API.G_MISS_CHAR then
559 l_inv_currency_code := NULL;
560 else
561 l_inv_currency_code := p_inv_currency_code;
562 end if;
563
564 if p_inv_rate_type = FND_API.G_MISS_CHAR then
565 l_inv_rate_type := NULL;
566 else
567 l_inv_rate_type := p_inv_rate_type;
571 l_inv_rate_date := NULL;
568 end if;
569
570 if p_inv_rate_date = FND_API.G_MISS_DATE then
572 else
573 l_inv_rate_date := p_inv_rate_date;
574 end if;
575
576 if p_inv_exchange_rate = FND_API.G_MISS_NUM OR
577 upper(NVL(l_inv_rate_type,'x')) <> 'USER' --bug 3116595
578 then
579 l_inv_exchange_rate := NULL;
580 else
581 l_inv_exchange_rate := p_inv_exchange_rate;
582 end if;
583
584 if p_receiver_task_id = FND_API.G_MISS_NUM then
585 l_receiver_task_id := NULL;
586 else
587 l_receiver_task_id := p_receiver_task_id;
588 end if;
589 --dbms_output.put_line('Value of p_validate_only'||p_validate_only);
590 if p_validate_only <> FND_API.G_TRUE then
591 open C(p_project_id, p_customer_id);
592 fetch C into l_rowid;
593 close C;
594 --dbms_output.put_line('value of p_project_relationship_code in pvt be4 update_row '||p_project_relationship_code);
595 --dbms_output.put_line('Value of l_bill_another_project_flag before insert_row '||l_bill_another_project_flag);
596 /* Added for tracking bug 4153629 */
597 IF (p_calling_module = 'AMG') THEN
598 l_bill_another_project_flag := p_bill_another_project_flag; -- aDDED FOR TRACKING bug
599 End If;
600 /* End of changes for tracking bug 4153629*/
601 PA_PROJECT_CUSTOMERS_PKG.UPDATE_ROW
602 ( X_Rowid => l_rowid
603 ,X_Project_Id => p_project_id
604 ,X_Customer_Id => p_customer_id
605 ,X_Last_Update_Date => l_last_update_date
606 ,X_Last_Updated_By => l_last_updated_by
607 ,X_Last_Update_Login => l_last_update_login
608 ,X_Project_Relationship_Code => p_project_relationship_code
609 ,X_Customer_Bill_Split => p_customer_bill_split
610 ,X_Bill_To_Customer_Id => p_bill_to_customer_id /* For Bug 2731449 */
611 ,X_Ship_To_Customer_Id => p_ship_to_customer_id /* For Bug 2731449 */
612 ,X_Bill_To_Address_Id => l_bill_to_address_id
613 ,X_Ship_To_Address_Id => l_ship_to_address_id
614 ,X_Inv_Currency_Code => l_inv_currency_code
615 ,X_Inv_Rate_Type => l_inv_rate_type
616 ,X_Inv_Rate_Date => l_inv_rate_date
617 ,X_Inv_Exchange_Rate => l_inv_exchange_rate
618 ,X_Allow_Inv_User_Rate_Type_Fg => p_allow_user_rate_type_flag
619 ,X_Bill_Another_Project_Flag => l_bill_another_project_flag
620 ,X_Receiver_Task_Id => l_receiver_task_id
621 ,X_Record_Version_Number => p_record_version_number
622 --Billing setup related changes for FP_M development. Tracking bug 3279981
623 ,X_Default_Top_Task_Cust_Flag => p_Default_Top_Task_Cust_Flag );
624 end if;
625
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627
628 if p_commit = FND_API.G_TRUE then
629 commit work;
630 end if;
631
632 if (p_debug_mode = 'Y') then
633 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Update_Project_Customer END');
634 end if;
635
636 EXCEPTION
637 when FND_API.G_EXC_ERROR then
638 if p_commit = FND_API.G_TRUE then
639 rollback to update_project_customer_pvt;
640 end if;
641 x_return_status := FND_API.G_RET_STS_ERROR;
642 when others then
643 if p_commit = FND_API.G_TRUE then
644 rollback to update_project_customer_pvt;
645 end if;
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
648 p_procedure_name => 'Update_Project_Customer',
649 p_error_text => SUBSTRB(SQLERRM,1,240));
650 raise;
651 END UPDATE_PROJECT_CUSTOMER;
652
653
654 -- API name : Delete_Project_Customer
655 -- Type : Private
656 -- Pre-reqs : None.
657 -- Parameters :
658 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
659 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
660 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
661 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
662 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
663 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
664 -- p_project_id IN NUMBER Required
665 -- p_customer_id IN NUMBER Required
666 -- p_record_version_number IN NUMBER Required Default = FND_API.G_MISS_NUM
667 -- x_return_status OUT VARCHAR2 Required
668 -- x_msg_count OUT NUMBER Required
669 -- x_msg_data OUT VARCHAR2 Optional
670
671 PROCEDURE DELETE_PROJECT_CUSTOMER
672 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
673 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
674 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
675 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
676 ,p_debug_mode IN VARCHAR2 := 'N'
677 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
678 ,p_project_id IN NUMBER
682 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
679 ,p_customer_id IN NUMBER
680 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
681 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
683 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
684 )
685 IS
686 l_bill_another_project_flag pa_project_customers.bill_another_project_flag%TYPE;
687 l_return_status VARCHAR2(1);
688 l_msg_count NUMBER;
689 l_msg_data VARCHAR2(250);
690 l_rowid VARCHAR2(250);
691 l_dummy VARCHAR2(1);
692 /* Variables declared for bug 3101990 */
693 l_project_party_id pa_project_customers.project_party_id%TYPE;
694 l_ext_people_exists pa_project_customers.project_party_id%TYPE;
695 l_billing_accnt_exists pa_project_customers.project_party_id%TYPE;
696
697 CURSOR C (c_project_id NUMBER, c_customer_id NUMBER) IS
698 SELECT rowid
699 FROM pa_project_customers
700 WHERE project_id = c_project_id AND
701 customer_id = c_customer_id;
702
703 /* 3 new cursors defined for bug 3101990 */
704
705 CURSOR get_project_party_id IS
706 SELECT project_party_id
707 FROM pa_project_customers
708 WHERE project_id = p_project_id AND
709 customer_id = p_customer_id;
710
711 -- 4616302 TCA UPTAKE: HZ_PARTY_RELATIONS IMPACTS
712 -- changed hz_party_relationships usage to hz_relationships
713 -- changed column party_relationship_type usage to relationship_type
714
715 /*
716 CURSOR c_ext_people_exists(c_project_party_id NUMBER) IS
717 SELECT pp.project_party_id project_party_id
718 FROM pa_project_parties po,
719 pa_project_parties pp,
720 hz_party_relationships hzr
721 WHERE po.resource_type_id = 112
722 AND po.project_party_id = c_project_party_id
723 AND pp.resource_type_id = 112
724 AND pp.object_type = po.object_type
725 AND pp.object_id = po.object_id
726 AND hzr.party_relationship_type IN ( 'EMPLOYEE_OF', 'CONTACT_OF')
727 AND hzr.subject_id = pp.resource_source_id
728 AND hzr.object_id = po.resource_source_id;
729 */
730
731 CURSOR c_ext_people_exists(c_project_party_id NUMBER) IS
732 SELECT pp.project_party_id project_party_id
733 FROM pa_project_parties po,
734 pa_project_parties pp,
735 hz_relationships hzr
736 WHERE po.resource_type_id = 112
737 AND po.project_party_id = c_project_party_id
738 AND pp.resource_type_id = 112
739 AND pp.object_type = po.object_type
740 AND pp.object_id = po.object_id
741 AND hzr.relationship_code IN ( 'EMPLOYEE_OF', 'CONTACT_OF')
742 AND hzr.subject_id = pp.resource_source_id
743 AND hzr.object_id = po.resource_source_id
744 AND hzr.object_table_name = 'HZ_PARTIES'
745 AND hzr.subject_type = 'PERSON'
746 AND hzr.subject_table_name = 'HZ_PARTIES';
747
748 -- 4616302 end
749
750 CURSOR c_billing_accnt_exists(c_project_party_id NUMBER) IS
751 SELECT project_party_id
752 FROM pa_project_customers
753 WHERE project_id = p_project_id
754 AND project_party_id = c_project_party_id
755 AND customer_id <> p_customer_id;
756
757 /* End of cursors added for bug 3101990 */
758
759 BEGIN
760 if (p_debug_mode = 'Y') then
761 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_Project_Customer BEGIN');
762 end if;
763
764 if p_commit = FND_API.G_TRUE then
765 savepoint delete_project_customer_pvt;
766 end if;
767
768 if (p_debug_mode = 'Y') then
769 pa_debug.debug('Locking record...');
770 end if;
771
772 if p_validate_only <> FND_API.G_TRUE then
773 BEGIN
774 SELECT 'x' INTO l_dummy
775 FROM pa_project_customers
776 WHERE project_id = p_project_id
777 AND customer_id = p_customer_id
778 AND record_version_number = p_record_version_number
779 FOR UPDATE OF record_version_number NOWAIT;
780 EXCEPTION
781 when TIMEOUT_ON_RESOURCE then
782 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
783 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
784 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
785 when NO_DATA_FOUND then
786 if p_calling_module = 'FORM' then
787 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
788 p_msg_name => 'FORM_RECORD_CHANGED');
789 l_msg_data := 'FORM_RECORD_CHANGED';
790 else
791 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
792 p_msg_name => 'PA_XC_RECORD_CHANGED');
793 l_msg_data := 'PA_XC_RECORD_CHANGED';
794 end if;
795 when OTHERS then
796 if SQLCODE = -54 then
797 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
798 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
799 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
800 else
801 raise;
802 end if;
803 END;
804 else
805 BEGIN
809 AND customer_id = p_customer_id
806 SELECT 'x' INTO l_dummy
807 FROM pa_project_customers
808 WHERE project_id = p_project_id
810 AND record_version_number = p_record_version_number;
811 EXCEPTION
812 when NO_DATA_FOUND then
813 if p_calling_module = 'FORM' then
814 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
815 p_msg_name => 'FORM_RECORD_CHANGED');
816 l_msg_data := 'FORM_RECORD_CHANGED';
817 else
818 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
819 p_msg_name => 'PA_XC_RECORD_CHANGED');
820 l_msg_data := 'PA_XC_RECORD_CHANGED';
821 end if;
822 when OTHERS then
823 raise;
824 END;
825 end if;
826
827 l_msg_count := FND_MSG_PUB.count_msg;
828 if l_msg_count > 0 then
829 x_msg_count := l_msg_count;
830 if x_msg_count = 1 then
831 x_msg_data := l_msg_data;
832 end if;
833 raise FND_API.G_EXC_ERROR;
834 end if;
835
836 if p_validation_level > 0 then
837 if (p_debug_mode = 'Y') then
838 pa_debug.debug('Performing validation...');
839 end if;
840
841 PA_CUSTOMERS_CONTACTS_PVT.VALIDATE_CUSTOMER
842 ( p_validation_level => p_validation_level
843 ,p_calling_module => p_calling_module
844 ,p_debug_mode => p_debug_mode
845 ,p_max_msg_count => p_max_msg_count
846 ,p_action => 'DELETE'
847 ,p_project_id => p_project_id
848 ,p_customer_id => p_customer_id
849 ,x_bill_another_project_flag => l_bill_another_project_flag);
850
851 l_msg_count := FND_MSG_PUB.count_msg;
852 if l_msg_count > 0 then
853 x_msg_count := l_msg_count;
854
855 raise FND_API.G_EXC_ERROR;
856 end if;
857 end if;
858
859 PA_CUSTOMERS_CONTACTS_PVT.DELETE_ALL_CONTACTS
860 ( p_commit => FND_API.G_FALSE
861 ,p_validate_only => p_validate_only
862 ,p_validation_level => p_validation_level
863 ,p_calling_module => p_calling_module
864 ,p_debug_mode => p_debug_mode
865 ,p_max_msg_count => p_max_msg_count
866 ,p_project_id => p_project_id
867 ,p_customer_id => p_customer_id
868 ,x_return_status => l_return_status
869 ,x_msg_count => l_msg_count
870 ,x_msg_data => l_msg_data);
871
872 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
873 x_msg_count := l_msg_count;
874 if x_msg_count = 1 then
875 x_msg_data := l_msg_data;
876 end if;
877 raise FND_API.G_EXC_ERROR;
878 end if;
879
880 if p_validate_only <> FND_API.G_TRUE then
881
882 -- anlee
883 -- retention changes
884 PA_RETENTION_UTIL.delete_retn_rules_customer
885 ( p_project_id => p_project_id
886 ,p_customer_id => p_customer_id
887 ,x_return_status => l_return_status
888 ,x_msg_count => l_msg_count
889 ,x_msg_data => l_msg_data);
890
891 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
892 x_msg_count := l_msg_count;
893 if x_msg_count = 1 then
894 x_msg_data := l_msg_data;
895 end if;
896 raise FND_API.G_EXC_ERROR;
897 end if;
898
899 /* Bug 3101990 - We are deleting the Customer Org role record in pa_project_parties if calling module is FORM,
900 and if there are no references for this project party record */
901
902 IF p_calling_module = 'FORM' THEN
903
904 OPEN get_project_party_id;
905 FETCH get_project_party_id INTO l_project_party_id;
906 CLOSE get_project_party_id;
907
908 IF (l_project_party_id IS NOT NULL) THEN
909 OPEN c_ext_people_exists(l_project_party_id);
910 FETCH c_ext_people_exists INTO l_ext_people_exists;
911 IF (c_ext_people_exists%NOTFOUND) THEN
912 OPEN c_billing_accnt_exists(l_project_party_id);
913 FETCH c_billing_accnt_exists INTO l_billing_accnt_exists;
914 IF (c_billing_accnt_exists%NOTFOUND) THEN
915 pa_project_parties_pkg.delete_row(x_project_id => p_project_id,
916 x_project_party_id => l_project_party_id,
917 x_record_version_number => null);
918
919 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
920 CLOSE c_billing_accnt_exists;
921 CLOSE c_ext_people_exists;
922 x_msg_count := l_msg_count;
923 IF x_msg_count = 1 THEN
924 x_msg_data := l_msg_data;
925 END IF;
926 RAISE FND_API.G_EXC_ERROR;
927 END IF;
928 END IF;
929 CLOSE c_billing_accnt_exists;
930 END IF;
931 CLOSE c_ext_people_exists;
932 END IF;
933 END IF; --- If p_calling_module
934
935 /* End of code added for bug 3101990 */
936
937 -- Delete the customer
938 open C(p_project_id, p_customer_id);
942 PA_PROJECT_CUSTOMERS_PKG.DELETE_ROW(l_rowid, p_record_version_number);
939 fetch C into l_rowid;
940 close C;
941
943 end if;
944
945 x_return_status := FND_API.G_RET_STS_SUCCESS;
946
947 if p_commit = FND_API.G_TRUE then
948 commit work;
949 end if;
950
951 if (p_debug_mode = 'Y') then
952 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_Project_Customer END');
953 end if;
954
955 EXCEPTION
956 when FND_API.G_EXC_ERROR then
957 if p_commit = FND_API.G_TRUE then
958 rollback to delete_project_customer_pvt;
959 end if;
960 x_return_status := FND_API.G_RET_STS_ERROR;
961 when others then
962 if p_commit = FND_API.G_TRUE then
963 rollback to delete_project_customer_pvt;
964 end if;
965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
967 p_procedure_name => 'Delete_Project_Customer',
968 p_error_text => SUBSTRB(SQLERRM,1,240));
969 raise;
970 END DELETE_PROJECT_CUSTOMER;
971
972
973 -- API name : Create_Customer_Contact
974 -- Type : Private
975 -- Pre-reqs : None.
976 -- Parameters :
977 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
978 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
979 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
980 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
981 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
982 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
983 -- p_project_id IN NUMBER Required
984 -- p_customer_id IN NUMBER Required
985 -- p_bill_ship_customer_id IN NUMBER Required
986 -- p_contact_id IN NUMBER Required
987 -- p_project_contact_type_code IN VARCHAR2 Required
988 -- x_return_status OUT VARCHAR2 Required
989 -- x_msg_count OUT NUMBER Required
990 -- x_msg_data OUT VARCHAR2 Optional
991
992 PROCEDURE CREATE_CUSTOMER_CONTACT
993 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
994 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
995 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
996 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
997 ,p_debug_mode IN VARCHAR2 := 'N'
998 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
999 ,p_project_id IN NUMBER
1000 ,p_customer_id IN NUMBER
1001 ,p_bill_ship_customer_id IN NUMBER /* For Bug 2731449 */
1002 ,p_contact_id IN NUMBER
1003 ,p_project_contact_type_code IN VARCHAR2
1004 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1005 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1006 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1007 )
1008 IS
1009 l_msg_count NUMBER;
1010 l_last_update_date DATE;
1011 l_last_updated_by NUMBER(15);
1012 l_creation_date DATE;
1013 l_created_by NUMBER(15);
1014 l_last_update_login NUMBER(15);
1015 l_rowid VARCHAR2(250);
1016 BEGIN
1017 if (p_debug_mode = 'Y') then
1018 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Create_Customer_Contact BEGIN');
1019 end if;
1020
1021 if p_commit = FND_API.G_TRUE then
1022 savepoint create_customer_contact_pvt;
1023 end if;
1024
1025 if p_validation_level > 0 then
1026 if (p_debug_mode = 'Y') then
1027 pa_debug.debug('Performing validation...');
1028 end if;
1029
1030 PA_CUSTOMERS_CONTACTS_PVT.VALIDATE_CONTACT
1031 ( p_validation_level => p_validation_level
1032 ,p_calling_module => p_calling_module
1033 ,p_debug_mode => p_debug_mode
1034 ,p_max_msg_count => p_max_msg_count
1035 ,p_action => 'INSERT'
1036 ,p_project_id => p_project_id
1037 ,p_customer_id => p_bill_ship_customer_id /* For Bug 2731449 */
1038 ,p_contact_id => p_contact_id
1039 ,p_project_contact_type_code => p_project_contact_type_code);
1040
1041 l_msg_count := FND_MSG_PUB.count_msg;
1042 if l_msg_count > 0 then
1043 x_msg_count := l_msg_count;
1044
1045 raise FND_API.G_EXC_ERROR;
1046 end if;
1047 end if;
1048
1049 -- Populate who column values
1050 l_last_update_date := sysdate;
1051 l_last_updated_by := fnd_global.user_id;
1052 l_creation_date := sysdate;
1053 l_created_by := fnd_global.user_id;
1054 l_last_update_login := fnd_global.login_id;
1055
1056 if p_validate_only <> FND_API.G_TRUE then
1057 PA_PROJECT_CONTACTS_PKG.INSERT_ROW
1058 ( X_Rowid => l_rowid
1059 ,X_Project_Id => p_project_id
1063 ,X_Project_Contact_Type_Code => p_project_contact_type_code
1060 ,X_Customer_Id => p_customer_id
1061 ,X_Bill_Ship_Customer_Id => p_bill_ship_customer_id /* For Bug 2731449 */
1062 ,X_Contact_Id => p_contact_id
1064 ,X_Last_Update_Date => l_last_update_date
1065 ,X_Last_Updated_By => l_last_updated_by
1066 ,X_Creation_Date => l_creation_date
1067 ,X_Created_By => l_created_by
1068 ,X_Last_Update_Login => l_last_update_login
1069 ,X_Record_Version_Number => 1);
1070 end if;
1071
1072 x_return_status := FND_API.G_RET_STS_SUCCESS;
1073
1074 if p_commit = FND_API.G_TRUE then
1075 commit work;
1076 end if;
1077
1078 if (p_debug_mode = 'Y') then
1079 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Create_Customer_Contact END');
1080 end if;
1081
1082 EXCEPTION
1083 when FND_API.G_EXC_ERROR then
1084 if p_commit = FND_API.G_TRUE then
1085 rollback to create_customer_contact_pvt;
1086 end if;
1087 x_return_status := FND_API.G_RET_STS_ERROR;
1088 when others then
1089 if p_commit = FND_API.G_TRUE then
1090 rollback to create_customer_contact_pvt;
1091 end if;
1092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1093 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
1094 p_procedure_name => 'Create_Customer_Contact',
1095 p_error_text => SUBSTRB(SQLERRM,1,240));
1096 raise;
1097 END CREATE_CUSTOMER_CONTACT;
1098
1099
1100 -- API name : Update_Customer_Contact
1101 -- Type : Public
1102 -- Pre-reqs : None.
1103 -- Parameters :
1104 -- p_api_version IN NUMBER Required Default = 1.0
1105 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
1106 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
1107 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
1108 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1109 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1110 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1111 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1112 -- p_project_id IN NUMBER Required
1113 -- p_customer_id IN NUMBER Required
1114 -- p_bill_ship_customer_id IN NUMBER Required
1115 -- p_contact_id IN NUMBER Required
1116 -- p_project_contact_type_code IN VARCHAR2 Required
1117 -- p_rowid IN VARCHAR2 Required
1118 -- p_record_version_number IN NUMBER Required
1119 -- x_return_status OUT VARCHAR2 Required
1120 -- x_msg_count OUT NUMBER Required
1121 -- x_msg_data OUT VARCHAR2 Optional
1122
1123 PROCEDURE UPDATE_CUSTOMER_CONTACT
1124 ( p_api_version IN NUMBER := 1.0
1125 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1126 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1127 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1128 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1129 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1130 ,p_debug_mode IN VARCHAR2 := 'N'
1131 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1132 ,p_project_id IN NUMBER
1133 ,p_customer_id IN NUMBER
1134 ,p_bill_ship_customer_id IN NUMBER /* For Bug 2731449 */
1135 ,p_contact_id IN NUMBER
1136 ,p_project_contact_type_code IN VARCHAR2
1137 ,p_rowid IN VARCHAR2
1138 ,p_record_version_number IN NUMBER
1139 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1140 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1141 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1142 )
1143 IS
1144 l_msg_count NUMBER;
1145 l_msg_data VARCHAR2(250);
1146 l_dummy VARCHAR2(1);
1147 l_last_update_date DATE;
1148 l_last_updated_by NUMBER(15);
1149 l_last_update_login NUMBER(15);
1150 BEGIN
1151 if (p_debug_mode = 'Y') then
1152 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Update_Customer_Contact BEGIN');
1153 end if;
1154
1155 if p_commit = FND_API.G_TRUE then
1156 savepoint update_customer_contact_pvt;
1157 end if;
1158
1159 if (p_debug_mode = 'Y') then
1160 pa_debug.debug('Locking record...');
1161 end if;
1162
1163 if p_validate_only <> FND_API.G_TRUE then
1164 BEGIN
1165 SELECT 'x' INTO l_dummy
1166 FROM pa_project_contacts
1167 WHERE rowid = p_rowid
1168 AND record_version_number = p_record_version_number
1169 FOR UPDATE OF record_version_number NOWAIT;
1170 EXCEPTION
1171 when TIMEOUT_ON_RESOURCE then
1172 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1173 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1177 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1174 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1175 when NO_DATA_FOUND then
1176 if p_calling_module = 'FORM' then
1178 p_msg_name => 'FORM_RECORD_CHANGED');
1179 l_msg_data := 'FORM_RECORD_CHANGED';
1180 else
1181 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1182 p_msg_name => 'PA_XC_RECORD_CHANGED');
1183 l_msg_data := 'PA_XC_RECORD_CHANGED';
1184 end if;
1185 when OTHERS then
1186 if SQLCODE = -54 then
1187 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1188 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1189 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1190 else
1191 raise;
1192 end if;
1193 END;
1194 else
1195 BEGIN
1196 SELECT 'x' INTO l_dummy
1197 FROM pa_project_contacts
1198 WHERE rowid = p_rowid
1199 AND record_version_number = p_record_version_number;
1200 EXCEPTION
1201 when NO_DATA_FOUND then
1202 if p_calling_module = 'FORM' then
1203 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1204 p_msg_name => 'FORM_RECORD_CHANGED');
1205 l_msg_data := 'FORM_RECORD_CHANGED';
1206 else
1207 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1208 p_msg_name => 'PA_XC_RECORD_CHANGED');
1209 l_msg_data := 'PA_XC_RECORD_CHANGED';
1210 end if;
1211 when OTHERS then
1212 raise;
1213 END;
1214 end if;
1215
1216 l_msg_count := FND_MSG_PUB.count_msg;
1217 if l_msg_count > 0 then
1218 x_msg_count := l_msg_count;
1219 if x_msg_count = 1 then
1220 x_msg_data := l_msg_data;
1221 end if;
1222 raise FND_API.G_EXC_ERROR;
1223 end if;
1224
1225 if p_validation_level > 0 then
1226 if (p_debug_mode = 'Y') then
1227 pa_debug.debug('Performing validation...');
1228 end if;
1229
1230 PA_CUSTOMERS_CONTACTS_PVT.VALIDATE_CONTACT
1231 ( p_validation_level => p_validation_level
1232 ,p_calling_module => p_calling_module
1233 ,p_debug_mode => p_debug_mode
1234 ,p_max_msg_count => p_max_msg_count
1235 ,p_action => 'UPDATE'
1236 ,p_project_id => p_project_id
1237 ,p_customer_id => p_bill_ship_customer_id /* For Bug 2731449 */
1238 ,p_contact_id => p_contact_id
1239 ,p_project_contact_type_code => p_project_contact_type_code
1240 ,p_rowid => p_rowid);
1241
1242 l_msg_count := FND_MSG_PUB.count_msg;
1243 if l_msg_count > 0 then
1244 x_msg_count := l_msg_count;
1245
1246 raise FND_API.G_EXC_ERROR;
1247 end if;
1248 end if;
1249
1250 -- Populate who column values
1251 l_last_update_date := sysdate;
1252 l_last_updated_by := fnd_global.user_id;
1253 l_last_update_login := fnd_global.login_id;
1254
1255 if p_validate_only <> FND_API.G_TRUE then
1256 PA_PROJECT_CONTACTS_PKG.UPDATE_ROW
1257 ( X_Rowid => p_rowid
1258 ,X_Project_Id => p_project_id
1259 ,X_Customer_Id => p_customer_id
1260 ,X_Bill_Ship_Customer_Id => p_bill_ship_customer_id /* For Bug 2731449 */
1261 ,X_Contact_Id => p_contact_id
1262 ,X_Project_Contact_Type_Code => p_project_contact_type_code
1263 ,X_Last_Update_Date => l_last_update_date
1264 ,X_Last_Updated_By => l_last_updated_by
1265 ,X_Last_Update_Login => l_last_update_login
1266 ,X_Record_Version_Number => p_record_version_number);
1267 end if;
1268
1269 x_return_status := FND_API.G_RET_STS_SUCCESS;
1270
1271 if p_commit = FND_API.G_TRUE then
1272 commit work;
1273 end if;
1274
1275 if (p_debug_mode = 'Y') then
1276 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Update_Customer_Contact END');
1277 end if;
1278
1279 EXCEPTION
1280 when FND_API.G_EXC_ERROR then
1281 if p_commit = FND_API.G_TRUE then
1282 rollback to update_customer_contact_pvt;
1283 end if;
1284 x_return_status := FND_API.G_RET_STS_ERROR;
1285 when others then
1286 if p_commit = FND_API.G_TRUE then
1287 rollback to update_customer_contact_pvt;
1288 end if;
1289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1290 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
1291 p_procedure_name => 'Update_Customer_Contact',
1292 p_error_text => SUBSTRB(SQLERRM,1,240));
1293 raise;
1294 END UPDATE_CUSTOMER_CONTACT;
1295
1296
1297 -- API name : Delete_Customer_Contact
1298 -- Type : Private
1299 -- Pre-reqs : None.
1300 -- Parameters :
1301 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
1305 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1302 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
1303 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1304 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1306 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1307 -- p_project_id IN NUMBER Required
1308 -- p_customer_id IN NUMBER Required
1309 -- p_contact_id IN NUMBER Required
1310 -- p_project_contact_type_code IN VARCHAR2 Required
1311 -- p_record_version_number IN NUMBER Required Default = FND_API.G_MISS_NUM
1312 -- x_return_status OUT VARCHAR2 Required
1313 -- x_msg_count OUT NUMBER Required
1314 -- x_msg_data OUT VARCHAR2 Optional
1315
1316 PROCEDURE DELETE_CUSTOMER_CONTACT
1317 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
1318 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1319 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1320 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1321 ,p_debug_mode IN VARCHAR2 := 'N'
1322 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1323 ,p_project_id IN NUMBER
1324 ,p_customer_id IN NUMBER
1325 ,p_contact_id IN NUMBER
1326 ,p_project_contact_type_code IN VARCHAR2
1327 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
1328 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1329 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1330 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1331 )
1332 IS
1333 l_rowid VARCHAR2(250);
1334 l_msg_count NUMBER;
1335 l_msg_data VARCHAR2(250);
1336 l_dummy VARCHAR2(1);
1337
1338 CURSOR C (c_project_id NUMBER, c_customer_id NUMBER, c_contact_id NUMBER,
1339 c_project_contact_type_code VARCHAR2) IS
1340 SELECT rowid
1341 FROM pa_project_contacts
1342 WHERE project_id = c_project_id AND
1343 customer_id = c_customer_id AND
1344 contact_id = c_contact_id AND
1345 project_contact_type_code = c_project_contact_type_code;
1346 BEGIN
1347 if (p_debug_mode = 'Y') then
1348 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_Customer_Contact BEGIN');
1349 end if;
1350
1351 if p_commit = FND_API.G_TRUE then
1352 savepoint delete_customer_contact_pvt;
1353 end if;
1354
1355 if (p_debug_mode = 'Y') then
1356 pa_debug.debug('Locking record...');
1357 end if;
1358
1359 if p_validate_only <> FND_API.G_TRUE then
1360 BEGIN
1361 SELECT 'x' INTO l_dummy
1362 FROM pa_project_contacts
1363 WHERE project_id = p_project_id
1364 AND customer_id = p_customer_id
1365 AND contact_id = p_contact_id
1366 AND project_contact_type_code = p_project_contact_type_code
1367 AND record_version_number = p_record_version_number
1368 FOR UPDATE OF record_version_number NOWAIT;
1369 EXCEPTION
1370 when TIMEOUT_ON_RESOURCE then
1371 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1372 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1373 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1374 when NO_DATA_FOUND then
1375 if p_calling_module = 'FORM' then
1376 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1377 p_msg_name => 'FORM_RECORD_CHANGED');
1378 l_msg_data := 'FORM_RECORD_CHANGED';
1379 else
1380 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1381 p_msg_name => 'PA_XC_RECORD_CHANGED');
1382 l_msg_data := 'PA_XC_RECORD_CHANGED';
1383 end if;
1384 when OTHERS then
1385 if SQLCODE = -54 then
1386 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1387 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1388 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1389 else
1390 raise;
1391 end if;
1392 END;
1393 else
1394 BEGIN
1395 SELECT 'x' INTO l_dummy
1396 FROM pa_project_contacts
1397 WHERE project_id = p_project_id
1398 AND customer_id = p_customer_id
1399 AND contact_id = p_contact_id
1400 AND project_contact_type_code = p_project_contact_type_code
1401 AND record_version_number = p_record_version_number;
1402 EXCEPTION
1403 when NO_DATA_FOUND then
1404 if p_calling_module = 'FORM' then
1405 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1406 p_msg_name => 'FORM_RECORD_CHANGED');
1407 l_msg_data := 'FORM_RECORD_CHANGED';
1408 else
1409 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1410 p_msg_name => 'PA_XC_RECORD_CHANGED');
1411 l_msg_data := 'PA_XC_RECORD_CHANGED';
1412 end if;
1416 end if;
1413 when OTHERS then
1414 raise;
1415 END;
1417
1418 l_msg_count := FND_MSG_PUB.count_msg;
1419 if l_msg_count > 0 then
1420 x_msg_count := l_msg_count;
1421 if x_msg_count = 1 then
1422 x_msg_data := l_msg_data;
1423 end if;
1424 raise FND_API.G_EXC_ERROR;
1425 end if;
1426
1427 if p_validate_only <> FND_API.G_TRUE then
1428 open C(p_project_id, p_customer_id, p_contact_id, p_project_contact_type_code);
1429 fetch C into l_rowid;
1430 close C;
1431
1432 PA_PROJECT_CONTACTS_PKG.DELETE_ROW(l_rowid, p_record_version_number);
1433 end if;
1434
1435 x_return_status := FND_API.G_RET_STS_SUCCESS;
1436
1437 if p_commit = FND_API.G_TRUE then
1438 commit work;
1439 end if;
1440
1441 if (p_debug_mode = 'Y') then
1442 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_Customer_Contact END');
1443 end if;
1444
1445 EXCEPTION
1446 when FND_API.G_EXC_ERROR then
1447 if p_commit = FND_API.G_TRUE then
1448 rollback to delete_customer_contact_pvt;
1449 end if;
1450 x_return_status := FND_API.G_RET_STS_ERROR;
1451 when others then
1452 if p_commit = FND_API.G_TRUE then
1453 rollback to delete_customer_contact_pvt;
1454 end if;
1455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1456 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
1457 p_procedure_name => 'Delete_Customer_Contact',
1458 p_error_text => SUBSTRB(SQLERRM,1,240));
1459 raise;
1460 END DELETE_CUSTOMER_CONTACT;
1461
1462
1463 -- API name : Delete_All_Contacts
1464 -- Type : Private
1465 -- Pre-reqs : None.
1466 -- Parameters :
1467 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
1468 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
1469 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1470 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1471 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1472 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1473 -- p_project_id IN NUMBER Required
1474 -- p_customer_id IN NUMBER Required
1475 -- x_return_status OUT VARCHAR2 Required
1476 -- x_msg_count OUT NUMBER Required
1477 -- x_msg_data OUT VARCHAR2 Optional
1478
1479 PROCEDURE DELETE_ALL_CONTACTS
1480 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
1481 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1482 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1483 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1484 ,p_debug_mode IN VARCHAR2 := 'N'
1485 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1486 ,p_project_id IN NUMBER
1487 ,p_customer_id IN NUMBER
1488 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1489 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1490 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1491 )
1492 IS
1493 l_return_status VARCHAR2(1);
1494 l_msg_count NUMBER;
1495 l_msg_data VARCHAR2(250);
1496
1497 CURSOR C (c_project_id NUMBER, c_customer_id NUMBER) IS
1498 SELECT contact_id, project_contact_type_code, record_version_number
1499 FROM pa_project_contacts
1500 WHERE project_id = c_project_id AND
1501 customer_id = c_customer_id;
1502 l_recinfo C%ROWTYPE;
1503 BEGIN
1504 if (p_debug_mode = 'Y') then
1505 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_All_Contacts BEGIN');
1506 end if;
1507
1508 if p_commit = FND_API.G_TRUE then
1509 savepoint delete_all_contacts_pvt;
1510 end if;
1511
1512 for l_recinfo in C(p_project_id, p_customer_id) loop
1513 PA_CUSTOMERS_CONTACTS_PVT.DELETE_CUSTOMER_CONTACT
1514 ( p_commit => FND_API.G_FALSE
1515 ,p_validate_only => p_validate_only
1516 ,p_validation_level => p_validation_level
1517 ,p_calling_module => p_calling_module
1518 ,p_debug_mode => p_debug_mode
1519 ,p_max_msg_count => p_max_msg_count
1520 ,p_project_id => p_project_id
1521 ,p_customer_id => p_customer_id
1522 ,p_contact_id => l_recinfo.contact_id
1523 ,p_project_contact_type_code => l_recinfo.project_contact_type_code
1524 ,p_record_version_number => l_recinfo.record_version_number
1525 ,x_return_status => l_return_status
1526 ,x_msg_count => l_msg_count
1527 ,x_msg_data => l_msg_data);
1528
1529 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1530 x_msg_count := l_msg_count;
1531 if x_msg_count = 1 then
1532 x_msg_data := l_msg_data;
1533 end if;
1534 raise FND_API.G_EXC_ERROR;
1535 end if;
1536 end loop;
1537
1538 x_return_status := FND_API.G_RET_STS_SUCCESS;
1539
1543
1540 if p_commit = FND_API.G_TRUE then
1541 commit work;
1542 end if;
1544 if (p_debug_mode = 'Y') then
1545 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_All_Contacts END');
1546 end if;
1547
1548 EXCEPTION
1549 when FND_API.G_EXC_ERROR then
1550 if p_commit = FND_API.G_TRUE then
1551 rollback to delete_all_contacts_pvt;
1552 end if;
1553 x_return_status := FND_API.G_RET_STS_ERROR;
1554 when others then
1555 if p_commit = FND_API.G_TRUE then
1556 rollback to delete_all_contacts_pvt;
1557 end if;
1558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1559 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
1560 p_procedure_name => 'Delete_All_Contacts',
1561 p_error_text => SUBSTRB(SQLERRM,1,240));
1562 raise;
1563 END DELETE_ALL_CONTACTS;
1564
1565
1566 -- API name : Validate_Customer
1567 -- Type : Private
1568 -- Pre-reqs : None.
1569 -- Parameters :
1570 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1571 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1572 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1573 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1574 -- p_action IN VARCHAR2 Required
1575 -- p_project_id IN NUMBER Required
1576 -- p_customer_id IN NUMBER Required
1577 -- p_customer_bill_split IN NUMBER Optional Default = FND_API.G_MISS_NUM
1578 -- p_bill_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1579 -- p_ship_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1580 -- p_inv_currency_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1581 -- p_inv_rate_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1582 -- p_inv_rate_date IN DATE Optional Default = FND_API.G_MISS_DATE
1583 -- p_inv_exchange_rate IN NUMBER Optional Default = FND_API.G_MISS_NUM
1584 -- p_allow_user_rate_type_flag IN VARCHAR2 Optional Default = 'N'
1585 -- p_receiver_task_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1586 -- x_bill_another_project_flag OUT VARCHAR2 Optional
1587
1588 PROCEDURE VALIDATE_CUSTOMER
1589 ( p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1590 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1591 ,p_debug_mode IN VARCHAR2 := 'N'
1592 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1593 ,p_action IN VARCHAR2
1594 ,p_project_id IN NUMBER
1595 ,p_customer_id IN NUMBER
1596 ,p_customer_bill_split IN NUMBER := FND_API.G_MISS_NUM
1597 ,p_bill_to_address_id IN NUMBER := FND_API.G_MISS_NUM
1598 ,p_ship_to_address_id IN NUMBER := FND_API.G_MISS_NUM
1599 ,p_inv_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR
1600 ,p_inv_rate_type IN VARCHAR2 := FND_API.G_MISS_CHAR
1601 ,p_inv_rate_date IN DATE := FND_API.G_MISS_DATE
1602 ,p_inv_exchange_rate IN NUMBER := FND_API.G_MISS_NUM
1603 ,p_allow_user_rate_type_flag IN VARCHAR2 := 'N'
1604 ,p_receiver_task_id IN NUMBER := FND_API.G_MISS_NUM
1605 ,x_bill_another_project_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1606 )
1607 IS
1608 l_return_status VARCHAR2(1);
1609 l_error_msg_code VARCHAR2(250);
1610 l_bill_another_project_flag VARCHAR2(1);
1611 BEGIN
1612 if (p_debug_mode = 'Y') then
1613 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Validate_Customer BEGIN');
1614 end if;
1615 --dbms_output.put_line('value of p_action'||p_action);
1616 --dbms_output.put_line('value of p_customer_id'||p_customer_id);
1617
1618 if p_action = 'INSERT' then
1619 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_DUPLICATE_CUSTOMER
1620 ( p_project_id => p_project_id
1621 ,p_customer_id => p_customer_id
1622 ,x_return_status => l_return_status
1623 ,x_error_msg_code => l_error_msg_code);
1624 --dbms_output.put_line('Value of l_return_status 1'||l_return_status);
1625 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1626 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1627 p_msg_name => l_error_msg_code);
1628 end if;
1629 end if;
1630
1631 if ((p_action = 'INSERT') OR (p_action = 'UPDATE')) then
1632
1633 --dbms_output.put_line('Before Calling CHECK_CONTRIBUTION_PERCENTAGE');
1634
1635 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTRIBUTION_PERCENTAGE
1636 ( p_customer_bill_split => p_customer_bill_split
1637 ,x_return_status => l_return_status
1638 ,x_error_msg_code => l_error_msg_code);
1639 --dbms_output.put_line('Value of l_return_status 2'||l_return_status||'det'||l_error_msg_code);
1640
1641 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1642 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1643 p_msg_name => l_error_msg_code);
1644 end if;
1645
1646 IF (p_calling_module <> 'FORM') AND (p_calling_module <> 'AMG') -- Added by aditi for tracking Bug --bug 2838822
1647 THEN
1648 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTRIBUTION_TOTAL
1649 ( p_customer_bill_split => p_customer_bill_split
1650 ,p_project_id => p_project_id
1651 ,p_customer_id => p_customer_id
1652 ,x_return_status => l_return_status
1653 ,x_error_msg_code => l_error_msg_code);
1654 --dbms_output.put_line('Value of l_return_status 3'||l_return_status);
1655
1656 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1657 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1658 p_msg_name => l_error_msg_code);
1659 end if;
1660 END IF;
1661 --dbms_output.put_line('value of p_bill_to_address_id'||p_bill_to_address_id);
1662 --dbms_output.put_line('value of p_ship_to_address_id'||p_ship_to_address_id);
1663
1664 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_BILL_WORK_SITES_REQUIRED
1665 ( p_customer_bill_split => p_customer_bill_split
1666 ,p_bill_to_address_id => p_bill_to_address_id
1667 ,p_ship_to_address_id => p_ship_to_address_id
1668 ,x_return_status => l_return_status
1669 ,x_error_msg_code => l_error_msg_code);
1670 --dbms_output.put_line('Value of l_return_status 4'||l_return_status);
1671
1672 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1673 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1674 p_msg_name => l_error_msg_code);
1675 end if;
1676
1677 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CC_PRVDR_FLAG_CONTRIB
1678 ( p_project_id => p_project_id
1679 ,p_customer_id => p_customer_id
1680 ,p_customer_bill_split => p_customer_bill_split
1681 ,p_action => p_action
1682 ,x_return_status => l_return_status
1683 ,x_error_msg_code => l_error_msg_code);
1684 --dbms_output.put_line('Value of l_return_status 5'||l_return_status);
1685
1686 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1687 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1688 p_msg_name => l_error_msg_code);
1689 end if;
1690 --dbms_output.put_line('Value of l_bill_another_project_flag outside loop'||l_bill_another_project_flag);
1691 IF p_calling_module <> 'AMG' THEN -- Added by aditi for tracking Bug 4153629
1692 /* These checks are performed in Update_project, hence need not be done here again */
1693 --dbms_output.put_line('Value of l_bill_another_project_flag'||l_bill_another_project_flag);
1694 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_RECEIVER_PROJ_ENTERABLE
1695 ( p_project_id => p_project_id
1696 ,p_customer_id => p_customer_id
1697 ,p_receiver_task_id => p_receiver_task_id
1698 ,x_bill_another_project_flag => l_bill_another_project_flag
1699 ,x_return_status => l_return_status
1700 ,x_error_msg_code => l_error_msg_code);
1701 --dbms_output.put_line('Value of l_return_status 6'||l_return_status);
1702 END IF; -- Tracking Bug 4153629
1703 --dbms_output.put_line('Value of l_bill_another_project_flag outside loop'||l_bill_another_project_flag);
1704
1705 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1706 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1707 p_msg_name => l_error_msg_code);
1708 end if;
1709 x_bill_another_project_flag := l_bill_another_project_flag;
1710
1711 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_INVOICE_CURRENCY_INFO
1712 ( p_project_id => p_project_id
1716 ,p_inv_exchange_rate => p_inv_exchange_rate
1713 ,p_inv_currency_code => p_inv_currency_code
1714 ,p_inv_rate_type => p_inv_rate_type
1715 ,p_inv_rate_date => p_inv_rate_date
1717 ,p_allow_user_rate_type_flag => p_allow_user_rate_type_flag
1718 ,x_return_status => l_return_status
1719 ,x_error_msg_code => l_error_msg_code);
1720 --dbms_output.put_line('Value of l_return_status 7'||l_return_status);
1721
1722 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1723 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1724 p_msg_name => l_error_msg_code);
1725 end if;
1726 end if;
1727
1728 if p_action = 'UPDATE' then
1729 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_UPDATE_CONTRIB_ALLOWED
1730 ( p_project_id => p_project_id
1731 ,p_customer_id => p_customer_id
1732 ,p_customer_bill_split => p_customer_bill_split
1733 ,x_return_status => l_return_status
1734 ,x_error_msg_code => l_error_msg_code);
1735 --dbms_output.put_line('Value of l_return_status 8'||l_return_status);
1736
1737 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1738 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1739 p_msg_name => l_error_msg_code);
1740 end if;
1741 end if;
1742
1743 if p_action = 'DELETE' then
1744 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_DELETE_CUSTOMER_ALLOWED
1745 ( p_project_id => p_project_id
1746 ,p_customer_id => p_customer_id
1747 ,x_return_status => l_return_status
1748 ,x_error_msg_code => l_error_msg_code);
1749 --dbms_output.put_line('Value of l_return_status 9'||l_return_status);
1750
1751 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1752 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1753 p_msg_name => l_error_msg_code);
1754 end if;
1755 end if;
1756
1757 if (p_debug_mode = 'Y') then
1758 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Validate_Customer END');
1759 end if;
1760
1761 EXCEPTION
1762 when others then
1763 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
1764 p_procedure_name => 'Validate_Customer',
1765 p_error_text => SUBSTRB(SQLERRM,1,240));
1766 raise;
1767 END VALIDATE_CUSTOMER;
1768
1769
1770 -- API name : Validate_Contact
1771 -- Type : Private
1772 -- Pre-reqs : None.
1773 -- Parameters :
1774 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1775 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1776 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1777 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1778 -- p_action IN VARCHAR2 Required
1779 -- p_project_id IN NUMBER Required
1780 -- p_customer_id IN NUMBER Required
1781 -- p_contact_id IN NUMBER Required
1782 -- p_project_contact_type_code IN VARCHAR2 Required
1783 -- p_rowid IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1784
1785 PROCEDURE VALIDATE_CONTACT
1786 ( p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1787 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1788 ,p_debug_mode IN VARCHAR2 := 'N'
1789 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1790 ,p_action IN VARCHAR2
1791 ,p_project_id IN NUMBER
1792 ,p_customer_id IN NUMBER
1793 ,p_contact_id IN NUMBER
1794 ,p_project_contact_type_code IN VARCHAR2
1795 ,p_rowid IN VARCHAR2 := FND_API.G_MISS_CHAR
1796 )
1797 IS
1798 l_return_status VARCHAR2(1);
1799 l_error_msg_code VARCHAR2(250);
1800 l_rowid VARCHAR2(250);
1801
1802 CURSOR C(c_project_id NUMBER, c_customer_id NUMBER, c_contact_id NUMBER,
1803 c_project_contact_type_code VARCHAR2) IS
1804 SELECT rowid
1805 FROM pa_project_contacts
1806 WHERE project_id = c_project_id AND
1807 customer_id = c_customer_id AND
1808 contact_id = c_contact_id AND
1809 project_contact_type_code = c_project_contact_type_code;
1810 BEGIN
1811 if (p_debug_mode = 'Y') then
1812 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Validate_Contact BEGIN');
1813 end if;
1814
1815 if ((p_action = 'INSERT') OR (p_action = 'UPDATE')) then
1816 if p_action = 'UPDATE' then
1817 if (p_rowid = FND_API.G_MISS_CHAR) OR (p_rowid is NULL) then
1818 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1819 end if;
1820
1821 open C(p_project_id, p_customer_id, p_contact_id, p_project_contact_type_code);
1822 fetch C into l_rowid;
1823 close C;
1824
1825 if p_rowid = l_rowid then
1826 return;
1827 end if;
1828 end if;
1829
1830 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_DUPLICATE_CONTACT
1831 ( p_project_id => p_project_id
1832 ,p_customer_id => p_customer_id
1833 ,p_contact_id => p_contact_id
1834 ,p_project_contact_type_code => p_project_contact_type_code
1835 ,x_return_status => l_return_status
1836 ,x_error_msg_code => l_error_msg_code);
1837 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1838 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1839 p_msg_name => l_error_msg_code);
1840 end if;
1841 end if;
1842
1843 if (p_debug_mode = 'Y') then
1844 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Validate_Contact END');
1845 end if;
1846
1847 EXCEPTION
1848 when FND_API.G_EXC_UNEXPECTED_ERROR then
1849 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
1850 p_procedure_name => 'Validate_Contact',
1851 p_error_text => SUBSTRB(SQLERRM,1,240));
1852 when others then
1853 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
1854 p_procedure_name => 'Validate_Contact',
1855 p_error_text => SUBSTRB(SQLERRM,1,240));
1856 raise;
1857 END VALIDATE_CONTACT;
1858
1859 END PA_CUSTOMERS_CONTACTS_PVT;