1 PACKAGE BODY PA_CUSTOMERS_CONTACTS_PUB AS
2 /* $Header: PARPCCPB.pls 120.4.12010000.2 2008/08/22 12:45:55 rballamu ship $ */
3
4
5 -- Global constant
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_CUSTOMERS_CONTACTS_PUB';
7
8
9 -- API name : Create_Project_Customer
10 -- Type : Public
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- p_api_version IN NUMBER Required Default = 1.0
14 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
15 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
16 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
17 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
18 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
19 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
20 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
21 -- p_project_id IN NUMBER Required
22 -- p_customer_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
23 -- p_customer_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
24 -- p_customer_number IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
25 -- p_project_relationship_code IN VARCHAR2 Required
26 -- p_customer_bill_split IN NUMBER Required
27 -- p_bill_to_customer_id IN NUMBER Optional Default = NULL /* For Bug 2731449 */
28 -- p_ship_to_customer_id IN NUMBER Optional Default = NULL /* For Bug 2731449 */
29 -- p_bill_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
30 -- p_ship_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
31 -- p_bill_site_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
32 -- p_work_site_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
33 -- p_inv_currency_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
34 -- p_inv_rate_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
35 -- p_inv_rate_date IN DATE Optional Default = FND_API.G_MISS_DATE
36 -- p_inv_exchange_rate IN NUMBER Optional Default = FND_API.G_MISS_NUM
37 -- p_allow_user_rate_type_flag IN VARCHAR2 Required Default = 'N'
38 -- p_receiver_task_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
39 -- p_receiver_project_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
40 -- x_customer_id OUT NUMBER Required
41 -- x_return_status OUT VARCHAR2 Required
42 -- x_msg_count OUT NUMBER Required
43 -- x_msg_data OUT VARCHAR2 Optional
44
45 PROCEDURE CREATE_PROJECT_CUSTOMER
46 ( p_api_version IN NUMBER := 1.0
47 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
48 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
49 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
50 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
51 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
52 ,p_debug_mode IN VARCHAR2 := 'N'
53 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
54 ,p_project_id IN NUMBER
55 ,p_customer_id IN NUMBER := FND_API.G_MISS_NUM
56 ,p_customer_name IN VARCHAR2 := FND_API.G_MISS_CHAR
57 ,p_customer_number IN VARCHAR2 := FND_API.G_MISS_CHAR
58 ,p_project_relationship_code IN VARCHAR2
59 ,p_customer_bill_split IN NUMBER
60 ,p_bill_to_customer_id IN NUMBER := FND_API.G_MISS_NUM /* For Bug 2731449 */
61 ,p_ship_to_customer_id IN NUMBER := FND_API.G_MISS_NUM /* For Bug 2731449 */
62 ,p_bill_to_customer_name IN VARCHAR2 := FND_API.G_MISS_CHAR /* For Bug 2965841 */
63 ,p_bill_to_customer_number IN VARCHAR2 := FND_API.G_MISS_CHAR /* For Bug 2965841 */
64 ,p_ship_to_customer_name IN VARCHAR2 := FND_API.G_MISS_CHAR /* For Bug 2965841 */
65 ,p_ship_to_customer_number IN VARCHAR2 := FND_API.G_MISS_CHAR /* For Bug 2965841 */
66 ,p_bill_to_address_id IN NUMBER := FND_API.G_MISS_NUM
67 ,p_ship_to_address_id IN NUMBER := FND_API.G_MISS_NUM
68 ,p_bill_site_name IN VARCHAR2 := FND_API.G_MISS_CHAR
69 ,p_work_site_name IN VARCHAR2 := FND_API.G_MISS_CHAR
70 ,p_inv_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR
71 ,p_inv_rate_type IN VARCHAR2 := FND_API.G_MISS_CHAR
72 ,p_inv_rate_date IN DATE := FND_API.G_MISS_DATE
73 ,p_inv_exchange_rate IN NUMBER := FND_API.G_MISS_NUM
74 ,p_allow_user_rate_type_flag IN VARCHAR2 := 'N'
75 ,p_receiver_task_id IN NUMBER := FND_API.G_MISS_NUM
76 --,p_receiver_project_name IN VARCHAR2 := FND_API.G_MISS_CHAR
77 ,x_customer_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
78 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
79 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
80 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
81 ,p_project_party_id IN NUMBER DEFAULT NULL
82 --Billing setup related changes for FP_M development. Tracking bug 3279981
83 ,p_Default_Top_Task_Cust_Flag IN VARCHAR2
84 ,p_en_top_task_cust_flag IN VARCHAR2 := 'N'
85 )
86 IS
87 l_api_name CONSTANT VARCHAR(30) := 'Create_Project_Customer';
88 l_api_version CONSTANT NUMBER := 1.0;
89
90 l_customer_id NUMBER := null;
91 l_bill_to_address_id NUMBER;
92 l_ship_to_address_id NUMBER;
93 -- l_receiver_task_id NUMBER;
94 l_return_status VARCHAR2(1);
95 l_error_msg_code VARCHAR2(250);
96 l_msg_count NUMBER;
97 l_msg_data VARCHAR2(250);
98 l_data VARCHAR2(250);
99 l_msg_index_out NUMBER;
100 l_inv_currency_code VARCHAR2(80);
101 /* START For Bug 2731449 */
102 l_cust_acc_rel_code VARCHAR2(1);
103 l_valid_bill_id Number;
104 l_valid_ship_id Number;
105 l_bill_to_customer_id NUMBER;
106 l_ship_to_customer_id NUMBER;
107 l_check_id_flag VARCHAR2(1) := 'A'; -- Variable added by aditi for tracking bug
108
109 CURSOR C1 IS
110 Select cust_acc_rel_code
111 From pa_implementations;
112
113 CURSOR C2 IS
114 SELECT related_cust_account_id
115 FROM hz_cust_acct_relate
116 WHERE cust_account_id = l_customer_id
117 AND bill_to_flag = 'Y'
118 AND status = 'A'
119 AND related_cust_account_id = l_bill_to_customer_id;
120
121 CURSOR C3 IS
122 SELECT related_cust_account_id
123 FROM hz_cust_acct_relate
124 WHERE cust_account_id = l_customer_id
125 AND ship_to_flag = 'Y'
126 AND status = 'A'
127 AND related_cust_account_id = l_ship_to_customer_id;
128
129 --Billing setup related changes for FP_M development. Tracking bug 3279981
130 CURSOR C4 IS
131 SELECT enable_top_task_customer_flag
132 FROM pa_projects_all
133 WHERE project_id = p_project_id;
134 l_en_top_task_cust_flag VARCHAR2(1);
135
136 /* End For Bug 2731449 */
137
138
139 --The following cursor has been added for federal changes by sunkalya bug#5511353
140 --sunkalya:federal bug#5511353
141 CURSOR cur_chk_funds_consumption_flag
142 IS
143 SELECT DATE_EFF_FUNDS_CONSUMPTION
144 FROM pa_projects_all
145 WHERE
146 project_id= p_project_id;
147
148 l_date_eff_funds_consumption VARCHAR2(1);
149
150 --End of federal changes by sunkalya
151 --sunkalya:federal bug#5511353
152
153 BEGIN
154 pa_debug.init_err_stack('PA_CUSTOMERS_CONTACTS_PUB.Create_Project_Customer');
155 --dbms_output.put_line('11111111111 Value of p_customer_id'||p_customer_id);
156 if (p_debug_mode = 'Y') then
157 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Create_Project_Customer BEGIN');
158 end if;
159
160 if p_commit = FND_API.G_TRUE then
161 savepoint create_project_customer;
162 end if;
163
164 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
165 raise FND_API.G_EXC_UNEXPECTED_ERROR;
166 end if;
167
168 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
169 FND_MSG_PUB.initialize;
170 end if;
171
172 if (p_debug_mode = 'Y') then
173 pa_debug.debug('Performing ID validations and conversions...');
174 end if;
175
176 --Billing setup related changes for FP_M development. Tracking bug 3279981
177 IF p_calling_module <> 'FORM' THEN
178 OPEN C4;
179 FETCH C4 INTO l_en_top_task_cust_flag;
180 CLOSE C4;
181 ELSE
182 l_en_top_task_cust_flag := p_en_top_task_cust_flag;
183 END IF;
184
185 --The following cursor has been added for federal changes by sunkalya
186 --sunkalya:federal bug#5511353
187
188 OPEN cur_chk_funds_consumption_flag;
189 FETCH cur_chk_funds_consumption_flag INTO l_date_eff_funds_consumption;
190 CLOSE cur_chk_funds_consumption_flag;
191
192 --End of federal changes by sunkalya
193 --sunkalya:federal bug#5511353
194
195 -- Required field validation
196 -- This is currently necessary because these validations cannot be done on
197 -- the self-service front end
198 -- This will be removed once that technology is available
199 -- Bug 2965841 : Added the code in front end so that these fields are mandatory. Hence the following code is not needed.
200 -- But let it be here, no harm.
201 --Billing setup related changes for FP_M development. Tracking bug 3279981. customer_bill_split can
202 --be null in case customer at top task is enabled. Adding foll IF condition
203
204 --added the AND condition in the IF below for federal changes.Sunkalya
205 --sunkalya:federal bug#5511353
206
207 IF l_en_top_task_cust_flag = 'N' AND nvl(l_date_eff_funds_consumption,'N') ='N' THEN --bug#5511353
208 if p_customer_bill_split is NULL then
209 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
210 ,p_msg_name => 'PA_CUST_BILL_SPLIT_INVALID');
211 end if;
212 END IF;
213
214 --sunkalya:federal bug#5511353
215
216 if p_project_relationship_code is NULL then
217 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
218 ,p_msg_name => 'PA_RELATIONSHIP_CODE_REQD');
219 end if;
220
221
222 /* START For Bug 2731449 */
223 -- checking for not null values as bill to customer and shipt to customer are mandatory fields
224 /* Amit 2965841 : Commented as not sufficent to check only for id. New check is added below.
225 if p_bill_to_customer_id is NULL then
226 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
227 ,p_msg_name => 'PA_BILL_TO_CUST_NAME_REQD');
228 end if;
229
230 if p_ship_to_customer_id is NULL then
231 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
232 ,p_msg_name => 'PA_SHIP_TO_CUST_NAME_REQD');
233 end if;
234 : Amit 2965841*/
235 /* End For Bug 2731449 */
236 -- End of required field validation
237
238 --dbms_output.put_line('22222222222222 Value of p_customer_id'||p_customer_id);
239
240 if ((p_customer_name = FND_API.G_MISS_CHAR) OR (p_customer_name is NULL)) AND
241 ((p_customer_number = FND_API.G_MISS_CHAR) OR (p_customer_number is NULL)) AND
242 ((p_customer_id = FND_API.G_MISS_NUM) OR (p_customer_id is NULL)) then
243 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
244 ,p_msg_name => 'PA_CUST_NAME_OR_NUM_REQD');
245 end if;
246 -- Amit : Bug 2965841 New two checks are added below
247 if ((p_bill_to_customer_name = FND_API.G_MISS_CHAR) OR (p_bill_to_customer_name is NULL)) AND
248 ((p_bill_to_customer_number = FND_API.G_MISS_CHAR) OR (p_bill_to_customer_number is NULL)) AND
249 ((p_bill_to_customer_id = FND_API.G_MISS_NUM) OR (p_bill_to_customer_id is NULL)) then
250 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
251 ,p_msg_name => 'PA_BILL_TO_CUST_NAME_REQD');
252 end if;
253
254 if ((p_ship_to_customer_name = FND_API.G_MISS_CHAR) OR (p_ship_to_customer_name is NULL)) AND
255 ((p_ship_to_customer_number = FND_API.G_MISS_CHAR) OR (p_ship_to_customer_number is NULL)) AND
256 ((p_ship_to_customer_id = FND_API.G_MISS_NUM) OR (p_ship_to_customer_id is NULL)) then
257 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
258 ,p_msg_name => 'PA_SHIP_TO_CUST_NAME_REQD');
259 end if;
260
261 IF p_calling_module = 'AMG' THEN
262 l_check_id_flag := 'N';
263 END IF;
264
265 if p_calling_module <> 'FORM' AND p_calling_module <> 'SELF_SERVICE' THEN -- Added second condition of 4593317
266
267 -- Check for Customer
268
269 -- Amit 2965841 : We shd first check for Number as it is unique. And if it is able to find the id then we shd skip the check for name
270 if ((p_customer_number <> FND_API.G_MISS_CHAR) AND (p_customer_number is not NULL)) OR
271 ((p_customer_id <> FND_API.G_MISS_NUM) AND (p_customer_id is not NULL)) then
272 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NUMBER_OR_ID
273 ( p_customer_id => p_customer_id
274 ,p_customer_number => p_customer_number
275 ,p_check_id_flag => l_check_id_flag
276 ,x_customer_id => l_customer_id
277 ,x_return_status => l_return_status
278 ,x_error_msg_code => l_error_msg_code);
279
280 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
281 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
282 end if;
283 end if;
284 --dbms_output.put_line('33333333333 Value of p_customer_id'||p_customer_id);
285 --dbms_output.put_line('33333333333333 Value of l_customer_id'||l_customer_id);
286
287 if l_customer_id is null then -- Amit 2965841 :
288 if ((p_customer_name <> FND_API.G_MISS_CHAR) AND (p_customer_name is not NULL)) OR
289 ((p_customer_id <> FND_API.G_MISS_NUM) AND (p_customer_id is not NULL)) then
290 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
291 ( p_customer_id => p_customer_id
292 ,p_customer_name => p_customer_name
293 ,x_customer_id => l_customer_id
294 ,x_return_status => l_return_status
295 ,x_error_msg_code => l_error_msg_code);
296
297 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
298 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
299 end if;
300 end if;
301 end if; -- : Amit 2965841
302 --dbms_output.put_line('44444444444 Value of p_customer_id'||p_customer_id);
303 --dbms_output.put_line('44444444444444 Value of l_customer_id'||l_customer_id);
304 if ((p_customer_name <> FND_API.G_MISS_CHAR) AND (p_customer_name is not NULL)) AND
305 ((p_customer_number <> FND_API.G_MISS_CHAR) AND (p_customer_number is not NULL)) THEN
306 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_AND_NUMBER
307 ( p_customer_name => p_customer_name
308 ,p_customer_number => p_customer_number
309 ,x_customer_id => l_customer_id
310 ,x_return_status => l_return_status
311 ,x_error_msg_code => l_error_msg_code);
312
313 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
314 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
315 end if;
316 end if;
317 --dbms_output.put_line('5555555 Value of p_customer_id'||p_customer_id);
318 --dbms_output.put_line('5555555555555 Value of l_customer_id'||l_customer_id);
319 /* START For Bug 2731449 */
320 -- check whether bill to and ship to customer passes are valid or not
321 /* Amit : Not sufficient to check for id only. New code is added below
322 if(p_bill_to_customer_id <> NULL) THEN
323 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
327 ,x_return_status => l_return_status
324 ( p_customer_id => p_bill_to_customer_id
325 ,p_check_id_flag => 'Y'
326 ,x_customer_id => l_bill_to_customer_id
328 ,x_error_msg_code => l_error_msg_code);
329
330 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
331 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
332 end if;
333 end if;
334
335 if(p_ship_to_customer_id <> NULL) THEN
336 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
337 ( p_customer_id => p_ship_to_customer_id
338 ,p_check_id_flag => 'Y'
339 ,x_customer_id => l_ship_to_customer_id
340 ,x_return_status => l_return_status
341 ,x_error_msg_code => l_error_msg_code);
342
343 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
344 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
345 end if;
346 end if;
347 : Amit */
348
349 -- Check for Bill Customer
350 -- Amit 2965841 : We shd first check for Number as it is unique. And if it is able to find the id then we shd skip the check for name
351 -- if ((p_bill_to_customer_number is not null and p_bill_to_customer_number <> FND_API.G_MISS_CHAR)
352 -- or (p_bill_to_customer_name is not null and p_bill_to_customer_name <> FND_API.G_MISS_CHAR))
353 -- then
354 if ((p_bill_to_customer_number <> FND_API.G_MISS_CHAR) AND (p_bill_to_customer_number is not NULL)) OR
355 ((p_bill_to_customer_id <> FND_API.G_MISS_NUM) AND (p_bill_to_customer_id is not NULL)) then
356 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NUMBER_OR_ID
357 ( p_customer_id => p_bill_to_customer_id
358 ,p_customer_number => p_bill_to_customer_number
359 ,x_customer_id => l_bill_to_customer_id
360 ,x_return_status => l_return_status
361 ,x_error_msg_code => l_error_msg_code);
362
363 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
364 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name =>l_error_msg_code||'_BILL');-- Amit 2965841
365 end if;
366 end if;
367
368 if l_bill_to_customer_id is null then -- Amit 2965841:
369 if ((p_bill_to_customer_name <> FND_API.G_MISS_CHAR) AND (p_bill_to_customer_name is not NULL)) OR
370 ((p_bill_to_customer_id <> FND_API.G_MISS_NUM) AND (p_bill_to_customer_id is not NULL)) then
371 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
372 ( p_customer_id => p_bill_to_customer_id
373 ,p_customer_name => p_bill_to_customer_name
374 ,x_customer_id => l_bill_to_customer_id
375 ,x_return_status => l_return_status
376 ,x_error_msg_code => l_error_msg_code);
377
378 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
379 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code||'_BILL');-- Amit 2965841
380 end if;
381 end if;
382 end if; -- : Amit 2965841
383
384 if ((p_bill_to_customer_name <> FND_API.G_MISS_CHAR) AND (p_bill_to_customer_name is not NULL)) AND
385 ((p_bill_to_customer_number <> FND_API.G_MISS_CHAR) AND (p_bill_to_customer_number is not NULL)) THEN
386 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_AND_NUMBER
387 ( p_customer_name => p_bill_to_customer_name
388 ,p_customer_number => p_bill_to_customer_number
389 ,x_customer_id => l_bill_to_customer_id
390 ,x_return_status => l_return_status
391 ,x_error_msg_code => l_error_msg_code);
392
393 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
394 if (l_error_msg_code = 'PA_CUST_NAME_NUMBER_INVALID') then
395 l_error_msg_code := l_error_msg_code||'_B';
396 else
397 l_error_msg_code := l_error_msg_code||'_BILL';
398 end if;
399 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
400 end if;
401 end if;
402 -- Bug 2984882 : added following condition
403 if (l_bill_to_customer_id is null and p_bill_to_customer_id is not null and p_bill_to_customer_id <> FND_API.G_MISS_NUM and l_return_status = FND_API.G_RET_STS_SUCCESS) then
404 l_bill_to_customer_id:=p_bill_to_customer_id;
405 end if;
406
407
408 -- Check for Ship Customer
409 -- Amit : We shd first check for Number as it is unique. And if it is able to find the id then we shd skip the check for name
410 if ((p_ship_to_customer_number <> FND_API.G_MISS_CHAR) AND (p_ship_to_customer_number is not NULL)) OR
411 ((p_ship_to_customer_id <> FND_API.G_MISS_NUM) AND (p_ship_to_customer_id is not NULL)) then
412 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NUMBER_OR_ID
413 ( p_customer_id => p_ship_to_customer_id
414 ,p_customer_number => p_ship_to_customer_number
415 ,x_customer_id => l_ship_to_customer_id
416 ,x_return_status => l_return_status
417 ,x_error_msg_code => l_error_msg_code);
418
419 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
420 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code||'_SHIP');-- Amit 2965841
421 end if;
422 end if;
423
424 if l_ship_to_customer_id is null then -- Amit 2965841:
425 if ((p_ship_to_customer_name <> FND_API.G_MISS_CHAR) AND (p_ship_to_customer_name is not NULL)) OR
426 ((p_ship_to_customer_id <> FND_API.G_MISS_NUM) AND (p_ship_to_customer_id is not NULL)) then
427 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
431 ,x_return_status => l_return_status
428 ( p_customer_id => p_ship_to_customer_id
429 ,p_customer_name => p_ship_to_customer_name
430 ,x_customer_id => l_ship_to_customer_id
432 ,x_error_msg_code => l_error_msg_code);
433
434 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
435 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code||'_SHIP');-- Amit 2965841
436 end if;
437 end if;
438 end if; -- : Amit 2965841
439
440
441 if ((p_ship_to_customer_name <> FND_API.G_MISS_CHAR) AND (p_ship_to_customer_name is not NULL)) AND
442 ((p_ship_to_customer_number <> FND_API.G_MISS_CHAR) AND (p_ship_to_customer_number is not NULL)) THEN
443 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_AND_NUMBER
444 ( p_customer_name => p_ship_to_customer_name
445 ,p_customer_number => p_ship_to_customer_number
446 ,x_customer_id => l_ship_to_customer_id
447 ,x_return_status => l_return_status
448 ,x_error_msg_code => l_error_msg_code);
449
450 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
451 if (l_error_msg_code = 'PA_CUST_NAME_NUMBER_INVALID') then
452 l_error_msg_code := l_error_msg_code||'_S';
453 else
454 l_error_msg_code := l_error_msg_code||'_SHIP';
455 end if;
456 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
457 end if;
458 end if;
459
460 -- Bug 2984882 : added following condition
461 if (l_ship_to_customer_id is null and p_ship_to_customer_id is not null and p_ship_to_customer_id <> FND_API.G_MISS_NUM and l_return_status = FND_API.G_RET_STS_SUCCESS) then
462 l_ship_to_customer_id:=p_ship_to_customer_id;
463 end if;
464
465
466 -- get the value of customer relationship if value is yes then check for the validity of relations with project customers
467 OPEN C1;
468 FETCH C1 into l_cust_acc_rel_code;
469 CLOSE C1;
470 IF l_cust_acc_rel_code = 'Y' THEN
471 IF (l_bill_to_customer_id <> l_customer_id) THEN
472 OPEN C2;
473 FETCH C2 INTO l_valid_bill_id;
474 IF C2%NOTFOUND Then
475 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_BILL_TO_NOT_VALID');
476 End If;
477 CLOSE C2; --Bug 3865203
478 END IF;
479 --BUG#2876256
480 IF (l_ship_to_customer_id <> l_customer_id) THEN
481 OPEN C3;
482 FETCH C3 INTO l_valid_ship_id;
483 IF C3%NOTFOUND Then
484 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_SHIP_TO_NOT_VALID');
485 End If;
486 CLOSE C3; --Bug 3865203
487 END IF;
488 END If;
489
490 /* End For Bug 2731449 */
491 /*changes for bug 7225756 start here*/
492
493 if ((p_bill_site_name <> FND_API.G_MISS_CHAR) AND (p_bill_site_name is not NULL)) AND
494 ((p_bill_to_address_id = FND_API.G_MISS_NUM) OR (p_bill_to_address_id is NULL)) then
495
496 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_BILL_SITE_NAME_OR_ID
497 ( p_customer_id => l_bill_to_customer_id /* For Bug 2731449 */
498 ,p_bill_to_address_id => p_bill_to_address_id
499 ,p_bill_site_name => p_bill_site_name
500 ,x_bill_to_address_id => l_bill_to_address_id
501 ,x_return_status => l_return_status
502 ,x_error_msg_code => l_error_msg_code);
503
504 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
505 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
506 end if;
507 end if;
508
509 if ((p_bill_to_address_id <> FND_API.G_MISS_NUM) AND (p_bill_to_address_id is not NULL)) then
510
511 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_BILL_SITE_NAME_OR_ID
512 ( p_customer_id => l_bill_to_customer_id /* For Bug 2731449 */
513 ,p_bill_to_address_id => p_bill_to_address_id
514 ,p_bill_site_name => p_bill_site_name
515 ,p_check_id_flag => 'Y' --bug 5563846
516 ,x_bill_to_address_id => l_bill_to_address_id
517 ,x_return_status => l_return_status
518 ,x_error_msg_code => l_error_msg_code);
519
520 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
521 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
522 end if;
523 end if;
524
525
526 if((p_work_site_name <> FND_API.G_MISS_CHAR) AND (p_work_site_name is not NULL)) AND
527 ((p_ship_to_address_id = FND_API.G_MISS_NUM) OR (p_ship_to_address_id is NULL)) then
528 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_WORK_SITE_NAME_OR_ID
529 ( p_customer_id => l_ship_to_customer_id /* For Bug 2731449 */
530 ,p_ship_to_address_id => p_ship_to_address_id
531 ,p_work_site_name => p_work_site_name
532 ,x_ship_to_address_id => l_ship_to_address_id
533 ,x_return_status => l_return_status
534 ,x_error_msg_code => l_error_msg_code);
535
536 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
537 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
538 end if;
539 end if;
540
541 if((p_ship_to_address_id <> FND_API.G_MISS_NUM) AND (p_ship_to_address_id is not NULL)) then
545 ,p_work_site_name => p_work_site_name
542 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_WORK_SITE_NAME_OR_ID
543 ( p_customer_id => l_ship_to_customer_id /* For Bug 2731449 */
544 ,p_ship_to_address_id => p_ship_to_address_id
546 ,p_check_id_flag => 'Y' --bug 5563846
547 ,x_ship_to_address_id => l_ship_to_address_id
548 ,x_return_status => l_return_status
549 ,x_error_msg_code => l_error_msg_code);
550
551 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
552 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
553 end if;
554 end if;
555
556
557 /*changes for bug 7225756 end here*/
558 /*
559 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_RECEIVER_PROJ_NAME_OR_ID
560 ( p_receiver_task_id => p_receiver_task_id
561 ,p_receiver_project_name => p_receiver_project_name
562 ,x_receiver_task_id => l_receiver_task_id
563 ,x_return_status => l_return_status
564 ,x_error_msg_code => l_error_msg_code);
565
566 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
567 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
568 end if;
569 */
570
571 --Added by Ansari for currency name to code check.
572
573 if (p_inv_currency_code <> FND_API.G_MISS_CHAR) AND
574 (p_inv_currency_code is not NULL)
575 then
576 PA_PROJECTS_MAINT_UTILS.Check_currency_name_or_code
577 ( p_agreement_currency => p_inv_currency_code
578 ,p_agreement_currency_name => null
579 ,p_check_id_flag => 'Y'
580 ,x_agreement_currency => l_inv_currency_code
581 ,x_return_status => l_return_status
582 ,x_error_msg_code => l_error_msg_code);
583
584 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
585 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
586 end if;
587 end if;
588
589
590
591 else
592 l_customer_id := p_customer_id;
593 l_bill_to_address_id := p_bill_to_address_id;
594 l_ship_to_address_id := p_ship_to_address_id;
595 l_inv_currency_code := p_inv_currency_code;
596 l_bill_to_customer_id := p_bill_to_customer_id; -- For Bug 2978086
597 l_ship_to_customer_id := p_ship_to_customer_id; -- For Bug 2978086
598 --dbms_output.put_line('9999999999 Value of p_customer_id'||p_customer_id);
599 --dbms_output.put_line('9999999999999 Value of l_customer_id'||l_customer_id);
600
601 end if;
602
603 l_msg_count := FND_MSG_PUB.count_msg;
604 if l_msg_count > 0 then
605 x_msg_count := l_msg_count;
606 if x_msg_count = 1 then
607 pa_interface_utils_pub.get_messages
608 (p_encoded => FND_API.G_TRUE,
609 p_msg_index => 1,
610 p_msg_count => l_msg_count,
611 p_msg_data => l_msg_data,
612 p_data => l_data,
613 p_msg_index_out => l_msg_index_out);
614 x_msg_data := l_data;
615 end if;
616 raise FND_API.G_EXC_ERROR;
617 end if;
618
619 -- For Bug 2978086 Adding this additional safe code below . If somehow ship and bill cst id is null(which shoule never be)
620 -- then populate it wioth cust id
621
622 IF l_bill_to_customer_id is null then
623 l_bill_to_customer_id := l_customer_id;
624 END IF;
625 IF l_ship_to_customer_id is null then
626 l_ship_to_customer_id := l_customer_id;
627 END IF;
628 --dbms_output.put_line('Before calling PA_CUSTOMERS_CONTACTS_PVT.CREATE_PROJECT_CUSTOMER');
629 --dbms_output.put_line('Before calling value of l_customer_id'||l_customer_id);
630
631 PA_CUSTOMERS_CONTACTS_PVT.CREATE_PROJECT_CUSTOMER
632 ( p_commit => FND_API.G_FALSE
633 ,p_validate_only => p_validate_only
634 ,p_validation_level => p_validation_level
635 ,p_calling_module => p_calling_module
636 ,p_debug_mode => p_debug_mode
637 ,p_max_msg_count => p_max_msg_count
638 ,p_project_id => p_project_id
639 ,p_customer_id => l_customer_id
640 ,p_project_relationship_code => p_project_relationship_code
641 ,p_customer_bill_split => p_customer_bill_split
642 ,p_bill_to_customer_id => l_bill_to_customer_id /* For Bug 2731449 */
643 ,p_ship_to_customer_id => l_ship_to_customer_id /* For Bug 2731449 */
644 ,p_bill_to_address_id => l_bill_to_address_id
645 ,p_ship_to_address_id => l_ship_to_address_id
646 ,p_inv_currency_code => l_inv_currency_code
647 ,p_inv_rate_type => p_inv_rate_type
648 ,p_inv_rate_date => p_inv_rate_date
649 ,p_inv_exchange_rate => p_inv_exchange_rate
650 ,p_allow_user_rate_type_flag => p_allow_user_rate_type_flag
651 ,p_receiver_task_id => p_receiver_task_id
652 ,x_return_status => l_return_status
653 ,x_msg_count => l_msg_count
654 ,x_msg_data => l_msg_data
655 ,p_project_party_id => p_project_party_id
656 --Billing setup related changes for FP_M development. Tracking bug 3279981
660 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
657 ,p_Default_Top_Task_Cust_Flag => p_Default_Top_Task_Cust_Flag );
658 --dbms_output.put_line('Before calling PA_CUSTOMERS_CONTACTS_PVT.CREATE_PROJECT_CUSTOMER l_return_status'||l_return_status);
659
661 x_msg_count := FND_MSG_PUB.count_msg;
662 if x_msg_count = 1 then
663 pa_interface_utils_pub.get_messages
664 (p_encoded => FND_API.G_TRUE,
665 p_msg_index => 1,
666 p_msg_count => l_msg_count,
667 p_msg_data => l_msg_data,
668 p_data => l_data,
669 p_msg_index_out => l_msg_index_out);
670 x_msg_data := l_data;
671 end if;
672 raise FND_API.G_EXC_ERROR;
673 end if;
674
675 x_customer_id := l_customer_id;
676 x_return_status := FND_API.G_RET_STS_SUCCESS;
677
678 if p_commit = FND_API.G_TRUE then
679 commit work;
680 end if;
681
682 if (p_debug_mode = 'Y') then
683 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Create_Project_Customer END');
684 end if;
685
686 EXCEPTION
687 when FND_API.G_EXC_ERROR then
688 if p_commit = FND_API.G_TRUE then
689 rollback to create_project_customer;
690 end if;
691 x_return_status := FND_API.G_RET_STS_ERROR;
692 when FND_API.G_EXC_UNEXPECTED_ERROR then
693 if p_commit = FND_API.G_TRUE then
694 rollback to create_project_customer;
695 end if;
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
698 p_procedure_name => 'Create_Project_Customer',
699 p_error_text => SUBSTRB(SQLERRM,1,240));
700 when OTHERS then
701 if p_commit = FND_API.G_TRUE then
702 rollback to create_project_customer;
703 end if;
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
706 p_procedure_name => 'Create_Project_Customer',
707 p_error_text => SUBSTRB(SQLERRM,1,240));
708 raise;
709 END CREATE_PROJECT_CUSTOMER;
710
711
712 -- API name : Update_Project_Customer
713 -- Type : Public
714 -- Pre-reqs : None.
715 -- Parameters :
716 -- p_api_version IN NUMBER Required Default = 1.0
717 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
718 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
719 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
720 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
721 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
722 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
723 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
724 -- p_project_id IN NUMBER Required
725 -- p_customer_id IN NUMBER Required
726 -- p_project_relationship_code IN VARCHAR2 Required
727 -- p_customer_bill_split IN NUMBER Required
728 -- p_bill_to_customer_id IN NUMBER Optional Default = NULL /* For Bug 2731449 */
729 -- p_ship_to_customer_id IN NUMBER Optional Default = NULL /* For Bug 2731449 */
730 -- p_bill_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
731 -- p_ship_to_address_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
732 -- p_bill_site_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
733 -- p_work_site_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
734 -- p_inv_currency_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
735 -- p_inv_rate_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
736 -- p_inv_rate_date IN DATE Optional Default = FND_API.G_MISS_DATE
737 -- p_inv_exchange_rate IN NUMBER Optional Default = FND_API.G_MISS_NUM
738 -- p_allow_user_rate_type_flag IN VARCHAR2 Required Default = 'N'
739 -- p_receiver_task_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
740 -- p_receiver_project_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
741 -- p_record_version_number IN NUMBER Required Default = FND_API.G_MISS_NUM
742 -- x_return_status OUT VARCHAR2 Required
743 -- x_msg_count OUT NUMBER Required
744 -- x_msg_data OUT VARCHAR2 Optional
745
746 PROCEDURE UPDATE_PROJECT_CUSTOMER
747 ( p_api_version IN NUMBER := 1.0
748 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
749 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
750 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
751 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
752 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
753 ,p_debug_mode IN VARCHAR2 := 'N'
754 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
755 ,p_project_id IN NUMBER
756 ,p_customer_id IN NUMBER
757 ,p_project_relationship_code IN VARCHAR2
758 ,p_customer_bill_split IN NUMBER
762 ,p_bill_to_customer_number IN VARCHAR2 := FND_API.G_MISS_CHAR /* For Bug 2965841 */
759 ,p_bill_to_customer_id IN NUMBER := FND_API.G_MISS_NUM /* For Bug 2731449 */
760 ,p_ship_to_customer_id IN NUMBER := FND_API.G_MISS_NUM /* For Bug 2731449 */
761 ,p_bill_to_customer_name IN VARCHAR2 := FND_API.G_MISS_CHAR /* For Bug 2965841 */
763 ,p_ship_to_customer_name IN VARCHAR2 := FND_API.G_MISS_CHAR /* For Bug 2965841 */
764 ,p_ship_to_customer_number IN VARCHAR2 := FND_API.G_MISS_CHAR /* For Bug 2965841 */
765 ,p_bill_to_address_id IN NUMBER := FND_API.G_MISS_NUM
766 ,p_ship_to_address_id IN NUMBER := FND_API.G_MISS_NUM
767 ,p_bill_site_name IN VARCHAR2 := FND_API.G_MISS_CHAR
768 ,p_work_site_name IN VARCHAR2 := FND_API.G_MISS_CHAR
769 ,p_inv_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR
770 ,p_inv_rate_type IN VARCHAR2 := FND_API.G_MISS_CHAR
771 ,p_inv_rate_date IN DATE := FND_API.G_MISS_DATE
772 ,p_inv_exchange_rate IN NUMBER := FND_API.G_MISS_NUM
773 ,p_allow_user_rate_type_flag IN VARCHAR2 := 'N'
774 ,p_receiver_task_id IN NUMBER := FND_API.G_MISS_NUM
775 --,p_receiver_project_name IN VARCHAR2 := FND_API.G_MISS_CHAR
776 ,p_bill_another_project_flag IN VARCHAR2 := 'N' --Added by Aditi for tracking bug 4153629
777 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
778 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
779 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
780 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
781 --Billing setup related changes for FP_M development. Tracking bug 3279981
782 ,p_Default_Top_Task_Cust_Flag IN VARCHAR2
783 ,p_en_top_task_cust_flag IN VARCHAR2 := 'N'
784 )
785 IS
786 l_api_name CONSTANT VARCHAR(30) := 'Update_Project_Customer';
787 l_api_version CONSTANT NUMBER := 1.0;
788
789 l_bill_to_address_id NUMBER;
790 l_ship_to_address_id NUMBER;
791 -- l_receiver_task_id NUMBER;
792 l_return_status VARCHAR2(1);
793 l_error_msg_code VARCHAR2(250);
794 l_msg_count NUMBER;
795 l_msg_data VARCHAR2(250);
796 l_data VARCHAR2(250);
797 l_msg_index_out NUMBER;
798
799 l_inv_rate_type VARCHAR2(100);
800 l_project_currency_code VARCHAR2(10);
801 l_project_currency_code2 VARCHAR2(10);
802 l_inv_currency_code VARCHAR2(80);
803
804 /* START For Bug 2731449 */
805 l_cust_acc_rel_code VARCHAR2(1);
806 l_valid_bill_id Number;
807 l_valid_ship_id Number;
808 l_bill_to_customer_id NUMBER := null;
809 l_ship_to_customer_id NUMBER := null;
810 l_db_bill_to_customer_id NUMBER;
811 l_db_ship_to_customer_id NUMBER;
812 l_check_id_flag VARCHAR2(1) := 'A'; -- Variable added by aditi for tracking bug
813
814 CURSOR C2 IS
815 Select cust_acc_rel_code
816 From pa_implementations;
817
818 CURSOR C3 IS
819 SELECT related_cust_account_id
820 FROM hz_cust_acct_relate
821 WHERE cust_account_id = p_customer_id
822 AND bill_to_flag = 'Y'
823 AND status = 'A'
824 AND related_cust_account_id = l_bill_to_customer_id;
825
826 CURSOR C4 IS
827 SELECT related_cust_account_id
828 FROM hz_cust_acct_relate
829 WHERE cust_account_id = p_customer_id
830 AND ship_to_flag = 'Y'
831 AND status = 'A'
832 AND related_cust_account_id = l_ship_to_customer_id;
833
834 CURSOR C1(c_project_id NUMBER) IS
835 SELECT project_currency_code
836 FROM pa_projects_all
837 WHERE project_id = c_project_id;
838
839 -- Changed reference from pa_project_customers_v (view) to pa_project_customers (base table)
840 -- for Performance Bug 4878827 SQL ID 14907728
841 CURSOR C5 IS
842 SELECT bill_to_customer_id, ship_to_customer_id
843 FROM pa_project_customers
844 WHERE project_id = p_project_id
845 AND customer_id = p_customer_id
846 AND record_version_number = p_record_version_number;
847
848 --Billing setup related changes for FP_M development. Tracking bug 3279981
849 CURSOR C6 IS
850 SELECT enable_top_task_customer_flag
851 FROM pa_projects_all
852 WHERE project_id = p_project_id;
853 l_en_top_task_cust_flag VARCHAR2(1);
854
855 /* END For Bug 2731449 */
856
857 --The following cursor has been added for federal changes by sunkalya bug#5511353
858
859 CURSOR cur_chk_funds_consumption_flag
860 IS
861 SELECT DATE_EFF_FUNDS_CONSUMPTION
862 FROM pa_projects_all
863 WHERE
864 project_id= p_project_id;
865
866 l_date_eff_funds_consumption VARCHAR2(1);
867
868 --End of federal changes by sunkalya bug#5511353
869
870 BEGIN
871 pa_debug.init_err_stack('PA_CUSTOMERS_CONTACTS_PUB.Update_Project_Customer');
872 --dbms_output.put_line('value of p_project_relationship_code'||p_project_relationship_code);
873 if (p_debug_mode = 'Y') then
874 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Update_Project_Customer BEGIN');
878 savepoint update_project_customer;
875 end if;
876
877 if p_commit = FND_API.G_TRUE then
879 end if;
880
881 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
882 raise FND_API.G_EXC_UNEXPECTED_ERROR;
883 end if;
884
885 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
886 FND_MSG_PUB.initialize;
887 end if;
888
889 if (p_debug_mode = 'Y') then
890 pa_debug.debug('Performing ID validations and conversions...');
891 end if;
892
893 --Billing setup related changes for FP_M development. Tracking bug 3279981
894 IF p_calling_module <> 'FORM' THEN
895 OPEN C6;
896 FETCH C6 INTO l_en_top_task_cust_flag;
897 CLOSE C6;
898 ELSE
899 l_en_top_task_cust_flag := p_en_top_task_cust_flag;
900 END IF;
901
902 --The following cursor has been added for federal changes by sunkalya bug#5511353
903
904 OPEN cur_chk_funds_consumption_flag;
905 FETCH cur_chk_funds_consumption_flag INTO l_date_eff_funds_consumption;
906 CLOSE cur_chk_funds_consumption_flag;
907
908 --End of federal changes by sunkalya bug#5511353
909
910
911 -- Required field validation
912 -- This is currently necessary because these validations cannot be done on
913 -- the self-service front end
914 -- This will be removed once that technology is available
915 -- Amit 2965841 : Made these fields mandatory in front end. So no need for the below code now.
916 -- But let it be here as no harm.
917 --Billing setup related changes for FP_M development. Tracking bug 3279981. customer_bill_split can
918 --be null in case customer at top task is enabled. Adding foll IF condition
919
920
921 --added the AND condition in the IF below for federal changes.Sunkalya bug#5511353
922
923 IF l_en_top_task_cust_flag = 'N' AND nvl(l_date_eff_funds_consumption,'N') ='N' THEN
924 if p_customer_bill_split is NULL then
925 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
926 ,p_msg_name => 'PA_CUST_BILL_SPLIT_INVALID');
927 end if;
928 END IF;
929 if p_project_relationship_code is NULL then
930 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
931 ,p_msg_name => 'PA_RELATIONSHIP_CODE_REQD');
932 end if;
933 /* START For Bug 2731449 */
934 -- checking for not null values as bill to customer and ship to customer are mandatory fields
935 /* Amit 2965841: Chcecking only id is not sufficient . New code added below.
936 if p_bill_to_customer_id is NULL then
937 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
938 ,p_msg_name => 'PA_BILL_TO_CUST_NAME_REQD');
939 end if;
940
941 if p_ship_to_customer_id is NULL then
942 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
943 ,p_msg_name => 'PA_SHIP_TO_CUST_NAME_REQD');
944 end if;
945 : Amit 2965841 */
946
947 -- Amit 2965841 : Added beloe two checks
948 --dbms_output.put_line('value of p_project_relationship_code 2'||p_project_relationship_code);
949
950 if ((p_bill_to_customer_name = FND_API.G_MISS_CHAR) OR (p_bill_to_customer_name is NULL)) AND
951 ((p_bill_to_customer_number = FND_API.G_MISS_CHAR) OR (p_bill_to_customer_number is NULL)) AND
952 ((p_bill_to_customer_id = FND_API.G_MISS_NUM) OR (p_bill_to_customer_id is NULL)) then
953 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
954 ,p_msg_name => 'PA_BILL_TO_CUST_NAME_REQD');
955 end if;
956
957 if ((p_ship_to_customer_name = FND_API.G_MISS_CHAR) OR (p_ship_to_customer_name is NULL)) AND
958 ((p_ship_to_customer_number = FND_API.G_MISS_CHAR) OR (p_ship_to_customer_number is NULL)) AND
959 ((p_ship_to_customer_id = FND_API.G_MISS_NUM) OR (p_ship_to_customer_id is NULL)) then
960 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
961 ,p_msg_name => 'PA_SHIP_TO_CUST_NAME_REQD');
962 end if;
963
964
965 /* END For Bug 2731449 */
966 -- End of required field validation
967
968 if p_calling_module <> 'FORM' AND p_calling_module <> 'SELF_SERVICE' THEN --Added second if condition for 4593317
969 /* START For Bug 2731449 */
970 /* Amit 2965841: Chcecking only id is not sufficient. Added new code below
971
972 -- check whether bill to and ship to customer passes are valid or not
973 if(p_bill_to_customer_id <> NULL) THEN
974 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
975 ( p_customer_id => p_bill_to_customer_id
976 ,p_check_id_flag => 'Y'
977 ,x_customer_id => l_bill_to_customer_id
978 ,x_return_status => l_return_status
979 ,x_error_msg_code => l_error_msg_code);
980
981 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
982 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
983 end if;
984 end if;
985
986 if(p_ship_to_customer_id <> NULL) THEN
987 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
988 ( p_customer_id => p_ship_to_customer_id
989 ,p_check_id_flag => 'Y'
990 ,x_customer_id => l_ship_to_customer_id
991 ,x_return_status => l_return_status
992 ,x_error_msg_code => l_error_msg_code);
993
994 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
998 : Amit 2965841*/
995 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
996 end if;
997 end if;
999 -- Check for Bill Customer
1000 -- Amit 2965841: We shd first check for Number as it is unique. And if it is able to find the id then we shd skip the check for name
1001 if ((p_bill_to_customer_number <> FND_API.G_MISS_CHAR) AND (p_bill_to_customer_number is not NULL)) OR
1002 ((p_bill_to_customer_id <> FND_API.G_MISS_NUM) AND (p_bill_to_customer_id is not NULL)) then
1003 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NUMBER_OR_ID
1004 ( p_customer_id => p_bill_to_customer_id
1005 ,p_customer_number => p_bill_to_customer_number
1006 ,x_customer_id => l_bill_to_customer_id
1007 ,x_return_status => l_return_status
1008 ,x_error_msg_code => l_error_msg_code);
1009
1010 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1011 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name =>l_error_msg_code||'_BILL'); -- Amit 2965841
1012 end if;
1013 end if;
1014 if l_bill_to_customer_id is null then -- Amit 2965841:
1015 if ((p_bill_to_customer_name <> FND_API.G_MISS_CHAR) AND (p_bill_to_customer_name is not NULL)) OR
1016 ((p_bill_to_customer_id <> FND_API.G_MISS_NUM) AND (p_bill_to_customer_id is not NULL)) then
1017 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
1018 ( p_customer_id => p_bill_to_customer_id
1019 ,p_customer_name => p_bill_to_customer_name
1020 ,x_customer_id => l_bill_to_customer_id
1021 ,x_return_status => l_return_status
1022 ,x_error_msg_code => l_error_msg_code);
1023
1024 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1025 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code||'_BILL');-- Amit 2965841
1026 end if;
1027 end if;
1028 end if; -- : Amit 2965841
1029
1030 if ((p_bill_to_customer_name <> FND_API.G_MISS_CHAR) AND (p_bill_to_customer_name is not NULL)) AND
1031 ((p_bill_to_customer_number <> FND_API.G_MISS_CHAR) AND (p_bill_to_customer_number is not NULL)) THEN
1032 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_AND_NUMBER
1033 ( p_customer_name => p_bill_to_customer_name
1034 ,p_customer_number => p_bill_to_customer_number
1035 ,x_customer_id => l_bill_to_customer_id
1036 ,x_return_status => l_return_status
1037 ,x_error_msg_code => l_error_msg_code);
1038
1039 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1040 if (l_error_msg_code = 'PA_CUST_NAME_NUMBER_INVALID') then
1041 l_error_msg_code := l_error_msg_code||'_B';
1042 else
1043 l_error_msg_code := l_error_msg_code||'_BILL';
1044 end if;
1045 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1046 end if;
1047 end if;
1048
1049 -- Bug 2984882 : added following condition
1050 if (l_bill_to_customer_id is null and p_bill_to_customer_id is not null and p_bill_to_customer_id <> FND_API.G_MISS_NUM and l_return_status = FND_API.G_RET_STS_SUCCESS) then
1051 l_bill_to_customer_id:=p_bill_to_customer_id;
1052 end if;
1053
1054
1055 -- Check for Ship Customer
1056 -- Amit 2965841: We shd first check for Number as it is unique. And if it is able to find the id then we shd skip the check for name
1057 if ((p_ship_to_customer_number <> FND_API.G_MISS_CHAR) AND (p_ship_to_customer_number is not NULL)) OR
1058 ((p_ship_to_customer_id <> FND_API.G_MISS_NUM) AND (p_ship_to_customer_id is not NULL)) then
1059 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NUMBER_OR_ID
1060 ( p_customer_id => p_ship_to_customer_id
1061 ,p_customer_number => p_ship_to_customer_number
1062 ,x_customer_id => l_ship_to_customer_id
1063 ,x_return_status => l_return_status
1064 ,x_error_msg_code => l_error_msg_code);
1065
1066 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1067 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code||'_SHIP');-- Amit 2965841
1068 end if;
1069 end if;
1070
1071 if l_ship_to_customer_id is null then -- : Amit 2965841
1072 if ((p_ship_to_customer_name <> FND_API.G_MISS_CHAR) AND (p_ship_to_customer_name is not NULL)) OR
1073 ((p_ship_to_customer_id <> FND_API.G_MISS_NUM) AND (p_ship_to_customer_id is not NULL)) then
1074 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_OR_ID
1075 ( p_customer_id => p_ship_to_customer_id
1076 ,p_customer_name => p_ship_to_customer_name
1077 ,x_customer_id => l_ship_to_customer_id
1078 ,x_return_status => l_return_status
1079 ,x_error_msg_code => l_error_msg_code);
1080
1081 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1082 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code||'_SHIP');-- Amit 2965841
1083 end if;
1084 end if;
1085 end if; -- : Amit 2965841
1086
1087 if ((p_ship_to_customer_name <> FND_API.G_MISS_CHAR) AND (p_ship_to_customer_name is not NULL)) AND
1088 ((p_ship_to_customer_number <> FND_API.G_MISS_CHAR) AND (p_ship_to_customer_number is not NULL)) THEN
1089 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CUSTOMER_NAME_AND_NUMBER
1090 ( p_customer_name => p_ship_to_customer_name
1091 ,p_customer_number => p_ship_to_customer_number
1092 ,x_customer_id => l_ship_to_customer_id
1093 ,x_return_status => l_return_status
1094 ,x_error_msg_code => l_error_msg_code);
1095
1099 else
1096 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1097 if (l_error_msg_code = 'PA_CUST_NAME_NUMBER_INVALID') then
1098 l_error_msg_code := l_error_msg_code||'_S';
1100 l_error_msg_code := l_error_msg_code||'_SHIP';
1101 end if;
1102 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1103 end if;
1104 end if;
1105
1106 -- Bug 2984882 : added following condition
1107 if (l_ship_to_customer_id is null and p_ship_to_customer_id is not null and p_ship_to_customer_id <> FND_API.G_MISS_NUM and l_return_status = FND_API.G_RET_STS_SUCCESS) then
1108 l_ship_to_customer_id:=p_ship_to_customer_id;
1109 end if;
1110
1111 -- get the value of customer relationship if value is yes then check for the validity of relations with project customers
1112 OPEN C2;
1113 FETCH C2 into l_cust_acc_rel_code;
1114 CLOSE C2;
1115 IF l_cust_acc_rel_code = 'Y' THEN
1116 IF (l_bill_to_customer_id <> p_customer_id) THEN
1117 OPEN C3;
1118 FETCH C3 INTO l_valid_bill_id;
1119 IF C3%NOTFOUND Then
1120 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_BILL_TO_NOT_VALID');
1121 End If;
1122 CLOSE C3; --Bug 3865203
1123 END IF;
1124 --BUG#2876256
1125 IF (l_ship_to_customer_id <> p_customer_id) THEN
1126 OPEN C4;
1127 FETCH C4 INTO l_valid_ship_id;
1128 IF C4%NOTFOUND Then
1129 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_SHIP_TO_NOT_VALID');
1130 End If;
1131 CLOSE C4; --Bug 3865203
1132 END IF;
1133 END If;
1134 --dbms_output.put_line('Value of l_bill_to_customer_id'||l_bill_to_customer_id);
1135 --dbms_output.put_line('Value of p_bill_to_address_id'||p_bill_to_address_id);
1136
1137 IF p_calling_module = 'AMG' THEN
1138 l_check_id_flag := 'N';
1139 END IF;
1140 if ((p_bill_site_name <> FND_API.G_MISS_CHAR) AND (p_bill_site_name is not NULL)) OR
1141 ((p_bill_to_address_id <> FND_API.G_MISS_NUM) AND (p_bill_to_address_id is not NULL)) then
1142 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_BILL_SITE_NAME_OR_ID
1143 ( p_customer_id => l_bill_to_customer_id -- Amit 2965841
1144 ,p_bill_to_address_id => p_bill_to_address_id
1145 ,p_bill_site_name => p_bill_site_name
1146 ,p_check_id_flag => l_check_id_flag --Variable added for tarcking bug by aditi
1147 ,x_bill_to_address_id => l_bill_to_address_id
1148 ,x_return_status => l_return_status
1149 ,x_error_msg_code => l_error_msg_code);
1150
1151 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1152 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1153 end if;
1154 end if;
1155 --dbms_output.put_line('Value of l_bill_to_customer_id after '||l_bill_to_customer_id);
1156 --dbms_output.put_line('Value of p_bill_to_address_id after '||p_bill_to_address_id);
1157 --dbms_output.put_line('Value of l_bill_to_address_id after '||l_bill_to_address_id);
1158 --dbms_output.put_line('Value of l_ship_to_customer_id'||l_ship_to_customer_id);
1159 --dbms_output.put_line('Value of p_ship_to_address_id'||p_ship_to_address_id);
1160
1161 if ((p_work_site_name <> FND_API.G_MISS_CHAR) AND (p_work_site_name is not NULL)) OR
1162 ((p_ship_to_address_id <> FND_API.G_MISS_NUM) AND (p_ship_to_address_id is not NULL)) then
1163 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_WORK_SITE_NAME_OR_ID
1164 ( p_customer_id => l_ship_to_customer_id -- Amit 2965841
1165 ,p_ship_to_address_id => p_ship_to_address_id
1166 ,p_work_site_name => p_work_site_name
1167 ,p_check_id_flag => l_check_id_flag --Variable added for tarcking bug by aditi
1168 ,x_ship_to_address_id => l_ship_to_address_id
1169 ,x_return_status => l_return_status
1170 ,x_error_msg_code => l_error_msg_code);
1171
1172 /* End For Bug 2731449 */
1173
1174 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1175 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1176 end if;
1177 end if;
1178 --dbms_output.put_line('Value of l_ship_to_customer_id after '||l_ship_to_customer_id);
1179 --dbms_output.put_line('Value of p_ship_to_address_id after '||p_ship_to_address_id);
1180 --dbms_output.put_line('Value of l_ship_to_address_id after '||l_ship_to_address_id);
1181
1182 /*
1183 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_RECEIVER_PROJ_NAME_OR_ID
1184 ( p_receiver_task_id => p_receiver_task_id
1185 ,p_receiver_project_name => p_receiver_project_name
1186 ,x_receiver_task_id => l_receiver_task_id
1187 ,x_return_status => l_return_status
1188 ,x_error_msg_code => l_error_msg_code);
1189
1190 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1191 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1192 end if;
1193 */
1194
1195 --Added by Ansari for currency name to code check.
1196
1197 if (p_inv_currency_code <> FND_API.G_MISS_CHAR) AND
1198 (p_inv_currency_code is not NULL)
1199 then
1200 PA_PROJECTS_MAINT_UTILS.Check_currency_name_or_code
1201 ( p_agreement_currency => p_inv_currency_code
1205 ,x_return_status => l_return_status
1202 ,p_agreement_currency_name => null
1203 ,p_check_id_flag => 'Y'
1204 ,x_agreement_currency => l_inv_currency_code
1206 ,x_error_msg_code => l_error_msg_code);
1207
1208 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1209 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1210 end if;
1211 end if;
1212 /* START For Bug 2731449 */
1213 -- Delete all the contacts of type billing or shipping respective of either bill to customer or ship to customer has been changed
1214 OPEN C5;
1215 FETCH C5 into l_db_bill_to_customer_id,l_db_ship_to_customer_id; -- Amit
1216 CLOSE C5;
1217
1218 If (p_ship_to_customer_id <> l_ship_to_customer_id) Then
1219 PA_CUSTOMERS_CONTACTS_PUB.DELETE_ALL_BILL_SHIP_CONTACTS(
1220 P_API_VERSION => 1.0
1221 ,P_INIT_MSG_LIST => 'T'
1222 , P_COMMIT => 'F'
1223 , P_VALIDATE_ONLY => 'F'
1224 , P_VALIDATION_LEVEL => 100
1225 , P_DEBUG_MODE => 'N'
1226 , P_MAX_MSG_COUNT => 100
1227 , P_PROJECT_ID => P_PROJECT_ID
1228 , P_CUSTOMER_ID => P_CUSTOMER_ID
1229 , P_BILL_SHIP_CUSTOMER_ID => l_db_ship_to_customer_id
1230 , P_PROJECT_CONTACT_TYPE_CODE=> 'SHIPPING'
1231 , X_RETURN_STATUS => l_return_status
1232 , X_MSG_COUNT => l_msg_count
1233 , X_MSG_DATA => l_msg_data
1234 );
1235 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1236 x_msg_count := FND_MSG_PUB.count_msg;
1237 if x_msg_count = 1 then
1238 pa_interface_utils_pub.get_messages
1239 (p_encoded => FND_API.G_TRUE,
1240 p_msg_index => 1,
1241 p_msg_count => l_msg_count,
1242 p_msg_data => l_msg_data,
1243 p_data => l_data,
1244 p_msg_index_out => l_msg_index_out);
1245 x_msg_data := l_data;
1246 end if;
1247 raise FND_API.G_EXC_ERROR;
1248 end if;
1249
1250 End If;
1251
1252 If (p_bill_to_customer_id <> l_bill_to_customer_id) Then
1253 PA_CUSTOMERS_CONTACTS_PUB.DELETE_ALL_BILL_SHIP_CONTACTS(
1254 P_API_VERSION => 1.0
1255 ,P_INIT_MSG_LIST => 'T'
1256 , P_COMMIT => 'F'
1257 , P_VALIDATE_ONLY => 'F'
1258 , P_VALIDATION_LEVEL => 100
1259 , P_DEBUG_MODE => 'N'
1260 , P_MAX_MSG_COUNT => 100
1261 , P_PROJECT_ID => P_PROJECT_ID
1262 , P_CUSTOMER_ID => P_CUSTOMER_ID
1263 , P_BILL_SHIP_CUSTOMER_ID => l_db_bill_to_customer_id
1264 , P_PROJECT_CONTACT_TYPE_CODE=> 'BILLING'
1265 , X_RETURN_STATUS => l_return_status
1266 , X_MSG_COUNT => l_msg_count
1267 , X_MSG_DATA => l_msg_data
1268 );
1269
1270 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1271 x_msg_count := FND_MSG_PUB.count_msg;
1272 if x_msg_count = 1 then
1273 pa_interface_utils_pub.get_messages
1274 (p_encoded => FND_API.G_TRUE,
1275 p_msg_index => 1,
1276 p_msg_count => l_msg_count,
1277 p_msg_data => l_msg_data,
1278 p_data => l_data,
1279 p_msg_index_out => l_msg_index_out);
1280 x_msg_data := l_data;
1281 end if;
1282 raise FND_API.G_EXC_ERROR;
1283 end if;
1284 End If;
1285 else
1286 l_bill_to_customer_id := p_bill_to_customer_id; -- Amit
1287 l_ship_to_customer_id := p_ship_to_customer_id; -- Amit
1288 l_bill_to_address_id := p_bill_to_address_id;
1289 l_ship_to_address_id := p_ship_to_address_id;
1290 l_inv_currency_code := p_inv_currency_code;
1291 /* End For Bug 2731449 */
1292 end if;
1293
1294 l_msg_count := FND_MSG_PUB.count_msg;
1295 if l_msg_count > 0 then
1296 x_msg_count := l_msg_count;
1297 if x_msg_count = 1 then
1298 pa_interface_utils_pub.get_messages
1299 (p_encoded => FND_API.G_TRUE,
1300 p_msg_index => 1,
1301 p_msg_count => l_msg_count,
1302 p_msg_data => l_msg_data,
1303 p_data => l_data,
1304 p_msg_index_out => l_msg_index_out);
1305 x_msg_data := l_data;
1306 end if;
1307 raise FND_API.G_EXC_ERROR;
1308 end if;
1309
1310 -- Fix for bug #1656846
1311 open C1(p_project_id);
1312 fetch C1 into l_project_currency_code;
1313 close C1;
1314
1315 if p_inv_currency_code = FND_API.G_MISS_CHAR then
1316 l_project_currency_code2 := NULL;
1317 else
1318 l_project_currency_code2 := p_inv_currency_code;
1319 end if;
1320
1321 -- if(l_project_currency_code = nvl(l_project_currency_code2, l_project_currency_code)) AND (p_calling_module = 'FORM') then
1322 -- l_inv_rate_type := null;
1323 -- else
1324 l_inv_rate_type := p_inv_rate_type;
1325 -- end if; --This if condition is commented ( leaving just the preceeding statement only ) by Ansari for fixing 2097530
1326 -- End of fix
1327 --dbms_output.put_line('value of p_project_relationship_code 3'||p_project_relationship_code);
1331 --dbms_output.put_line('Value of l_ship_to_address_id before pvt '||l_ship_to_address_id);
1328 --dbms_output.put_line('value of p_calling_module passed from pub to pvt'||p_calling_module);
1329 --dbms_output.put_line('Value of l_bill_to_customer_id before pvt '||l_bill_to_customer_id);
1330 --dbms_output.put_line('Value of l_bill_to_address_id before pvt '||l_bill_to_address_id);
1332 --dbms_output.put_line('Value of l_ship_to_customer_id before pvt '||l_ship_to_customer_id);
1333 --dbms_output.put_line('Value of p_bill_another_project_flag before pvt '||p_bill_another_project_flag);
1334 PA_CUSTOMERS_CONTACTS_PVT.UPDATE_PROJECT_CUSTOMER
1335 ( p_commit => FND_API.G_FALSE
1336 ,p_validate_only => p_validate_only
1337 ,p_validation_level => p_validation_level
1338 ,p_calling_module => p_calling_module
1339 ,p_debug_mode => p_debug_mode
1340 ,p_max_msg_count => p_max_msg_count
1341 ,p_project_id => p_project_id
1342 ,p_customer_id => p_customer_id
1343 ,p_project_relationship_code => p_project_relationship_code
1344 ,p_customer_bill_split => p_customer_bill_split
1345 ,p_bill_to_customer_id => l_bill_to_customer_id /* For Bug 2731449 */
1346 ,p_ship_to_customer_id => l_ship_to_customer_id /* For Bug 2731449 */
1347 ,p_bill_to_address_id => l_bill_to_address_id
1348 ,p_ship_to_address_id => l_ship_to_address_id
1349 ,p_inv_currency_code => nvl(l_inv_currency_code, l_project_currency_code)
1350 ,p_inv_rate_type => l_inv_rate_type
1351 ,p_inv_rate_date => p_inv_rate_date
1352 ,p_inv_exchange_rate => p_inv_exchange_rate
1353 ,p_allow_user_rate_type_flag => p_allow_user_rate_type_flag
1354 ,p_receiver_task_id => p_receiver_task_id
1355 ,p_bill_another_project_flag => p_bill_another_project_flag --Added by Aditi for tracking bug 4153629
1356 ,p_record_version_number => p_record_version_number
1357 ,x_return_status => l_return_status
1358 ,x_msg_count => l_msg_count
1359 ,x_msg_data => l_msg_data
1360 --Billing setup related changes for FP_M development. Tracking bug 3279981
1361 ,p_Default_Top_Task_Cust_Flag => p_Default_Top_Task_Cust_Flag );
1362
1363 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1364 x_msg_count := FND_MSG_PUB.count_msg;
1365 if x_msg_count = 1 then
1366 pa_interface_utils_pub.get_messages
1367 (p_encoded => FND_API.G_TRUE,
1368 p_msg_index => 1,
1369 p_msg_count => l_msg_count,
1370 p_msg_data => l_msg_data,
1371 p_data => l_data,
1372 p_msg_index_out => l_msg_index_out);
1373 x_msg_data := l_data;
1374 end if;
1375 raise FND_API.G_EXC_ERROR;
1376 end if;
1377
1378 x_return_status := FND_API.G_RET_STS_SUCCESS;
1379
1380 if p_commit = FND_API.G_TRUE then
1381 commit work;
1382 end if;
1383
1384 if (p_debug_mode = 'Y') then
1385 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Update_Project_Customer END');
1386 end if;
1387
1388 EXCEPTION
1389 when FND_API.G_EXC_ERROR then
1390 if p_commit = FND_API.G_TRUE then
1391 rollback to update_project_customer;
1392 end if;
1393 x_return_status := FND_API.G_RET_STS_ERROR;
1394 when FND_API.G_EXC_UNEXPECTED_ERROR then
1395 if p_commit = FND_API.G_TRUE then
1396 rollback to update_project_customer;
1397 end if;
1398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1399 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
1400 p_procedure_name => 'Update_Project_Customer',
1401 p_error_text => SUBSTRB(SQLERRM,1,240));
1402 when OTHERS then
1403 if p_commit = FND_API.G_TRUE then
1404 rollback to update_project_customer;
1405 end if;
1406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1407 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
1408 p_procedure_name => 'Update_Project_Customer',
1409 p_error_text => SUBSTRB(SQLERRM,1,240));
1410 raise;
1411 END UPDATE_PROJECT_CUSTOMER;
1412
1413
1414 -- API name : Delete_Project_Customer
1415 -- Type : Public
1416 -- Pre-reqs : None.
1417 -- Parameters :
1418 -- p_api_version IN NUMBER Required Default = 1.0
1419 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
1420 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
1421 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
1422 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1423 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1424 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1425 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1426 -- p_project_id IN NUMBER Required
1427 -- p_customer_id IN NUMBER Required
1428 -- p_record_version_number IN NUMBER Required Default = FND_API.G_MISS_NUM
1429 -- x_return_status OUT VARCHAR2 Required
1430 -- x_msg_count OUT NUMBER Required
1431 -- x_msg_data OUT VARCHAR2 Optional
1432
1436 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1433 PROCEDURE DELETE_PROJECT_CUSTOMER
1434 ( p_api_version IN NUMBER := 1.0
1435 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1437 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1438 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1439 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1440 ,p_debug_mode IN VARCHAR2 := 'N'
1441 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1442 ,p_project_id IN NUMBER
1443 ,p_customer_id IN NUMBER
1444 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
1445 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1446 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1447 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1448 )
1449 IS
1450 l_api_name CONSTANT VARCHAR(30) := 'Delete_Project_Customer';
1451 l_api_version CONSTANT NUMBER := 1.0;
1452
1453 l_return_status VARCHAR2(1);
1454 l_msg_count NUMBER;
1455 l_msg_data VARCHAR2(250);
1456 l_data VARCHAR2(250);
1457 l_msg_index_out NUMBER;
1458 BEGIN
1459 pa_debug.init_err_stack('PA_CUSTOMERS_CONTACTS_PUB.Delete_Project_Customer');
1460
1461 if (p_debug_mode = 'Y') then
1462 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Delete_Project_Customer BEGIN');
1463 end if;
1464
1465 if p_commit = FND_API.G_TRUE then
1466 savepoint delete_project_customer;
1467 end if;
1468
1469 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1470 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1471 end if;
1472
1473 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
1474 FND_MSG_PUB.initialize;
1475 end if;
1476
1477 PA_CUSTOMERS_CONTACTS_PVT.DELETE_PROJECT_CUSTOMER
1478 ( p_commit => FND_API.G_FALSE
1479 ,p_validate_only => p_validate_only
1480 ,p_validation_level => p_validation_level
1481 ,p_calling_module => p_calling_module
1482 ,p_debug_mode => p_debug_mode
1483 ,p_max_msg_count => p_max_msg_count
1484 ,p_project_id => p_project_id
1485 ,p_customer_id => p_customer_id
1486 ,p_record_version_number => p_record_version_number
1487 ,x_return_status => l_return_status
1488 ,x_msg_count => l_msg_count
1489 ,x_msg_data => l_msg_data);
1490
1491 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1492 x_msg_count := FND_MSG_PUB.count_msg;
1493 if x_msg_count = 1 then
1494 pa_interface_utils_pub.get_messages
1495 (p_encoded => FND_API.G_TRUE,
1496 p_msg_index => 1,
1497 p_msg_count => l_msg_count,
1498 p_msg_data => l_msg_data,
1499 p_data => l_data,
1500 p_msg_index_out => l_msg_index_out);
1501 x_msg_data := l_data;
1502 end if;
1503 raise FND_API.G_EXC_ERROR;
1504 end if;
1505
1506 x_return_status := FND_API.G_RET_STS_SUCCESS;
1507
1508 if p_commit = FND_API.G_TRUE then
1509 commit work;
1510 end if;
1511
1512 if (p_debug_mode = 'Y') then
1513 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Delete_Project_Customer END');
1514 end if;
1515
1516 EXCEPTION
1517 when FND_API.G_EXC_ERROR then
1518 if p_commit = FND_API.G_TRUE then
1519 rollback to delete_project_customer;
1520 end if;
1521 x_return_status := FND_API.G_RET_STS_ERROR;
1522 when FND_API.G_EXC_UNEXPECTED_ERROR then
1523 if p_commit = FND_API.G_TRUE then
1524 rollback to delete_project_customer;
1525 end if;
1526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1527 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
1528 p_procedure_name => 'Delete_Project_Customer',
1529 p_error_text => SUBSTRB(SQLERRM,1,240));
1530 when OTHERS then
1531 if p_commit = FND_API.G_TRUE then
1532 rollback to delete_project_customer;
1533 end if;
1534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1535 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
1536 p_procedure_name => 'Delete_Project_Customer',
1537 p_error_text => SUBSTRB(SQLERRM,1,240));
1538 raise;
1539 END DELETE_PROJECT_CUSTOMER;
1540
1541
1542 -- API name : Create_Customer_Contact
1543 -- Type : Public
1544 -- Pre-reqs : None.
1545 -- Parameters :
1546 -- p_api_version IN NUMBER Required Default = 1.0
1547 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
1548 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
1549 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
1550 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1554 -- p_project_id IN NUMBER Required
1551 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1552 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1553 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1555 -- p_customer_id IN NUMBER Required
1556 -- p_bill_ship_customer_id IN NUMBER Required
1557 -- p_contact_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1558 -- p_contact_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1559 -- p_project_contact_type_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1560 -- p_project_contact_type_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1561 -- x_return_status OUT VARCHAR2 Required
1562 -- x_msg_count OUT NUMBER Required
1563 -- x_msg_data OUT VARCHAR2 Optional
1564
1565 PROCEDURE CREATE_CUSTOMER_CONTACT
1566 ( p_api_version IN NUMBER := 1.0
1567 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1568 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1569 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1570 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1571 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1572 ,p_debug_mode IN VARCHAR2 := 'N'
1573 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1574 ,p_project_id IN NUMBER
1575 ,p_customer_id IN NUMBER
1576 ,p_bill_ship_customer_id IN NUMBER /* For Bug 2731449 */
1577 ,p_contact_id IN NUMBER := FND_API.G_MISS_NUM
1578 ,p_contact_name IN VARCHAR2 := FND_API.G_MISS_CHAR
1579 ,p_project_contact_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
1580 ,p_project_contact_type_name IN VARCHAR2 := FND_API.G_MISS_CHAR
1581 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1582 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1583 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1584 )
1585 IS
1586 l_api_name CONSTANT VARCHAR(30) := 'Create_Customer_Contact';
1587 l_api_version CONSTANT NUMBER := 1.0;
1588
1589 /* Bug 2874261 */
1590 /* l_address_id Number;
1591 l_contact_idt Number; */
1592 l_site_use_code VARCHAR2(30);
1593 l_validate Number;
1594
1595 l_contact_id NUMBER;
1596 l_project_contact_type_code pa_project_contacts.project_contact_type_code%TYPE;
1597 l_return_status VARCHAR2(1);
1598 l_error_msg_code VARCHAR2(250);
1599 l_msg_count NUMBER;
1600 l_msg_data VARCHAR2(250);
1601 l_data VARCHAR2(250);
1602 l_msg_index_out NUMBER;
1603 l_address_id NUMBER; -- Added for Bug 2964227
1604
1605 /* For Bug 2731449 */
1606
1607 /* Bug 2874261 - Commenting the cursors below */
1608 /* CURSOR C1 IS
1609 SELECT a.Address_id, su.Contact_id, su.site_use_code
1610 FROM Ra_Addresses a,
1611 Ra_Site_Uses su
1612 WHERE a.Address_Id = su.Address_Id
1613 AND Nvl(a.Status,'A') = 'A'
1614 AND a.Customer_id = p_bill_ship_customer_id
1615 AND Nvl(su.Status, 'A') = 'A'
1616 AND su.primary_flag = 'Y'
1617 AND su.site_use_code = l_site_use_code
1618 AND su.Contact_id = p_contact_id;
1619
1620 CURSOR C2 IS
1621 SELECT a.Address_id, su.Contact_id, su.site_use_code
1622 FROM Ra_Addresses a,
1623 Ra_Site_Uses su
1624 WHERE a.Address_Id = su.Address_Id
1625 AND Nvl(a.Status,'A') = 'A'
1626 AND a.Customer_id = p_customer_id
1627 AND Nvl(su.Status, 'A') = 'A'
1628 AND su.primary_flag = 'Y'
1629 AND su.Contact_id = p_contact_id; */
1630
1631 /* Bug 2874261 - Adding the modified cursors below */
1632 /*Bug 3691196 - Commenting out both the cursors below
1633 CURSOR C1 IS
1634 SELECT 1 from dual
1635 where exists (select c.contact_id
1636 from ra_contacts c ,
1637 ra_contact_roles cr ,
1638 ra_addresses a
1639 where c.contact_id = cr.contact_id
1640 AND nvl(c.status, 'A') = 'A'
1641 AND nvl(c.address_id, a.address_id) = a.address_id
1642 and c.customer_id = p_bill_ship_customer_id
1643 and cr.usage_code = l_site_use_code
1644 and c.contact_id = p_contact_id);
1645
1646 CURSOR C2 IS
1647 SELECT 1 from dual
1648 where exists (select c.contact_id
1649 from ra_contacts c ,
1650 ra_contact_roles cr ,
1651 ra_addresses a
1652 where c.contact_id = cr.contact_id
1653 AND nvl(c.status, 'A') = 'A'
1654 AND nvl(c.address_id, a.address_id) = a.address_id
1655 and c.customer_id = p_customer_id
1656 and c.contact_id = p_contact_id);
1657 */
1658
1659 BEGIN
1663 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Create_Customer_Contact BEGIN');
1660 pa_debug.init_err_stack('PA_CUSTOMERS_CONTACTS_PUB.Create_Customer_Contact');
1661
1662 if (p_debug_mode = 'Y') then
1664 end if;
1665
1666 if p_commit = FND_API.G_TRUE then
1667 savepoint create_customer_contact;
1668 end if;
1669
1670 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1671 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1672 end if;
1673
1674 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
1675 FND_MSG_PUB.initialize;
1676 end if;
1677
1678 if (p_debug_mode = 'Y') then
1679 pa_debug.debug('Performing ID validations and conversions...');
1680 end if;
1681
1682 if ((p_project_contact_type_code = FND_API.G_MISS_CHAR) OR (p_project_contact_type_code is NULL)) AND
1683 ((p_project_contact_type_name = FND_API.G_MISS_CHAR) OR (p_project_contact_type_name is NULL)) then
1684 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1685 ,p_msg_name => 'PA_CONTACT_TYPE_REQD');
1686 end if;
1687
1688 if p_calling_module <> 'FORM' then
1689 if ((p_project_contact_type_name <> FND_API.G_MISS_CHAR) AND (p_project_contact_type_name is not NULL)) OR
1690 ((p_project_contact_type_code <> FND_API.G_MISS_CHAR) AND (p_project_contact_type_code is not NULL)) then
1691 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTACT_TYP_NAME_OR_CODE
1692 ( p_project_contact_type_code => p_project_contact_type_code
1693 ,p_project_contact_type_name => p_project_contact_type_name
1694 ,p_check_id_flag => 'N'
1695 ,x_project_contact_type_code => l_project_contact_type_code
1696 ,x_return_status => l_return_status
1697 ,x_error_msg_code => l_error_msg_code);
1698
1699 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1700 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1701 end if;
1702 end if;
1703 end if;
1704
1705 if ((p_contact_id = FND_API.G_MISS_NUM) OR (p_contact_id is NULL)) AND
1706 ((p_contact_name = FND_API.G_MISS_CHAR) OR (p_contact_name is NULL)) then
1707 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1708 ,p_msg_name => 'PA_CONTACT_NAME_REQD');
1709 end if;
1710
1711 -- Amit 2965841 : Commented the whole code for p_calling_module <> 'FORM' condition and added new code below it.
1712 /* if p_calling_module <> 'FORM' then
1713 For Bug 2731449
1714
1715 -- check for contact id with respect to bill to or ship to or project customer depending on value of p_project_contact_type_code
1716 if ((p_contact_name <> FND_API.G_MISS_CHAR) AND (p_contact_name is not NULL)) OR
1717 ((p_contact_id <> FND_API.G_MISS_NUM) AND (p_contact_id is not NULL)) then
1718 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTACT_NAME_OR_ID
1719 ( p_customer_id => p_customer_id
1720 ,p_project_contact_type_code => l_project_contact_type_code
1721 ,p_contact_id => p_contact_id
1722 ,p_contact_name => p_contact_name
1723 ,x_contact_id => l_contact_id
1724 ,x_return_status => l_return_status
1725 ,x_error_msg_code => l_error_msg_code);
1726
1727 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1728 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1729 end if;
1730 end if;
1731
1732 if ((p_project_contact_type_code <> 'BILLING') AND (p_project_contact_type_code <> 'SHIPPING'))THEN
1733
1734 OPEN C2;
1735 FETCH C2 INTO l_validate;
1736 IF C2%NOTFOUND THEN
1737 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_INV_CONTRACT_CONTACT');
1738 END IF;
1739 CLOSE C2; -- Bug 2874261
1740 else
1741 if(p_project_contact_type_code <> 'BILLING') Then
1742 l_site_use_code := 'SHIP_TO';
1743 else
1744 l_site_use_code := 'BILL_TO';
1745 end if;
1746 OPEN C1;
1747 FETCH C1 INTO l_validate;
1748 IF C1%NOTFOUND THEN
1749 if(p_project_contact_type_code <> 'BILLING') Then
1750 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_INV_SHIP_CONTACT');
1751 else
1752 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_INV_BILL_CONTACT');
1753 end if;
1754 END IF;
1755 CLOSE C1; -- Bug 2874261
1756 End if;
1757 END IF;
1758 Bug 2874261
1759 l_contact_id := p_contact_id;
1760 l_project_contact_type_code := p_project_contact_type_code;
1761 else
1762 l_contact_id := p_contact_id;
1763 l_project_contact_type_code := p_project_contact_type_code;
1764
1765 end if;
1766 : Amit 2965841 */
1767 -- Amit 2965841 Added the below code for p_calling_module <> FORM
1768 if p_calling_module <> 'FORM' then
1769 -- Added the following of condition for Bug 2964227
1770 IF l_project_contact_type_code = 'BILLING' THEN
1771 begin
1772 select bill_to_address_id into l_address_id from pa_project_customers where project_id = p_project_id and customer_id=p_customer_id;
1773 exception when others then
1774 l_address_id := null;
1775 end;
1776 ELSIF l_project_contact_type_code = 'SHIPPING' THEN
1777 begin
1781 end;
1778 select ship_to_address_id into l_address_id from pa_project_customers where project_id = p_project_id and customer_id=p_customer_id;
1779 exception when others then
1780 l_address_id := null;
1782 END IF;
1783
1784 -- check for contact id with respect to bill to or ship to or project customer depending on value of p_project_contact_type_code
1785 if ((l_project_contact_type_code <> 'BILLING') AND (l_project_contact_type_code <> 'SHIPPING'))THEN
1786 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTACT_NAME_OR_ID
1787 ( p_customer_id => p_customer_id
1788 ,p_project_contact_type_code => l_project_contact_type_code
1789 ,p_contact_id => p_contact_id
1790 ,p_contact_name => p_contact_name
1791 ,x_contact_id => l_contact_id
1792 ,x_return_status => l_return_status
1793 ,x_error_msg_code => l_error_msg_code);
1794
1795 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1796 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1797 end if;
1798 else
1799 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTACT_NAME_OR_ID
1800 ( p_customer_id => p_bill_ship_customer_id
1801 ,p_project_contact_type_code => l_project_contact_type_code
1802 ,p_contact_id => p_contact_id
1803 ,p_contact_name => p_contact_name
1804 ,p_address_id => l_address_id -- Added for Bug 2964227
1805 ,x_contact_id => l_contact_id
1806 ,x_return_status => l_return_status
1807 ,x_error_msg_code => l_error_msg_code);
1808
1809 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1810 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
1811 end if;
1812 end if;
1813
1814 else -- p_calling_module <> 'FORM'
1815 l_contact_id := p_contact_id;
1816 l_project_contact_type_code := p_project_contact_type_code;
1817
1818 end if;
1819
1820 l_msg_count := FND_MSG_PUB.count_msg;
1821 if l_msg_count > 0 then
1822 x_msg_count := l_msg_count;
1823 if x_msg_count = 1 then
1824 pa_interface_utils_pub.get_messages
1825 (p_encoded => FND_API.G_TRUE,
1826 p_msg_index => 1,
1827 p_msg_count => l_msg_count,
1828 p_msg_data => l_msg_data,
1829 p_data => l_data,
1830 p_msg_index_out => l_msg_index_out);
1831 x_msg_data := l_data;
1832 end if;
1833 raise FND_API.G_EXC_ERROR;
1834 end if;
1835
1836 PA_CUSTOMERS_CONTACTS_PVT.CREATE_CUSTOMER_CONTACT
1837 ( p_commit => FND_API.G_FALSE
1838 ,p_validate_only => p_validate_only
1839 ,p_validation_level => p_validation_level
1840 ,p_calling_module => p_calling_module
1841 ,p_debug_mode => p_debug_mode
1842 ,p_max_msg_count => p_max_msg_count
1843 ,p_project_id => p_project_id
1844 ,p_customer_id => p_customer_id
1845 ,p_bill_ship_customer_id => p_bill_ship_customer_id /* For Bug 2731449 */
1846 ,p_contact_id => l_contact_id
1847 ,p_project_contact_type_code => l_project_contact_type_code
1848 ,x_return_status => l_return_status
1849 ,x_msg_count => l_msg_count
1850 ,x_msg_data => l_msg_data);
1851
1852 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1853 x_msg_count := FND_MSG_PUB.count_msg;
1854 if x_msg_count = 1 then
1855 pa_interface_utils_pub.get_messages
1856 (p_encoded => FND_API.G_TRUE,
1857 p_msg_index => 1,
1858 p_msg_count => l_msg_count,
1859 p_msg_data => l_msg_data,
1860 p_data => l_data,
1861 p_msg_index_out => l_msg_index_out);
1862 x_msg_data := l_data;
1863 end if;
1864 raise FND_API.G_EXC_ERROR;
1865 end if;
1866
1867 x_return_status := FND_API.G_RET_STS_SUCCESS;
1868
1869 if p_commit = FND_API.G_TRUE then
1870 commit work;
1871 end if;
1872
1873 if (p_debug_mode = 'Y') then
1874 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Create_Customer_Contact END');
1875 end if;
1876
1877 EXCEPTION
1878 when FND_API.G_EXC_ERROR then
1879 if p_commit = FND_API.G_TRUE then
1880 rollback to create_customer_contact;
1881 end if;
1882 x_return_status := FND_API.G_RET_STS_ERROR;
1883 when FND_API.G_EXC_UNEXPECTED_ERROR then
1884 if p_commit = FND_API.G_TRUE then
1885 rollback to create_customer_contact;
1886 end if;
1887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1888 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
1889 p_procedure_name => 'Create_Customer_Contact',
1890 p_error_text => SUBSTRB(SQLERRM,1,240));
1891 when OTHERS then
1892 if p_commit = FND_API.G_TRUE then
1893 rollback to create_customer_contact;
1894 end if;
1895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1896 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
1900 END CREATE_CUSTOMER_CONTACT;
1897 p_procedure_name => 'Create_Customer_Contact',
1898 p_error_text => SUBSTRB(SQLERRM,1,240));
1899 raise;
1901
1902
1903 -- API name : Update_Customer_Contact
1904 -- Type : Public
1905 -- Pre-reqs : None.
1906 -- Parameters :
1907 -- p_api_version IN NUMBER Required Default = 1.0
1908 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
1909 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
1910 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
1911 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1912 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1913 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1914 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1915 -- p_project_id IN NUMBER Required
1916 -- p_customer_id IN NUMBER Required
1917 -- p_bill_ship_customer_id IN NUMBER Required
1918 -- p_contact_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1919 -- p_contact_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1920 -- p_project_contact_type_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1921 -- p_project_contact_type_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1922 -- p_rowid IN VARCHAR2 Required
1923 -- p_record_version_number IN NUMBER Required
1924 -- x_return_status OUT VARCHAR2 Required
1925 -- x_msg_count OUT NUMBER Required
1926 -- x_msg_data OUT VARCHAR2 Optional
1927
1928 PROCEDURE UPDATE_CUSTOMER_CONTACT
1929 ( p_api_version IN NUMBER := 1.0
1930 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1931 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1932 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1933 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1934 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1935 ,p_debug_mode IN VARCHAR2 := 'N'
1936 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1937 ,p_project_id IN NUMBER
1938 ,p_customer_id IN NUMBER
1939 ,p_bill_ship_customer_id IN NUMBER
1940 ,p_contact_id IN NUMBER := FND_API.G_MISS_NUM
1941 ,p_contact_name IN VARCHAR2 := FND_API.G_MISS_CHAR
1942 ,p_project_contact_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
1943 ,p_project_contact_type_name IN VARCHAR2 := FND_API.G_MISS_CHAR
1944 ,p_rowid IN VARCHAR2
1945 ,p_record_version_number IN NUMBER
1946 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1947 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1948 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1949 )
1950 IS
1951 l_api_name CONSTANT VARCHAR(30) := 'Update_Customer_Contact';
1952 l_api_version CONSTANT NUMBER := 1.0;
1953
1954 /* Bug 2874261 */
1955 /* l_address_id Number;
1956 l_contact_idt Number; */
1957 l_site_use_code VARCHAR2(30);
1958 l_validate NUMBER;
1959
1960 l_contact_id NUMBER;
1961 l_project_contact_type_code pa_project_contacts.project_contact_type_code%TYPE;
1962 l_return_status VARCHAR2(1);
1963 l_error_msg_code VARCHAR2(250);
1964 l_msg_count NUMBER;
1965 l_msg_data VARCHAR2(250);
1966 l_data VARCHAR2(250);
1967 l_msg_index_out NUMBER;
1968 l_address_id NUMBER; -- Added for Bug 2964227
1969
1970 /* Bug 2874261 - Commenting the cursors below */
1971 /* For Bug 2731449 */
1972 /* CURSOR C1 IS
1973 SELECT a.Address_id, su.Contact_id, su.site_use_code
1974 FROM Ra_Addresses a,
1975 Ra_Site_Uses su
1976 WHERE a.Address_Id = su.Address_Id
1977 AND Nvl(a.Status,'A') = 'A'
1978 AND a.Customer_id = p_bill_ship_customer_id
1979 AND Nvl(su.Status, 'A') = 'A'
1980 AND su.primary_flag = 'Y'
1981 AND su.site_use_code = l_site_use_code
1982 AND su.Contact_id = p_contact_id;
1983
1984 CURSOR C2 IS
1985 SELECT a.Address_id, su.Contact_id, su.site_use_code
1986 FROM Ra_Addresses a,
1987 Ra_Site_Uses su
1988 WHERE a.Address_Id = su.Address_Id
1989 AND Nvl(a.Status,'A') = 'A'
1990 AND a.Customer_id = p_customer_id
1991 AND Nvl(su.Status, 'A') = 'A'
1992 AND su.primary_flag = 'Y'
1993 AND su.Contact_id = p_contact_id; */
1994
1995
1996 /* Bug 2874261 - Adding the modified cursors below */
1997 /*Bug 3691196 - Commenting out both the cursors below
1998 CURSOR C1 IS
1999 SELECT 1 from dual
2000 where exists (select c.contact_id
2001 from ra_contacts c ,
2002 ra_contact_roles cr ,
2003 ra_addresses a
2007 and c.customer_id = p_bill_ship_customer_id
2004 where c.contact_id = cr.contact_id
2005 AND nvl(c.status, 'A') = 'A'
2006 AND nvl(c.address_id, a.address_id) = a.address_id
2008 and cr.usage_code = l_site_use_code
2009 and c.contact_id = p_contact_id);
2010
2011 CURSOR C2 IS
2012 SELECT 1 from dual
2013 where exists (select c.contact_id
2014 from ra_contacts c ,
2015 ra_contact_roles cr ,
2016 ra_addresses a
2017 where c.contact_id = cr.contact_id
2018 AND nvl(c.status, 'A') = 'A'
2019 AND nvl(c.address_id, a.address_id) = a.address_id
2020 and c.customer_id = p_customer_id
2021 and c.contact_id = p_contact_id);
2022 */
2023 BEGIN
2024 pa_debug.init_err_stack('PA_CUSTOMERS_CONTACTS_PUB.Update_Customer_Contact');
2025
2026 if (p_debug_mode = 'Y') then
2027 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Update_Customer_Contact BEGIN');
2028 end if;
2029
2030 if p_commit = FND_API.G_TRUE then
2031 savepoint update_customer_contact;
2032 end if;
2033
2034 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2035 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2036 end if;
2037
2038 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
2039 FND_MSG_PUB.initialize;
2040 end if;
2041
2042 if (p_debug_mode = 'Y') then
2043 pa_debug.debug('Performing ID validations and conversions...');
2044 end if;
2045
2046 if ((p_project_contact_type_code = FND_API.G_MISS_CHAR) OR (p_project_contact_type_code is NULL)) AND
2047 ((p_project_contact_type_name = FND_API.G_MISS_CHAR) OR (p_project_contact_type_name is NULL)) then
2048 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2049 ,p_msg_name => 'PA_CONTACT_TYPE_REQD');
2050 end if;
2051
2052 -- Amit 2965841: Uncommented the below code. It is required.
2053
2054 if p_calling_module <> 'FORM' then
2055 if ((p_project_contact_type_name <> FND_API.G_MISS_CHAR) AND (p_project_contact_type_name is not NULL)) OR
2056 ((p_project_contact_type_code <> FND_API.G_MISS_CHAR) AND (p_project_contact_type_code is not NULL)) then
2057 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTACT_TYP_NAME_OR_CODE
2058 ( p_project_contact_type_code => p_project_contact_type_code
2059 ,p_project_contact_type_name => p_project_contact_type_name
2060 ,p_check_id_flag => 'N'
2061 ,x_project_contact_type_code => l_project_contact_type_code
2062 ,x_return_status => l_return_status
2063 ,x_error_msg_code => l_error_msg_code);
2064
2065 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2066 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
2067 end if;
2068 end if;
2069 end if;
2070
2071 if ((p_contact_id = FND_API.G_MISS_NUM) OR (p_contact_id is NULL)) AND
2072 ((p_contact_name = FND_API.G_MISS_CHAR) OR (p_contact_name is NULL)) then
2073 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2074 ,p_msg_name => 'PA_CONTACT_NAME_REQD');
2075 end if;
2076
2077 -- Amit 2965841: Commented the whole code for p_calling_module <> 'FORM' condition and added new code below it.
2078 /* if p_calling_module <> 'FORM' then
2079 if ((p_contact_name <> FND_API.G_MISS_CHAR) AND (p_contact_name is not NULL)) OR
2080 ((p_contact_id <> FND_API.G_MISS_NUM) AND (p_contact_id is not NULL)) then
2081 For Bug 2731449
2082 -- check for contact id with respect to bill to or ship to or project customer depending on value of p_project_contact_type_code
2083
2084 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTACT_NAME_OR_ID
2085 ( p_customer_id => p_customer_id
2086 ,p_project_contact_type_code => l_project_contact_type_code
2087 ,p_contact_id => p_contact_id
2088 ,p_contact_name => p_contact_name
2089 ,x_contact_id => l_contact_id
2090 ,x_return_status => l_return_status
2091 ,x_error_msg_code => l_error_msg_code);
2092
2093 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2094 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
2095 end if;
2096 end if;
2097
2098 if ((p_project_contact_type_code <> 'BILLING') AND (p_project_contact_type_code <> 'SHIPPING'))THEN
2099
2100 OPEN C2;
2101 FETCH C2 INTO l_validate;
2102 IF C2%NOTFOUND THEN
2103 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_INV_CONTRACT_CONTACT');
2104 END IF;
2105 CLOSE C2;
2106 else
2107 if(p_project_contact_type_code <> 'BILLING') Then
2108 l_site_use_code := 'SHIP_TO';
2109 else
2110 l_site_use_code := 'BILL_TO';
2111 end if;
2112 OPEN C1;
2113 FETCH C1 INTO l_validate;
2114 IF C1%NOTFOUND THEN
2115 if(p_project_contact_type_code <> 'BILLING') Then
2116 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_INV_SHIP_CONTACT');
2117 else
2118 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => 'PA_INV_BILL_CONTACT');
2119 end if;
2120 END IF;
2124 Bug 2874261
2121 CLOSE C1;
2122 End if;
2123 END IF;
2125 l_contact_id := p_contact_id;
2126 l_project_contact_type_code := p_project_contact_type_code;
2127
2128 else
2129 l_contact_id := p_contact_id;
2130 l_project_contact_type_code := p_project_contact_type_code;
2131 end if;
2132 : Amit */
2133 -- Amit 2965841 Added the below code for p_calling_module <> FORM
2134 if p_calling_module <> 'FORM' then
2135 -- Added the following of condition for Bug 2964227
2136 IF l_project_contact_type_code = 'BILLING' THEN
2137 begin
2138 select bill_to_address_id into l_address_id from pa_project_customers where project_id = p_project_id and customer_id=p_customer_id;
2139 exception when others then
2140 l_address_id := null;
2141 end;
2142 ELSIF l_project_contact_type_code = 'SHIPPING' THEN
2143 begin
2144 select ship_to_address_id into l_address_id from pa_project_customers where project_id = p_project_id and customer_id=p_customer_id;
2145 exception when others then
2146 l_address_id := null;
2147 end;
2148 END IF;
2149
2150 -- check for contact id with respect to bill to or ship to or project customer depending on value of p_project_contact_type_code
2151 if ((l_project_contact_type_code <> 'BILLING') AND (l_project_contact_type_code <> 'SHIPPING'))THEN
2152 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTACT_NAME_OR_ID
2153 ( p_customer_id => p_customer_id
2154 ,p_project_contact_type_code => l_project_contact_type_code
2155 ,p_contact_id => p_contact_id
2156 ,p_contact_name => p_contact_name
2157 ,x_contact_id => l_contact_id
2158 ,x_return_status => l_return_status
2159 ,x_error_msg_code => l_error_msg_code);
2160
2161 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2162 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
2163 end if;
2164 else
2165 PA_CUSTOMERS_CONTACTS_UTILS.CHECK_CONTACT_NAME_OR_ID
2166 ( p_customer_id => p_bill_ship_customer_id
2167 ,p_project_contact_type_code => l_project_contact_type_code
2168 ,p_contact_id => p_contact_id
2169 ,p_contact_name => p_contact_name
2170 ,p_address_id => l_address_id -- Added for Bug 2964227
2171 ,x_contact_id => l_contact_id
2172 ,x_return_status => l_return_status
2173 ,x_error_msg_code => l_error_msg_code);
2174
2175 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2176 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA', p_msg_name => l_error_msg_code);
2177 end if;
2178 end if;
2179
2180 else -- p_calling_module <> 'FORM'
2181 l_contact_id := p_contact_id;
2182 l_project_contact_type_code := p_project_contact_type_code;
2183 end if;
2184
2185 l_msg_count := FND_MSG_PUB.count_msg;
2186 if l_msg_count > 0 then
2187 x_msg_count := l_msg_count;
2188 if x_msg_count = 1 then
2189 pa_interface_utils_pub.get_messages
2190 (p_encoded => FND_API.G_TRUE,
2191 p_msg_index => 1,
2192 p_msg_count => l_msg_count,
2193 p_msg_data => l_msg_data,
2194 p_data => l_data,
2195 p_msg_index_out => l_msg_index_out);
2196 x_msg_data := l_data;
2197 end if;
2198 raise FND_API.G_EXC_ERROR;
2199 end if;
2200
2201 PA_CUSTOMERS_CONTACTS_PVT.UPDATE_CUSTOMER_CONTACT
2202 ( p_commit => FND_API.G_FALSE
2203 ,p_validate_only => p_validate_only
2204 ,p_validation_level => p_validation_level
2205 ,p_calling_module => p_calling_module
2206 ,p_debug_mode => p_debug_mode
2207 ,p_max_msg_count => p_max_msg_count
2208 ,p_project_id => p_project_id
2209 ,p_customer_id => p_customer_id
2210 ,p_bill_ship_customer_id => p_bill_ship_customer_id /* For Bug 2731449 */
2211 ,p_contact_id => l_contact_id
2212 ,p_project_contact_type_code => l_project_contact_type_code
2213 ,p_rowid => p_rowid
2214 ,p_record_version_number => p_record_version_number
2215 ,x_return_status => l_return_status
2216 ,x_msg_count => l_msg_count
2217 ,x_msg_data => l_msg_data);
2218
2219 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2220 x_msg_count := FND_MSG_PUB.count_msg;
2221 if x_msg_count = 1 then
2222 pa_interface_utils_pub.get_messages
2223 (p_encoded => FND_API.G_TRUE,
2224 p_msg_index => 1,
2225 p_msg_count => l_msg_count,
2226 p_msg_data => l_msg_data,
2227 p_data => l_data,
2228 p_msg_index_out => l_msg_index_out);
2229 x_msg_data := l_data;
2230 end if;
2231 raise FND_API.G_EXC_ERROR;
2232 end if;
2233
2234 x_return_status := FND_API.G_RET_STS_SUCCESS;
2235
2236 if p_commit = FND_API.G_TRUE then
2237 commit work;
2238 end if;
2239
2240 if (p_debug_mode = 'Y') then
2241 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Update_Customer_Contact END');
2242 end if;
2243
2244 EXCEPTION
2248 end if;
2245 when FND_API.G_EXC_ERROR then
2246 if p_commit = FND_API.G_TRUE then
2247 rollback to update_customer_contact;
2249 x_return_status := FND_API.G_RET_STS_ERROR;
2250 when FND_API.G_EXC_UNEXPECTED_ERROR then
2251 if p_commit = FND_API.G_TRUE then
2252 rollback to update_customer_contact;
2253 end if;
2254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2255 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
2256 p_procedure_name => 'Update_Customer_Contact',
2257 p_error_text => SUBSTRB(SQLERRM,1,240));
2258 when OTHERS then
2259 if p_commit = FND_API.G_TRUE then
2260 rollback to update_customer_contact;
2261 end if;
2262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2263 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
2264 p_procedure_name => 'Update_Customer_Contact',
2265 p_error_text => SUBSTRB(SQLERRM,1,240));
2266 raise;
2267 END UPDATE_CUSTOMER_CONTACT;
2268
2269
2270 -- API name : Delete_Customer_Contact
2271 -- Type : Public
2272 -- Pre-reqs : None.
2273 -- Parameters :
2274 -- p_api_version IN NUMBER Required Default = 1.0
2275 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
2276 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
2277 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
2278 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2279 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2280 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2281 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2282 -- p_project_id IN NUMBER Required
2283 -- p_customer_id IN NUMBER Required
2284 -- p_contact_id IN NUMBER Required
2285 -- p_project_contact_type_code IN VARCHAR2 Required
2286 -- p_record_version_number IN NUMBER Required Default = FND_API.G_MISS_NUM
2287 -- x_return_status OUT VARCHAR2 Required
2288 -- x_msg_count OUT NUMBER Required
2289 -- x_msg_data OUT VARCHAR2 Optional
2290
2291 PROCEDURE DELETE_CUSTOMER_CONTACT
2292 ( p_api_version IN NUMBER := 1.0
2293 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2294 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2295 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2296 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2297 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2298 ,p_debug_mode IN VARCHAR2 := 'N'
2299 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
2300 ,p_project_id IN NUMBER
2301 ,p_customer_id IN NUMBER
2302 ,p_contact_id IN NUMBER
2303 ,p_project_contact_type_code IN VARCHAR2
2304 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
2305 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2306 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2307 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2308 )
2309 IS
2310 l_api_name CONSTANT VARCHAR(30) := 'Delete_Customer_Contact';
2311 l_api_version CONSTANT NUMBER := 1.0;
2312
2313 l_return_status VARCHAR2(1);
2314 l_msg_count NUMBER;
2315 l_msg_data VARCHAR2(250);
2316 l_data VARCHAR2(250);
2317 l_msg_index_out NUMBER;
2318 BEGIN
2319 pa_debug.init_err_stack('PA_CUSTOMERS_CONTACTS_PUB.Delete_Customer_Contact');
2320
2321 if (p_debug_mode = 'Y') then
2322 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Delete_Customer_Contact BEGIN');
2323 end if;
2324
2325 if p_commit = FND_API.G_TRUE then
2326 savepoint delete_customer_contact;
2327 end if;
2328
2329 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2330 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2331 end if;
2332
2333 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
2334 FND_MSG_PUB.initialize;
2335 end if;
2336
2337 PA_CUSTOMERS_CONTACTS_PVT.DELETE_CUSTOMER_CONTACT
2338 ( p_commit => FND_API.G_FALSE
2339 ,p_validate_only => p_validate_only
2340 ,p_validation_level => p_validation_level
2341 ,p_calling_module => p_calling_module
2342 ,p_debug_mode => p_debug_mode
2343 ,p_max_msg_count => p_max_msg_count
2344 ,p_project_id => p_project_id
2345 ,p_customer_id => p_customer_id
2346 ,p_contact_id => p_contact_id
2347 ,p_project_contact_type_code => p_project_contact_type_code
2351 ,x_msg_data => l_msg_data);
2348 ,p_record_version_number => p_record_version_number
2349 ,x_return_status => l_return_status
2350 ,x_msg_count => l_msg_count
2352
2353 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2354 x_msg_count := FND_MSG_PUB.count_msg;
2355 if x_msg_count = 1 then
2356 pa_interface_utils_pub.get_messages
2357 (p_encoded => FND_API.G_TRUE,
2358 p_msg_index => 1,
2359 p_msg_count => l_msg_count,
2360 p_msg_data => l_msg_data,
2361 p_data => l_data,
2362 p_msg_index_out => l_msg_index_out);
2363 x_msg_data := l_data;
2364 end if;
2365 raise FND_API.G_EXC_ERROR;
2366 end if;
2367
2368 x_return_status := FND_API.G_RET_STS_SUCCESS;
2369
2370 if p_commit = FND_API.G_TRUE then
2371 commit work;
2372 end if;
2373
2374 if (p_debug_mode = 'Y') then
2375 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PUB.Delete_Customer_Contact END');
2376 end if;
2377
2378 EXCEPTION
2379 when FND_API.G_EXC_ERROR then
2380 if p_commit = FND_API.G_TRUE then
2381 rollback to delete_customer_contact;
2382 end if;
2383 x_return_status := FND_API.G_RET_STS_ERROR;
2384 when FND_API.G_EXC_UNEXPECTED_ERROR then
2385 if p_commit = FND_API.G_TRUE then
2386 rollback to delete_customer_contact;
2387 end if;
2388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2389 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
2390 p_procedure_name => 'Delete_Customer_Contact',
2391 p_error_text => SUBSTRB(SQLERRM,1,240));
2392 when OTHERS then
2393 if p_commit = FND_API.G_TRUE then
2394 rollback to delete_customer_contact;
2395 end if;
2396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2397 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PUB',
2398 p_procedure_name => 'Delete_Customer_Contact',
2399 p_error_text => SUBSTRB(SQLERRM,1,240));
2400 raise;
2401 END DELETE_CUSTOMER_CONTACT;
2402
2403 /* For Bug 2731449 created this api which gets all the contacts of passed contact type for customer passed and deletes them*/
2404
2405 -- API name : Delete_All_Bill_Ship_Contacts
2406 -- Type : Private
2407 -- Pre-reqs : None.
2408 -- Parameters :
2409 -- p_api_version IN NUMBER Required Default = 1.0
2410 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_TRUE
2411 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
2412 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
2413 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2414 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2415 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2416 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2417 -- p_project_id IN NUMBER Required
2418 -- p_customer_id IN NUMBER Required
2419 -- p_bill_ship_customer_id IN NUMBER Required
2420 -- p_project_contact_type_code IN VARCHAR2 Required
2421 -- x_return_status OUT VARCHAR2 Required
2422 -- x_msg_count OUT NUMBER Required
2423 -- x_msg_data OUT VARCHAR2 Optional
2424
2425 PROCEDURE DELETE_ALL_BILL_SHIP_CONTACTS
2426 ( p_api_version IN NUMBER := 1.0
2427 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2428 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2429 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2430 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2431 ,p_debug_mode IN VARCHAR2 := 'N'
2432 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
2433 ,p_project_id IN NUMBER
2434 ,p_customer_id IN NUMBER
2435 ,p_bill_ship_customer_id IN NUMBER
2436 ,p_project_contact_type_code IN VARCHAR2
2437 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2438 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2442 l_api_name CONSTANT VARCHAR(30) := 'Delete_All_Bill_Ship_Contacts';
2439 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2440 )
2441 IS
2443 l_api_version CONSTANT NUMBER := 1.0;
2444
2445 l_return_status VARCHAR2(1);
2446 l_msg_count NUMBER;
2447 l_msg_data VARCHAR2(250);
2448
2449 CURSOR C (c_project_id NUMBER, c_customer_id NUMBER, c_bill_ship_customer_id NUMBER, c_project_contact_type_code VARCHAR2) IS
2450 SELECT contact_id, record_version_number
2451 FROM pa_project_contacts
2452 WHERE project_id = c_project_id AND
2453 customer_id = c_customer_id AND
2454 bill_ship_customer_id = c_bill_ship_customer_id AND
2455 project_contact_type_code = c_project_contact_type_code;
2456
2457 l_recinfo C%ROWTYPE;
2458 BEGIN
2459
2460 pa_debug.init_err_stack('PA_CUSTOMERS_CONTACTS_PUB.Delete_All_Bill_Ship_Contacts');
2461
2462 if (p_debug_mode = 'Y') then
2463 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_All_BILL_SHIP_Contacts BEGIN');
2464 end if;
2465
2466 if p_commit = FND_API.G_TRUE then
2467 savepoint delete_all_bill_ship_contacts;
2468 end if;
2469
2470 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2471 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2472 end if;
2473
2474 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
2475 FND_MSG_PUB.initialize;
2476 end if;
2477
2478 for l_recinfo in C(p_project_id, p_customer_id, p_bill_ship_customer_id, p_project_contact_type_code) loop
2479 PA_CUSTOMERS_CONTACTS_PVT.DELETE_CUSTOMER_CONTACT
2480 ( p_commit => FND_API.G_FALSE
2481 ,p_validate_only => p_validate_only
2482 ,p_validation_level => p_validation_level
2483 ,p_calling_module => 'FORM'
2484 ,p_debug_mode => p_debug_mode
2485 ,p_max_msg_count => p_max_msg_count
2486 ,p_project_id => p_project_id
2487 ,p_customer_id => p_customer_id
2488 ,p_contact_id => l_recinfo.contact_id
2489 ,p_project_contact_type_code => p_project_contact_type_code
2490 ,p_record_version_number => l_recinfo.record_version_number
2491 ,x_return_status => l_return_status
2492 ,x_msg_count => l_msg_count
2493 ,x_msg_data => l_msg_data);
2494
2495 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2496 x_msg_count := l_msg_count;
2497 if x_msg_count = 1 then
2498 x_msg_data := l_msg_data;
2499 end if;
2500 raise FND_API.G_EXC_ERROR;
2501 end if;
2502 end loop;
2503
2504 x_return_status := FND_API.G_RET_STS_SUCCESS;
2505
2506 if p_commit = FND_API.G_TRUE then
2507 commit work;
2508 end if;
2509
2510 if (p_debug_mode = 'Y') then
2511 pa_debug.debug('PA_CUSTOMERS_CONTACTS_PVT.Delete_All_BILL_SHIP_Contacts END');
2512 end if;
2513
2514 EXCEPTION
2515 when FND_API.G_EXC_ERROR then
2516 if p_commit = FND_API.G_TRUE then
2517 rollback to delete_all_bill_ship_contacts;
2518 end if;
2519 x_return_status := FND_API.G_RET_STS_ERROR;
2520 when others then
2521 if p_commit = FND_API.G_TRUE then
2522 rollback to delete_all_bill_ship_contacts;
2523 end if;
2524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2525 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CUSTOMERS_CONTACTS_PVT',
2526 p_procedure_name => 'Delete_All_Bill_Ship_Contacts',
2527 p_error_text => SUBSTRB(SQLERRM,1,240));
2528 raise;
2529 END DELETE_ALL_BILL_SHIP_CONTACTS;
2530
2531 END PA_CUSTOMERS_CONTACTS_PUB;
[Home] [Help]