[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECTS_MAINT_PVT
Source
1 PACKAGE BODY PA_PROJECTS_MAINT_PVT AS
2 /* $Header: PARMPRVB.pls 120.10.12010000.6 2009/10/13 11:19:04 nkapling ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROJECTS_MAINT_PVT';
4 -- API name : create_project
5 -- Type : Private
6 -- Pre-reqs : None.
7 -- Parameters :
8 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
9 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
10 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
11 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
12 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
13 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
14 -- p_orig_project_id IN NUMBER Required
15 -- p_project_name IN VARCHAR2 Required
16 -- p_project_number IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
17 -- p_description IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
18 -- p_project_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
19 -- p_project_status_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
20 -- p_distribution_rule IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
21 -- p_public_sector_flag IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
22 -- p_carrying_out_organization_id IN NUMBER Optional
23 -- Default = FND_API.G_MISS_NUM
24 -- p_start_date IN DATE Optional Default = FND_API.G_MISS_DATE
25 -- p_completion_date IN DATE Optional Default = FND_API.G_MISS_DATE
26 -- p_probability_member_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
27 -- p_project_value IN NUMBER Optional Default = FND_API.G_MISS_NUM
28 -- p_expected_approval_date IN DATE Optional Default = FND_API.G_MISS_DATE
29 -- p_team_template_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
30 -- p_country_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
31 -- p_region IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
32 -- p_city IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
33 -- p_customer_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
34 -- p_agreement_currency IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
35 -- p_agreement_amount IN NUMBER Optional Default = FND_API.G_MISS_NUM
36 -- p_agreement_org_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
37 -- p_opp_value_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
38 -- p_bill_to_customer_id IN NUMBER := NULL , /* For Bug 2731449 */
39 -- p_ship_to_customer_id IN NUMBER := NULL , /* For Bug 2731449 */
40
41 -- p_long_name IN VARCHAR2 Optional Default = NULL
42 -- p_project_id OUT NUMBER Required
43 -- p_new_project_number OUT VARCHAR2 Required
44 -- x_return_status OUT VARCHAR2 Required
45 -- x_msg_count OUT NUMBER Required
46 -- x_msg_data OUT VARCHAR2 Required
47 --
48 -- History
49 --
50 -- 18-AUG-2000 -- Sakthi/William - Created.
51 --
52 --
53 PROCEDURE CREATE_PROJECT
54 (p_commit IN VARCHAR2 := FND_API.G_FALSE ,
55 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
56 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
57 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
58 p_debug_mode IN VARCHAR2 := 'N' ,
59 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
60 p_orig_project_id IN NUMBER ,
61 p_project_name IN VARCHAR2 ,
62 p_project_number IN VARCHAR2 := FND_API.G_MISS_CHAR ,
63 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR ,
64 p_project_type IN VARCHAR2 := FND_API.G_MISS_CHAR ,
65 p_project_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
66 p_distribution_rule IN VARCHAR2 := FND_API.G_MISS_CHAR ,
67 p_public_sector_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
68 p_carrying_out_organization_id IN NUMBER := FND_API.G_MISS_NUM ,
69 p_start_date IN DATE := FND_API.G_MISS_DATE ,
70 p_completion_date IN DATE := FND_API.G_MISS_DATE ,
71 p_probability_member_id IN NUMBER := FND_API.G_MISS_NUM ,
72 p_project_value IN NUMBER := FND_API.G_MISS_NUM ,
73 p_expected_approval_date IN DATE := FND_API.G_MISS_DATE ,
74 p_team_template_id IN NUMBER := FND_API.G_MISS_NUM ,
75 p_country_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
76 p_region IN VARCHAR2 := FND_API.G_MISS_CHAR ,
77 p_city IN VARCHAR2 := FND_API.G_MISS_CHAR ,
78 p_customer_id IN NUMBER := FND_API.G_MISS_NUM ,
79 p_agreement_currency IN VARCHAR2 := FND_API.G_MISS_CHAR ,
80 p_agreement_amount IN NUMBER := FND_API.G_MISS_NUM ,
81 p_agreement_org_id IN NUMBER := FND_API.G_MISS_NUM ,
82 p_opp_value_currency_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
83 p_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
84 p_template_flag IN VARCHAR2 := 'N',
85 p_security_level IN NUMBER := FND_API.G_MISS_NUM ,
86 -- Customer Account Relationship
87 p_bill_to_customer_id IN NUMBER := NULL , /* For Bug 2731449 */
88 p_ship_to_customer_id IN NUMBER := NULL , /* For Bug 2731449 */
89 --Customer Account Relationship
90 -- anlee
91 -- Project Long Name changes
92 p_long_name IN VARCHAR2 DEFAULT NULL ,
93 -- end of changes
94 p_project_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
95 p_new_project_number OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
96 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
97 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
98 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
99 IS
100
101 -- 4363092 TCA changes, replaced RA views with HZ tables
102 --l_customer_id ra_customers.customer_id%TYPE;
103 l_customer_id hz_cust_accounts.cust_account_id%TYPE;
104 -- 4363092 end
105
106 l_organization_id hr_organization_units.organization_id%TYPE;
107 l_project_status_code pa_project_statuses.project_status_code%TYPE;
108 l_project_id pa_projects.project_id%TYPE;
109 l_project_number_out pa_projects.segment1%TYPE;
110 l_probability_member_id pa_probability_members.probability_member_id%TYPE;
111 l_project_value PA_PROJECTS_ALL.PROJECT_VALUE%TYPE;
112 l_expected_approval_date PA_PROJECTS_ALL.EXPECTED_APPROVAL_DATE%TYPE;
113 l_completion_date PA_PROJECTS_ALL.COMPLETION_DATE%TYPE;
114 l_public_sector_flag PA_PROJECTS_ALL.PUBLIC_SECTOR_FLAG%TYPE;
115 l_description PA_PROJECTS_ALL.DESCRIPTION%TYPE;
116 l_project_number PA_PROJECTS_ALL.SEGMENT1%TYPE;
117 l_distribution_rule PA_PROJECTS_ALL.DISTRIBUTION_RULE%TYPE;
118 l_team_template_id PA_TEAM_TEMPLATES.TEAM_TEMPLATE_ID%TYPE;
119 l_country_code PA_LOCATIONS.COUNTRY_CODE%TYPE;
120 l_region PA_LOCATIONS.REGION%TYPE;
121 l_city PA_LOCATIONS.CITY%TYPE;
122 l_return_status VARCHAR2(1);
123 l_error_msg_code VARCHAR2(250);
124 l_msg_count NUMBER;
125 l_msg_data VARCHAR2(250);
126 l_err_code VARCHAR2(250);
127 l_err_stage VARCHAR2(2000);
128 l_err_stack VARCHAR2(2000);
129 l_data VARCHAR2(250);
130 l_msg_index_out NUMBER;
131 l_relationship_type VARCHAR2(30);
132 l_agreement_currency FND_CURRENCIES_VL.CURRENCY_CODE%TYPE;
133 l_agreement_amount NUMBER;
134 l_agreement_org_id NUMBER;
135 l_opp_value_currency_code FND_CURRENCIES_VL.CURRENCY_CODE%TYPE;
136 l_priority_code VARCHAR2(30);
137 -- Added the nvl condition in the cursor query for bug 4954698
138 CURSOR l_override_fields_csr --(c_project_id NUMBER) Bug 5478390: c_project_id no longer used
139 IS
140 SELECT TYPE
141 FROM pa_override_fields_v pof
142 WHERE pof.pa_field_name = 'CUSTOMER_NAME'
143 AND EXISTS(SELECT 'x' from pa_projects_all PP
144 -- replaced c_project_id with p_orig_project_id in where clause for Bug 5478390
145 WHERE PP.project_id = p_orig_project_id
146 AND nvl(PP.created_from_project_id,p_orig_project_id) =
147 POF.pa_source_template_id);
148
149 -- anlee
150 -- added for copy retention
151 -- Modified below cursor for bug 5724556
152 CURSOR l_get_project_dates_csr (c_project_id NUMBER)
153 IS
154 SELECT start_date, completion_date, enable_top_task_customer_flag
155 FROM pa_projects_all
156 WHERE project_id = c_project_id;
157
158 l_proj_start_date DATE;
159 l_proj_completion_date DATE;
160
161 /* Bug2450468 Begin */
162
163 l_project_type_class_code VARCHAR2(80);
164
165 CURSOR l_get_prj_class_code
166 IS
167 select meaning
168 from pa_project_types pt
169 , pa_lookups lps
170 , pa_projects pp
171 where pt.project_type = pp.project_type
172 and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
173 and lps.lookup_code(+) = pt.project_type_class_code
174 and pp.project_id=p_orig_project_id;
175
176 /* Bug2450468 End */
177 -- bug 5724556
178 l_calling_context varchar2(25);
179 l_top_task_cust_flag varchar2(1) :='N';
180
181 BEGIN
182
183 -- Standard call to check for call compatibility
184
185 if (p_debug_mode = 'Y') then
186 pa_debug.debug('Create_Project PVT: Checking the api version number.');
187 end if;
188
189 --dbms_output.put_line('Starts here PA_PROJECTS_MAINT_PVT.CREATE_PROJECT ... ');
190
191 IF p_commit = FND_API.G_TRUE THEN
192 SAVEPOINT prm_create_project;
193 END IF;
194
195 x_return_status := 'S';
196
197 --dbms_output.put_line('Before p_carrying_out_organization_id ... ');
198
199 --dbms_output.put_line('Before copy_project call ... ');
200
201 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
202
203 if (p_debug_mode = 'Y') then
204 pa_debug.debug('Create_Project PVT: Calling copy project');
205 end if;
206
207 if p_project_Value = FND_API.G_MISS_NUM
208 then
209 l_project_value := null;
210 else
211 l_project_value := p_project_value;
212 end if;
213
214 if p_expected_Approval_Date= FND_API.G_MISS_DATE
215 then
216 l_expected_approval_date := null;
217 else
218 l_expected_approval_Date := p_expected_approval_date;
219 end if;
220
221 if p_completion_date = FND_API.G_MISS_DATE
222 then
223 l_completion_Date := null;
224 else
225 l_completion_date := p_completion_date;
226 end if;
227
228 if p_public_Sector_Flag = FND_API.G_MISS_CHAR
229 then
230 l_public_sector_flag := null;
231 else
232 l_public_sector_flag := p_public_Sector_flag;
233 end if;
234
235 if p_distribution_rule = FND_API.G_MISS_CHAR
236 then
237 l_distribution_rule := null;
238 else
239 l_distribution_rule := p_distribution_rule;
240 end if;
241
242 if p_description = FND_API.G_MISS_CHAR
243 then
244 l_description := null;
245 else
246 l_description := p_description;
247 end if;
248
249 if p_project_number = FND_API.G_MISS_CHAR
250 then
251 l_project_number := null;
252 else
253 l_project_number := p_project_number;
254 end if;
255
256 if p_team_template_id = FND_API.G_MISS_NUM then
257 l_team_template_id := null;
258 else
259 l_team_template_id := p_team_template_id;
260 end if;
261
262 if p_country_code = FND_API.G_MISS_CHAR then
263 l_country_code := null;
264 else
265 l_country_code := p_country_code;
266 end if;
267
268 if p_region = FND_API.G_MISS_CHAR then
269 l_region := null;
270 else
271 l_region := p_region;
272 end if;
273
274 if p_city = FND_API.G_MISS_CHAR then
275 l_city := null;
276 else
277 l_city := p_city;
278 end if;
279
280 if p_agreement_currency = FND_API.G_MISS_CHAR then
281 l_agreement_currency := null;
282 else
283 l_agreement_currency := p_agreement_currency;
284 end if;
285
286 if p_agreement_amount = FND_API.G_MISS_NUM then
287 l_agreement_amount := null;
288 else
289 l_agreement_amount := p_agreement_amount;
290 end if;
291
292 if p_agreement_org_id = FND_API.G_MISS_NUM then
293 l_agreement_org_id := null;
294 else
295 l_agreement_org_id := p_agreement_org_id;
296 end if;
297
298 if p_opp_value_currency_code = FND_API.G_MISS_CHAR then
299 l_opp_value_currency_code := null;
300 else
301 l_opp_value_currency_code := p_opp_value_currency_code;
302 end if;
303
304 --Priority code changes
305 if p_priority_code = FND_API.G_MISS_CHAR then
306 l_priority_code := null;
307 else
308 l_priority_code := p_priority_code;
309 end if;
310
311 If (p_project_value is not null and p_project_value < 0) then
312 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
313 p_msg_name =>'PA_BU_NEED_POS_NUM');
314 RAISE FND_API.G_EXC_ERROR;
315 end if;
316
317 --dbms_output.put_line('Before UPDATE PA_PROJECTS_ALL Status : ');
318
319 pa_project_core1.copy_project (
320 x_orig_project_id => p_orig_project_id
321 ,x_project_name => RTRIM(p_project_name)
322 ,x_project_number => RTRIM(l_project_number)
323 ,x_description => RTRIM(l_description)
324 ,x_project_type => NULL --project_type is always defaulted from template
325 ,x_project_status_code => p_project_status_code
326 ,x_distribution_rule => l_distribution_rule
327 ,x_public_sector_flag => l_public_sector_flag
328 ,x_organization_id => p_carrying_out_organization_id
329 ,x_start_date => p_start_date
330 ,x_completion_date => l_completion_date
331 ,x_probability_member_id => p_probability_member_id
332 ,x_project_value => l_project_value
333 ,x_expected_approval_date => l_expected_approval_date
334 ,x_copy_task_flag => 'Y'
335 ,x_copy_budget_flag => 'Y'
336 ,x_use_override_flag => 'Y'
337 ,x_copy_assignment_flag => 'N'
338 ,x_template_flag => p_template_flag
339 ,x_project_id => l_project_id
340 ,x_err_code => l_err_code
341 ,x_err_stage => l_err_stage
342 ,x_err_stack => l_err_stack
343 ,x_new_project_number => l_project_number_out
344 ,x_team_template_id => l_team_template_id
345 ,x_country_code => l_country_code
346 ,x_region => l_region
347 ,x_city => l_city
348 ,x_opp_value_currency_code => l_opp_value_currency_code
349 ,x_agreement_currency => l_agreement_currency
350 ,x_agreement_amount => l_agreement_amount
351 ,x_agreement_org_id => l_agreement_org_id
352 ,x_org_project_copy_flag => 'N'
353 ,x_priority_code => l_priority_code
354 ,x_security_level => p_security_level
355 -- anlee
356 -- Project Long Name changes
357 ,x_long_name => p_long_name
358 -- End of changes
359 --maansari for bug 2783257
360 ,x_customer_id => p_customer_id
361 --End of changes.
362 );
363
364 if (p_debug_mode = 'Y') then
365 pa_debug.debug('Create_Project PVT: Checking error messages returned from copy project');
366 end if;
367 IF l_err_code > 0
368 THEN
369 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
370 THEN
371 /* IF NOT pa_project_pvt.check_valid_message(l_err_stage)
372 THEN
373 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
374 p_msg_name => 'PA_PROJ_COPY_PROJECT_FAILED');
375
376 x_msg_data := 'PA_PROJ_COPY_PROJECT_FAILED';
377 ELSE*/
378 /* Bug2450468 Begin - Commenting the following code and adding the code*/
379 /* IF l_err_stage IS NOT NULL
380 THEN
381 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
382 p_msg_name => l_err_stage);
383 END IF;
384 */
385
386 IF l_err_stage = 'PA_INVALID_PT_CLASS_ORG' THEN
387 OPEN l_get_prj_class_code;
388 FETCH l_get_prj_class_code INTO l_project_type_class_code;
389 CLOSE l_get_prj_class_code;
390
391 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
392 p_msg_name => 'PA_INVALID_PT_CLASS_ORG',
393 p_token1 => 'PT_CLASS',
394 p_value1 => l_project_type_class_code);
395 ELSE
396 IF l_err_stage IS NOT NULL THEN
397 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
398 p_msg_name => l_err_stage);
399 END IF;
400
401 END IF; -- l_err_stage = 'PA_INVALID_PT_CLASS_ORG'
402 /* Bug2450468 End */
403
404 x_msg_data := l_err_stage;
405 -- END IF;
406
407 END IF;
408
409 x_return_status := FND_API.G_RET_STS_ERROR;
410
411 ELSIF l_err_code < 0
412 THEN
413 /* IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
414 THEN
415 PA_UTILS.ADD_MESSAGE
416 (p_app_short_name => 'PA',
417 p_msg_name => 'PA_PROJ_COPY_PROJECT_FAILED');
418 x_msg_data := 'PA_PROJ_COPY_PROJECT_FAILED';
419 END IF;*/
420 IF l_err_stage IS NOT NULL
421 THEN
422 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
423 p_msg_name => l_err_stage);
424 END IF;
425 x_msg_data := l_err_stage;
426 x_return_status := 'E';
427 END IF;
428
429 p_project_id := l_project_id;
430 p_new_project_number := l_project_number_out;
431
432 end if; -- p_validate_only = 'Y'
433
434 l_msg_count := FND_MSG_PUB.count_msg;
435
436 --dbms_output.put_line('MSG_COUNT : CREATE_PROJECT ERROR : '||to_char(l_msg_count));
437
438 IF l_msg_count > 0 THEN
439 x_return_status := 'E';
440 x_msg_count := l_msg_count;
441 RAISE FND_API.G_EXC_ERROR;
442 END IF;
443
444 --dbms_output.put_line('*** Before create_customer call *** '||to_char(p_customer_id));
445
446 -- added below code for bug 5724556
447 OPEN l_get_project_dates_csr(l_project_id);
448 FETCH l_get_project_dates_csr INTO l_proj_start_date, l_proj_completion_date, l_top_task_cust_flag;
449 CLOSE l_get_project_dates_csr;
450
451 if nvl(l_top_task_cust_flag,'N') = 'Y' then
452 l_calling_context := 'CREATE_PROJ_TT_CUST';
453 else
454 l_calling_context := 'CREATE_PROJ_NO_TT_CUST';
455 end if;
456
457 IF p_customer_id is not null
458 THEN
459
460 --dbms_output.put_line('*** Before create_customer call ... '||to_char(l_project_id));
461
462 OPEN l_override_fields_csr; --(p_project_id)Bug 5478390: p_project_id no longer required.
463 FETCH l_override_fields_csr INTO l_relationship_type;
464 CLOSE l_override_fields_csr;
465
466 --dbms_output.put_line('*** Relationship ... '||l_relationship_type);
467 -- l_relationship_type := 'Primary';
468
469 if (p_debug_mode = 'Y') then
470 pa_debug.debug('Create_Project PVT: Calling create customer private API');
471 end if;
472 PA_PROJECTS_MAINT_PVT.CREATE_CUSTOMER
473 ( p_commit => FND_API.G_FALSE ,
474 p_validate_only => p_validate_only ,
475 p_validation_level => p_validation_level ,
476 p_debug_mode => p_debug_mode ,
477 p_max_msg_count => FND_API.G_MISS_NUM ,
478 p_calling_module => l_calling_context , -- bug 5724556
479 p_project_id => l_project_id ,
480 p_customer_id => p_customer_id ,
481 p_relationship_type => l_relationship_type,
482 --Customer Account relationship
483 p_bill_to_customer_id => p_bill_to_customer_id,
484 p_ship_to_customer_id => p_ship_to_customer_id,
485 --Customer Account relationship
486 x_return_status => l_return_status ,
487 x_msg_count => l_msg_count ,
488 x_msg_data => l_msg_data) ;
489
490 --dbms_output.put_line('IN create_customer call ... '||l_return_status);
491
492 END IF;
493
494 l_msg_count := FND_MSG_PUB.count_msg;
495
496 --dbms_output.put_line('After create_customer call ... '||to_char(l_msg_count));
497
498 IF l_msg_count > 0 THEN
499 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
500 x_msg_count := l_msg_count;
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503
504 -- anlee
505 -- Copy rentention
506 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
507 if (p_debug_mode = 'Y') then
508 pa_debug.debug('Create_Project PVT: Calling copy retention API');
509 end if;
510
511 /* Moved this code to before call to create customer to get enable top task customer flag
512 bug 5690529
513 OPEN l_get_project_dates_csr(l_project_id);
514 FETCH l_get_project_dates_csr INTO l_proj_start_date, l_proj_completion_date;
515 CLOSE l_get_project_dates_csr;
516 */
517 pa_retention_util.copy_retention_setup
518 ( p_fr_project_id => p_orig_project_id
519 ,p_to_project_id => l_project_id
520 ,p_fr_date => l_proj_start_date
521 ,p_to_date => l_proj_completion_date
522 ,x_return_status => l_return_status
523 ,x_msg_count => l_msg_count
524 ,x_msg_data => l_msg_data );
525
526 l_msg_count := FND_MSG_PUB.count_msg;
527
528 IF l_msg_count > 0 THEN
529 x_msg_count := l_msg_count;
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532
533 end if;
534
535 IF FND_API.TO_BOOLEAN(P_COMMIT)
536 THEN
537 COMMIT WORK;
538 END IF;
539
540 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
541 IF p_commit = FND_API.G_TRUE THEN
542 ROLLBACK TO prm_create_project;
543 END IF;
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
546 p_procedure_name => 'CREATE_PROJECT',
547 p_error_text => SUBSTRB(SQLERRM,1,240));
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549
550 WHEN FND_API.G_EXC_ERROR THEN
551 IF p_commit = FND_API.G_TRUE THEN
552 ROLLBACK TO prm_create_project;
553 END IF;
554 x_return_status := 'E';
555
556 WHEN OTHERS THEN
557 IF p_commit = FND_API.G_TRUE THEN
558 ROLLBACK TO prm_create_project;
559 END IF;
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
562 p_procedure_name => 'CREATE_PROJECT',
563 p_error_text => SUBSTRB(SQLERRM,1,240));
564 raise;
565
566 END CREATE_PROJECT;
567
568 -- API name : create_customer
569 -- Type : Public
570 -- Pre-reqs : None.
571 -- Parameters :
572 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
573 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
574 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
575 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
576 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
577 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
578 -- p_project_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
579 -- p_customer_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
580 -- p_relationship_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
581 -- x_return_status OUT VARCHAR2 REQUIRED
582 -- x_msg_count OUT NUMBER REQUIRED
583 -- x_msg_data OUT VARCHAR2 REQUIRED
584 --
585 -- History
586 --
587 -- 18-AUG-2000 -- Sakthi/William - Created.
588 --
589 --
590 PROCEDURE CREATE_CUSTOMER
591 (
592 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
593 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
594 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
595 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
596 p_debug_mode IN VARCHAR2 := 'N' ,
597 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
598 p_project_id IN NUMBER := FND_API.G_MISS_NUM ,
599 p_customer_id IN NUMBER := FND_API.G_MISS_NUM ,
600 p_relationship_type IN VARCHAR2 := FND_API.G_MISS_CHAR ,
601 --Customer Account Relationship
602 p_bill_to_customer_id IN NUMBER := NULL ,
603 p_ship_to_customer_id IN NUMBER := NULL ,
604 --Customer Account Relationship
605 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
606 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
607 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
608 IS
609
610 CURSOR l_project_csr (c_project_id NUMBER) -- Bug 6166197: cursor modified to fetch project_type_class_code also
611 Is
612 Select project_currency_code, project_type_class_code
613 From pa_projects_all pa, pa_project_types_all pt
614 Where pa.project_id = c_project_id
615 And pa.project_type = pt.project_type
616 And nvl(pa.org_id,-99) = nvl(pt.org_id,-99);
617
618 /*Commented since not being used anymore: Bug 5478390
619 CURSOR l_override_fields_csr (c_project_id NUMBER)
620 IS
621 SELECT TYPE
622 FROM pa_override_fields_v pof
623 WHERE pof.pa_field_name = 'CUSTOMER_NAME'
624 AND EXISTS(SELECT 'x' from pa_projects_all PP
625 WHERE PP.project_id = c_project_id
626 AND PP.created_from_project_id =
627 POF.pa_source_template_id);*/
628
629 CURSOR cur_pa_impl IS
630 SELECT default_rate_type
631 FROM pa_implementations;
632
633
634
635 -- 4363092 TCA changes, replaced RA views with HZ tables
636 --l_customer_id ra_customers.customer_id%TYPE;
637 l_customer_id hz_cust_accounts.cust_account_id%TYPE;
638 -- 4363092 end
639 l_return_status VARCHAR2(1);
640 l_error_msg_code VARCHAR2(250);
641 l_bill_to_address_id NUMBER := NULL; --Initialized to NULL for Bug 3911782
642 l_ship_to_address_id NUMBER := NULL; --Initialized to NULL for Bug 3911782
643 l_bill_to_contact_id NUMBER := NULL; --Initialized to NULL for Bug 4764943 - R12 Bug 4764449
644 l_ship_to_contact_id NUMBER := NULL; --Initialized to NULL for Bug 4764943 - R12 Bug 4764449
645 l_err_code NUMBER;
646 l_err_stage VARCHAR2(250);
647 l_err_stack VARCHAR2(250);
648 l_msg_count NUMBER;
649 l_msg_data VARCHAR2(250);
650 l_msg_index_out NUMBER;
651 --customer
652 l_inv_currency_code VARCHAR2(15);
653 l_inv_rate_type VARCHAR2(30);
654 l_inv_rate_date DATE;
655 l_inv_exchange_rate NUMBER;
656 l_allow_inv_rate_type_fg VARCHAR2(1);
657 l_bill_another_prj_flag VARCHAR2(1);
658 l_receiver_task_id NUMBER;
659 -- override
660 l_relationship_type VARCHAR2(30);
661 l_data VARCHAR2(250);
662 l_party_id NUMBER;
663
664 --Customer account relationship changes
665
666 l_bill_to_customer_id Number;
667 l_ship_to_customer_id Number;
668
669 --customer Account Relationship changes
670
671 l_proj_type_class_code pa_project_types_all.project_type_class_code%TYPE; -- Bug 6166197
672
673 -- anlee org role changes
674 l_project_party_id NUMBER;
675 l_resource_id NUMBER;
676 l_wf_item_type VARCHAR2(30);
677 l_wf_type VARCHAR2(30);
678 l_wf_party_process VARCHAR2(30);
679 l_assignment_id NUMBER;
680 l_end_date_active DATE;
681
682 CURSOR l_check_org_csr IS
683 SELECT PARTY_ID
684 FROM PA_CUSTOMERS_V
685 WHERE CUSTOMER_ID = p_customer_id
686 AND PARTY_TYPE = 'ORGANIZATION';
687
688
689 --sunkalya:federal Bug#5511353
690
691 l_date_eff_funds_flag VARCHAR2(1);
692 customer_bill_split NUMBER;
693 CURSOR get_date_eff_funds_flag( c_project_id IN NUMBER )
694 IS
695 SELECT
696 nvl(DATE_EFF_FUNDS_CONSUMPTION,'N')
697 FROM
698 pa_projects_all
699 WHERE project_id = c_project_id ;
700 --sunkalya:federal Bug#5511353
701
702 -- bug 5724556
703 l_default_top_task_customer varchar2(1) := 'N';
704 l_contribution number;
705
706 BEGIN
707
708 --dbms_output.put_line('INSIDE PROCEDURE create_customer call ... ');
709
710 IF p_commit = FND_API.G_TRUE
711 THEN
712 SAVEPOINT create_customer;
713 END IF;
714
715 x_return_status := 'S';
716 -- Customer Account Relationship
717 l_Bill_To_Customer_id := p_bill_To_Customer_id;
718 l_Ship_To_Customer_id := p_Ship_To_Customer_id;
719 -- Customer Account Relationship
720
721
722 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
723
724 --dbms_output.put_line('Before get customer info call ... ');
725
726 -- Please check only if p_calling_module 'QUECK_ENTRY' ...
727
728 -- Bug 6166197: changes start
729 -- l_project_csr moved up here to fetch project-type-class-code before calling pa_customer_info.get_customer_info
730 OPEN l_project_csr (p_project_id);
731 FETCH l_project_csr INTO l_inv_currency_code, l_proj_type_class_code;
732 CLOSE l_project_csr;
733 -- Bug 6166197: changes end
734
735 -- Bug 6166197: if condition to check for project-type-class-code introduced
736 -- bypass call to pa_customer_info.get_customer_info for non-contract type projects
737
738 if (nvl(l_proj_type_class_code,'NONE') = 'CONTRACT') then
739
740 if (p_debug_mode = 'Y') then
741 pa_debug.debug('Create_customer PVT: Calling get customer info API');
742 end if;
743 pa_customer_info.get_customer_info
744 ( x_customer_id => p_customer_id
745 -- Customer Account Relationship
746 ,x_Bill_To_Customer_Id => l_bill_to_customer_id
747 ,x_Ship_To_Customer_Id => l_ship_to_customer_id
748 -- Customer Account Relationship
749 ,x_bill_to_address_id =>l_bill_to_address_id
750 ,x_ship_to_address_id =>l_ship_to_address_id
751 ,x_bill_to_contact_id =>l_bill_to_contact_id
752 ,x_ship_to_contact_id =>l_ship_to_contact_id
753 ,x_err_code =>l_err_code
754 ,x_err_stage =>l_err_stage
755 ,x_err_stack =>l_err_stack );
756
757 -- For this API,error code 10 is a warning.Anything above 10 is an error
758
759 if (p_debug_mode = 'Y') then
760 pa_debug.debug('Create_customer PVT: Checking Erorr messages returned from get_customer_info API');
761 end if;
762 IF l_err_code > 10
763 THEN
764
765 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
766 THEN
767 IF NOT pa_project_pvt.check_valid_message(l_err_stage)
768 THEN
769 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
770 p_msg_name =>
771 'PA_GET_CUSTOMER_INFO_FAILED');
772 x_msg_data := 'PA_GET_CUSTOMER_INFO_FAILED';
773 ELSE
774 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
775 p_msg_name => l_err_stage);
776 x_msg_data := l_err_stage;
777 END IF;
778 END IF;
779
780 x_return_status := 'E';
781
782 ELSIF l_err_code < 0
783 THEN
784 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
785 THEN
786 PA_UTILS.ADD_MESSAGE
787 (p_app_short_name => 'PA',
788 p_msg_name => 'PA_PROJ_GET_CUST_INFO_FAILED');
789 x_msg_data := 'PA_PROJ_GET_CUST_INFO_FAILED';
790 END IF;
791 x_return_status := 'E';
792 END IF;
793 end if; -- Bug 6166197
794
795 --dbms_output.put_line('Before create_customer contacts ... '||to_char(p_project_id));
796
797 -- Bug 6166197: changes start
798 -- open/fetch/close of l_project_csr moved up before the call to pa_customer_info.get_customer_info
799 -- to fetch project-type-class-code
800 --OPEN l_project_csr (p_project_id);
801 --FETCH l_project_csr INTO l_inv_currency_code;
802 --CLOSE l_project_csr;
803 -- Bug 6166197: changes end
804
805 --dbms_output.put_line('Before create_customer contacts ... '||l_inv_currency_code);
806 --dbms_output.put_line('Before create_customer contacts ... '||l_relationship_type);
807
808 -- Bug 6166197: changes start
809 -- If bill-to or ship-to customer id is null, set them to customer-id
810 If l_bill_to_customer_id is null then
811 l_bill_to_customer_id := p_customer_id;
812 end if;
813
814 If l_ship_to_customer_id is null then
815 l_ship_to_customer_id := p_customer_id;
816 end if;
817 -- Bug 6166197: changes end
818
819 if (p_debug_mode = 'Y') then
820 pa_debug.debug('Create_customer PVT: Calling create_customer_contacts API');
821 end if;
822 OPEN cur_pa_impl;
823 FETCH cur_pa_impl INTO l_inv_rate_type;
824 CLOSE cur_pa_impl;
825
826 -- added below code for bug 5724556
827 if p_calling_module = 'CREATE_PROJ_TT_CUST' then
828 l_default_top_task_customer := 'Y';
829 l_contribution := null;
830 elsif p_calling_module = 'CREATE_PROJ_NO_TT_CUST' then
831 l_default_top_task_customer := 'N';
832 l_contribution :=100;
833 end if;
834
835 --sunkalya:federal Bug#5511353
836
837 OPEN get_date_eff_funds_flag(p_project_id);
838 FETCH get_date_eff_funds_flag INTO l_date_eff_funds_flag;
839 CLOSE get_date_eff_funds_flag;
840
841 IF l_date_eff_funds_flag ='Y' THEN
842 customer_bill_split := null;
843 ELSE
844 customer_bill_split := l_contribution; -- changed for Bug 5724556
845 END IF;
846
847 --sunkalya:federal Bug#5511353
848
849 pa_customer_info.create_customer_contacts
850 ( x_project_id => p_project_id
851 ,x_customer_id => p_customer_id
852 ,X_Project_Relation_Code => p_relationship_type
853 ,X_Customer_Bill_Split =>customer_bill_split --sunkalya:federal Bug#5511353
854 -- Customer Account Relationship
855 ,X_Bill_To_Customer_Id => l_bill_to_customer_id
856 ,X_Ship_To_Customer_Id => l_ship_to_customer_id
857 -- Customer Account Relationship
858 ,X_Bill_To_Address_Id => l_bill_to_address_id
859 ,X_Ship_To_Address_Id => l_ship_to_address_id
860 ,X_Bill_To_Contact_Id => l_bill_to_contact_id
861 ,X_Ship_To_Contact_Id => l_ship_to_contact_id
862 ,X_Inv_Currency_Code => l_inv_currency_code
863 ,X_Inv_Rate_Type => l_inv_rate_type
864 ,X_Inv_Rate_Date => NULL
865 ,X_Inv_Exchange_Rate => NULL
866 ,X_Allow_Inv_Rate_Type_Fg => 'N'
867 ,X_Bill_Another_Project_Fg => 'N'
868 ,X_Receiver_Task_Id => NULL
869 ,P_default_top_task_customer=>l_default_top_task_customer --bug 5724556
870 ,X_User => fnd_global.user_id
871 ,X_Login => fnd_global.login_id
872 ,X_Err_Code => l_err_code
873 ,X_Err_Stage => l_err_stage
874 ,X_Err_Stack => l_err_stack );
875
876 --dbms_output.put_line('ERROR CODE : CREATE_CUSTOMER : '||to_char(l_err_code));
877
878 if (p_debug_mode = 'Y') then
879 pa_debug.debug('Create_customer PVT: Checking errors returned from create_customer_contacts API');
880 end if;
881 IF l_err_code > 0
882 THEN
883 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
884 THEN
885 IF NOT pa_project_pvt.check_valid_message(l_err_stage)
886 THEN
887 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
888 p_msg_name => 'PA_PROJ_CR_CONTACTS_FAILED');
889 x_msg_data := 'PA_PROJ_CR_CONTACTS_FAILED';
890 ELSE
891 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
892 p_msg_name => l_err_stage);
893 x_msg_data := l_err_stage ;
894 END IF;
895
896 END IF;
897
898 x_return_status := FND_API.G_RET_STS_ERROR;
899
900 ELSIF l_err_code < 0
901 THEN
902 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
903 p_msg_name => 'PA_PROJ_CR_CONTACTS_FAILED');
904 x_msg_data := 'PA_PROJ_CR_CONTACTS_FAILED';
905 x_return_status := 'E';
906 END IF;
907
908 --dbms_output.put_line('RETURN STATUS : CREATE_CUSTOMER ERROR : '||x_return_status);
909
910 l_msg_count := FND_MSG_PUB.count_msg;
911
912 --dbms_output.put_line('MSG_COUNT : CREATE_CUSTOMER ERROR : '||to_char(l_msg_count));
913
914 IF l_msg_count > 0 THEN
915 x_msg_count := l_msg_count;
916 x_return_status := 'E';
917 RAISE FND_API.G_EXC_ERROR;
918 END IF;
919
920 -- anlee org role changes
921 -- create a project party if the added customer is an organization
922 l_party_id := null;
923 l_project_party_id := null;
924 OPEN l_check_org_csr;
925 FETCH l_check_org_csr INTO l_party_id;
926 IF l_check_org_csr%NOTFOUND then
927 l_party_id := null;
928 END IF;
929 CLOSE l_check_org_csr;
930
931 if l_party_id is not null then
932
933 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
934 p_validate_only => FND_API.G_FALSE
935 , p_object_id => p_project_id
936 , p_OBJECT_TYPE => 'PA_PROJECTS'
937 , p_project_role_id => 100
938 , p_project_role_type => 'CUSTOMER_ORG'
939 , p_RESOURCE_TYPE_ID => 112
940 , p_resource_source_id => l_party_id
941 , p_start_date_active => null
942 , p_calling_module => 'FORM'
943 , p_project_id => p_project_id
944 , p_project_end_date => null
945 , p_end_date_active => l_end_date_active
946 , x_project_party_id => l_project_party_id
947 , x_resource_id => l_resource_id
948 , x_wf_item_type => l_wf_item_type
949 , x_wf_type => l_wf_type
950 , x_wf_process => l_wf_party_process
951 , x_assignment_id => l_assignment_id
952 , x_return_status => l_return_status
953 , x_msg_count => l_msg_count
954 , x_msg_data => l_msg_data );
955
956 l_msg_count := FND_MSG_PUB.count_msg;
957 IF l_msg_count > 0 THEN
958 x_msg_count := l_msg_count;
959 x_return_status := 'E';
960 RAISE FND_API.G_EXC_ERROR;
961 END IF;
962
963 -- Add the new project party ID to the customers row
964 UPDATE PA_PROJECT_CUSTOMERS
965 SET project_party_id = l_project_party_id
966 WHERE project_id = p_project_id
967 AND customer_id = p_customer_id;
968 end if;
969
970 end if;
971
972
973 IF FND_API.TO_BOOLEAN(P_COMMIT)
974 THEN
975 COMMIT WORK;
976 END IF;
977 --dbms_output.put_line('SUCCESSFULLY CREATED CUSTOMER ... ');
978
979 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
980 IF p_commit = FND_API.G_TRUE THEN
981 ROLLBACK TO prm_create_customer;
982 END IF;
983 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
984 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
985 p_procedure_name => 'CREATE_CUSTOMER',
986 p_error_text => SUBSTRB(SQLERRM,1,240));
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988
989 WHEN FND_API.G_EXC_ERROR THEN
990 IF p_commit = FND_API.G_TRUE THEN
991 ROLLBACK TO prm_create_customer;
992 END IF;
993 x_return_status := 'E';
994
995 WHEN OTHERS THEN
996 IF p_commit = FND_API.G_TRUE THEN
997 ROLLBACK TO prm_create_customer;
998 END IF;
999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1000 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
1001 p_procedure_name => 'CREATE_CUSTOMER',
1002 p_error_text => SUBSTRB(SQLERRM,1,240));
1003 raise;
1004
1005 END CREATE_CUSTOMER;
1006
1007 -- API name : Update_project_basic_info
1008 -- Type : Public
1009 -- Pre-reqs : None.
1010 -- Parameters :
1011 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1012 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
1013 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1014 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1015 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1016 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1017 -- p_project_id IN NUMBER Required
1018 -- p_project_name IN VARCHAR2 Required
1019 -- p_project_number IN VARCHAR2 Required
1020 -- p_project_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1021 -- p_description IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1022 -- p_project_status_code IN VARCHAR2 Required
1023 -- p_public_sector_flag IN VARCHAR2 Required
1024 -- p_carrying_out_organization_id IN NUMBER Optional
1025 -- Default = FND_API.G_MISS_NUM
1026 -- p_start_date IN DATE Required
1027 -- p_completion_date IN DATE Optional Default = FND_API.G_MISS_DATE
1028 -- p_territory_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1029 -- p_country IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1030 -- p_location_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1031 -- p_state_region IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1032 -- p_city IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1033 -- p_attribute_category IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1034 -- p_attribute1 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1035 -- p_attribute2 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1036 -- p_attribute3 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1037 -- p_attribute4 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1038 -- p_attribute5 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1039 -- p_attribute6 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1040 -- p_attribute7 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1041 -- p_attribute8 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1042 -- p_attribute9 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1043 -- p_attribute10 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1044 -- p_record_version_number IN NUMBER Required
1045 -- p_recalculate_flag IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1046 -- p_target_start_date IN DATE Optional Default = FND_API.G_MISS_DATE
1047 -- p_target_finish_dateIN DATE Optional Default = FND_API.G_MISS_DATE
1048 -- p_security_level IN NUMBER := FND_API.G_MISS_NUM ,
1049 -- p_long_name IN VARCHAR2 Optional Default = NULL
1050 -- x_return_status OUT VARCHAR2 Required
1051 -- x_msg_count OUT NUMBER Required
1052 -- x_msg_data OUT VARCHAR2 Required
1053 --
1054 -- History
1055 --
1056 -- 18-AUG-2000 -- Sakthi/William - Created.
1057 --
1058 --
1059 PROCEDURE UPDATE_PROJECT_BASIC_INFO
1060 (
1061 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1062 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1063 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1064 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
1065 p_debug_mode IN VARCHAR2 := 'N' ,
1066 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
1067 p_project_id IN NUMBER ,
1068 p_project_name IN VARCHAR2 ,
1069 p_project_number IN VARCHAR2 ,
1070 p_project_type IN VARCHAR2 ,
1071 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1072 p_project_status_code IN VARCHAR2 ,
1073 p_public_sector_flag IN VARCHAR2 ,
1074 p_carrying_out_organization_id IN NUMBER ,
1075 p_start_date IN DATE ,
1076 p_completion_date IN DATE := FND_API.G_MISS_DATE ,
1077 p_territory_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1078 p_country IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1079 p_location_id IN NUMBER := FND_API.G_MISS_NUM ,
1080 p_state_region IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1081 p_city IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1082 p_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1083 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1084 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1085 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1086 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1087 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1088 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1089 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1090 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1091 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1092 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1093 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1094 p_record_version_number IN NUMBER ,
1095 p_recalculate_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1096 -- anlee
1097 -- Dates changes
1098 p_target_start_date IN DATE := FND_API.G_MISS_DATE ,
1099 p_target_finish_date IN DATE := FND_API.G_MISS_DATE ,
1100 -- End of changes
1101 p_security_level IN NUMBER := FND_API.G_MISS_NUM ,
1102 -- anlee
1103 -- Project Long Name changes
1104 p_long_name IN VARCHAR2 DEFAULT NULL ,
1105 -- end of changes
1106 p_funding_approval_status IN VARCHAR2 DEFAULT NULL , -- added for 4055319
1107 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1108 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1109 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1110 IS
1111
1112 l_return_status VARCHAR2(1);
1113 l_dummy VARCHAR2(1);
1114 l_error_msg_code VARCHAR2(250);
1115 l_msg_count NUMBER;
1116 l_msg_data VARCHAR2(250);
1117 l_err_code VARCHAR2(250);
1118 l_err_stage VARCHAR2(250);
1119 l_err_stack VARCHAR2(250);
1120 l_data VARCHAR2(250);
1121 l_msg_index_out NUMBER;
1122 l_calling_module VARCHAR2(100); -- bug7204572
1123
1124 l_project_name PA_PROJECTS_ALL.NAME%TYPE;
1125 l_project_number PA_PROJECTS_ALL.SEGMENT1%TYPE;
1126 l_project_type PA_PROJECTS_ALL.PROJECT_TYPE%TYPE;
1127 l_description PA_PROJECTS_ALL.DESCRIPTION%TYPE;
1128 l_project_status_code PA_PROJECTS_ALL.PROJECT_STATUS_CODE%TYPE;
1129 l_public_sector_flag PA_PROJECTS_ALL.PUBLIC_SECTOR_FLAG%TYPE;
1130 l_carrying_out_organization_id PA_PROJECTS_ALL.CARRYING_OUT_ORGANIZATION_ID%TYPE;
1131 l_start_date PA_PROJECTS_ALL.START_DATE%TYPE;
1132 l_completion_date PA_PROJECTS_ALL.COMPLETION_DATE%TYPE;
1133 l_record_version_number PA_PROJECTS_ALL.record_Version_number%TYPE;
1134 l_attribute_category PA_PROJECTS_ALL.attribute_category%TYPE;
1135 l_attribute1 PA_PROJECTS_ALL.attribute1%TYPE;
1136 l_attribute2 PA_PROJECTS_ALL.attribute2%TYPE;
1137 l_attribute3 PA_PROJECTS_ALL.attribute3%TYPE;
1138 l_attribute4 PA_PROJECTS_ALL.attribute4%TYPE;
1139 l_attribute5 PA_PROJECTS_ALL.attribute5%TYPE;
1140 l_attribute6 PA_PROJECTS_ALL.attribute6%TYPE;
1141 l_attribute7 PA_PROJECTS_ALL.attribute7%TYPE;
1142 l_attribute8 PA_PROJECTS_ALL.attribute8%TYPE;
1143 l_attribute9 PA_PROJECTS_ALL.attribute9%TYPE;
1144 l_attribute10 PA_PROJECTS_ALL.attribute10%TYPE;
1145 l_priority_code PA_PROJECTS_ALL.priority_code%TYPE;
1146 -- anlee
1147 -- Dates changes
1148 l_target_start_date PA_PROJECTS_ALL.TARGET_START_DATE%TYPE;
1149 l_target_finish_date PA_PROJECTS_ALL.TARGET_FINISH_DATE%TYPE;
1150 -- End of changes
1151
1152 l_mass_adj_outcome VARCHAR2(100);
1153 l_batch_id NUMBER(25);
1154 l_row_id VARCHAR2(25);
1155 l_line_id NUMBER;
1156 l_dummy1 NUMBER;
1157 l_dummy2 NUMBER;
1158 l_batch_name VARCHAR2(100);
1159 l_batch_description VARCHAR2(100);
1160 l_project_rec pa_projects%ROWTYPE;
1161 l_old_organization_id NUMBER;
1162 cursor get_batch_name_csr
1163 IS
1164 SELECT meaning
1165 FROM pa_lookups
1166 WHERE lookup_type = 'TRANSLATION'
1167 AND lookup_code = 'MANUAL';
1168
1169 cursor get_batch_desc_csr
1170 IS
1171 SELECT meaning
1172 FROM pa_lookups
1173 WHERE lookup_type = 'TRANSLATION'
1174 AND lookup_code = 'MASS_UPDATE_BATCH_DESC';
1175
1176 CURSOR l_project_details_csr
1177 IS
1178 SELECT *
1179 FROM pa_projects p
1180 WHERE p.project_id = p_project_id;
1181
1182 CURSOR l_get_organization_csr
1183 IS
1184 SELECT carrying_out_organization_id
1185 FROM pa_projects_all
1186 WHERE project_id = p_project_id;
1187
1188 -- anlee
1189 -- Ext Attribute changes
1190 -- Bug 2904327
1191 CURSOR l_get_project_type_id
1192 IS
1193 SELECT ppt.project_type_id
1194 FROM pa_projects_all ppa, pa_project_types ppt
1195 WHERE ppa.project_id = p_project_id
1196 AND ppa.project_type = ppt.project_type;
1197
1198 l_old_project_type_id NUMBER;
1199 l_new_project_type_id NUMBER;
1200 -- anlee end of changes
1201
1202 -- 4199336 commented below code because of pjp dependancy issue
1203 -- 4055319 Added for pjp api call
1204 CURSOR c_template_flag
1205 IS
1206 SELECT template_flag
1207 FROM pa_projects_all
1208 WHERE project_id = p_project_id;
1209
1210 l_template_flag VARCHAR2(1);
1211 -- 4055319 end
1212
1213
1214 BEGIN
1215
1216 IF p_commit = FND_API.G_TRUE
1217 THEN
1218 SAVEPOINT update_project_basic_info;
1219 END IF;
1220
1221
1222 --dbms_output.put_line('Starts here UPDATE_PROJECT_BASIC_INFO ... ');
1223
1224 x_return_status := 'S';
1225
1226 if (p_debug_mode = 'Y') then
1227 pa_debug.debug('UPDATE_PROJECT_BASIC_INFO: Calling validate_project_details.');
1228 end if;
1229
1230 -- Checking Locking Procedures.
1231
1232 if (p_debug_mode = 'Y') then
1233 pa_debug.debug('Update_project_basic_info PVT: Locking record');
1234 end if;
1235 if p_validate_only <> FND_API.G_TRUE then
1236 BEGIN
1237 SELECT 'x' INTO l_dummy
1238 FROM pa_projects
1239 WHERE project_id = p_project_id
1240 AND record_version_number = p_record_version_number
1241 FOR UPDATE OF record_version_number NOWAIT;
1242 EXCEPTION WHEN TIMEOUT_ON_RESOURCE THEN
1243 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1244 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1245 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1246 x_return_status := 'E' ;
1247 WHEN NO_DATA_FOUND THEN
1248 if p_calling_module = 'FORM' then
1249 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1250 p_msg_name => 'FORM_RECORD_CHANGED');
1251 x_msg_data := 'FORM_RECORD_CHANGED';
1252 else
1253 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1254 p_msg_name => 'PA_XC_RECORD_CHANGED');
1255 x_msg_data := 'PA_XC_RECORD_CHANGED';
1256 end if;
1257 x_return_status := 'E' ;
1258 WHEN OTHERS THEN
1259 IF SQLCODE = -54 THEN
1260 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1261 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1262 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1263 x_return_status := 'E' ;
1264 ELSE
1265 raise;
1266 END IF;
1267 END;
1268 else
1269 BEGIN
1270 SELECT 'x' INTO l_dummy
1271 FROM pa_projects
1272 WHERE project_id = p_project_id
1273 AND record_version_number = p_record_version_number;
1274 EXCEPTION WHEN NO_DATA_FOUND THEN
1275 if p_calling_module = 'FORM' then
1276 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1277 p_msg_name => 'FORM_RECORD_CHANGED');
1278 x_msg_data := 'FORM_RECORD_CHANGED';
1279 else
1280 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1281 p_msg_name => 'PA_XC_RECORD_CHANGED');
1282 x_msg_data := 'PA_XC_RECORD_CHANGED';
1283 end if;
1284 x_return_status := 'E' ;
1285 WHEN OTHERS THEN
1286 IF SQLCODE = -54 THEN
1287 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1288 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1289 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1290 x_return_status := 'E' ;
1291 END IF;
1292 END;
1293 end if;
1294 l_msg_count := FND_MSG_PUB.count_msg;
1295
1296 IF l_msg_count > 0 THEN
1297 x_msg_count := l_msg_count;
1298 x_return_status := 'E';
1299 RAISE FND_API.G_EXC_ERROR;
1300 END IF;
1301
1302 if p_validation_level > 0 then
1303 pa_debug.G_err_stage := 'Calling Validate_Basic_Info';
1304
1305 if (p_debug_mode = 'Y') then
1306 pa_debug.debug('Update_project_basic_info PVT: Calling validate project info API');
1307 end if;
1308
1309 --Code starts for bug 7204572
1310 l_calling_module := p_calling_module;
1311
1312 if (NVL(l_calling_module,'NO_CM') <> 'SETUP_PAGE') then
1313 l_calling_module := 'BASIC_INFO';
1314 end if;
1315 --Code ends for bug 7204572
1316
1317 PA_PROJECTS_MAINT_PVT.Validate_Project_Info
1318 (
1319 p_validation_level => p_validation_level,
1320 p_calling_module => l_calling_module, -- bug 7204572 changed 'BASIC_INFO' to l_calling_module
1321 p_debug_mode => p_debug_mode,
1322 p_action => 'UPDATE',
1323 p_max_msg_count => p_max_msg_count,
1324 p_project_id => p_project_id,
1325 p_project_name => p_project_name,
1326 p_project_number => p_project_number,
1327 p_project_type => p_project_type,
1328 p_description => p_description,
1329 p_project_status_code => p_project_status_code,
1330 p_public_sector_flag => p_public_sector_flag,
1331 p_carrying_out_organization_id => p_carrying_out_organization_id,
1332 p_start_date => p_start_date,
1333 p_completion_date => p_completion_date,
1334 p_territory_code => p_territory_code,
1335 p_country => p_country,
1336 p_location_id => p_location_id,
1337 p_state_region => p_state_region,
1338 p_city => p_city,
1339 p_record_version_number => p_record_version_number,
1340 -- anlee
1341 -- Dates changes
1342 p_target_start_date => p_target_start_date,
1343 p_target_finish_date => p_target_finish_date,
1344 -- End of changes
1345 -- anlee
1346 -- Project Long Name changes
1347 p_long_name => p_long_name );
1348 -- End of changes
1349
1350 l_msg_count := FND_MSG_PUB.count_msg;
1351
1352 IF l_msg_count > 0 THEN
1353 x_msg_count := l_msg_count;
1354 x_return_status := 'E';
1355 RAISE FND_API.G_EXC_ERROR;
1356 END IF;
1357 END IF;
1358
1359 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
1360 if p_project_name = FND_API.G_MISS_CHAR
1361 then
1362 l_project_name := null;
1363 else
1364 l_project_name := p_project_name;
1365 end if;
1366
1367 if p_project_number = FND_API.G_MISS_CHAR
1368 then
1369 l_project_number := null;
1370 else
1371 l_project_number := p_project_number;
1372 end if;
1373
1374 if p_project_type = FND_API.G_MISS_CHAR
1375 then
1376 l_project_type := null;
1377 else
1378 l_project_type := p_project_type;
1379 end if;
1380
1381 if p_description = FND_API.G_MISS_CHAR
1382 then
1383 l_description := null;
1384 else
1385 l_description := p_description;
1386 end if;
1387
1388 if p_priority_code = FND_API.G_MISS_CHAR
1389 then
1390 l_priority_code := null;
1391 else
1392 l_priority_code := p_priority_code;
1393 end if;
1394
1395 if p_project_Status_code = FND_API.G_MISS_CHAR
1396 then
1397 l_project_status_code := null;
1398 else
1399 l_project_status_code := p_project_status_code;
1400 end if;
1401
1402 if p_public_Sector_Flag = FND_API.G_MISS_CHAR
1403 then
1404 l_public_sector_flag := null;
1405 else
1406 l_public_sector_flag := p_public_Sector_flag;
1407 end if;
1408 --dbms_output.put_line('Before UPDATE PA_PROJECTS_ALL Status : ');
1409
1410 if p_carrying_out_organization_id = FND_API.G_MISS_NUM
1411 then
1412 l_carrying_out_organization_id := null;
1413 else
1414 l_carrying_out_organization_id := p_carrying_out_organization_id;
1415 end if;
1416
1417 if p_start_date = FND_API.G_MISS_DATE
1418 then
1419 l_start_date := null;
1420 else
1421 l_start_date := p_start_date;
1422 end if;
1423
1424 if p_completion_date = FND_API.G_MISS_DATE
1425 then
1426 l_completion_Date := null;
1427 else
1428 l_completion_date := p_completion_date;
1429 end if;
1430 /*
1431 if p_record_Version_number = FND_API.G_MISS_NUM
1432 then
1433 l_record_version_number := null;
1434 else
1435 l_record_version_number := p_carrying_out_organization_id;
1436 end if;
1437 */
1438
1439 if p_attribute_category = FND_API.G_MISS_CHAR
1440 then
1441 l_attribute_category := null;
1442 else
1443 l_attribute_category := p_attribute_category;
1444 end if;
1445
1446 if p_attribute1 = FND_API.G_MISS_CHAR
1447 then
1448 l_attribute1 := null;
1449 else
1450 l_attribute1 := p_attribute1;
1451 end if;
1452
1453 if p_attribute2 = FND_API.G_MISS_CHAR
1454 then
1455 l_attribute2 := null;
1456 else
1457 l_attribute2 := p_attribute2;
1458 end if;
1459
1460 if p_attribute3 = FND_API.G_MISS_CHAR
1461 then
1462 l_attribute3 := null;
1463 else
1464 l_attribute3 := p_attribute3;
1465 end if;
1466
1467 if p_attribute4 = FND_API.G_MISS_CHAR
1468 then
1469 l_attribute4 := null;
1470 else
1471 l_attribute4 := p_attribute4;
1472 end if;
1473
1474 if p_attribute5 = FND_API.G_MISS_CHAR
1475 then
1476 l_attribute5 := null;
1477 else
1478 l_attribute5 := p_attribute5;
1479 end if;
1480
1481 if p_attribute6 = FND_API.G_MISS_CHAR
1482 then
1483 l_attribute6 := null;
1484 else
1485 l_attribute6 := p_attribute6;
1486 end if;
1487
1488 if p_attribute7 = FND_API.G_MISS_CHAR
1489 then
1490 l_attribute7 := null;
1491 else
1492 l_attribute7 := p_attribute7;
1493 end if;
1494
1495 if p_attribute8 = FND_API.G_MISS_CHAR
1496 then
1497 l_attribute8 := null;
1498 else
1499 l_attribute8 := p_attribute8;
1500 end if;
1501
1502 if p_attribute9 = FND_API.G_MISS_CHAR
1503 then
1504 l_attribute9 := null;
1505 else
1506 l_attribute9 := p_attribute9;
1507 end if;
1508
1509 if p_attribute10 = FND_API.G_MISS_CHAR
1510 then
1511 l_attribute10 := null;
1512 else
1513 l_attribute10 := p_attribute10;
1514 end if;
1515
1516 -- anlee
1517 -- Dates changes
1518 if p_target_start_date = FND_API.G_MISS_DATE
1519 then
1520 l_target_start_date := null;
1521 else
1522 l_target_start_date := p_target_start_date;
1523 end if;
1524
1525 if p_target_finish_date = FND_API.G_MISS_DATE
1526 then
1527 l_target_finish_date := null;
1528 else
1529 l_target_finish_date := p_target_finish_date;
1530 end if;
1531 -- End of changes
1532
1533 OPEN l_get_organization_csr;
1534 FETCH l_get_organization_csr INTO l_old_organization_id;
1535 CLOSE l_get_organization_csr;
1536
1537 -- anlee
1538 -- Ext Attribute changes
1539 -- Bug 2904327
1540 OPEN l_get_project_type_id;
1541 FETCH l_get_project_type_id into l_old_project_type_id;
1542 CLOSE l_get_project_type_id;
1543 -- anlee end of changes
1544
1545 if (p_debug_mode = 'Y') then
1546 pa_debug.debug('Update_project_basic_info PVT: upating pa_projects table');
1547 end if;
1548
1549 UPDATE PA_PROJECTS
1550 SET name = l_project_name,
1551 segment1 = l_project_number,
1552 project_type = l_project_type,
1553 description = l_description,
1554 project_status_code = l_project_status_code,
1555 public_sector_flag = l_public_sector_flag,
1556 carrying_out_organization_id = l_carrying_out_organization_id,
1557 start_date = l_start_date,
1558 completion_date = l_completion_date,
1559 -- location_id = p_location_id,
1560 -- calendar_id = p_calendar_id,
1561 priority_code = l_priority_code,
1562 attribute_category = l_attribute_category,
1563 attribute1 = l_attribute1,
1564 attribute2 = l_attribute2,
1565 attribute3 = l_attribute3,
1566 attribute4 = l_attribute4,
1567 attribute5 = l_attribute5,
1568 attribute6 = l_attribute6,
1569 attribute7 = l_attribute7,
1570 attribute8 = l_attribute8,
1571 attribute9 = l_attribute9,
1572 attribute10 = l_attribute10,
1573 record_version_number = record_version_number + 1,
1574 -- anlee
1575 -- Dates changes
1576 last_update_date = sysdate,
1577 last_updated_by = fnd_global.user_id,
1578 last_update_login = fnd_global.login_id,
1579 target_start_date = l_target_start_date,
1580 target_finish_date = l_target_finish_date,
1581 -- End of changes
1582 -- anlee Project Long Name changes
1583 long_name = p_long_name,
1584 -- End of changes
1585 funding_approval_status_code = p_funding_approval_status, -- 4055319
1586 security_level = p_security_level
1587 WHERE project_id = p_project_id;
1588
1589
1590 -- 4199336 commented below because of pjp dependancy issue
1591
1592 -- 4055319 : When ever a project is created or updated in PJT, it may be considered for funding approval,
1593 -- i.e. Submitted to PJP. below API from PJP is called to achieve this.
1594
1595 BEGIN
1596 OPEN c_template_flag;
1597 FETCH c_template_flag INTO l_template_flag;
1598 CLOSE c_template_flag;
1599
1600 IF l_template_flag = 'N' THEN
1601 PA_PJP_PVT.Submit_Project_Aw -- Changed from FPA_PROCESS_PVT to PA_PJP_PVT package
1602 (
1603 p_api_version => 1.0
1604 ,p_init_msg_list => FND_API.G_FALSE
1605 ,p_commit => FND_API.G_FALSE
1606 ,p_project_id => p_project_id
1607 ,x_return_status => l_return_status
1608 ,x_msg_count => l_msg_count
1609 ,x_msg_data => l_msg_data
1610 );
1611 END IF;
1612
1613 EXCEPTION WHEN OTHERS THEN
1614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1615 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
1616 p_procedure_name => 'UPDATE_PROJECT_BASIC_INFO',
1617 p_error_text => SUBSTRB(SQLERRM,1,240));
1618 rollback to copy_project;
1619 return;
1620 END;
1621
1622 -- 4055319 end
1623
1624 -- 4199336 end
1625
1626 -- anlee
1627 -- Added for intermedia search
1628 PA_PROJECT_CTX_SEARCH_PVT.UPDATE_ROW (
1629 p_project_id => p_project_id
1630 ,p_template_flag => 'N'
1631 ,p_project_name => l_project_name
1632 ,p_project_number => l_project_number
1633 ,p_project_long_name => p_long_name
1634 ,p_project_description => l_description
1635 ,x_return_status => l_return_status );
1636
1637 l_msg_count := FND_MSG_PUB.count_msg;
1638
1639 IF l_msg_count > 0 THEN
1640 x_msg_count := l_msg_count;
1641 x_return_status := 'E';
1642 RAISE FND_API.G_EXC_ERROR;
1643 END IF;
1644 -- anlee end of changes
1645
1646 -- anlee
1647 -- Ext Attribute changes
1648 -- Bug 2904327
1649 OPEN l_get_project_type_id;
1650 FETCH l_get_project_type_id into l_new_project_type_id;
1651 CLOSE l_get_project_type_id;
1652
1653 PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
1654 p_validate_only => FND_API.G_FALSE
1655 ,p_project_id => p_project_id
1656 ,p_old_classification_id => l_old_project_type_id
1657 ,p_new_classification_id => l_new_project_type_id
1658 ,p_classification_type => 'PROJECT_TYPE'
1659 ,x_return_status => l_return_status
1660 ,x_msg_count => l_msg_count
1661 ,x_msg_data => l_msg_data );
1662
1663 l_msg_count := FND_MSG_PUB.count_msg;
1664
1665 IF l_msg_count > 0 THEN
1666 x_msg_count := l_msg_count;
1667 x_return_status := 'E';
1668 RAISE FND_API.G_EXC_ERROR;
1669 END IF;
1670 -- anlee end of changes
1671
1672 -- hyau
1673 -- Added validations for recalc if CDLs, txns, or revenues exist
1674 IF p_recalculate_flag = 'Y' then
1675
1676 OPEN l_project_details_csr;
1677 FETCH l_project_details_csr INTO l_project_rec;
1678 CLOSE l_project_details_csr;
1679
1680 PA_ADJUSTMENTS.MassAdjust(
1681 x_adj_action => 'COST AND REV RECALC'
1682 ,x_module => 'PAXPREPR'
1683 ,x_user => fnd_global.user_id
1684 ,x_login => fnd_global.login_id
1685 ,x_project_id => p_project_id
1686 ,x_dest_prj_id => NULL
1687 ,x_dest_task_id => NULL
1688 ,x_project_currency_code => l_project_rec.project_currency_code
1689 ,x_project_rate_type => l_project_rec.project_rate_type
1690 ,x_project_rate_date => l_project_rec.project_rate_date
1691 ,x_project_exchange_rate => NULL
1692 ,x_task_id => NULL
1693 ,x_inc_by_person_id => NULL
1694 ,x_inc_by_org_id => NULL
1695 ,x_ei_date_low => SYSDATE /* 8242683 */
1696 ,x_ei_date_high => NULL
1697 ,x_ex_end_date_low => NULL
1698 ,x_ex_end_date_high => NULL
1699 ,x_system_linkage => NULL
1700 ,x_expenditure_type => NULL
1701 ,x_expenditure_catg => NULL
1702 ,x_expenditure_group => NULL
1703 ,x_vendor_id => NULL
1704 ,x_job_id => NULL
1705 ,x_nl_resource_org_id => NULL
1706 ,x_nl_resource => NULL
1707 ,x_transaction_source => NULL
1708 ,x_cost_distributed_flag => NULL
1709 ,x_revenue_distributed_flag => NULL
1710 ,x_grouped_cip_flag => NULL
1711 ,x_bill_status => NULL
1712 ,x_hold_flag => NULL
1713 ,x_billable_flag => NULL
1714 ,x_capitalizable_flag => NULL
1715 ,x_net_zero_adjust_flag => NULL
1716 ,x_inv_num => NULL
1717 ,x_inv_line_num => NULL
1718 ,x_outcome => l_mass_adj_outcome
1719 ,x_num_processed => l_dummy1
1720 ,x_num_rejected => l_dummy2 );
1721
1722 OPEN get_batch_name_csr;
1723 FETCH get_batch_name_csr INTO l_batch_name;
1724 CLOSE get_batch_name_csr;
1725
1726 OPEN get_batch_desc_csr;
1727 FETCH get_batch_desc_csr INTO l_batch_description;
1728 CLOSE get_batch_desc_csr;
1729
1730 PA_MU_BATCHES_V_PKG.INSERT_ROW(
1731 x_rowid => l_row_id
1732 ,x_batch_id => l_batch_id
1733 ,x_creation_date => l_project_rec.last_update_date
1734 ,x_created_by => l_project_rec.last_updated_by
1735 ,x_last_updated_by => l_project_rec.last_updated_by
1736 ,x_last_update_date => l_project_rec.last_update_date
1737 ,x_last_update_login => l_project_rec.last_update_login
1738 ,x_batch_name => l_batch_name
1739 ,x_batch_status_code => 'C'
1740 ,x_description => l_batch_description
1741 ,x_project_attribute => 'ORGANIZATION'
1742 ,x_effective_date => trunc(sysdate)
1743 ,x_attribute_category => NULL
1744 ,x_attribute1 => NULL
1745 ,x_attribute2 => NULL
1746 ,x_attribute3 => NULL
1747 ,x_attribute4 => NULL
1748 ,x_attribute5 => NULL
1749 ,x_attribute6 => NULL
1750 ,x_attribute7 => NULL
1751 ,x_attribute8 => NULL
1752 ,x_attribute9 => NULL
1753 ,x_attribute10 => NULL
1754 ,x_attribute11 => NULL
1755 ,x_attribute12 => NULL
1756 ,x_attribute13 => NULL
1757 ,x_attribute14 => NULL
1758 ,x_attribute15 => NULL );
1759
1760 UPDATE PA_MASS_UPDATE_BATCHES
1761 SET batch_name = substr(l_batch_name,1,20)||'-'||to_char(l_batch_id)
1762 WHERE rowid = l_row_id;
1763
1764 PA_MU_DETAILS_V_PKG.INSERT_ROW(
1765 x_rowid => l_row_id
1766 ,x_line_id => l_line_id
1767 ,x_batch_id => l_batch_id
1768 ,x_creation_date => l_project_rec.last_update_date
1769 ,x_created_by => l_project_rec.last_updated_by
1770 ,x_last_updated_by => l_project_rec.last_updated_by
1771 ,x_last_update_date => l_project_rec.last_update_date
1772 ,x_last_update_login => l_project_rec.last_update_login
1773 ,x_project_id => p_project_id
1774 ,x_task_id => NULL
1775 ,x_old_attribute_value => to_char(l_old_organization_id)
1776 ,x_new_attribute_value => to_char(p_carrying_out_organization_id)
1777 ,x_update_flag => 'Y'
1778 ,x_recalculate_flag => 'Y' );
1779
1780 END IF; -- if p_recalculate_flag = 'Y'
1781
1782 end if;
1783
1784 IF FND_API.TO_BOOLEAN(P_COMMIT)
1785 THEN
1786 COMMIT WORK;
1787 END IF;
1788
1789 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1790 IF p_commit = FND_API.G_TRUE THEN
1791 ROLLBACK TO update_project_basic_info;
1792 END IF;
1793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1794 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
1795 p_procedure_name => 'UPDATE_PROJECT_BASIC_INFO',
1796 p_error_text => SUBSTRB(SQLERRM,1,240));
1797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1798
1799 WHEN FND_API.G_EXC_ERROR THEN
1800 IF p_commit = FND_API.G_TRUE THEN
1801 ROLLBACK TO update_project_basic_info;
1802 END IF;
1803 x_return_status := 'E';
1804
1805 WHEN OTHERS THEN
1806 IF p_commit = FND_API.G_TRUE THEN
1807 ROLLBACK TO update_project_basic_info;
1808 END IF;
1809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1810 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
1811 p_procedure_name => 'UPDATE_PROJECT_BASIC_INFO',
1812 p_error_text => SUBSTRB(SQLERRM,1,240));
1813 raise;
1814
1815 END UPDATE_PROJECT_BASIC_INFO;
1816
1817 -- API name : Update_project_additional_info
1818 -- Type : Public
1819 -- Pre-reqs : None.
1820 -- Parameters :
1821 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1822 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
1823 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1824 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1825 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1826 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1827 -- p_project_id IN NUMBER Required
1828 -- p_calendar_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1829 -- p_work_type_id IN NUMBER
1830 -- p_role_list_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1831 -- p_cost_job_group_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1832 -- p_bill_job_group_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
1833 -- p_record_version_number IN NUMBER Required
1834 -- x_return_status OUT VARCHAR2 Required
1835 -- x_msg_count OUT NUMBER Required
1836 -- x_msg_data OUT VARCHAR2 Required
1837 --
1838 -- History
1839 --
1840 -- 18-AUG-2000 -- Sakthi/William - Created.
1841 -- 02-Sep-2008 Bug 7286976 : Added WHO columns while updating pa_projects
1842 --
1843 --
1844 PROCEDURE UPDATE_PROJECT_ADDITIONAL_INFO
1845 (
1846 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1847 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1848 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1849 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
1850 p_debug_mode IN VARCHAR2 := 'N',
1851 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
1852 p_project_id IN NUMBER ,
1853 p_calendar_id IN NUMBER := FND_API.G_MISS_NUM ,
1854 p_work_type_id IN NUMBER ,
1855 p_role_list_id IN NUMBER := FND_API.G_MISS_NUM ,
1856 p_cost_job_group_id IN NUMBER := FND_API.G_MISS_NUM ,
1857 p_bill_job_group_id IN NUMBER := FND_API.G_MISS_NUM ,
1858 p_split_cost_from_wokplan_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1859 p_split_cost_from_bill_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1860 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1861 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1862 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1863 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1864 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1865 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1866 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1867 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1868 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1869 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1870 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1871 p_record_version_number IN NUMBER ,
1872 p_sys_program_flag IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1873 p_allow_multi_prog_rollup IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
1874 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1875 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1876 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1877 IS
1878
1879
1880 l_return_status VARCHAR2(250);
1881 l_error_msg_code VARCHAR2(250);
1882 l_msg_count NUMBER;
1883 l_msg_data VARCHAR2(250);
1884 l_err_code VARCHAR2(250);
1885 l_err_stage VARCHAR2(250);
1886 l_err_stack VARCHAR2(250);
1887 l_data VARCHAR2(250);
1888 l_msg_index_out NUMBER;
1889 l_dummy VARCHAR2(1);
1890
1891 l_record_version_number PA_PROJECTS_ALL.RECORD_VERSION_NUMBER%TYPE;
1892 l_calendar_id PA_PROJECTS_ALL.CALENDAR_ID%TYPE;
1893 l_work_type_id PA_PROJECTS_ALL.WORK_TYPE_ID%TYPE;
1894 l_role_list_id PA_PROJECTS_ALL.ROLE_LIST_ID%TYPE;
1895 l_cost_job_group_id PA_PROJECTS_ALL.COST_JOB_GROUP_ID%TYPE;
1896 l_bill_job_group_id PA_PROJECTS_ALL.BILL_JOB_GROUP_ID%TYPE;
1897 l_split_cost_from_wokplan_flag PA_PROJECTS_ALL.split_cost_from_workplan_flag%TYPE;
1898 l_split_cost_from_bill_flag PA_PROJECTS_ALL.split_cost_from_bill_flag%TYPE;
1899
1900 l_project_name PA_PROJECTS_ALL.NAME%TYPE;
1901 l_project_number PA_PROJECTS_ALL.SEGMENT1%TYPE;
1902 l_project_description PA_PROJECTS_ALL.DESCRIPTION%TYPE;
1903 l_old_split PA_PROJECTS_ALL.SPLIT_COST_FROM_WORKPLAN_FLAG%TYPE;
1904
1905 l_old_calendar_id PA_PROJECTS_ALL.CALENDAR_ID%TYPE;
1906
1907 l_attribute_category PA_PROJECTS_ALL.attribute_category%TYPE;
1908 l_attribute1 PA_PROJECTS_ALL.attribute1%TYPE;
1909 l_attribute2 PA_PROJECTS_ALL.attribute2%TYPE;
1910 l_attribute3 PA_PROJECTS_ALL.attribute3%TYPE;
1911 l_attribute4 PA_PROJECTS_ALL.attribute4%TYPE;
1912 l_attribute5 PA_PROJECTS_ALL.attribute5%TYPE;
1913 l_attribute6 PA_PROJECTS_ALL.attribute6%TYPE;
1914 l_attribute7 PA_PROJECTS_ALL.attribute7%TYPE;
1915 l_attribute8 PA_PROJECTS_ALL.attribute8%TYPE;
1916 l_attribute9 PA_PROJECTS_ALL.attribute9%TYPE;
1917 l_attribute10 PA_PROJECTS_ALL.attribute10%TYPE;
1918
1919 --FPM Changes
1920 --l_sys_program_flag PA_PROJECTS_ALL.sys_program_flag%TYPE;
1921 --l_allow_multi_program_rollup PA_PROJECTS_ALL.sys_program_flag%TYPE;
1922 --l_proj_sys_program_flag PA_PROJECTS_ALL.sys_program_flag%TYPE;
1923 --l_proj_allow_program_rollup PA_PROJECTS_ALL.sys_program_flag%TYPE;
1924 --l_flag VARCHAR2(1);
1925
1926
1927 CURSOR l_get_project_attrs_csr
1928 IS
1929 SELECT name, segment1, description, split_cost_from_workplan_flag, calendar_id
1930 --,sys_program_flag,allow_multi_program_rollup
1931 FROM PA_PROJECTS_ALL
1932 WHERE project_id = p_project_id;
1933
1934 BEGIN
1935
1936 IF p_commit = FND_API.G_TRUE THEN
1937 SAVEPOINT update_project_additional_info;
1938 END IF;
1939
1940 -- write your program logic from here
1941
1942 if (p_debug_mode = 'Y') then
1943 pa_debug.debug('Update_project_additional_info PVT: locking record');
1944 end if;
1945
1946 if p_validate_only <> FND_API.G_TRUE then
1947 BEGIN
1948 SELECT 'x' INTO l_dummy
1949 FROM pa_projects
1950 WHERE project_id = p_project_id
1951 AND record_version_number = p_record_version_number
1952 FOR UPDATE OF record_version_number NOWAIT;
1953 EXCEPTION WHEN TIMEOUT_ON_RESOURCE THEN
1954 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1955 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1956 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1957 x_return_status := 'E' ;
1958 WHEN NO_DATA_FOUND THEN
1959 if p_calling_module = 'FORM' then
1960 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1961 p_msg_name => 'FORM_RECORD_CHANGED');
1962 x_msg_data := 'FORM_RECORD_CHANGED';
1963 else
1964 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1965 p_msg_name => 'PA_XC_RECORD_CHANGED');
1966 x_msg_data := 'PA_XC_RECORD_CHANGED';
1967 end if;
1968 x_return_status := 'E' ;
1969 WHEN OTHERS THEN
1970 IF SQLCODE = -54 THEN
1971 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1972 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1973 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1974 x_return_status := 'E' ;
1975 ELSE
1976 RAISE;
1977 END IF;
1978 END;
1979 else
1980
1981 BEGIN
1982 SELECT 'x' INTO l_dummy
1983 FROM pa_projects
1984 WHERE project_id = p_project_id
1985 AND record_version_number = p_record_version_number;
1986 EXCEPTION
1987 WHEN NO_DATA_FOUND THEN
1988 if p_calling_module = 'FORM' then
1989 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1990 p_msg_name => 'FORM_RECORD_CHANGED');
1991 x_msg_data := 'FORM_RECORD_CHANGED';
1992 else
1993 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1994 p_msg_name => 'PA_XC_RECORD_CHANGED');
1995 x_msg_data := 'PA_XC_RECORD_CHANGED';
1996 end if;
1997 x_return_status := 'E' ;
1998 WHEN OTHERS THEN
1999 IF SQLCODE = -54 THEN
2000 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2001 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
2002 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
2003 x_return_status := 'E' ;
2004 Else
2005 raise;
2006 END IF;
2007 END;
2008 end if;
2009 l_msg_count := FND_MSG_PUB.count_msg;
2010
2011 IF l_msg_count > 0 THEN
2012 x_msg_count := l_msg_count;
2013 x_return_status := 'E';
2014 RAISE FND_API.G_EXC_ERROR;
2015 END IF;
2016
2017 -- write your program logic from here
2018
2019 --dbms_output.put_line('PA_PROJECTS_MAINT_PVT.VALIDATE_PROJECT_INFO Project Data ');
2020
2021 if p_validation_level > 0 then
2022 pa_debug.G_err_stage := 'Calling validate additional Info';
2023 if (p_debug_mode = 'Y') then
2024 pa_debug.debug('Update_project_additional_info PVT: Calling validate project info API');
2025 end if;
2026 PA_PROJECTS_MAINT_PVT.VALIDATE_PROJECT_INFO
2027 (
2028 p_validation_level => p_validation_level,
2029 p_calling_module => 'ADDITIONAL_INFORMATION',
2030 p_action => 'UPDATE',
2031 p_debug_mode => p_debug_mode,
2032 p_max_msg_count => p_max_msg_count,
2033 p_project_id => p_project_id,
2034 p_calendar_id => p_calendar_id,
2035 p_work_type_id => p_work_type_id,
2036 p_role_list_id => p_role_list_id,
2037 p_cost_job_group_id => p_cost_job_group_id,
2038 p_bill_job_group_id => p_bill_job_group_id,
2039 p_record_version_number => p_record_version_number);
2040
2041 l_msg_count := FND_MSG_PUB.count_msg;
2042
2043 IF l_msg_count > 0 THEN
2044 x_msg_count := l_msg_count;
2045 x_return_status := 'E';
2046 RAISE FND_API.G_EXC_ERROR;
2047 END IF;
2048 END IF;
2049
2050 IF NOT FND_API.TO_BOOLEAN(p_validate_only)
2051 THEN
2052 if (p_debug_mode = 'Y') then
2053 pa_debug.debug('Update_project_additional_info PVT: updating pa_projects table');
2054 end if;
2055 /* if p_record_version_number = FND_API.G_MISS_NUM
2056 then
2057 l_record_version_number := 0;
2058 else
2059 l_record_version_number := p_record_version_number;
2060 end if;
2061 */
2062 if p_calendar_id= FND_API.G_MISS_NUM
2063 then
2064 l_calendar_id := null;
2065 else
2066 l_calendar_id := p_calendar_id ;
2067 end if;
2068
2069 if p_work_type_id = FND_API.G_MISS_NUM
2070 then
2071 l_work_type_id := null;
2072 else
2073 l_work_type_id := p_work_type_id;
2074 end if;
2075
2076 if p_role_list_id = FND_API.G_MISS_NUM
2077 then
2078 l_role_list_id := null;
2079 else
2080 l_role_list_id := p_role_list_id;
2081 end if;
2082
2083 if p_cost_job_Group_id = FND_API.G_MISS_NUM
2084 then
2085 l_cost_job_group_id := null;
2086 else
2087 l_cost_job_group_id := p_cost_job_group_id;
2088 end if;
2089
2090 if p_bill_job_Group_id = FND_API.G_MISS_NUM
2091 then
2092 l_bill_job_group_id := null;
2093 else
2094 l_bill_job_group_id := p_bill_job_group_id;
2095 end if;
2096
2097 if p_split_cost_from_wokplan_flag = FND_API.G_MISS_CHAR
2098 then
2099 l_split_cost_from_wokplan_flag := null;
2100 else
2101 l_split_cost_from_wokplan_flag := p_split_cost_from_wokplan_flag;
2102 end if;
2103
2104 if p_split_cost_from_bill_flag = FND_API.G_MISS_CHAR
2105 then
2106 l_split_cost_from_bill_flag := null;
2107 else
2108 l_split_cost_from_bill_flag := p_split_cost_from_bill_flag;
2109 end if;
2110
2111 if p_attribute_category = FND_API.G_MISS_CHAR
2112 then
2113 l_attribute_category := null;
2114 else
2115 l_attribute_category := p_attribute_category;
2116 end if;
2117
2118 if p_attribute1 = FND_API.G_MISS_CHAR
2119 then
2120 l_attribute1 := null;
2121 else
2122 l_attribute1 := p_attribute1;
2123 end if;
2124
2125 if p_attribute2 = FND_API.G_MISS_CHAR
2126 then
2127 l_attribute2 := null;
2128 else
2129 l_attribute2 := p_attribute2;
2130 end if;
2131
2132 if p_attribute3 = FND_API.G_MISS_CHAR
2133 then
2134 l_attribute3 := null;
2135 else
2136 l_attribute3 := p_attribute3;
2137 end if;
2138
2139 if p_attribute4 = FND_API.G_MISS_CHAR
2140 then
2141 l_attribute4 := null;
2142 else
2143 l_attribute4 := p_attribute4;
2144 end if;
2145
2146 if p_attribute5 = FND_API.G_MISS_CHAR
2147 then
2148 l_attribute5 := null;
2149 else
2150 l_attribute5 := p_attribute5;
2151 end if;
2152
2153 if p_attribute6 = FND_API.G_MISS_CHAR
2154 then
2155 l_attribute6 := null;
2156 else
2157 l_attribute6 := p_attribute6;
2158 end if;
2159
2160 if p_attribute7 = FND_API.G_MISS_CHAR
2161 then
2162 l_attribute7 := null;
2163 else
2164 l_attribute7 := p_attribute7;
2165 end if;
2166
2167 if p_attribute8 = FND_API.G_MISS_CHAR
2168 then
2169 l_attribute8 := null;
2170 else
2171 l_attribute8 := p_attribute8;
2172 end if;
2173
2174 if p_attribute9 = FND_API.G_MISS_CHAR
2175 then
2176 l_attribute9 := null;
2177 else
2178 l_attribute9 := p_attribute9;
2179 end if;
2180
2181 if p_attribute10 = FND_API.G_MISS_CHAR
2182 then
2183 l_attribute10 := null;
2184 else
2185 l_attribute10 := p_attribute10;
2186 end if;
2187 /*
2188 If p_sys_program_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2189 l_sys_program_flag := null;
2190 else
2191 l_sys_program_flag := p_sys_program_flag;
2192 end if;
2193
2194 If p_allow_multi_prog_rollup = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
2195 l_allow_multi_program_rollup :=null;
2196 else
2197 l_allow_multi_program_rollup :=p_allow_multi_prog_rollup;
2198 end if;
2199 */
2200 OPEN l_get_project_attrs_csr;
2201 FETCH l_get_project_attrs_csr INTO l_project_name, l_project_number, l_project_description, l_old_split,
2202 l_old_calendar_id;
2203 -- ,l_proj_sys_program_flag,l_proj_allow_program_rollup;
2204 CLOSE l_get_project_attrs_csr;
2205
2206 if l_old_split <> l_split_cost_from_wokplan_flag then
2207 PA_PROJ_TASK_STRUC_PUB.create_delete_workplan_struc(
2208 p_calling_module => p_calling_module
2209 ,p_project_id => p_project_id
2210 ,p_project_number => l_project_number
2211 ,p_project_name => l_project_name
2212 ,p_project_description => l_project_description
2213 ,p_split_workplan => l_split_cost_from_wokplan_flag
2214 ,x_msg_count => l_msg_count
2215 ,x_msg_data => l_msg_data
2216 ,x_return_status => l_return_status);
2217
2218 l_msg_count := FND_MSG_PUB.count_msg;
2219
2220 IF l_msg_count > 0 THEN
2221 x_msg_count := l_msg_count;
2222 x_return_status := 'E';
2223 RAISE FND_API.G_EXC_ERROR;
2224 END IF;
2225 end if;
2226
2227 --hsiu
2228 --added for workplan changes
2229 IF (l_old_calendar_id <> l_calendar_id) THEN
2230 PA_PROJECT_STRUCTURE_PVT1.update_wp_calendar(
2231 p_project_id => p_project_id
2232 ,p_calendar_id => l_calendar_id
2233 ,x_return_status => l_return_status
2234 ,x_msg_count => l_msg_count
2235 ,x_msg_data => l_msg_data
2236 );
2237
2238 l_msg_count := FND_MSG_PUB.count_msg;
2239
2240 IF l_msg_count > 0 THEN
2241 x_msg_count := l_msg_count;
2242 x_return_status := 'E';
2243 RAISE FND_API.G_EXC_ERROR;
2244 END IF;
2245
2246 END IF;
2247 --end workplan changes
2248
2249 --Added for FPM Changes for Project Relationships
2250 /*
2251 IF (l_proj_sys_program_flag='Y' and nvl(l_sys_program_flag,'N')='N' ) then
2252 l_flag := PA_RELATIONSHIP_UTILS.DISABLE_SYS_PROG_OK(p_project_id);
2253
2254 If l_flag='N' Then
2255 Pa_Utils.ADD_MESSAGE
2256 ( p_app_short_name => 'PA',
2257 p_msg_name =>'PA_PS_DIS_SYS_PROG_ERR');
2258 RAISE FND_API.G_EXC_ERROR;
2259 END IF;
2260 END IF;
2261
2262 IF (l_proj_allow_program_rollup='Y' and nvl(l_allow_multi_program_rollup,'N')='N' ) then
2263 l_flag := PA_RELATIONSHIP_UTILS.DISABLE_MULTI_PROG_OK(p_project_id);
2264
2265 IF l_flag ='N' Then
2266 Pa_Utils.ADD_MESSAGE
2267 ( p_app_short_name => 'PA',
2268 p_msg_name =>'PA_PS_DIS_MULTI_PROG_ERR');
2269 RAISE FND_API.G_EXC_ERROR;
2270 END IF;
2271 END IF;
2272 */
2273 UPDATE pa_projects
2274 SET record_version_number = record_version_number +1,
2275 attribute_category = l_attribute_category,
2276 attribute1 = l_attribute1,
2277 attribute2 = l_attribute2,
2278 attribute3 = l_attribute3,
2279 attribute4 = l_attribute4,
2280 attribute5 = l_attribute5,
2281 attribute6 = l_attribute6,
2282 attribute7 = l_attribute7,
2283 attribute8 = l_attribute8,
2284 attribute9 = l_attribute9,
2285 attribute10 = l_attribute10,
2286 calendar_id = l_calendar_id ,
2287 work_type_id = l_work_type_id ,
2288 role_list_id = l_role_list_id ,
2289 split_cost_from_workplan_flag = l_split_cost_from_wokplan_flag,
2290 split_cost_from_bill_flag = l_split_cost_from_bill_flag,
2291 cost_job_group_id = l_cost_job_group_id ,
2292 bill_job_group_id = l_bill_job_group_id ,
2293 -- Added WHO columns for Bug 7286976
2294 last_update_date = sysdate,
2295 last_updated_by = fnd_global.user_id,
2296 last_update_login = fnd_global.login_id
2297 --,
2298 -- sys_program_flag = l_sys_program_flag,
2299 -- allow_multi_program_rollup = l_allow_multi_program_rollup
2300 WHERE project_id = p_project_id;
2301
2302
2303 END IF;
2304
2305 IF FND_API.TO_BOOLEAN(P_COMMIT)
2306 THEN
2307 COMMIT WORK;
2308 END IF;
2309
2310 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2311 IF p_commit = FND_API.G_TRUE THEN
2312 ROLLBACK TO update_project_additional_info;
2313 END IF;
2314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2315 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2316 p_procedure_name => 'UPDATE_PROJECT_ADDITIONAL_INFO',
2317 p_error_text => SUBSTRB(SQLERRM,1,240));
2318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2319
2320 WHEN FND_API.G_EXC_ERROR THEN
2321 IF p_commit = FND_API.G_TRUE THEN
2322 ROLLBACK TO prm_create_project;
2323 END IF;
2324 x_return_status := 'E';
2325
2326 WHEN OTHERS THEN
2327 IF p_commit = FND_API.G_TRUE THEN
2328 ROLLBACK TO update_project_additional_info;
2329 END IF;
2330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2331 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2332 p_procedure_name => 'UPDATE_PROJECT_ADDITIONAL_INFO',
2333 p_error_text => SUBSTRB(SQLERRM,1,240));
2334 raise;
2335
2336 END UPDATE_PROJECT_ADDITIONAL_INFO;
2337
2338 -- API name : Update_project_pipeline_info
2339 -- Type : Public
2340 -- Pre-reqs : None.
2341 -- Parameters :
2342 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2343 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
2344 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2345 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2346 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2347 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2348 -- p_project_id IN NUMBER Required
2349 -- p_probability_member_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
2350 -- p_project_value IN NUMBER Optional Default = FND_API.G_MISS_NUM
2351 -- p_expected_approval_date IN DATE Required
2352 -- p_record_version_number IN NUMBER Required
2353 -- x_return_status OUT VARCHAR2 Required
2354 -- x_msg_count OUT NUMBER Required
2355 -- x_msg_data OUT VARCHAR2 Required
2356 --
2357 -- History
2358 --
2359 -- 18-AUG-2000 -- Sakthi/William - Created.
2360 --
2361 --
2362 PROCEDURE UPDATE_PROJECT_PIPELINE_INFO
2363 ( p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2364 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2365 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2366 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2367 p_debug_mode IN VARCHAR2 := 'N',
2368 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
2369 p_project_id IN NUMBER ,
2370 p_probability_member_id IN NUMBER := FND_API.G_MISS_NUM ,
2371 p_project_value IN NUMBER := FND_API.G_MISS_NUM ,
2372 p_expected_approval_date IN DATE ,
2373 p_record_version_number IN NUMBER ,
2374 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2375 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2376 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2377 IS
2378
2379 l_msg_count NUMBER;
2380 l_msg_index_out NUMBER;
2381 l_msg_data VARCHAR2(250);
2382 l_data VARCHAR2(250);
2383 l_dummy VARCHAR2(1);
2384
2385 l_probability_member_id PA_PROJECTS_ALL.PROBABILITY_MEMBER_ID%TYPE;
2386 l_project_value PA_PROJECTS_ALL.PROJECT_VALUE%TYPE;
2387 l_expected_approval_date PA_PROJECTS_ALL.EXPECTED_APPROVAL_DATE%TYPE;
2388 BEGIN
2389
2390 IF p_commit = FND_API.G_TRUE THEN
2391 SAVEPOINT update_project_pipeline_info;
2392 END IF;
2393
2394 x_return_status := FND_API.G_RET_STS_SUCCESS;
2395
2396 if (p_debug_mode = 'Y') then
2397 pa_debug.debug('Update_project_pipeline_info PVT: locking record');
2398 end if;
2399 -- write your program logic from here
2400 if p_validate_only <> FND_API.G_TRUE then
2401 BEGIN
2402 SELECT 'x' INTO l_dummy
2403 FROM pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
2404 WHERE project_id = p_project_id
2405 AND record_version_number = p_record_version_number
2406 FOR UPDATE OF record_version_number NOWAIT;
2407 EXCEPTION WHEN TIMEOUT_ON_RESOURCE THEN
2408 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2409 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
2410 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
2411 x_return_status := 'E' ;
2412 WHEN NO_DATA_FOUND THEN
2413 if p_calling_module = 'FORM' then
2414 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
2415 p_msg_name => 'FORM_RECORD_CHANGED');
2416 x_msg_data := 'FORM_RECORD_CHANGED';
2417 else
2418 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2419 p_msg_name => 'PA_XC_RECORD_CHANGED');
2420 x_msg_data := 'PA_XC_RECORD_CHANGED';
2421 end if;
2422 x_return_status := 'E' ;
2423 WHEN OTHERS THEN
2424 IF SQLCODE = -54 THEN
2425 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2426 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
2427 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
2428 x_return_status := 'E' ;
2429 ELSE
2430 raise;
2431 END IF;
2432 END;
2433 else
2434 BEGIN
2435 SELECT 'x' INTO l_dummy
2436 FROM pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
2437 WHERE project_id = p_project_id
2438 AND record_version_number = p_record_version_number;
2439 EXCEPTION
2440 WHEN NO_DATA_FOUND THEN
2441 if p_calling_module = 'FORM' then
2442 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
2443 p_msg_name => 'FORM_RECORD_CHANGED');
2444 x_msg_data := 'FORM_RECORD_CHANGED';
2445 else
2446 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2447 p_msg_name => 'PA_XC_RECORD_CHANGED');
2448 x_msg_data := 'PA_XC_RECORD_CHANGED';
2449 end if;
2450 x_return_status := 'E' ;
2451 WHEN OTHERS THEN
2452 IF SQLCODE = -54 THEN
2453 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2454 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
2455 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
2456 x_return_status := 'E' ;
2457 ELSE
2458 raise;
2459 END IF;
2460 END;
2461 end if;
2462 l_msg_count := FND_MSG_PUB.count_msg;
2463
2464 IF l_msg_count > 0 THEN
2465 x_msg_count := l_msg_count;
2466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2467 RAISE FND_API.G_EXC_ERROR;
2468 END IF;
2469
2470 if p_validation_level > 0 then
2471 pa_debug.G_err_stage := 'Calling validate pipeline Info';
2472 if (p_debug_mode = 'Y') then
2473 pa_debug.debug('Update_project_pipeline_info PVT: Calling validate project API');
2474 end if;
2475 PA_PROJECTS_MAINT_PVT.VALIDATE_PROJECT_INFO
2476 ( p_validation_level => p_validation_level,
2477 p_calling_module => 'PIPELINE',
2478 p_action => 'UPDATE',
2479 p_debug_mode => p_debug_mode,
2480 p_max_msg_count => p_max_msg_count,
2481 p_project_id => p_project_id,
2482 p_probability_member_id => p_probability_member_id,
2483 p_project_value => p_project_value,
2484 p_expected_approval_date => p_expected_approval_date,
2485 p_record_version_number => p_record_version_number);
2486
2487 l_msg_count := FND_MSG_PUB.count_msg;
2488
2489 IF l_msg_count > 0 THEN
2490 x_msg_count := l_msg_count;
2491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2492 RAISE FND_API.G_EXC_ERROR;
2493 END IF;
2494 END IF;
2495 IF NOT FND_API.TO_BOOLEAN(p_validate_only)
2496 THEN
2497 if (p_debug_mode = 'Y') then
2498 pa_debug.debug('Update_project_pipeline_info PVT: update pa_projects table');
2499 end if;
2500 if p_probability_member_id = FND_API.G_MISS_NUM
2501 then
2502 l_probability_member_id := null;
2503 else
2504 l_probability_member_id := p_probability_member_id;
2505 end if;
2506
2507 if p_project_Value = FND_API.G_MISS_NUM
2508 then
2509 l_project_value := null;
2510 else
2511 l_project_value := p_project_value;
2512 end if;
2513
2514 if p_expected_Approval_Date= FND_API.G_MISS_DATE
2515 then
2516 l_expected_approval_date := null;
2517 else
2518 l_expected_approval_Date := p_expected_approval_date;
2519 end if;
2520 UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
2521 SET record_version_number = record_version_number +1 ,
2522 probability_member_id = l_probability_member_id,
2523 project_value = l_project_value,
2524 expected_approval_date = l_expected_approval_date
2525 WHERE project_id = p_project_id;
2526 END IF;
2527
2528 IF FND_API.TO_BOOLEAN(P_COMMIT)
2529 THEN
2530 COMMIT WORK;
2531 END IF;
2532
2533 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2534 IF p_commit = FND_API.G_TRUE THEN
2535 ROLLBACK TO update_project_pipeline_info;
2536 END IF;
2537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2538 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2539 p_procedure_name => 'UPDATE_PROJECT_PIPELINE_INFO',
2540 p_error_text => SUBSTRB(SQLERRM,1,240));
2541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2542
2543 WHEN FND_API.G_EXC_ERROR THEN
2544 IF p_commit = FND_API.G_TRUE THEN
2545 ROLLBACK TO update_project_pipeline_info;
2546 END IF;
2547 x_return_status := 'E';
2548
2549 WHEN OTHERS THEN
2550 IF p_commit = FND_API.G_TRUE THEN
2551 ROLLBACK TO update_project_pipeline_info;
2552 END IF;
2553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2554 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
2555 p_procedure_name => 'UPDATE_PROJECT_PIPELINE_INFO',
2556 p_error_text => SUBSTRB(SQLERRM,1,240));
2557 raise;
2558
2559 END UPDATE_PROJECT_PIPELINE_INFO;
2560
2561 -- API name : Create_classifications
2562 -- Type : Public
2563 -- Pre-reqs : None.
2564 -- Parameters :
2565 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2566 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
2567 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2568 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2569 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2570 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2571 -- p_object_id IN NUMBER
2572 -- p_object_type IN VARCHAR2
2573 -- p_class_category IN VARCHAR2 Required
2574 -- p_class_code IN VARCHAR2 Required
2575 -- p_code_percentage IN NUMBER Optional Default = FND_API.G_MISS_NUM
2576 -- p_attribute_category IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2577 -- p_attribute1 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2578 -- p_attribute2 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2579 -- p_attribute3 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2580 -- p_attribute4 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2581 -- p_attribute5 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2582 -- p_attribute6 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2583 -- p_attribute7 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2584 -- p_attribute8 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2585 -- p_attribute9 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2586 -- p_attribute10 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2587 -- p_attribute11 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2588 -- p_attribute12 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2589 -- p_attribute13 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2590 -- p_attribute14 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2591 -- p_attribute15 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2592 -- x_return_status OUT VARCHAR2 Required
2593 -- x_msg_count OUT NUMBER Required
2594 -- x_msg_data OUT VARCHAR2 Required
2595 --
2596 -- History
2597 --
2598 -- 18-AUG-2000 -- Sakthi/William - Created.
2599 --
2600 --
2601 PROCEDURE CREATE_CLASSIFICATIONS
2602 (
2603 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2604 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2605 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2606 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2607 p_debug_mode IN VARCHAR2 := 'N',
2608 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
2609 p_object_id IN NUMBER,
2610 p_object_type IN VARCHAR2,
2611 p_class_category IN VARCHAR2 ,
2612 p_class_code IN VARCHAR2 ,
2613 p_code_percentage IN NUMBER := FND_API.G_MISS_NUM ,
2614 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2615 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2616 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2617 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2618 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2619 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2620 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2621 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2622 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2623 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2624 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2625 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2626 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2627 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2628 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2629 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2630 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2631 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2632 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2633 )
2634 IS
2635 l_row_id VARCHAR2(50);
2636 l_return_status VARCHAR2(250);
2637 l_error_msg_code VARCHAR2(250);
2638 l_msg_count number;
2639 l_msg_data VARCHAR2(250);
2640 l_msg_index_out number;
2641 l_data VARCHAR2(250);
2642 l_code_percentage NUMBER;
2643 l_attribute_category VARCHAR2(30);
2644 l_attribute1 VARCHAR2(150);
2645 l_attribute2 VARCHAR2(150);
2646 l_attribute3 VARCHAR2(150);
2647 l_attribute4 VARCHAR2(150);
2648 l_attribute5 VARCHAR2(150);
2649 l_attribute6 VARCHAR2(150);
2650 l_attribute7 VARCHAR2(150);
2651 l_attribute8 VARCHAR2(150);
2652 l_attribute9 VARCHAR2(150);
2653 l_attribute10 VARCHAR2(150);
2654 l_attribute11 VARCHAR2(150);
2655 l_attribute12 VARCHAR2(150);
2656 l_attribute13 VARCHAR2(150);
2657 l_attribute14 VARCHAR2(150);
2658 l_attribute15 VARCHAR2(150);
2659 l_application_id NUMBER;
2660
2661 BEGIN
2662
2663 --dbms_output.put_line('PA_PROJECTS_MAINT_PVT.CREATE_CLASSIFICATION ');
2664
2665 IF p_commit = FND_API.G_TRUE THEN
2666 SAVEPOINT create_classifications_pvt;
2667 END IF;
2668
2669 x_return_status := FND_API.G_RET_STS_SUCCESS;
2670 if p_validation_level > 0 then
2671
2672 if (p_debug_mode = 'Y') then
2673 pa_debug.debug('create_classification: Calling validate_classifications');
2674 end if;
2675
2676 pa_debug.G_err_stage := 'Calling validate_classifications';
2677
2678 --dbms_output.put_line('PA_PROJECTS_MAINT_PVT.VALIDATE_CLASSIFICATIONS Before ');
2679
2680 if (p_debug_mode = 'Y') then
2681 pa_debug.debug('Create classification PVT: Calling validate_classification API ');
2682 end if;
2683
2684 l_code_percentage := p_code_percentage;
2685
2686 PA_PROJECTS_MAINT_PVT.VALIDATE_CLASSIFICATIONS
2687 (
2688 p_validation_level => p_validation_level,
2689 p_calling_module => p_calling_module,
2690 p_action => 'INSERT',
2691 p_debug_mode => p_debug_mode,
2692 p_max_msg_count => p_max_msg_count,
2693 p_object_id => p_object_id,
2694 p_object_type => p_object_type,
2695 p_class_category => p_class_category,
2696 p_class_code => p_class_code,
2697 p_code_percentage => l_code_percentage);
2698
2699 l_msg_count := FND_MSG_PUB.count_msg;
2700
2701 --dbms_output.put_line('VALIDATE_CLASSIFICATIONS '||to_char(l_msg_count));
2702
2703 IF l_msg_count > 0 THEN
2704 x_msg_count := l_msg_count;
2705 x_return_status := 'E';
2706 RAISE FND_API.G_EXC_ERROR;
2707 END IF;
2708 END IF;
2709
2710 if p_attribute_category = FND_API.G_MISS_CHAR then
2711 l_attribute_category := NULL;
2712 else
2713 l_attribute_category := p_attribute_category;
2714 end if;
2715
2716 if p_attribute1 = FND_API.G_MISS_CHAR then
2717 l_attribute1 := NULL;
2718 else
2719 l_attribute1 := p_attribute1;
2720 end if;
2721
2722 if p_attribute2 = FND_API.G_MISS_CHAR then
2723 l_attribute2 := NULL;
2724 else
2725 l_attribute2 := p_attribute2;
2726 end if;
2727
2728 if p_attribute3 = FND_API.G_MISS_CHAR then
2729 l_attribute3 := NULL;
2730 else
2731 l_attribute3 := p_attribute3;
2732 end if;
2733
2734 if p_attribute4 = FND_API.G_MISS_CHAR then
2735 l_attribute4 := NULL;
2736 else
2737 l_attribute4 := p_attribute4;
2738 end if;
2739
2740 if p_attribute5 = FND_API.G_MISS_CHAR then
2741 l_attribute5 := NULL;
2742 else
2743 l_attribute5 := p_attribute5;
2744 end if;
2745
2746 if p_attribute6 = FND_API.G_MISS_CHAR then
2747 l_attribute6 := NULL;
2748 else
2749 l_attribute6 := p_attribute6;
2750 end if;
2751
2752 if p_attribute7 = FND_API.G_MISS_CHAR then
2753 l_attribute7 := NULL;
2754 else
2755 l_attribute7 := p_attribute7;
2756 end if;
2757
2758 if p_attribute8 = FND_API.G_MISS_CHAR then
2759 l_attribute8 := NULL;
2760 else
2761 l_attribute8 := p_attribute8;
2762 end if;
2763
2764 if p_attribute9 = FND_API.G_MISS_CHAR then
2765 l_attribute9 := NULL;
2766 else
2767 l_attribute9 := p_attribute9;
2768 end if;
2769
2770 if p_attribute10 = FND_API.G_MISS_CHAR then
2771 l_attribute10 := NULL;
2772 else
2773 l_attribute10 := p_attribute10;
2774 end if;
2775
2776 if p_attribute11 = FND_API.G_MISS_CHAR then
2777 l_attribute11 := NULL;
2778 else
2779 l_attribute11 := p_attribute11;
2780 end if;
2781
2782 if p_attribute12 = FND_API.G_MISS_CHAR then
2783 l_attribute12 := NULL;
2784 else
2785 l_attribute12 := p_attribute12;
2786 end if;
2787
2788 if p_attribute13 = FND_API.G_MISS_CHAR then
2789 l_attribute13 := NULL;
2790 else
2791 l_attribute13 := p_attribute13;
2792 end if;
2793
2794 if p_attribute14 = FND_API.G_MISS_CHAR then
2795 l_attribute14 := NULL;
2796 else
2797 l_attribute14 := p_attribute14;
2798 end if;
2799
2800 if p_attribute15 = FND_API.G_MISS_CHAR then
2801 l_attribute15 := NULL;
2802 else
2803 l_attribute15 := p_attribute15;
2804 end if;
2805
2806 IF NOT FND_API.TO_BOOLEAN(p_validate_only)
2807 THEN
2808
2809 /* Call to PA_PROJECT_UTILS.validate_dff as per Bug 5647964*/
2810 IF p_calling_module = 'ADD_CLASS_CATEGORIES' THEN /*Added ADD_CLASS_CATEGORIES condition to validate only in the add classifications flow. */
2811 l_application_id := FND_GLOBAL.resp_appl_id;
2812 PA_PROJECT_UTILS.validate_dff(
2813 p_application_id => l_application_id,
2814 p_flexfield_name => 'PA_PROJECT_CLASSES_DESC_FLEX',
2815 p_attribute_category => l_attribute_category,
2816 p_calling_module => p_calling_module,
2817 p_attribute1 => l_attribute1,
2818 p_attribute2 => l_attribute2,
2819 p_attribute3 => l_attribute3,
2820 p_attribute4 => l_attribute4,
2821 p_attribute5 => l_attribute5,
2822 p_attribute6 => l_attribute6,
2823 p_attribute7 => l_attribute7,
2824 p_attribute8 => l_attribute8,
2825 p_attribute9 => l_attribute9,
2826 p_attribute10 => l_attribute10,
2827 p_attribute11 => l_attribute11,
2828 p_attribute12 => l_attribute12,
2829 p_attribute13 => l_attribute13,
2830 p_attribute14 => l_attribute14,
2831 p_attribute15 => l_attribute15,
2832 x_return_status => l_return_status,
2833 x_msg_count => l_msg_count,
2834 x_msg_data => l_msg_data);
2835
2836 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2837
2838 /*
2839 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
2840 p_msg_name => 'FLEX-MISSING SEGMENT VALUE',
2841 p_token1 => l_segment_name,
2842 p_token2 => p_flexfield_name); */
2843
2844 RAISE FND_API.G_EXC_ERROR;
2845 End if;
2846 End if;
2847
2848 --dbms_output.put_line('Pa_project_classes_pkg.insert_row Project Data ');
2849 if (p_debug_mode = 'Y') then
2850 pa_debug.debug('Create classification PVT: Calling Table handler to insert new classification record ');
2851 end if;
2852 pa_project_classes_pkg.insert_row
2853 ( l_row_id
2854 ,p_object_id
2855 ,p_object_type
2856 ,p_class_category
2857 ,p_class_code
2858 ,l_code_percentage
2859 ,l_attribute_category
2860 ,l_attribute1
2861 ,l_attribute2
2862 ,l_attribute3
2863 ,l_attribute4
2864 ,l_attribute5
2865 ,l_attribute6
2866 ,l_attribute7
2867 ,l_attribute8
2868 ,l_attribute9
2869 ,l_attribute10
2870 ,l_attribute11
2871 ,l_attribute12
2872 ,l_attribute13
2873 ,l_attribute14
2874 ,l_attribute15
2875 ,sysdate
2876 ,fnd_global.user_id
2877 ,sysdate
2878 ,fnd_global.user_id
2879 ,fnd_global.login_id );
2880 END IF;
2881
2882 IF FND_API.TO_BOOLEAN(P_COMMIT)
2883 THEN
2884 COMMIT WORK;
2885 END IF;
2886
2887 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2888 IF p_commit = FND_API.G_TRUE THEN
2889 ROLLBACK TO create_classifications_pvt;
2890 END IF;
2891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2892 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
2893 p_procedure_name => 'CREATE_CLASSIFICATIONS',
2894 p_error_text => SUBSTRB(SQLERRM,1,240));
2895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2896
2897 WHEN FND_API.G_EXC_ERROR THEN
2898 IF p_commit = FND_API.G_TRUE THEN
2899 ROLLBACK TO create_classifications_pvt;
2900 END IF;
2901 x_return_status := 'E';
2902
2903 WHEN OTHERS THEN
2904 IF p_commit = FND_API.G_TRUE THEN
2905 ROLLBACK TO create_classifications_pvt;
2906 END IF;
2907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2908 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
2909 p_procedure_name => 'CREATE_CLASSIFICATIONS',
2910 p_error_text => SUBSTRB(SQLERRM,1,240));
2911 raise;
2912
2913 END CREATE_CLASSIFICATIONS;
2914
2915
2916 -- API name : Update_Classifications
2917 -- Type : Public
2918 -- Pre-reqs : None.
2919 -- Parameters :
2920 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2921 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
2922 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2923 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
2924 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
2925 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
2926 -- p_object_id IN NUMBER
2927 -- p_object_type IN VARCHAR2
2928 -- p_class_category IN VARCHAR2 Required
2929 -- p_class_code IN VARCHAR2 Required
2930 -- p_code_percentage IN NUMBER Optional Default = FND_API.G_MISS_NUM
2931 -- p_attribute_category IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2932 -- p_attribute1 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2933 -- p_attribute2 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2934 -- p_attribute3 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2935 -- p_attribute4 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2936 -- p_attribute5 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2937 -- p_attribute6 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2938 -- p_attribute7 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2939 -- p_attribute8 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2940 -- p_attribute9 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2941 -- p_attribute10 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2942 -- p_attribute11 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2943 -- p_attribute12 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2944 -- p_attribute13 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2945 -- p_attribute14 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2946 -- p_attribute15 IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2947 -- p_rowid IN VARCHAR2 Required
2948 -- p_record_version_number IN NUMBER Required
2949 -- x_return_status OUT VARCHAR2 Required
2950 -- x_msg_count OUT NUMBER Required
2951 -- x_msg_data OUT VARCHAR2 Required
2952 --
2953 -- History
2954 --
2955 -- 12-OCT-2001 -- anlee created.
2956 --
2957 --
2958 PROCEDURE UPDATE_CLASSIFICATIONS
2959 (
2960 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2961 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2962 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2963 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2964 p_debug_mode IN VARCHAR2 := 'N',
2965 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
2966 p_object_id IN NUMBER,
2967 p_object_type IN VARCHAR2,
2968 p_class_category IN VARCHAR2 ,
2969 p_class_code IN VARCHAR2 ,
2970 p_code_percentage IN NUMBER := FND_API.G_MISS_NUM ,
2971 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2972 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2973 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2974 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2975 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2976 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2977 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2978 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2979 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2980 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2981 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2982 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2983 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2984 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2985 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2986 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2987 p_rowid IN VARCHAR2 ,
2988 p_record_version_number IN NUMBER ,
2989 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2990 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2991 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2992 )
2993 IS
2994
2995 l_code_percentage NUMBER;
2996 l_msg_count NUMBER;
2997 l_msg_data VARCHAR2(250);
2998 l_return_status VARCHAR2(1);
2999 l_dummy VARCHAR2(1);
3000 l_attribute_category VARCHAR2(30);
3001 l_attribute1 VARCHAR2(150);
3002 l_attribute2 VARCHAR2(150);
3003 l_attribute3 VARCHAR2(150);
3004 l_attribute4 VARCHAR2(150);
3005 l_attribute5 VARCHAR2(150);
3006 l_attribute6 VARCHAR2(150);
3007 l_attribute7 VARCHAR2(150);
3008 l_attribute8 VARCHAR2(150);
3009 l_attribute9 VARCHAR2(150);
3010 l_attribute10 VARCHAR2(150);
3011 l_attribute11 VARCHAR2(150);
3012 l_attribute12 VARCHAR2(150);
3013 l_attribute13 VARCHAR2(150);
3014 l_attribute14 VARCHAR2(150);
3015 l_attribute15 VARCHAR2(150);
3016
3017 -- anlee
3018 -- Ext Attribute changes
3019 -- Bug 2904327
3020 CURSOR l_get_classification
3021 IS
3022 SELECT cat.class_category_id, code.class_code_id
3023 FROM PA_PROJECT_CLASSES ppc, PA_CLASS_CATEGORIES cat, PA_CLASS_CODES code
3024 WHERE ppc.rowid = p_rowid
3025 AND ppc.class_category = cat.class_category
3026 AND ppc.class_category = code.class_category
3027 AND ppc.class_code = code.class_code;
3028
3029 l_old_category_id NUMBER;
3030 l_new_category_id NUMBER;
3031 l_old_code_id NUMBER;
3032 l_new_code_id NUMBER;
3033
3034 -- anlee end of changes
3035
3036 BEGIN
3037 if (p_debug_mode = 'Y') then
3038 pa_debug.debug('PA_PROJECTS_MAINT_PVT.Update_Classifications BEGIN');
3039 end if;
3040
3041 if p_commit = FND_API.G_TRUE then
3042 savepoint update_classifications_pvt;
3043 end if;
3044
3045 if (p_debug_mode = 'Y') then
3046 pa_debug.debug('Locking record...');
3047 end if;
3048
3049 if p_validate_only <> FND_API.G_TRUE then
3050 BEGIN
3051 SELECT 'x' INTO l_dummy
3052 FROM pa_project_classes
3053 WHERE rowid = p_rowid
3054 AND record_version_number = p_record_version_number
3055 FOR UPDATE OF record_version_number NOWAIT;
3056 EXCEPTION
3057 when TIMEOUT_ON_RESOURCE then
3058 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3059 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
3060 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
3061 when NO_DATA_FOUND then
3062 if p_calling_module = 'FORM' then
3063 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
3064 p_msg_name => 'FORM_RECORD_CHANGED');
3065 l_msg_data := 'FORM_RECORD_CHANGED';
3066 else
3067 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3068 p_msg_name => 'PA_XC_RECORD_CHANGED');
3069 l_msg_data := 'PA_XC_RECORD_CHANGED';
3070 end if;
3071 when OTHERS then
3072 if SQLCODE = -54 then
3073 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3074 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
3075 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
3076 else
3077 raise;
3078 end if;
3079 END;
3080 else
3081 BEGIN
3082 SELECT 'x' INTO l_dummy
3083 FROM pa_project_classes
3084 WHERE rowid = p_rowid
3085 AND record_version_number = p_record_version_number;
3086 EXCEPTION
3087 when NO_DATA_FOUND then
3088 if p_calling_module = 'FORM' then
3089 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
3090 p_msg_name => 'FORM_RECORD_CHANGED');
3091 l_msg_data := 'FORM_RECORD_CHANGED';
3092 else
3093 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3094 p_msg_name => 'PA_XC_RECORD_CHANGED');
3095 l_msg_data := 'PA_XC_RECORD_CHANGED';
3096 end if;
3097 when OTHERS then
3098 raise;
3099 END;
3100 end if;
3101
3102 l_msg_count := FND_MSG_PUB.count_msg;
3103 if l_msg_count > 0 then
3104 x_msg_count := l_msg_count;
3105 if x_msg_count = 1 then
3106 x_msg_data := l_msg_data;
3107 end if;
3108 raise FND_API.G_EXC_ERROR;
3109 end if;
3110
3111 if p_validation_level > 0 then
3112 if (p_debug_mode = 'Y') then
3113 pa_debug.debug('Performing validation...');
3114 end if;
3115
3116 l_code_percentage := p_code_percentage;
3117
3118 PA_PROJECTS_MAINT_PVT.VALIDATE_CLASSIFICATIONS
3119 (
3120 p_validation_level => p_validation_level,
3121 p_calling_module => p_calling_module,
3122 p_action => 'UPDATE',
3123 p_debug_mode => p_debug_mode,
3124 p_max_msg_count => p_max_msg_count,
3125 p_object_id => p_object_id,
3126 p_object_type => p_object_type,
3127 p_class_category => p_class_category,
3128 p_class_code => p_class_code,
3129 p_code_percentage => l_code_percentage,
3130 p_rowid => p_rowid);
3131
3132 l_msg_count := FND_MSG_PUB.count_msg;
3133 if l_msg_count > 0 then
3134 x_msg_count := l_msg_count;
3135
3136 raise FND_API.G_EXC_ERROR;
3137 end if;
3138 end if;
3139
3140 if p_attribute_category = FND_API.G_MISS_CHAR then
3141 l_attribute_category := NULL;
3142 else
3143 l_attribute_category := p_attribute_category;
3144 end if;
3145
3146 if p_attribute1 = FND_API.G_MISS_CHAR then
3147 l_attribute1 := NULL;
3148 else
3149 l_attribute1 := p_attribute1;
3150 end if;
3151
3152 if p_attribute2 = FND_API.G_MISS_CHAR then
3153 l_attribute2 := NULL;
3154 else
3155 l_attribute2 := p_attribute2;
3156 end if;
3157
3158 if p_attribute3 = FND_API.G_MISS_CHAR then
3159 l_attribute3 := NULL;
3160 else
3161 l_attribute3 := p_attribute3;
3162 end if;
3163
3164 if p_attribute4 = FND_API.G_MISS_CHAR then
3165 l_attribute4 := NULL;
3166 else
3167 l_attribute4 := p_attribute4;
3168 end if;
3169
3170 if p_attribute5 = FND_API.G_MISS_CHAR then
3171 l_attribute5 := NULL;
3172 else
3173 l_attribute5 := p_attribute5;
3174 end if;
3175
3176 if p_attribute6 = FND_API.G_MISS_CHAR then
3177 l_attribute6 := NULL;
3178 else
3179 l_attribute6 := p_attribute6;
3180 end if;
3181
3182 if p_attribute7 = FND_API.G_MISS_CHAR then
3183 l_attribute7 := NULL;
3184 else
3185 l_attribute7 := p_attribute7;
3186 end if;
3187
3188 if p_attribute8 = FND_API.G_MISS_CHAR then
3189 l_attribute8 := NULL;
3190 else
3191 l_attribute8 := p_attribute8;
3192 end if;
3193
3194 if p_attribute9 = FND_API.G_MISS_CHAR then
3195 l_attribute9 := NULL;
3196 else
3197 l_attribute9 := p_attribute9;
3198 end if;
3199
3200 if p_attribute10 = FND_API.G_MISS_CHAR then
3201 l_attribute10 := NULL;
3202 else
3203 l_attribute10 := p_attribute10;
3204 end if;
3205
3206 if p_attribute11 = FND_API.G_MISS_CHAR then
3207 l_attribute11 := NULL;
3208 else
3209 l_attribute11 := p_attribute11;
3210 end if;
3211
3212 if p_attribute12 = FND_API.G_MISS_CHAR then
3213 l_attribute12 := NULL;
3214 else
3215 l_attribute12 := p_attribute12;
3216 end if;
3217
3218 if p_attribute13 = FND_API.G_MISS_CHAR then
3219 l_attribute13 := NULL;
3220 else
3221 l_attribute13 := p_attribute13;
3222 end if;
3223
3224 if p_attribute14 = FND_API.G_MISS_CHAR then
3225 l_attribute14 := NULL;
3226 else
3227 l_attribute14 := p_attribute14;
3228 end if;
3229
3230 if p_attribute15 = FND_API.G_MISS_CHAR then
3231 l_attribute15 := NULL;
3232 else
3233 l_attribute15 := p_attribute15;
3234 end if;
3235
3236 -- anlee
3237 -- Ext Attribute changes
3238 -- Bug 2904327
3239 OPEN l_get_classification;
3240 FETCH l_get_classification into l_old_category_id, l_old_code_id;
3241 CLOSE l_get_classification;
3242 -- anlee end of changes
3243
3244 if p_validate_only <> FND_API.G_TRUE then
3245 pa_project_classes_pkg.update_row
3246 ( X_rowid => p_rowid
3247 ,X_object_id => p_object_id
3248 ,X_object_type => p_object_type
3249 ,X_class_category => p_class_category
3250 ,X_class_code => p_class_code
3251 ,X_code_percentage => l_code_percentage
3252 ,X_attribute_category => l_attribute_category
3253 ,X_attribute1 => l_attribute1
3254 ,X_attribute2 => l_attribute2
3255 ,X_attribute3 => l_attribute3
3256 ,X_attribute4 => l_attribute4
3257 ,X_attribute5 => l_attribute5
3258 ,X_attribute6 => l_attribute6
3259 ,X_attribute7 => l_attribute7
3260 ,X_attribute8 => l_attribute8
3261 ,X_attribute9 => l_attribute9
3262 ,X_attribute10 => l_attribute10
3263 ,X_attribute11 => l_attribute11
3264 ,X_attribute12 => l_attribute12
3265 ,X_attribute13 => l_attribute13
3266 ,X_attribute14 => l_attribute14
3267 ,X_attribute15 => l_attribute15
3268 ,X_Last_Update_Date => sysdate
3269 ,X_Last_Updated_By => fnd_global.user_id
3270 ,X_Creation_Date => sysdate
3271 ,X_Created_By => fnd_global.user_id
3272 ,X_Last_Update_Login => fnd_global.login_id
3273 ,X_record_version_number => p_record_version_number);
3274 end if;
3275
3276 -- anlee
3277 -- Ext Attribute changes
3278 -- Bug 2904327
3279 OPEN l_get_classification;
3280 FETCH l_get_classification into l_new_category_id, l_new_code_id;
3281 CLOSE l_get_classification;
3282
3283 PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
3284 p_validate_only => FND_API.G_FALSE
3285 ,p_project_id => p_object_id
3286 ,p_old_classification_id => l_old_category_id
3287 ,p_new_classification_id => l_new_category_id
3288 ,p_classification_type => 'CLASS_CATEGORY'
3289 ,x_return_status => l_return_status
3290 ,x_msg_count => l_msg_count
3291 ,x_msg_data => l_msg_data );
3292
3293 l_msg_count := FND_MSG_PUB.count_msg;
3294
3295 IF l_msg_count > 0 THEN
3296 x_msg_count := l_msg_count;
3297 x_return_status := 'E';
3298 RAISE FND_API.G_EXC_ERROR;
3299 END IF;
3300
3301
3302 PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
3303 p_validate_only => FND_API.G_FALSE
3304 ,p_project_id => p_object_id
3305 ,p_old_classification_id => l_old_code_id
3306 ,p_new_classification_id => l_new_code_id
3307 ,p_classification_type => 'CLASS_CODE'
3308 ,x_return_status => l_return_status
3309 ,x_msg_count => l_msg_count
3310 ,x_msg_data => l_msg_data );
3311
3312 l_msg_count := FND_MSG_PUB.count_msg;
3313
3314 IF l_msg_count > 0 THEN
3315 x_msg_count := l_msg_count;
3316 x_return_status := 'E';
3317 RAISE FND_API.G_EXC_ERROR;
3318 END IF;
3319
3320 -- anlee end of changes
3321
3322 x_return_status := FND_API.G_RET_STS_SUCCESS;
3323
3324 if p_commit = FND_API.G_TRUE then
3325 commit work;
3326 end if;
3327
3328 if (p_debug_mode = 'Y') then
3329 pa_debug.debug('PA_PROJECTS_MAINT_PVT.Update_Classifications END');
3330 end if;
3331
3332 EXCEPTION
3333 when FND_API.G_EXC_ERROR then
3334 if p_commit = FND_API.G_TRUE then
3335 rollback to update_classifications_pvt;
3336 end if;
3337 x_return_status := FND_API.G_RET_STS_ERROR;
3338 when others then
3339 if p_commit = FND_API.G_TRUE then
3340 rollback to update_classifications_pvt;
3341 end if;
3342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3343 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
3344 p_procedure_name => 'Update_Classifications',
3345 p_error_text => SUBSTRB(SQLERRM,1,240));
3346 raise;
3347 END UPDATE_CLASSIFICATIONS;
3348
3349
3350
3351 -- API name : validate_classifications
3352 -- Type : Validation
3353 -- Pre-reqs : None.
3354 -- Parameters :
3355 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
3356 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
3357 -- p_action IN VARCHAR2 Optional Default = 'INSERT'
3358 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
3359 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
3360 -- p_object_id IN NUMBER Required
3361 -- p_object_type IN VARCHAR2 Required
3362 -- p_class_category IN VARCHAR2 Required
3363 -- p_class_code IN VARCHAR2 Required
3364 -- p_code_percentage IN VARCHAR2 Optional Default = FND_API.G_MISS_NUM
3365 --
3366 -- History
3367 --
3368 -- 18-AUG-2000 -- Sakthi/William - Created.
3369 --
3370 --
3371 PROCEDURE VALIDATE_CLASSIFICATIONS
3372 (
3373 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3374 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
3375 p_action IN VARCHAR2 := 'INSERT' ,
3376 p_debug_mode IN VARCHAR2 := 'N',
3377 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
3378 p_object_id IN NUMBER ,
3379 p_object_type IN VARCHAR2 ,
3380 p_class_category IN VARCHAR2 ,
3381 p_class_code IN VARCHAR2,
3382 p_code_percentage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3383 p_rowid IN VARCHAR2 := FND_API.G_MISS_CHAR)
3384 IS
3385 l_return_status VARCHAR2(250);
3386 l_error_msg_code VARCHAR2(250);
3387 l_object_type_id NUMBER;
3388 l_rowid VARCHAR2(250);
3389 l_class_category VARCHAR2(250);
3390
3391 CURSOR C(c_object_id NUMBER, c_object_type VARCHAR2, c_class_category VARCHAR2, c_class_code VARCHAR2)
3392 IS
3393 SELECT rowid
3394 FROM pa_project_classes
3395 WHERE object_id = c_object_id
3396 AND object_type = c_object_type
3397 AND class_category = c_class_category
3398 AND class_code = c_class_code;
3399
3400 CURSOR C2(c_rowid VARCHAR2)
3401 IS
3402 SELECT class_category
3403 FROM PA_PROJECT_CLASSES
3404 WHERE rowid = c_rowid;
3405
3406 BEGIN
3407
3408 if p_action = 'INSERT' then
3409
3410 --dbms_output.put_line('PA_PROJECTS_MAINT_UTILS.CHECK_CLASS_CATG_CAN_OVERRIDE ');
3411
3412 IF p_calling_module = 'QUICK_ENTRY'
3413 THEN
3414 if (p_debug_mode = 'Y') then
3415 pa_debug.debug('Validate classification PVT: Calling API to check if catg code can be overridden');
3416 end if;
3417 PA_PROJECTS_MAINT_UTILS.CHECK_CLASS_CATG_CAN_OVERRIDE(
3418 p_object_id,
3419 p_class_category,
3420 p_class_code,
3421 l_return_status,
3422 l_error_msg_code);
3423
3424 IF l_return_status = 'E'
3425 THEN
3426 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3427 p_msg_name =>l_error_msg_code);
3428 END IF;
3429 END IF;
3430
3431 --dbms_output.put_line('CHECK_CLASS_CATG_CAN_OVERRIDE '||l_error_msg_code||'Status :'||l_return_status);
3432
3433 if (p_debug_mode = 'Y') then
3434 pa_debug.debug('Validate classification PVT: Calling API to check if category is valid');
3435 end if;
3436
3437 if p_object_type = 'PA_PROJECTS' then
3438 SELECT ppta.project_type_id
3439 INTO l_object_type_id
3440 FROM PA_PROJECT_TYPES_ALL ppta,
3441 PA_PROJECTS_ALL ppa
3442 WHERE ppa.project_id = p_object_id
3443 AND ppa.project_type = ppta.project_type
3444 -- AND NVL(ppa.org_id, -99) = NVL(ppta.org_id, -99); MOAC Changes: Bug 4363092 - removed nvl
3445 AND ppa.org_id = ppta.org_id;
3446 else
3447 l_object_type_id := -1;
3448 end if;
3449
3450 -- for bug# 3690967, SQL mentioned in issue 3, is used in CHECK_CATEGORY_VALID API . This API is
3451 -- called from CREATE_CLASSIFICATIONS,UPDATE_CLASSIFICATIONS API which is used
3452 -- in forms only. In forms class category is already validated as it is picked
3453 -- from LOV which is based on PA_VALID_CATEGORIES_V. Verified that this API is
3454 -- not used in AMG/SS flow. Hence we can comment out the call to this API in
3455 -- CREATE/UPDATE flow from forms.
3456
3457 /*
3458 PA_PROJECTS_MAINT_UTILS.CHECK_CATEGORY_VALID(
3459 l_object_type_id,
3460 p_class_category,
3461 l_return_status,
3462 l_error_msg_code);
3463
3464 IF l_return_status = 'E'
3465 THEN
3466 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3467 p_msg_name =>l_error_msg_code);
3468 END IF;
3469
3470 */
3471
3472 PA_PROJECTS_MAINT_UTILS.CHECK_PERCENTAGE_ALLOWED(
3473 p_class_category,
3474 l_return_status,
3475 l_error_msg_code);
3476 if (p_code_percentage is not NULL) AND (p_code_percentage <> FND_API.G_MISS_NUM) then
3477 if l_return_status = 'E' then
3478 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3479 p_msg_name =>l_error_msg_code);
3480 else
3481 if (p_code_percentage < 0) OR (p_code_percentage > 100) then
3482 l_return_status := 'E';
3483 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3484 p_msg_name => 'PA_CODE_PERCENT_INVALID');
3485 /* Commented for bug 3161704
3486 else
3487 if p_calling_module = 'FORMS' then
3488 PA_PROJECTS_MAINT_UTILS.CHECK_CATEGORY_TOTAL_VALID(
3489 p_object_id,
3490 p_object_type,
3491 p_class_category,
3492 NULL,
3493 p_code_percentage,
3494 l_return_status,
3495 l_error_msg_code);
3496
3497 if l_return_status = 'E' then
3498 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3499 p_msg_name =>l_error_msg_code);
3500 end if;
3501 end if;
3502 */
3503 end if;
3504 end if;
3505 else
3506 if l_return_status = 'S' then
3507 p_code_percentage := 0;
3508 else
3509 p_code_percentage := NULL;
3510 end if;
3511 end if;
3512
3513
3514 if (p_debug_mode = 'Y') then
3515 pa_debug.debug('Validate classification PVT: Calling API to check if only one catg code can be entered');
3516 end if;
3517 PA_PROJECTS_MAINT_UTILS.CHECK_CLASS_CATG_ONE_ONLY_CODE(
3518 p_object_id,
3519 p_object_type,
3520 p_class_category,
3521 l_return_status,
3522 l_error_msg_code);
3523
3524 IF l_return_status = 'E'
3525 THEN
3526 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3527 p_msg_name =>l_error_msg_code);
3528 END IF;
3529
3530 --dbms_output.put_line('CHECK_DUPLICATE_CLASS_CATG '||l_error_msg_code||'Status :'||l_return_status);
3531
3532 if (p_debug_mode = 'Y') then
3533 pa_debug.debug('Validate classification PVT: Calling API to check if the catg code entered is a duplicate');
3534 end if;
3535 PA_PROJECTS_MAINT_UTILS.CHECK_DUPLICATE_CLASS_CATG(
3536 p_object_id,
3537 p_object_type,
3538 p_class_category,
3539 p_class_code,
3540 l_return_status,
3541 l_error_msg_code);
3542 IF l_return_status = 'E'
3543 THEN
3544 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3545 p_msg_name =>l_error_msg_code);
3546 END IF;
3547 --dbms_output.put_line('CHECK_DUPLICATE_CLASS_CATG '||l_error_msg_code||'Status :'||l_return_status);
3548
3549
3550 ELSIF p_action = 'UPDATE' then
3551
3552 if (p_debug_mode = 'Y') then
3553 pa_debug.debug('Validate classification PVT: Calling API to check if category is valid');
3554 end if;
3555
3556 if p_object_type = 'PA_PROJECTS' then
3557 SELECT ppta.project_type_id
3558 INTO l_object_type_id
3559 FROM PA_PROJECT_TYPES_ALL ppta,
3560 PA_PROJECTS_ALL ppa
3561 WHERE ppa.project_id = p_object_id
3562 AND ppa.project_type = ppta.project_type
3563 -- AND NVL(ppa.org_id, -99) = NVL(ppta.org_id, -99); MOAC Changes: Bug 4363092 - removed nvl
3564 AND ppa.org_id = ppta.org_id;
3565 else
3566 l_object_type_id := -1;
3567 end if;
3568
3569 PA_PROJECTS_MAINT_UTILS.CHECK_CATEGORY_VALID(
3570 l_object_type_id,
3571 p_class_category,
3572 l_return_status,
3573 l_error_msg_code);
3574
3575 IF l_return_status = 'E'
3576 THEN
3577 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3578 p_msg_name =>l_error_msg_code);
3579 END IF;
3580
3581 PA_PROJECTS_MAINT_UTILS.CHECK_PERCENTAGE_ALLOWED(
3582 p_class_category,
3583 l_return_status,
3584 l_error_msg_code);
3585 if (p_code_percentage is not NULL) AND (p_code_percentage <> FND_API.G_MISS_NUM) then
3586 if l_return_status = 'E' then
3587 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3588 p_msg_name =>l_error_msg_code);
3589 else
3590 if (p_code_percentage < 0) OR (p_code_percentage > 100) then
3591 l_return_status := 'E';
3592 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3593 p_msg_name => 'PA_CODE_PERCENT_INVALID');
3594 /* Commented for bug 3161704
3595 else
3596 if p_calling_module = 'FORMS' then
3597 PA_PROJECTS_MAINT_UTILS.CHECK_CATEGORY_TOTAL_VALID(
3598 p_object_id,
3599 p_object_type,
3600 p_class_category,
3601 p_rowid,
3602 p_code_percentage,
3603 l_return_status,
3604 l_error_msg_code);
3605
3606 if l_return_status = 'E' then
3607 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3608 p_msg_name =>l_error_msg_code);
3609 end if;
3610 end if;
3611 */
3612 end if;
3613 end if;
3614 else
3615 if l_return_status = 'S' then
3616 p_code_percentage := 0;
3617 else
3618 p_code_percentage := NULL;
3619 end if;
3620 end if;
3621
3622
3623 if (p_rowid = FND_API.G_MISS_CHAR) OR (p_rowid is NULL) then
3624 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3625 end if;
3626
3627 OPEN C(p_object_id, p_object_type, p_class_category, p_class_code);
3628 FETCH C INTO l_rowid;
3629 close C;
3630
3631 if p_rowid = l_rowid then
3632 return;
3633 end if;
3634
3635 OPEN C2(p_rowid);
3636 FETCH C2 INTO l_class_category;
3637 close C2;
3638
3639 if l_class_category <> p_class_category then
3640 if (p_debug_mode = 'Y') then
3641 pa_debug.debug('Validate classification PVT: Calling API to check if only one catg code can be entered');
3642 end if;
3643 PA_PROJECTS_MAINT_UTILS.CHECK_CLASS_CATG_ONE_ONLY_CODE(
3644 p_object_id,
3645 p_object_type,
3646 p_class_category,
3647 l_return_status,
3648 l_error_msg_code);
3649
3650 IF l_return_status = 'E'
3651 THEN
3652 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3653 p_msg_name =>l_error_msg_code);
3654 END IF;
3655 END IF;
3656
3657 --dbms_output.put_line('CHECK_DUPLICATE_CLASS_CATG '||l_error_msg_code||'Status :'||l_return_status);
3658
3659 if (p_debug_mode = 'Y') then
3660 pa_debug.debug('Validate classification PVT: Calling API to check if the catg code entered is a duplicate');
3661 end if;
3662 PA_PROJECTS_MAINT_UTILS.CHECK_DUPLICATE_CLASS_CATG(
3663 p_object_id,
3664 p_object_type,
3665 p_class_category,
3666 p_class_code,
3667 l_return_status,
3668 l_error_msg_code);
3669 IF l_return_status = 'E'
3670 THEN
3671 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3672 p_msg_name =>l_error_msg_code);
3673 END IF;
3674 --dbms_output.put_line('CHECK_DUPLICATE_CLASS_CATG '||l_error_msg_code||'Status :'||l_return_status);
3675
3676
3677 ELSIF p_action = 'DELETE' then
3678
3679 if (p_debug_mode = 'Y') then
3680 pa_debug.debug('Validate classification PVT: Calling API to check if the catg code entered can be deleted');
3681 end if;
3682 PA_PROJECTS_MAINT_UTILS.CHECK_CLASS_CATG_CAN_DELETE
3683 (p_object_id,
3684 p_object_type,
3685 p_class_category,
3686 l_return_status,
3687 l_error_msg_code);
3688 IF l_return_status = 'E'
3689 THEN
3690 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3691 p_msg_name => l_error_msg_code);
3692 END IF;
3693
3694 END IF;
3695
3696 --dbms_output.put_line('SUCCESSFUL VALIDATION OVER ... '||l_error_msg_code||'Status :'||l_return_status);
3697
3698 EXCEPTION
3699 when FND_API.G_EXC_UNEXPECTED_ERROR then
3700 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3701 p_procedure_name => 'VALIDATE_CLASSIFICATIONS',
3702 p_error_text => SUBSTRB(SQLERRM,1,240));
3703 WHEN OTHERS THEN
3704 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3705 p_procedure_name => 'VALIDATE_CLASSIFICATIONS',
3706 p_error_text => SUBSTRB(SQLERRM,1,240));
3707 raise;
3708
3709 END VALIDATE_CLASSIFICATIONS;
3710
3711 -- API name : delete_classifications
3712 -- Type : Public
3713 -- Pre-reqs : None.
3714 -- Parameters :
3715 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
3716 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
3717 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
3718 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
3719 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
3720 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
3721 -- p_object_id IN NUMBER Required
3722 -- p_object_type IN VARCHAR2 Required
3723 -- p_class_category IN VARCHAR2 Required
3724 -- p_class_code IN VARCHAR2 Required
3725 -- p_record_version_number IN NUMBER Optional Default = FND_API.G_MISS_NUM
3726 -- x_return_status OUT VARCHAR2 Required
3727 -- x_msg_count OUT NUMBER Required
3728 -- x_msg_data OUT VARCHAR2 Required
3729 --
3730 -- History
3731 --
3732 -- 18-AUG-2000 -- Sakthi/William - Created.
3733 --
3734 --
3735 PROCEDURE DELETE_CLASSIFICATIONS
3736 (
3737 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
3738 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
3739 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3740 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
3741 p_debug_mode IN VARCHAR2 := 'N',
3742 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
3743 p_object_id IN NUMBER ,
3744 p_object_type IN VARCHAR2 ,
3745 p_class_category IN VARCHAR2 ,
3746 p_class_code IN VARCHAR2 ,
3747 p_record_version_number IN NUMBER := FND_API.G_MISS_NUM ,
3748 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
3749 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
3750 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3751 IS
3752
3753 l_dummy VARCHAR2(1);
3754 l_delete_flag VARCHAR2(1) := 'N';
3755 l_return_status VARCHAR2(250);
3756 l_error_msg_code VARCHAR2(250);
3757 l_msg_count NUMBER;
3758 l_msg_index_out NUMBER;
3759 l_msg_data VARCHAR2(250);
3760 l_data VARCHAR2(250);
3761 l_row_id VARCHAR2(250);
3762 l_code_percentage NUMBER;
3763
3764 -- anlee
3765 -- Ext Attribute changes
3766 -- Bug 2904327
3767 CURSOR get_category_id
3768 IS
3769 SELECT class_category_id
3770 FROM PA_CLASS_CATEGORIES
3771 WHERE class_category = p_class_category;
3772
3773 CURSOR get_code_id
3774 IS
3775 SELECT class_code_id
3776 FROM PA_CLASS_CODES
3777 WHERE class_category = p_class_category
3778 AND class_code = p_class_code;
3779
3780 l_category_id NUMBER;
3781 l_code_id NUMBER;
3782 -- anlee end of changes
3783
3784 BEGIN
3785
3786 IF p_commit = FND_API.G_TRUE THEN
3787 SAVEPOINT delete_classifications_pvt;
3788 END IF;
3789
3790 x_return_status := FND_API.G_RET_STS_SUCCESS;
3791
3792 if (p_debug_mode = 'Y') then
3793 pa_debug.debug('Delete classification PVT: locking record for deletion');
3794 end if;
3795 if p_validate_only <> FND_API.G_TRUE then
3796 BEGIN
3797 SELECT rowid INTO l_row_id
3798 FROM pa_project_classes
3799 WHERE object_id = p_object_id
3800 AND object_type = p_object_type
3801 AND class_category = p_class_category
3802 and class_code = p_class_code
3803 and record_version_number = p_record_version_number
3804 FOR UPDATE OF class_code NOWAIT;
3805 EXCEPTION WHEN TIMEOUT_ON_RESOURCE THEN
3806 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3807 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
3808 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
3809 x_return_status := 'E' ;
3810 WHEN NO_DATA_FOUND THEN
3811 if p_calling_module = 'FORM' then
3812 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
3813 p_msg_name => 'FORM_RECORD_CHANGED');
3814 x_msg_data := 'FORM_RECORD_CHANGED';
3815 else
3816 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3817 p_msg_name => 'PA_XC_RECORD_CHANGED');
3818 x_msg_data := 'PA_XC_RECORD_CHANGED';
3819 end if;
3820 x_return_status := 'E' ;
3821 WHEN OTHERS THEN
3822 IF SQLCODE = -54 THEN
3823 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3824 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
3825 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
3826 x_return_status := 'E' ;
3827 ELSE
3828 raise;
3829 END IF;
3830 END;
3831 ELSE
3832 BEGIN
3833 SELECT rowid INTO l_row_id
3834 FROM pa_project_classes
3835 WHERE object_id = p_object_id
3836 AND object_type = p_object_type
3837 AND class_category = p_class_category
3838 and class_code = p_class_code
3839 and record_version_number = p_record_version_number;
3840 EXCEPTION
3841 WHEN NO_DATA_FOUND THEN
3842 if p_calling_module = 'FORM' then
3843 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
3844 p_msg_name => 'FORM_RECORD_CHANGED');
3845 x_msg_data := 'FORM_RECORD_CHANGED';
3846 else
3847 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3848 p_msg_name => 'PA_XC_RECORD_CHANGED');
3849 x_msg_data := 'PA_XC_RECORD_CHANGED';
3850 end if;
3851 x_return_status := 'E' ;
3852 WHEN OTHERS THEN
3853 IF SQLCODE = -54 THEN
3854 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3855 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
3856 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
3857 x_return_status := 'E' ;
3858 ELSE
3859 raise;
3860 END IF;
3861 END;
3862
3863 END IF;
3864 l_msg_count := FND_MSG_PUB.count_msg;
3865
3866 IF l_msg_count > 0 THEN
3867 x_msg_count := l_msg_count;
3868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3869 RAISE FND_API.G_EXC_ERROR;
3870 END IF;
3871
3872 if p_validation_level > 0 then
3873 pa_debug.G_err_stage := 'Calling validate_classifications';
3874 if (p_debug_mode = 'Y') then
3875 pa_debug.debug('Delete classification PVT: Calling API to validate classfications');
3876 end if;
3877 PA_PROJECTS_MAINT_PVT.VALIDATE_CLASSIFICATIONS
3878 (
3879 p_validation_level => p_validation_level,
3880 p_calling_module => p_calling_module,
3881 p_action => 'DELETE',
3882 p_debug_mode => p_debug_mode,
3883 p_max_msg_count => p_max_msg_count,
3884 p_object_id => p_object_id,
3885 p_object_type => p_object_type,
3886 p_class_category => p_class_category ,
3887 p_class_code => p_class_Code,
3888 p_code_percentage => l_code_percentage,
3889 p_rowid => l_row_id);
3890
3891 l_msg_count := FND_MSG_PUB.count_msg;
3892
3893 IF l_msg_count > 0 THEN
3894 x_msg_count := l_msg_count;
3895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3896 RAISE FND_API.G_EXC_ERROR;
3897 END IF;
3898 END IF;
3899 IF NOT FND_API.TO_BOOLEAN(p_validate_only)
3900 THEN
3901 if (p_debug_mode = 'Y') then
3902 pa_debug.debug('Delete classification PVT: Calling table handler to delete classfications record');
3903 end if;
3904 pa_project_classes_pkg.delete_row
3905 ( l_row_id);
3906
3907 -- anlee
3908 -- Ext Attribute changes
3909 -- Bug 2904327
3910
3911 OPEN get_category_id;
3912 FETCH get_category_id INTO l_category_id;
3913 CLOSE get_category_id;
3914
3915 PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
3916 p_validate_only => FND_API.G_FALSE
3917 ,p_project_id => p_object_id
3918 ,p_old_classification_id => l_category_id
3919 ,p_classification_type => 'CLASS_CATEGORY'
3920 ,x_return_status => l_return_status
3921 ,x_msg_count => l_msg_count
3922 ,x_msg_data => l_msg_data );
3923
3924 l_msg_count := FND_MSG_PUB.count_msg;
3925
3926 IF l_msg_count > 0 THEN
3927 x_msg_count := l_msg_count;
3928 x_return_status := 'E';
3929 RAISE FND_API.G_EXC_ERROR;
3930 END IF;
3931
3932
3933 OPEN get_code_id;
3934 FETCH get_code_id INTO l_code_id;
3935 CLOSE get_code_id;
3936
3937 PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
3938 p_validate_only => FND_API.G_FALSE
3939 ,p_project_id => p_object_id
3940 ,p_old_classification_id => l_code_id
3941 ,p_classification_type => 'CLASS_CODE'
3942 ,x_return_status => l_return_status
3943 ,x_msg_count => l_msg_count
3944 ,x_msg_data => l_msg_data );
3945
3946 l_msg_count := FND_MSG_PUB.count_msg;
3947
3948 IF l_msg_count > 0 THEN
3949 x_msg_count := l_msg_count;
3950 x_return_status := 'E';
3951 RAISE FND_API.G_EXC_ERROR;
3952 END IF;
3953
3954 -- anlee end of changes
3955 END IF;
3956
3957 IF FND_API.TO_BOOLEAN(P_COMMIT)
3958 THEN
3959 COMMIT WORK;
3960 END IF;
3961
3962 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3963 IF p_commit = FND_API.G_TRUE THEN
3964 ROLLBACK TO delete_classifications_pvt;
3965 END IF;
3966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3967 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
3968 p_procedure_name => 'DELETE_CLASSIFICATIONS',
3969 p_error_text => SUBSTRB(SQLERRM,1,240));
3970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3971
3972 WHEN FND_API.G_EXC_ERROR THEN
3973 IF p_commit = FND_API.G_TRUE THEN
3974 ROLLBACK TO delete_classifications_pvt;
3975 END IF;
3976 x_return_status := 'E';
3977
3978 WHEN OTHERS THEN
3979 IF p_commit = FND_API.G_TRUE THEN
3980 ROLLBACK TO prm_create_project;
3981 END IF;
3982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3983 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
3984 p_procedure_name => 'DELETE_CLASSIFICATIONS',
3985 p_error_text => SUBSTRB(SQLERRM,1,240));
3986 raise;
3987
3988 END DELETE_CLASSIFICATIONS;
3989
3990 -- API name : Validate_Project_Info
3991 -- Type : Validation
3992 -- Pre-reqs : None.
3993 -- Parameters :
3994 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
3995 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
3996 -- p_action IN VARCHAR2 Optional Default = 'INSERT', 'UPDATE', 'DELETE'
3997 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
3998 -- p_project_id IN NUMBER Required
3999 -- p_project_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4000 -- p_project_number IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4001 -- p_project_type IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4002 -- p_description IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4003 -- p_project_status_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4004 -- p_public_sector_flag IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4005 -- p_carrying_out_organization_id IN NUMBER Optional
4006 -- Default = FND_API.G_MISS_NUM
4007 -- p_start_date IN DATE Optional Default = FND_API.G_MISS_DATE
4008 -- p_completion_date IN DATE Optional Default = FND_API.G_MISS_DATE
4009 -- p_territory_code IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4010 -- p_country IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4011 -- p_location_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
4012 -- p_state_region IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4013 -- p_city IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
4014 -- p_calendar_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
4015 -- p_work_type_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
4016 -- p_role_list_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
4017 -- p_cost_job_group_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
4018 -- p_bill_job_group_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
4019 -- p_probability_member_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
4020 -- p_project_value IN NUMBER Optional Default = FND_API.G_MISS_NUM
4021 -- p_expected_approval_date IN DATE Optional Default = FND_API.G_MISS_DATE
4022 -- p_record_version_number IN NUMBER Required
4023 -- p_project_start_date IN DATE Optional Default = FND_API.G_MISS_DATE
4024 -- p_project_finish_date IN DATE Optional Default = FND_API.G_MISS_DATE
4025 -- p_long_name IN VARCHAR2 Optional Default = NULL
4026 --
4027 -- History
4028 --
4029 -- 18-AUG-2000 -- Sakthi/William - Created.
4030 --
4031 --
4032 PROCEDURE Validate_Project_Info
4033 (
4034 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4035 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
4036 p_action IN VARCHAR2 := 'UPDATE' ,
4037 p_debug_mode IN VARCHAR2 := 'N' ,
4038 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
4039 p_project_id IN NUMBER ,
4040 p_project_name IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4041 p_project_number IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4042 p_project_type IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4043 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4044 p_project_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4045 p_public_sector_flag IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4046 p_carrying_out_organization_id IN NUMBER := FND_API.G_MISS_NUM ,
4047 p_start_date IN DATE := FND_API.G_MISS_DATE ,
4048 p_completion_date IN DATE := FND_API.G_MISS_DATE ,
4049 p_territory_code IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4050 p_country IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4051 p_location_id IN NUMBER := FND_API.G_MISS_NUM ,
4052 p_state_region IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4053 p_city IN VARCHAR2 := FND_API.G_MISS_CHAR ,
4054 p_calendar_id IN NUMBER := FND_API.G_MISS_NUM ,
4055 p_work_type_id IN NUMBER := FND_API.G_MISS_NUM ,
4056 p_role_list_id IN NUMBER := FND_API.G_MISS_NUM ,
4057 p_cost_job_group_id IN NUMBER := FND_API.G_MISS_NUM ,
4058 p_bill_job_group_id IN NUMBER := FND_API.G_MISS_NUM ,
4059 p_probability_member_id IN NUMBER := FND_API.G_MISS_NUM ,
4060 p_project_value IN NUMBER := FND_API.G_MISS_NUM ,
4061 p_expected_approval_date IN DATE := FND_API.G_MISS_DATE ,
4062 p_record_version_number IN NUMBER ,
4063 -- anlee
4064 -- Dates changes
4065 p_target_start_date IN DATE := FND_API.G_MISS_DATE ,
4066 p_target_finish_date IN DATE := FND_API.G_MISS_DATE ,
4067 -- End of changes
4068 -- anlee
4069 -- Project Long Name changes
4070 p_long_name IN VARCHAR2 DEFAULT NULL )
4071 -- end of changes
4072 IS
4073
4074 l_dummy VARCHAR2(1) := 'N';
4075 l_project_id pa_projects.project_id%TYPE;
4076 l_return_status VARCHAR2(1);
4077 l_error_msg_code VARCHAR2(250);
4078 l_msg_count NUMBER;
4079 l_msg_data VARCHAR2(250);
4080 l_err_code VARCHAR2(250);
4081 l_err_stage VARCHAR2(250);
4082 l_err_stack VARCHAR2(250);
4083 l_data VARCHAR2(250);
4084 l_msg_index_out NUMBER;
4085
4086 -- Start date and Completion date validation
4087
4088 l_project_start_date DATE;
4089 l_project_completion_date DATE;
4090 l_update_start_date_flag VARCHAR2(1);
4091 l_update_end_date_flag VARCHAR2(1);
4092
4093 -- Project Number Validation
4094
4095 l_proj_num_numeric NUMBER;
4096
4097 -- Organization Name Validation
4098
4099 l_carrying_out_organization_id NUMBER;
4100 l_calendar_id NUMBER;
4101 function_flag VARCHAR2(1);
4102 t_project_type_class_code VARCHAR2(50);
4103 l_calendar_name VARCHAR2(250);
4104
4105 -- Project status Validation
4106
4107 l_closing_project VARCHAR2(1);
4108 l_project_status_changed VARCHAR2(1);
4109 l_reopening_project VARCHAR2(1);
4110 l_verify_ok_flag VARCHAR2(1);
4111 l_wf_enabled_flag VARCHAR2(1);
4112 l_warnings_only_flag VARCHAR2(1);
4113 x_status_changeable VARCHAR2(1);
4114 l_err_msg_count NUMBER;
4115 l_wf_status_code VARCHAR2(30);
4116
4117 -- Location Validation
4118
4119 x_location_id NUMBER;
4120 x_rowid VARCHAR2(25);
4121 x_country_name VARCHAR2(250);
4122 x_country_code VARCHAR2(25);
4123 x_city_name VARCHAR2(250);
4124 x_region_name VARCHAR2(250);
4125 l_error_message_code VARCHAR2(250);
4126
4127 --project type validation
4128 l_status_code NUMBER ;
4129
4130 --needed to check whether given project name is unique
4131 CURSOR l_project_name_unique_csr( p_project_name VARCHAR2)
4132 IS
4133 SELECT 'x'
4134 FROM pa_projects_all
4135 WHERE name = p_project_name;
4136
4137 --needed to check whether given project number is unique
4138 CURSOR l_project_number_unique_csr( p_project_number VARCHAR2)
4139 IS
4140 SELECT 'x'
4141 FROM pa_projects_all
4142 WHERE segment1 = p_project_number;
4143
4144 CURSOR l_project_details_csr( p_project_id NUMBER)
4145 IS
4146 SELECT *
4147 FROM pa_projects_all p -- Bug#3807805 : Modifed pa_projects to pa_projects_all
4148 WHERE p.project_id = p_project_id
4149 FOR UPDATE OF p.record_version_number;
4150
4151 Cursor c1 (x_country_code varchar2,
4152 x_state_region varchar2,
4153 x_city varchar2)
4154 IS
4155 Select location_id
4156 from pa_locations
4157 where country_code = x_country_code
4158 and region = x_state_region
4159 and city = x_city;
4160
4161 Cursor c2 (x_country_name Varchar2)
4162 Is
4163 select territory_code
4164 from fnd_territories_tl
4165 where territory_short_name = x_country_name;
4166
4167 cursor prob_per (c_probability_member_id number)
4168 IS
4169 select probability_percentage
4170 from pa_probability_members
4171 where probability_member_id = c_probability_member_id;
4172
4173 -- anlee
4174 -- Added validations for bug 2327927
4175 -- MOAC Changes: Bug 4363092 - removed nvl used with org_id
4176 cursor get_project_type_info (c_project_type VARCHAR2)
4177 IS
4178 select *
4179 from pa_project_types_all -- Bug#3807805 : Modifed pa_project_types to pa_project_types_all
4180 where project_type = c_project_type
4181 and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
4182 -- anlee end of changes
4183
4184 -- MOAC Changes: Bug 4363092 - removed nvl used with org_id
4185 cursor get_unassigned_time (c_project_type VARCHAR2)
4186 IS
4187 Select unassigned_time
4188 from pa_project_types_all -- Bug#3807805 : Modifed pa_project_types to pa_project_types_all
4189 where project_type = c_project_type
4190 and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
4191
4192 l_unassigned_time pa_project_types.unassigned_time%TYPE;
4193 l_project_old_rec pa_projects_all%ROWTYPE; -- Bug#3807805 : Modifed pa_projects to pa_projects_all
4194 l_project_type_rec get_project_type_info%ROWTYPE;
4195 l_probability_member_id number;
4196
4197 -- anlee
4198 -- Added validations for bug 2327927
4199 -- MOAC Changes: Bug 4363092 - removed nvl used with org_id
4200 cursor get_project_type_class_code (c_project_type VARCHAR2)
4201 IS
4202 Select project_type_class_code
4203 from pa_project_types_all -- Bug#3807805 : Modifed pa_project_types to pa_project_types_all
4204 where project_type = c_project_type
4205 and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
4206
4207 cursor get_billable_cap_flag (c_work_type_id NUMBER)
4208 IS
4209 select billable_capitalizable_flag
4210 from pa_work_types_vl
4211 where work_type_id = c_work_type_id;
4212
4213 cursor get_dist_rule (c_project_type VARCHAR2)
4214 IS
4215 select distribution_rule
4216 from pa_project_type_distributions
4217 where project_type = c_project_type
4218 and default_flag = 'Y';
4219
4220 cursor get_min_task_start_date
4221 IS
4222 select min(start_date) task_start_date
4223 from pa_tasks
4224 where project_id = p_project_id
4225 and start_date is not null;
4226
4227 cursor get_max_task_completion_date
4228 IS
4229 select max(completion_date) task_completion_date
4230 from pa_tasks
4231 where project_id = p_project_id
4232 and completion_date is not null;
4233
4234 l_min_task_start_date pa_tasks.start_date%TYPE;
4235 l_max_task_completion_date pa_tasks.completion_date%TYPE;
4236 l_start_date_error BOOLEAN := FALSE;
4237 l_completion_date_error BOOLEAN := FALSE;
4238 l_distribution_rule pa_project_type_distributions.distribution_rule%TYPE;
4239 l_billable_capitalizable_flag pa_work_types_vl.billable_capitalizable_flag%TYPE;
4240 l_old_project_type_class_code pa_project_types.project_type_class_code%TYPE;
4241 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
4242 l_project_type_error BOOLEAN := FALSE;
4243 -- anlee end of changes
4244
4245 -- anlee project actions changes
4246 l_delete_project_allowed VARCHAR2(1) := 'Y';
4247 l_update_proj_num_allowed VARCHAR2(1) := 'Y';
4248 l_update_proj_name_allowed VARCHAR2(1) := 'Y';
4249 l_update_proj_desc_allowed VARCHAR2(1) := 'Y';
4250 l_update_proj_dates_allowed VARCHAR2(1) := 'Y';
4251 l_update_proj_status_allowed VARCHAR2(1) := 'Y';
4252 l_update_proj_manager_allowed VARCHAR2(1) := 'Y';
4253 l_update_proj_org_allowed VARCHAR2(1) := 'Y';
4254 l_add_task_allowed VARCHAR2(1) := 'Y';
4255 l_delete_task_allowed VARCHAR2(1) := 'Y';
4256 l_update_task_num_allowed VARCHAR2(1) := 'Y';
4257 l_update_task_name_allowed VARCHAR2(1) := 'Y';
4258 l_update_task_dates_allowed VARCHAR2(1) := 'Y';
4259 l_update_task_desc_allowed VARCHAR2(1) := 'Y';
4260 l_update_parent_task_allowed VARCHAR2(1) := 'Y';
4261 l_update_task_org_allowed VARCHAR2(1) := 'Y';
4262
4263 l_pm_product_code pa_projects_all.pm_product_code%TYPE;
4264
4265 CURSOR get_product_code IS
4266 SELECT pm_product_code
4267 FROM PA_PROJECTS_ALL
4268 WHERE project_id = p_project_id;
4269 -- anlee end of changes
4270 --bug 3068781 maansari
4271 l_multi_currency_billing_flag VARCHAR2(1);
4272 l_PROJFUNC_BIL_RATE_DATE_CODE VARCHAR2(30);
4273 l_PROJECT_BIL_RATE_DATE_CODE VARCHAR2(30);
4274 l_FUNDING_RATE_DATE_CODE VARCHAR2(30);
4275 l_PROJFUNC_BIL_RATE_TYPE VARCHAR2(30);
4276 l_PROJECT_BIL_RATE_TYPE VARCHAR2(30);
4277 l_FUNDING_RATE_TYPE VARCHAR2(30);
4278 l_BTC_COST_BASE_REV_CODE VARCHAR2(90);
4279 --end bug 3068781
4280 BEGIN
4281
4282 --dbms_output.put_line('Starts here Validate_Basic_Info ... ');
4283
4284 OPEN l_project_details_csr (p_project_id);
4285 FETCH l_project_details_csr INTO l_project_old_rec;
4286
4287 --dbms_output.put_line('Check p_validation_level ... '||to_char(p_validation_level));
4288
4289 if (p_validation_level > 0) then
4290
4291 --if (p_calling_module like ('BASIC_INFO')) then
4292 if (p_calling_module = 'BASIC_INFO' OR p_calling_module = 'SETUP_PAGE') then -- bug 7204572 added one more condition to IF
4293
4294 if PA_PROJECT_REQUEST_PVT.G_ORG_ID is null and p_project_type is not null then -- Added the if block for Bug#3807805
4295 select org_id into PA_PROJECT_REQUEST_PVT.G_ORG_ID from pa_projects where project_id = p_project_id;--Bug 8882288
4296 end if;
4297
4298 -- anlee project actions changes
4299 OPEN get_product_code;
4300 FETCH get_product_code INTO l_pm_product_code;
4301 CLOSE get_product_code;
4302
4303 if l_pm_product_code IS NOT NULL then
4304
4305 PA_PM_CONTROLS.Get_Project_Actions_Allowed (
4306 p_pm_product_code => l_pm_product_code
4307 ,p_delete_project_allowed => l_delete_project_allowed
4308 ,p_update_proj_num_allowed => l_update_proj_num_allowed
4309 ,p_update_proj_name_allowed => l_update_proj_name_allowed
4310 ,p_update_proj_desc_allowed => l_update_proj_desc_allowed
4311 ,p_update_proj_dates_allowed => l_update_proj_dates_allowed
4312 ,p_update_proj_status_allowed => l_update_proj_status_allowed
4313 ,p_update_proj_manager_allowed => l_update_proj_manager_allowed
4314 ,p_update_proj_org_allowed => l_update_proj_org_allowed
4315 ,p_add_task_allowed => l_add_task_allowed
4316 ,p_delete_task_allowed => l_delete_task_allowed
4317 ,p_update_task_num_allowed => l_update_task_num_allowed
4318 ,p_update_task_name_allowed => l_update_task_name_allowed
4319 ,p_update_task_dates_allowed => l_update_task_dates_allowed
4320 ,p_update_task_desc_allowed => l_update_task_desc_allowed
4321 ,p_update_parent_task_allowed => l_update_parent_task_allowed
4322 ,p_update_task_org_allowed => l_update_task_org_allowed
4323 ,p_error_code => l_err_code
4324 ,p_error_stack => l_err_stack
4325 ,p_error_stage => l_err_stage );
4326
4327 IF l_err_code <> 0 THEN
4328 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4329 p_msg_name => l_err_stage);
4330 END IF;
4331 end if;
4332 -- anlee end of changes
4333
4334 if (p_debug_mode = 'Y') then
4335 pa_debug.debug('Validate_project_info PVT: Basic info validation');
4336 end if;
4337 --PROJECT NUMBER
4338 -- Check for implementation defined Project number generation mode
4339 -- If project number generation is automatic then ignore the input
4340 -- project number ,since it cannot be modified.Hence,the following
4341 -- checks will be done only if the mode was 'MANUAL'
4342
4343 IF (p_debug_mode = 'Y')
4344 THEN
4345 pa_debug.debug('Validate_project_info PVT: project number validation');
4346 END IF;
4347
4348 IF PA_PROJECT_UTILS.GetProjNumMode = 'MANUAL' THEN
4349 IF p_project_number IS NOT NULL
4350 THEN
4351 IF p_project_number <> l_project_old_rec.segment1
4352 THEN
4353
4354 -- anlee project actions changes
4355 IF l_update_proj_num_allowed = 'N' THEN
4356 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4357 p_msg_name => 'PA_PM_PROJ_NUM_ERR');
4358 ELSE
4359
4360 pa_project_utils.change_proj_num_ok
4361 (p_project_id,
4362 l_err_code,
4363 l_err_stage,
4364 l_err_stack );
4365
4366 IF l_err_code <> 0
4367 THEN
4368 IF NOT pa_project_pvt.check_valid_message (l_err_stage)
4369 THEN
4370 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4371 p_msg_name => 'PA_CANT_CHANGE_PROJ_NUM');
4372 ELSE
4373 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4374 p_msg_name => l_err_stage);
4375 END IF; -- pa_project_pvt.check_valid_message
4376
4377 -- Check for project number being numeric if project number type is
4378 -- defined as numeric
4379
4380 BEGIN
4381 IF PA_PROJECT_UTILS.GetProjNumType = 'NUMERIC' THEN
4382 l_proj_num_numeric :=
4383 TO_NUMBER(p_project_number);
4384 END IF; -- PA_PROJECT_UTILS.GetProjNumType
4385 EXCEPTION
4386 WHEN VALUE_ERROR THEN
4387 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
4388 THEN
4389 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4390 p_msg_name => 'PA_PR_NUMERIC_NUM_REQ');
4391 END IF; -- FND_MSG_PUB.check_msg_level
4392 END ;
4393
4394 END IF; -- l_err_code
4395
4396 OPEN l_project_number_unique_csr( p_project_number );
4397 FETCH l_project_number_unique_csr INTO l_DUMMY;
4398 CLOSE l_project_number_unique_csr; -- Added for Bug#3876261
4399
4400 if (pa_project_utils.check_unique_project_number (p_project_number, NULL) = 0)
4401 then
4402 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4403 p_msg_name => 'PA_PR_EPR_PROJ_NUM_NOT_UNIQUE');
4404 END IF;
4405
4406 END IF; -- l_update_proj_num_allowed
4407 END IF; -- p_project_number
4408
4409 END IF; -- p_project_number
4410
4411 ELSIF PA_PROJECT_UTILS.GetProjNumMode = 'AUTOMATIC' THEN
4412 -- Cannot modify project number if numbering mode is AUTOMATIC
4413 IF p_project_number <> l_project_old_rec.segment1 THEN
4414 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4415 p_msg_name => 'PA_CANT_CHANGE_PROJ_NUM');
4416 END IF;
4417
4418 END IF; -- PA_PROJECT_UTILS.GetProjNumMode
4419
4420 --dbms_output.put_line('Check PROJECT NAME ... ');
4421
4422 --PROJECT NAME
4423
4424 IF (p_debug_mode = 'Y')
4425 THEN
4426 pa_debug.debug('Validate_project_info PVT: project name validation');
4427 END IF;
4428
4429 IF p_project_name IS NOT NULL
4430 THEN
4431 IF p_project_name <> l_project_old_rec.name
4432 THEN
4433
4434 -- anlee project actions changes
4435 IF l_update_proj_name_allowed = 'N' THEN
4436 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4437 p_msg_name => 'PA_PM_PROJ_NAME_ERR');
4438 ELSE
4439
4440 if (pa_project_utils.check_unique_project_name (p_project_name, NULL) = 0)
4441 then
4442 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4443 p_msg_name => 'PA_PR_EPR_PROJ_NAME_NOT_UNIQUE');
4444 END IF; -- l_project_name_unique_csr
4445 END IF; -- l_update_proj_name_allowed
4446
4447 END IF; -- p_project_in.project_name
4448
4449 END IF; -- p_project_in.project_name
4450
4451 -- anlee
4452 -- Project Long Name changes
4453
4454 IF (p_debug_mode = 'Y')
4455 THEN
4456 pa_debug.debug('Validate_project_info PVT: project long name validation');
4457 END IF;
4458
4459 IF p_long_name IS NOT NULL
4460 THEN
4461 IF p_long_name <> l_project_old_rec.long_name
4462 THEN
4463
4464 if (pa_project_utils.check_unique_long_name (p_long_name, NULL) = 0)
4465 then
4466 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4467 p_msg_name => 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE');
4468 END IF;
4469
4470 END IF;
4471
4472 END IF;
4473
4474 -- End of changes
4475
4476 -- PROJECT DESCRIPTION
4477 -- anlee project actions changes
4478 IF p_description <> l_project_old_rec.description
4479 THEN
4480 if l_update_proj_desc_allowed = 'N' then
4481 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4482 p_msg_name => 'PA_PM_PROJ_DESC_ERR');
4483 end if;
4484 ELSIF (p_description is null and l_project_old_rec.description is not null) OR
4485 (p_description is not null and l_project_old_rec.description is null) THEN
4486 if l_update_proj_desc_allowed = 'N' then
4487 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4488 p_msg_name => 'PA_PM_PROJ_DESC_ERR');
4489 end if;
4490 END IF;
4491 -- anlee end of changes
4492
4493 -- dbms_output.put_line('Check Project Type Changes ... ');
4494
4495 -- PROJECT TYPE CHANGES
4496 -- anlee
4497 -- Added validations for bug 2327927
4498 IF (p_debug_mode = 'Y')
4499 THEN
4500 pa_debug.debug('Validate_project_info PVT: project type validation');
4501 END IF;
4502
4503 if p_project_Type is not null and p_project_type <> l_project_old_rec.project_type Then
4504
4505 -- This procedure checks if a project has CDLs,Rev or
4506 -- Draft invoices.If project has any of
4507 -- these information, then it's not ok to change the project
4508 -- type or org and specific reason will be returned.
4509 --
4510 -- If it's ok to change project type or org,
4511 -- the x_err_code will be 0.
4512 --
4513 pa_project_utils.change_pt_org_ok(p_project_id,
4514 l_err_code,
4515 l_err_stage,
4516 l_err_stack);
4517 if l_err_code <> 0 Then
4518 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4519 p_msg_name => 'PA_PR_CANT_CHG_PROJ_TYPE');
4520 else
4521 -- Check whether funding exists (if it exists cannot change from contract
4522 -- to non-contract project)
4523
4524 OPEN get_project_type_class_code(p_project_type);
4525 FETCH get_project_type_class_code INTO l_project_type_class_code;
4526 CLOSE get_project_type_class_code;
4527
4528 OPEN get_project_type_class_code(l_project_old_rec.project_type);
4529 FETCH get_project_type_class_code INTO l_old_project_type_class_code;
4530 CLOSE get_project_type_class_code;
4531
4532 if (l_project_type_class_code <> 'CONTRACT')
4533 AND (nvl(l_old_project_type_class_code, l_project_type_class_code) <> l_project_type_class_code) then
4534
4535 if (pa_project_utils.check_proj_funding (p_project_id)) <> 0 Then
4536 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4537 p_msg_name => 'PA_PR_CANT_CHG_DIR_TO_INDIR');
4538 l_project_type_error := TRUE;
4539 end if;
4540 end if;
4541 --maansari bug 3068806
4542 --moved the following code from below
4543 OPEN get_project_type_info (p_project_type);
4544 FETCH get_project_type_info INTO l_project_type_rec;
4545 CLOSE get_project_type_info;
4546
4547 --bug#2984611
4548 IF l_project_type_rec.cc_prvdr_flag = 'Y'
4549 THEN
4550 l_status_code := pa_project_utils.check_ic_proj_type_allowed(p_project_id,'Y');
4551 IF nvl(l_status_code ,0) <> 0 THEN
4552 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4553 p_msg_name => 'PA_IC_PT_NOT_ALLOWED');
4554 l_project_type_error := TRUE;
4555 ELSE
4556 IF l_project_old_rec.multi_currency_billing_flag = 'Y' THEN
4557 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4558 p_msg_name => 'PA_PR_CANT_CHG_IC_BIL_PT');
4559 l_project_type_error := TRUE;
4560
4561 END IF ;
4562 END IF ;
4563 END IF ;
4564 --bug#2984611
4565
4566 /**********
4567 The commented validation has been moved above as part of fix for bug#2984611
4568 IF l_project_old_rec.project_type IS NOT NULL AND
4569 p_project_type <> l_project_old_rec.project_type AND
4570 l_project_type_rec.cc_prvdr_flag = 'Y' AND
4571 l_project_old_rec.multi_currency_billing_flag = 'Y'
4572 THEN
4573 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4574 p_msg_name => 'PA_PR_CANT_CHG_IC_BIL_PT');
4575 l_project_type_error := TRUE;
4576
4577 END IF;
4578 *************/
4579 --end maansari bug 3068806
4580
4581 if l_project_type_error = FALSE then
4582
4583 /* Moved this up for bug 3068806
4584 OPEN get_project_type_info (p_project_type);
4585 FETCH get_project_type_info INTO l_project_type_rec;
4586 CLOSE get_project_type_info;
4587 */
4588 --bug 3068781
4589
4590 DECLARE
4591 CURSOR cur_job_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
4592 WHERE bill_rate_sch_id = l_project_type_rec.job_bill_rate_schedule_id ;
4593
4594 CURSOR cur_emp_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
4595 WHERE bill_rate_sch_id = l_project_type_rec.emp_bill_rate_schedule_id ;
4596
4597 CURSOR cur_nl_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
4598 WHERE bill_rate_sch_id = l_project_type_rec.non_lab_std_bill_rt_sch_id ;
4599
4600 CURSOR cur_impl IS SELECT default_rate_type FROM pa_implementations;
4601
4602 x_job_rate_sch_currency VARCHAR2(30);
4603 x_emp_rate_sch_currency VARCHAR2(30);
4604 x_nl_rate_sch_currency VARCHAR2(30);
4605 x_default_rate_type VARCHAR2(30);
4606
4607 BEGIN
4608 IF l_project_type_rec.cc_prvdr_flag = 'N' --This is not required if the project type is IC billing. bug 2179904
4609 THEN
4610 OPEN cur_job_cur;
4611 FETCH cur_job_cur INTO x_job_rate_sch_currency ;
4612 CLOSE cur_job_cur;
4613
4614 OPEN cur_emp_cur;
4615 FETCH cur_emp_cur INTO x_emp_rate_sch_currency ;
4616 CLOSE cur_emp_cur;
4617
4618 OPEN cur_nl_cur;
4619 FETCH cur_nl_cur INTO x_nl_rate_sch_currency ;
4620 CLOSE cur_nl_cur;
4621
4622 IF x_job_rate_sch_currency is not Null and
4623 x_job_rate_sch_currency <> l_project_old_rec.projfunc_currency_code
4624 THEN
4625 l_multi_currency_billing_flag := 'Y';
4626 ELSIF x_emp_rate_sch_currency is not Null and
4627 x_emp_rate_sch_currency <> l_project_old_rec.projfunc_currency_code
4628 THEN
4629 l_multi_currency_billing_flag := 'Y';
4630 ELSIF x_nl_rate_sch_currency is not Null and
4631 x_nl_rate_sch_currency <> l_project_old_rec.projfunc_currency_code
4632 THEN
4633 l_multi_currency_billing_flag := 'Y';
4634 END IF;
4635
4636 END IF;
4637
4638 IF l_project_type_rec.cc_prvdr_flag = 'N' AND
4639 l_project_old_rec.multi_currency_billing_flag = 'N' AND
4640 NVL( l_multi_currency_billing_flag, 'N') = 'Y'
4641 THEN
4642 OPEN cur_impl;
4643 FETCH cur_impl INTO x_default_rate_type ;
4644 CLOSE cur_impl;
4645
4646 l_PROJFUNC_BIL_RATE_TYPE := x_default_rate_type;
4647 l_PROJECT_BIL_RATE_TYPE := x_default_rate_type;
4648 l_FUNDING_RATE_TYPE := x_default_rate_type;
4649
4650 l_PROJFUNC_BIL_RATE_DATE_CODE := 'PA_INVOICE_DATE';
4651 l_PROJECT_BIL_RATE_DATE_CODE := 'PA_INVOICE_DATE';
4652 l_FUNDING_RATE_DATE_CODE := 'PA_INVOICE_DATE';
4653 l_BTC_COST_BASE_REV_CODE := 'EXP_TRANS_CURR';
4654
4655 UPDATE pa_project_customers
4656 SET inv_rate_type = x_default_rate_type
4657 WHERE project_id = p_project_id;
4658 ELSE
4659 l_PROJFUNC_BIL_RATE_TYPE := l_project_old_rec.PROJFUNC_BIL_RATE_TYPE;
4660 l_PROJECT_BIL_RATE_TYPE := l_project_old_rec.PROJECT_BIL_RATE_TYPE;
4661 l_FUNDING_RATE_TYPE := l_project_old_rec.FUNDING_RATE_TYPE;
4662
4663 l_PROJFUNC_BIL_RATE_DATE_CODE := l_project_old_rec.PROJFUNC_BIL_RATE_DATE_CODE;
4664 l_PROJECT_BIL_RATE_DATE_CODE := l_project_old_rec.PROJECT_BIL_RATE_DATE_CODE;
4665 l_FUNDING_RATE_DATE_CODE := l_project_old_rec.FUNDING_RATE_DATE_CODE;
4666 l_BTC_COST_BASE_REV_CODE := l_project_old_rec.BTC_COST_BASE_REV_CODE;
4667
4668 END IF;
4669 END;
4670 --end bug 3068781
4671
4672
4673 open get_unassigned_time (p_project_type);
4674 fetch get_unassigned_time into l_unassigned_time;
4675 close get_unassigned_time;
4676
4677 -- Update project attributes from project_type
4678 if nvl(l_unassigned_time,'N') ='Y' then
4679 UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
4680 SET role_list_id = l_project_type_rec.role_list_id,
4681 work_type_id = l_project_type_rec.work_type_id,
4682 cost_ind_rate_sch_id = l_project_type_rec.cost_ind_rate_sch_id,
4683 labor_sch_type = l_project_type_rec.labor_sch_type,
4684 labor_bill_rate_org_id = l_project_type_rec.labor_bill_rate_org_id,
4685 labor_std_bill_rate_schdl = l_project_type_rec.labor_std_bill_rate_schdl,
4686 non_labor_sch_type = l_project_type_rec.non_labor_sch_type,
4687 non_labor_bill_rate_org_id = l_project_type_rec.non_labor_bill_rate_org_id,
4688 non_labor_std_bill_rate_schdl = l_project_type_rec.non_labor_std_bill_rate_schdl,
4689 rev_ind_rate_sch_id = l_project_type_rec.rev_ind_rate_sch_id,
4690 inv_ind_rate_sch_id = l_project_type_rec.inv_ind_rate_sch_id,
4691 labor_invoice_format_id = l_project_type_rec.labor_invoice_format_id,
4692 non_labor_invoice_format_id = l_project_type_rec.non_labor_invoice_format_id,
4693 billing_offset = l_project_type_rec.billing_offset,
4694 billing_cycle_id = l_project_type_rec.billing_cycle_id,
4695 bill_job_group_id = l_project_type_rec.bill_job_group_id,
4696 cost_job_group_id = l_project_type_rec.cost_job_group_id,
4697 emp_bill_rate_schedule_id = l_project_type_rec.emp_bill_rate_schedule_id,
4698 job_bill_rate_schedule_id = l_project_type_rec.job_bill_rate_schedule_id,
4699 project_status_code = l_project_type_rec.def_start_proj_status_code,
4700 --bug 3068781
4701 -- multi_currency_billing_flag = decode(l_project_type_rec.cc_prvdr_flag, 'Y', 'N', multi_currency_billing_flag),
4702 multi_currency_billing_flag = NVL( l_multi_currency_billing_flag,multi_currency_billing_flag ),
4703 PROJFUNC_BIL_RATE_TYPE = l_PROJFUNC_BIL_RATE_TYPE,
4704 PROJECT_BIL_RATE_TYPE = l_PROJECT_BIL_RATE_TYPE,
4705 FUNDING_RATE_TYPE = l_FUNDING_RATE_TYPE,
4706 PROJFUNC_BIL_RATE_DATE_CODE = l_PROJFUNC_BIL_RATE_DATE_CODE,
4707 PROJECT_BIL_RATE_DATE_CODE = l_PROJECT_BIL_RATE_DATE_CODE,
4708 FUNDING_RATE_DATE_CODE = l_FUNDING_RATE_DATE_CODE,
4709 BTC_COST_BASE_REV_CODE = l_BTC_COST_BASE_REV_CODE,
4710 --end bug 3068781
4711 cc_process_labor_flag = 'N',
4712 cc_process_nl_flag = 'N',
4713 nl_tp_schedule_id = null,
4714 labor_tp_schedule_id = null,
4715 nl_tp_fixed_date = null,
4716 labor_tp_fixed_date = null,
4717 -- anlee
4718 -- patchset K changes
4719 revaluate_funding_flag = l_project_type_rec.revaluate_funding_flag,
4720 include_gains_losses_flag = l_project_type_rec.include_gains_losses_flag,
4721 -- End of changes
4722 --PA L Changes 2872708
4723 asset_allocation_method = l_project_type_rec.asset_allocation_method,
4724 capital_event_processing = l_project_type_rec.capital_event_processing,
4725 CINT_RATE_SCH_ID = l_project_type_rec.CINT_RATE_SCH_ID,
4726 --federal changes by sunkalya. Bug#5511353.
4727 date_eff_funds_consumption = nvl(l_project_type_rec.date_eff_funds_consumption,'N')
4728 --federal changes by sunkalya. Bug#5511353.
4729 --End PA L Changes 2872708
4730 WHERE project_id = p_project_id;
4731 else
4732 UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
4733 SET role_list_id = l_project_type_rec.role_list_id,
4734 work_type_id = l_project_type_rec.work_type_id,
4735 -- service_type_code = l_project_type_rec.service_type_code,
4736 cost_ind_rate_sch_id = l_project_type_rec.cost_ind_rate_sch_id,
4737 labor_sch_type = l_project_type_rec.labor_sch_type,
4738 labor_bill_rate_org_id = l_project_type_rec.labor_bill_rate_org_id,
4739 labor_std_bill_rate_schdl = l_project_type_rec.labor_std_bill_rate_schdl,
4740 non_labor_sch_type = l_project_type_rec.non_labor_sch_type,
4741 non_labor_bill_rate_org_id = l_project_type_rec.non_labor_bill_rate_org_id,
4742 non_labor_std_bill_rate_schdl = l_project_type_rec.non_labor_std_bill_rate_schdl,
4743 rev_ind_rate_sch_id = l_project_type_rec.rev_ind_rate_sch_id,
4744 inv_ind_rate_sch_id = l_project_type_rec.inv_ind_rate_sch_id,
4745 labor_invoice_format_id = l_project_type_rec.labor_invoice_format_id,
4746 non_labor_invoice_format_id = l_project_type_rec.non_labor_invoice_format_id,
4747 billing_offset = l_project_type_rec.billing_offset,
4748 billing_cycle_id = l_project_type_rec.billing_cycle_id,
4749 bill_job_group_id = l_project_type_rec.bill_job_group_id,
4750 cost_job_group_id = l_project_type_rec.cost_job_group_id,
4751 emp_bill_rate_schedule_id = l_project_type_rec.emp_bill_rate_schedule_id,
4752 job_bill_rate_schedule_id = l_project_type_rec.job_bill_rate_schedule_id,
4753 project_status_code = l_project_type_rec.def_start_proj_status_code,
4754 --bug 3068781
4755 -- multi_currency_billing_flag = decode(l_project_type_rec.cc_prvdr_flag, 'Y', 'N', multi_currency_billing_flag),
4756 multi_currency_billing_flag = NVL( l_multi_currency_billing_flag,multi_currency_billing_flag ),
4757 PROJFUNC_BIL_RATE_TYPE = l_PROJFUNC_BIL_RATE_TYPE,
4758 PROJECT_BIL_RATE_TYPE = l_PROJECT_BIL_RATE_TYPE,
4759 FUNDING_RATE_TYPE = l_FUNDING_RATE_TYPE,
4760 PROJFUNC_BIL_RATE_DATE_CODE = l_PROJFUNC_BIL_RATE_DATE_CODE,
4761 PROJECT_BIL_RATE_DATE_CODE = l_PROJECT_BIL_RATE_DATE_CODE,
4762 FUNDING_RATE_DATE_CODE = l_FUNDING_RATE_DATE_CODE,
4763 BTC_COST_BASE_REV_CODE = l_BTC_COST_BASE_REV_CODE,
4764 --bug end 3068781
4765 -- anlee
4766 -- patchset K changes
4767 revaluate_funding_flag = l_project_type_rec.revaluate_funding_flag,
4768 include_gains_losses_flag = l_project_type_rec.include_gains_losses_flag,
4769 -- End of changes
4770 --PA L Changes 2872708
4771 asset_allocation_method = l_project_type_rec.asset_allocation_method,
4772 capital_event_processing = l_project_type_rec.capital_event_processing,
4773 CINT_RATE_SCH_ID = l_project_type_rec.CINT_RATE_SCH_ID,
4774 --End PA L Changes 2872708
4775 --federal changes by sunkalya. Bug#5511353.
4776 date_eff_funds_consumption = nvl(l_project_type_rec.date_eff_funds_consumption,'N')
4777 --federal changes by sunkalya. Bug#5511353.
4778 WHERE project_id = p_project_id;
4779 end if; -- nvl(l_unassigned_time,'N') ='Y'
4780
4781 -- BUZA changes
4782 -- Delete existing budgetary control options for the old project type
4783 DELETE FROM PA_BUDGETARY_CONTROL_OPTIONS
4784 WHERE PROJECT_ID = P_PROJECT_ID;
4785
4786 -- Set cc_tax_task_id if project is a template and cc_prvdr_flag = 'N'
4787 if (l_project_old_rec.template_flag = 'Y') AND (l_project_type_rec.cc_prvdr_flag = 'N') then
4788 UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
4789 SET cc_tax_task_id = NULL
4790 WHERE project_id = p_project_id;
4791 end if;
4792
4793
4794 --Federal changes by sunkalya.Bug#5511353.
4795 --Manipulate the customer bill splits w.r.t to the
4796 --date_eff_funds_flag from the new type.
4797 DECLARE
4798
4799 hghst_ctr_cust_id NUMBER;
4800 l_return_status VARCHAR2(10);
4801 l_msg_count NUMBER := 0;
4802 l_msg_data VARCHAR2(2000);
4803
4804 BEGIN
4805
4806 IF l_project_type_rec.date_eff_funds_consumption ='Y' THEN
4807
4808 UPDATE pa_project_customers
4809 SET
4810 CUSTOMER_BILL_SPLIT = NULL
4811 WHERE
4812 PROJECT_ID = p_project_id;
4813
4814 ELSIF nvl(l_project_old_rec.date_eff_funds_consumption,'N') = 'Y' AND l_project_old_rec.enable_top_task_customer_flag = 'N' THEN
4815 --This api will determine which customer to be made as 100% contributor.
4816 PA_CUSTOMERS_CONTACTS_UTILS.Get_Highest_Contr_Fed_Cust(
4817 P_API_VERSION => 1.0
4818 , P_INIT_MSG_LIST => 'T'
4819 , P_COMMIT => 'F'
4820 , P_VALIDATE_ONLY => 'F'
4821 , P_VALIDATION_LEVEL => 100
4822 , P_DEBUG_MODE => 'N'
4823 , p_calling_module => 'AMG'
4824 , p_project_id => p_project_id
4825 , x_highst_contr_cust_id => hghst_ctr_cust_id
4826 , x_return_status => l_return_status
4827 , x_msg_count => l_msg_count
4828 , x_msg_data => l_msg_data );
4829
4830 IF hghst_ctr_cust_id IS NOT NULL AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4831
4832 UPDATE pa_project_customers SET customer_bill_split = 100
4833 WHERE customer_id = hghst_ctr_cust_id AND project_id = p_project_id;
4834
4835 UPDATE pa_project_customers SET customer_bill_split = 0
4836 WHERE customer_id <> hghst_ctr_cust_id AND project_id = p_project_id;
4837 END IF;
4838 END IF;
4839
4840 END;
4841
4842 --Federal changes by sunkalya.Bug#5511353.
4843
4844
4845 -- Update PA_TASKS with project type values
4846 OPEN get_billable_cap_flag(l_project_type_rec.work_type_id);
4847 FETCH get_billable_cap_flag INTO l_billable_capitalizable_flag;
4848 CLOSE get_billable_cap_flag;
4849
4850 UPDATE pa_tasks
4851 SET work_type_id = l_project_type_rec.work_type_id,
4852 billable_flag = decode(NVL(PA_INSTALL.is_prm_licensed(),'N'), 'Y', l_billable_capitalizable_flag, 'N', billable_flag), /* added decode for bug#3481807 */
4853 emp_bill_rate_schedule_id = l_project_type_rec.emp_bill_rate_schedule_id,
4854 job_bill_rate_schedule_id = l_project_type_rec.job_bill_rate_schedule_id,
4855 labor_sch_type = l_project_type_rec.labor_sch_type,
4856 service_type_code = l_project_type_rec.service_type_code,
4857 cost_ind_rate_sch_id = l_project_type_rec.cost_ind_rate_sch_id,
4858 labor_bill_rate_org_id = l_project_type_rec.labor_bill_rate_org_id,
4859 labor_std_bill_rate_schdl = l_project_type_rec.labor_std_bill_rate_schdl,
4860 non_labor_sch_type = l_project_type_rec.non_labor_sch_type,
4861 non_labor_bill_rate_org_id = l_project_type_rec.non_labor_bill_rate_org_id,
4862 non_labor_std_bill_rate_schdl = l_project_type_rec.non_labor_std_bill_rate_schdl,
4863 rev_ind_rate_sch_id = l_project_type_rec.rev_ind_rate_sch_id,
4864 inv_ind_rate_sch_id = l_project_type_rec.inv_ind_rate_sch_id
4865 WHERE project_id = p_project_id;
4866
4867 -- Update distribution rule
4868 OPEN get_dist_rule(p_project_type);
4869 FETCH get_dist_rule INTO l_distribution_rule;
4870 CLOSE get_dist_rule;
4871
4872 UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
4873 SET distribution_rule = l_distribution_rule,
4874 revenue_accrual_method = substr(l_distribution_rule,1,instr(l_distribution_rule,'/')-1), --Added for bug 8655611
4875 invoice_method = substr(l_distribution_rule,instr(l_distribution_rule,'/')+1) --Added for bug 8655611
4876 WHERE project_id = p_project_id;
4877
4878 End If; -- l_project_type_error = FALSE
4879 end if; -- l_err_code <> 0
4880
4881 end if; -- p_project_Type is not null and p_project_type <> l_project_old_rec.project_type
4882 -- anlee end of changes
4883
4884 --dbms_output.put_line('Check CARRYING_OUT_ORGANIZATION_ID ... ');
4885
4886 IF (p_debug_mode = 'Y')
4887 THEN
4888 pa_debug.debug('Validate_project_info PVT: Carrying out Organization validation');
4889 END IF;
4890 --CARRYING_OUT_ORGANIZATION_ID
4891 IF (p_carrying_out_organization_id <> FND_API.G_MISS_NUM
4892 AND p_carrying_out_organization_id IS NOT NULL)
4893 THEN
4894
4895 IF p_carrying_out_organization_id <>
4896 l_project_old_rec.carrying_out_organization_id
4897 THEN
4898
4899 -- anlee project actions changes
4900 IF l_update_proj_org_allowed = 'N' THEN
4901 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4902 p_msg_name => 'PA_PM_PROJ_ORG_ERR');
4903 ELSE
4904
4905 IF pa_project_pvt.check_valid_org
4906 (p_carrying_out_organization_id) = 'N' THEN
4907 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4908 p_msg_name => 'PA_INVALID_ORG');
4909 END IF;
4910
4911 -- Getting X_functional_security_flag through fnd_function_test function
4912
4913 if fnd_function.test('PA_PAXPREPR_UPDATE_ORG') then
4914 function_flag := 'Y'; -- function returns TRUE
4915 else
4916 function_flag := 'N'; -- function returns FALSE
4917 end if;
4918
4919 pa_project_utils2.validate_attribute_change(
4920 X_Context => 'ORGANIZATION_VALIDATION'
4921 ,X_insert_update_mode => NULL
4922 ,X_calling_module => 'UPDATE_PROJECT'
4923 ,X_project_id => p_project_id
4924 ,X_task_id => NULL
4925 ,X_old_value => To_char(l_project_old_rec.carrying_out_organization_id)
4926 ,X_new_value => To_char(p_carrying_out_organization_id)
4927 ,X_project_type => l_project_old_rec.project_type
4928 ,X_project_start_date => l_project_old_rec.start_date
4929 ,X_project_end_date => l_project_old_rec.completion_date
4930 ,X_public_sector_flag => l_project_old_rec.public_sector_flag
4931 ,X_task_manager_person_id => NULL
4932 ,X_Service_type => NULL
4933 ,X_task_start_date => NULL
4934 ,X_task_end_date => NULL
4935 ,X_entered_by_user_id => FND_GLOBAL.USER_ID
4936 ,X_attribute_category => l_project_old_rec.attribute_category
4937 ,X_attribute1 => l_project_old_rec.attribute1
4938 ,X_attribute2 => l_project_old_rec.attribute2
4939 ,X_attribute3 => l_project_old_rec.attribute3
4940 ,X_attribute4 => l_project_old_rec.attribute4
4941 ,X_attribute5 => l_project_old_rec.attribute5
4942 ,X_attribute6 => l_project_old_rec.attribute6
4943 ,X_attribute7 => l_project_old_rec.attribute7
4944 ,X_attribute8 => l_project_old_rec.attribute8
4945 ,X_attribute9 => l_project_old_rec.attribute9
4946 ,X_attribute10 => l_project_old_rec.attribute10
4947 ,X_pm_product_code => l_project_old_rec.pm_product_code
4948 ,X_pm_project_reference => l_project_old_rec.pm_project_reference
4949 ,X_pm_task_reference => NULL
4950 ,X_functional_security_flag => function_flag
4951 ,x_warnings_only_flag => l_warnings_only_flag --bug3134205
4952 ,X_err_code => l_err_code
4953 ,X_err_stage => l_err_stage
4954 ,X_err_stack => l_err_stack );
4955
4956 IF (l_err_code <> 0) /* AND (l_err_code <> 15) */ THEN /* Commented the <> 15 condition for bug 2981386*/
4957
4958 IF NOT pa_project_pvt.check_valid_message (l_err_stage) THEN
4959
4960 if l_err_stage = 'PA_INVALID_PT_CLASS_ORG' then
4961
4962 -- MOAC Changes: Bug 4363092 - removed nvl used with org_id
4963 select meaning
4964 into t_project_type_class_code
4965 from pa_project_types_all pt -- Bug#3807805 : Modifed pa_project_types to pa_project_types_all
4966 , pa_lookups lps
4967 where pt.project_type = p_project_type
4968 and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
4969 and lps.lookup_code(+) = pt.project_type_class_code
4970 and pt.org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
4971
4972 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4973 p_msg_name => 'PA_INVALID_PT_CLASS_ORG',
4974 p_token1 => 'PT_CLASS',
4975 p_value1 => t_project_type_class_code);
4976 else
4977 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4978 p_msg_name => l_err_stage);
4979
4980 end if; -- l_err_stage = 'PA_INVALID_PT_CLASS_ORG'
4981
4982 ELSE
4983 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
4984 p_msg_name => l_err_stage);
4985 end if; -- NOT pa_project_pvt.check_valid_message
4986
4987 END IF; -- (l_err_code <> 0) AND (l_err_code <> 15)
4988
4989
4990 -- anlee
4991 -- Added validations for bug 2327927
4992
4993 pa_location_utils.Get_ORG_Location_Details
4994 (p_organization_id => p_carrying_out_organization_id,
4995 x_country_name => x_country_name,
4996 x_city => x_city_name,
4997 x_region => x_region_name,
4998 x_country_code => x_country_code,
4999 x_return_status => l_return_status,
5000 x_error_message_code => l_error_msg_code);
5001
5002 if l_return_status <> 'S' Then
5003 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5004 p_msg_name => l_error_msg_code);
5005 End If;
5006
5007 -- Added for bug 2680595
5008 pa_location_utils.get_location(
5009 p_country_code => x_country_code,
5010 p_city => x_city_name,
5011 p_region => x_region_name,
5012 x_return_status => l_return_status,
5013 x_location_id => x_location_id,
5014 x_error_message_code => l_error_msg_code);
5015
5016 if l_return_status <> 'S' Then
5017 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5018 p_msg_name => l_error_msg_code);
5019 End If;
5020
5021 /*
5022 Commented out for bug 2680595
5023 pa_location_utils.check_location_exists(
5024 p_country_code => x_country_code,
5025 p_city => x_city_name,
5026 p_region => x_region_name,
5027 x_return_status => l_return_status,
5028 x_location_id => x_location_id);
5029
5030 If x_location_id is null then
5031
5032 pa_locations_pkg.INSERT_ROW(
5033 p_CITY => x_city_name,
5034 p_REGION => x_region_name,
5035 p_COUNTRY_CODE => x_country_code,
5036 p_CREATION_DATE => sysdate,
5037 p_CREATED_BY => fnd_global.user_id,
5038 p_LAST_UPDATE_DATE => sysdate,
5039 p_LAST_UPDATED_BY => fnd_global.user_id,
5040 p_LAST_UPDATE_LOGIN => fnd_global.login_id,
5041 X_ROWID => x_rowid,
5042 X_LOCATION_ID => x_location_id);
5043
5044 end if;
5045 */
5046 pa_schedule_pub.GET_PROJ_CALENDAR_DEFAULT
5047 ( p_proj_organization => p_carrying_out_organization_id,
5048 p_project_id => NULL,
5049 x_calendar_id => l_calendar_id,
5050 x_calendar_name => l_calendar_name,
5051 x_return_status => l_return_status,
5052 x_msg_count => l_msg_count,
5053 x_msg_data => l_error_msg_code);
5054
5055 if l_return_status <> 'S' Then
5056 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5057 p_msg_name => l_error_msg_code);
5058 End If; --l_return_status <> 'S'
5059 -- anlee end of changes
5060
5061 END IF; -- l_update_proj_org_allowed
5062 END IF; -- IF p_project_in.carrying_out_organization_id <>
5063 -- PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5064 END IF; -- IF p_project_in.carrying_out_organization_id <>
5065 -- l_project_rec.carrying_out_organization_id
5066
5067 -- anlee
5068 -- Dates changes
5069 -- target start date and target finish date validations
5070 IF(p_target_start_date IS NOT NULL AND p_target_finish_date IS NOT NULL) AND
5071 (p_target_start_date > p_target_finish_date) THEN
5072
5073 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5074 p_msg_name => 'PA_ENTERED_DATES_INVLD');
5075 END IF;
5076 -- End of changes
5077
5078 -- anlee
5079 -- Added validations for bug 2327927
5080 --dbms_output.put_line('Check pa_project_pvt.check_start_end_date ... ');
5081
5082 IF (p_debug_mode = 'Y')
5083 THEN
5084 pa_debug.debug('Validate_project_info PVT: Start and completion date validation');
5085 END IF;
5086
5087 l_project_start_date := p_start_date;
5088 l_project_completion_date := p_completion_date;
5089
5090 IF p_start_date = FND_API.G_MISS_DATE
5091 THEN l_project_start_date := NULL;
5092 END IF;
5093
5094 IF p_completion_date = FND_API.G_MISS_DATE
5095 THEN l_project_completion_date := NULL;
5096 END IF;
5097
5098 pa_project_pvt.check_start_end_date
5099 (p_old_start_date => l_project_old_rec.start_date
5100 ,p_new_start_date => l_project_start_date
5101 ,p_old_end_date => l_project_old_rec.completion_date
5102 ,p_new_end_date => l_project_completion_date
5103 ,p_update_start_date_flag => l_update_start_date_flag
5104 ,p_update_end_date_flag => l_update_end_date_flag
5105 ,p_return_status => l_return_status );
5106
5107 IF l_return_status <> 'S' THEN
5108 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5109 p_msg_name => 'PA_SU_INVALID_DATES');
5110 else
5111 -- Do validation if start date has changed
5112 if (nvl(l_project_old_rec.start_date,FND_API.G_MISS_DATE) <>
5113 nvl(l_project_start_date,FND_API.G_MISS_DATE)) then --Bug 6408115
5114
5115 -- anlee project actions changes
5116 IF l_update_proj_dates_allowed = 'N' THEN
5117 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5118 p_msg_name => 'PA_PM_PROJ_DATES_ERR');
5119 ELSE
5120 -- Check that project start date is less than min (task start dates)
5121 OPEN get_min_task_start_date;
5122 FETCH get_min_task_start_date INTO l_min_task_start_date;
5123 if (get_min_task_start_date%FOUND) AND (l_project_start_date is not null) then
5124 if l_min_task_start_date < l_project_start_date then
5125 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5126 p_msg_name => 'PA_PR_INVALID_START_DATE');
5127 l_start_date_error := TRUE;
5128 end if;
5129 end if;
5130 CLOSE get_min_task_start_date; -- Added for Bug#3876261
5131 -- Do not allow start date modification if there exists project level budget at completion
5132 if p_calling_module <> 'SETUP_PAGE' then -- bug 7204572 added an IF condition
5133 if l_start_date_error = FALSE then
5134 if pa_budget_utils2.check_budget_at_compl_exists(p_project_id,null) = 'Y' then
5135 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5136 p_msg_name => 'PA_PRJ_LVL_BUD_ERR');
5137 l_start_date_error := TRUE;
5138 end if;
5139 end if;
5140 end if; -- end if for p_calling module bug 7204572
5141 END IF; -- l_update_proj_dates_allowed
5142 end if; -- l_project_old_rec.start_date <> l_project_start_date
5143
5144 -- Do validation if completion date has changed
5145 if (nvl(l_project_old_rec.completion_date,FND_API.G_MISS_DATE) <>
5146 nvl(l_project_completion_date,FND_API.G_MISS_DATE)) then --Bug 6408115
5147 -- anlee project actions changes
5148 IF l_update_proj_dates_allowed = 'N' THEN
5149 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5150 p_msg_name => 'PA_PM_PROJ_DATES_ERR');
5151 ELSE
5152
5153 -- Check that project completion date is greater than max (task completion dates)
5154 OPEN get_max_task_completion_date;
5155 FETCH get_max_task_completion_date INTO l_max_task_completion_date;
5156 if (get_max_task_completion_date%FOUND) AND (l_project_completion_date is not null) then
5157 if l_max_task_completion_date > l_project_completion_date then
5158 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5159 p_msg_name => 'PA_PR_INVALID_COMPLETION_DATE');
5160 l_completion_date_error := TRUE;
5161 end if;
5162 end if;
5163 CLOSE get_max_task_completion_date; -- Added for Bug#3876261
5164
5165 -- Do not allow completion date modification if there exists project level budget at completion
5166 if p_calling_module <> 'SETUP_PAGE' then -- bug 7204572 added an IF condition
5167 if l_completion_date_error = FALSE then
5168 if pa_budget_utils2.check_budget_at_compl_exists(p_project_id,null) = 'Y' then
5169 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5170 p_msg_name => 'PA_PRJ_LVL_BUD_ERR');
5171
5172 end if;
5173 end if;
5174 end if; -- end if for calling module bug 7204572
5175
5176 END IF; -- l_update_proj_dates_allowed
5177 end if; -- l_project_old_rec.completion_date <> l_project_completion_date
5178
5179 END IF; -- l_return_status <> 'S'
5180 -- anlee end of changes
5181
5182
5183 IF (p_debug_mode = 'Y')
5184 THEN
5185 pa_debug.debug('Validate_project_info PVT: Public sector flag validation');
5186 END IF;
5187 IF p_public_sector_flag <> FND_API.G_MISS_CHAR AND
5188 p_public_sector_flag IS NOT NULL
5189 THEN
5190 IF p_public_sector_flag <> l_project_old_rec.public_sector_flag
5191 THEN
5192 IF p_public_sector_flag NOT IN ('Y','N')
5193 THEN
5194 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5195 p_msg_name => 'PA_PUBLIC_SECTOR_INVALID');
5196 END IF;
5197 END IF;
5198 END IF;
5199
5200
5201 IF (p_debug_mode = 'Y')
5202 THEN
5203 pa_debug.debug('Validate_project_info PVT: project status validation');
5204 END IF;
5205
5206 IF p_project_status_code <> FND_API.G_MISS_CHAR AND
5207 p_project_status_code IS NOT NULL
5208 THEN
5209 IF p_project_status_code <>
5210 l_project_old_rec.project_status_code THEN
5211
5212 -- anlee project actions changes
5213 IF l_update_proj_status_allowed = 'N' THEN
5214 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5215 p_msg_name => 'PA_PM_PROJ_STATUS_ERR');
5216 ELSE
5217 IF pa_project_pvt.check_valid_project_status
5218 (p_project_status => p_project_status_code ) = 'N'
5219 THEN
5220 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5221 p_msg_name => 'PA_PROJECT_STATUS_INVALID');
5222 END IF;
5223 -- special case for update to and from 'CLOSED'
5224 IF Pa_project_stus_utils.is_project_status_closed
5225 (p_project_status_code) = 'Y' THEN
5226 l_closing_project := 'Y';
5227 l_project_status_changed := 'Y';
5228
5229 -- anlee
5230 -- Added validations for bug 2327927
5231 UPDATE pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
5232 SET closed_date = trunc(sysdate)
5233 WHERE project_id = p_project_id;
5234 -- anlee end of changes
5235 ELSIF Pa_project_stus_utils.is_project_status_closed
5236 (l_project_old_rec.project_status_code) = 'Y' THEN
5237 l_reopening_project := 'Y';
5238 l_project_status_changed := 'Y';
5239 ELSE
5240 l_project_status_changed := 'Y';
5241 END IF;
5242 END IF; -- l_update_proj_status_allowed
5243 END IF;
5244 END IF;
5245
5246 -- Check Next Allowable Status
5247
5248 IF p_project_status_code <> FND_API.G_MISS_CHAR AND
5249 p_project_status_code IS NOT NULL
5250 THEN
5251 IF p_project_status_code <>
5252 l_project_old_rec.project_status_code
5253 THEN
5254 x_status_changeable := PA_Project_Stus_Utils.allow_status_change
5255 (l_project_old_rec.project_status_code,
5256 p_project_status_code);
5257
5258 IF (x_status_changeable = 'N')
5259 THEN
5260 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5261 p_msg_name => 'PA_STATUS_CANT_CHANGE');
5262 END IF;
5263 END IF;
5264 END IF;
5265
5266 IF l_project_status_changed = 'Y' THEN
5267 Pa_project_stus_utils.Handle_Project_Status_Change
5268 (x_calling_module => 'UPDATE_PROJECT'
5269 ,X_project_id => l_project_old_rec.project_id
5270 ,X_old_proj_status_code => l_project_old_rec.project_status_code
5271 ,X_new_proj_status_code => p_project_status_code
5272 ,X_project_type => l_project_old_rec.project_type
5273 ,X_project_start_date => l_project_old_rec.start_date
5274 ,X_project_end_date => l_project_old_rec.completion_date
5275 ,X_public_sector_flag => l_project_old_rec.public_sector_flag
5276 ,X_attribute_category => l_project_old_rec.attribute_category
5277 ,X_attribute1 => l_project_old_rec.attribute1
5278 ,X_attribute2 => l_project_old_rec.attribute2
5279 ,X_attribute3 => l_project_old_rec.attribute3
5280 ,X_attribute4 => l_project_old_rec.attribute4
5281 ,X_attribute5 => l_project_old_rec.attribute5
5282 ,X_attribute6 => l_project_old_rec.attribute6
5283 ,X_attribute7 => l_project_old_rec.attribute7
5284 ,X_attribute8 => l_project_old_rec.attribute8
5285 ,X_attribute9 => l_project_old_rec.attribute9
5286 ,X_attribute10 => l_project_old_rec.attribute10
5287 ,X_pm_product_code => l_project_old_rec.pm_product_code
5288 ,x_init_msg => 'N'
5289 ,x_verify_ok_flag => l_verify_ok_flag
5290 ,x_wf_enabled_flag => l_wf_enabled_flag
5291 ,X_err_stage => l_err_stage
5292 ,X_err_stack => l_err_stack
5293 ,x_err_msg_count => l_err_msg_count
5294 ,x_warnings_only_flag => l_warnings_only_flag );
5295
5296 IF l_verify_ok_flag = 'N' THEN
5297 -- x_return_status := 'E';
5298 NULL;
5299 --dbms_output.put_line('l_verify_ok_flag Check ERROR IORGANIZATION ERROR ... ');
5300
5301 ELSIF l_verify_ok_flag = 'Y' THEN
5302 SELECT wf_status_code
5303 INTO l_wf_status_code
5304 FROM PA_PROJECTS_ALL
5305 WHERE project_id = p_project_id;
5306
5307 if l_wf_status_code is null then
5308 IF l_wf_enabled_flag = 'Y' THEN
5309
5310 -- update wf status in pa_projects_all
5311 UPDATE pa_projects_all
5312 SET wf_status_code = 'IN_ROUTE',
5313 project_status_code = p_project_status_code
5314 WHERE project_id = p_project_id;
5315
5316 pa_project_wf.start_project_wf
5317 ( p_project_id
5318 , l_err_stack
5319 , l_err_stage
5320 , l_err_code );
5321
5322 If l_err_code <> 0 Then
5323 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5324 p_msg_name => l_err_stage);
5325 end if;
5326 end if;
5327 else
5328 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5329 p_msg_name => 'PA_STATUS_CANT_CHANGE');
5330 end if;
5331
5332 END IF;
5333
5334 END IF;
5335
5336 --dbms_output.put_line('Check LOCATION DETAILS ... ');
5337
5338 -- LOCATION DETAILS
5339
5340 if p_location_id is not null and
5341 p_location_id <> FND_API.G_MISS_NUM
5342 then
5343 x_location_id := p_location_id;
5344 else
5345 if p_territory_code is null or
5346 p_territory_code = FND_API.G_MISS_CHAR
5347 then
5348 open c2(p_country);
5349 fetch c2 into x_country_code;
5350 close c2;
5351 else
5352 x_country_code := p_territory_code;
5353 end if;
5354 /*
5355 open c1(x_country_code,p_state_region,p_city);
5356 fetch c1 into x_location_id;
5357 close c1;
5358 */
5359 IF (p_debug_mode = 'Y')
5360 THEN
5361 pa_debug.debug('Validate_project_info PVT: location validation');
5362 END IF;
5363 -- Modified for bug 2038542
5364 -- If p_city is not null
5365 -- and p_state_region is not null
5366 -- and x_country_code is not null then
5367
5368 If x_country_code is not null then
5369
5370 pa_location_utils.check_location_exists(
5371 p_country_code => x_country_code,
5372 p_city => p_city,
5373 p_region => p_state_region,
5374 x_return_status => l_return_status,
5375 x_location_id => x_location_id);
5376
5377 end if;
5378
5379 If x_location_id is null or x_location_id = FND_API.G_MISS_NUM then
5380
5381 -- Modified for bug 2038542
5382 -- If p_city is not null
5383 -- and p_state_region is not null
5384 -- and x_country_code is not null then
5385
5386 If x_country_code is not null then
5387
5388 IF (p_debug_mode = 'Y')
5389 THEN
5390 pa_debug.debug('Validate_project_info PVT: location
5391 table handler to insert new record');
5392 End if;
5393 pa_locations_pkg.INSERT_ROW(
5394 p_CITY => p_city,
5395 p_REGION => p_state_region,
5396 p_COUNTRY_CODE => x_country_code,
5397 p_CREATION_DATE => sysdate,
5398 p_CREATED_BY => fnd_global.user_id,
5399 p_LAST_UPDATE_DATE => sysdate,
5400 p_LAST_UPDATED_BY => fnd_global.user_id,
5401 p_LAST_UPDATE_LOGIN => fnd_global.login_id,
5402 X_ROWID => x_rowid,
5403 X_LOCATION_ID => x_location_id);
5404
5405 End if;
5406 End if;
5407 end if;
5408 update pa_projects_all -- Bug#3807805 : Modifed pa_projects to pa_projects_all
5409 set location_id = x_location_id
5410 where project_id = p_project_id;
5411 end if; -- P_calling_module 'BASIC_INFO'
5412
5413 if (p_calling_module like ('ADDITIONAL_INFORMATION')) then
5414 -- Cost job group defaulting logic.
5415
5416 IF (p_debug_mode = 'Y')
5417 THEN
5418 pa_debug.debug('Validate_project_info PVT: Additional information validation');
5419 END IF;
5420 --dbms_output.put_line('INSDIE ADDITIONAL_INFORMATION PROCEDURE ... ');
5421
5422 IF nvl(p_bill_job_group_id, -999) <>
5423 nvl(l_project_old_rec.bill_job_group_id,-999)
5424 THEN
5425
5426 IF (p_debug_mode = 'Y')
5427 THEN
5428 pa_debug.debug('Validate_project_info PVT: Bill job group validation');
5429 END IF;
5430 --dbms_output.put_line('BEFORE PA_PROJECTS_MAINT_UTILS.CHECK_BILL_JOB_GRP_REQ ');
5431
5432 PA_PROJECTS_MAINT_UTILS.CHECK_BILL_JOB_GRP_REQ(
5433 l_project_old_rec.project_type,
5434 p_bill_job_group_id,
5435 l_return_status,
5436 l_error_msg_code);
5437
5438 --dbms_output.put_line('AFTER PA_PROJECTS_MAINT_UTILS.CHECK_BILL_JOB_GRP_REQ ');
5439
5440 IF l_return_status = 'E'
5441 THEN
5442 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5443 p_msg_name =>l_error_msg_code);
5444 END IF;
5445
5446 END IF;
5447
5448 end if; -- p_calling_module like ('ADDITIONAL_INFORMATION')
5449
5450 if (p_calling_module like ('PIPELINE')) then
5451
5452 IF (p_debug_mode = 'Y')
5453 THEN
5454 pa_debug.debug('Validate_project_info PVT: pipeline validation');
5455 END IF;
5456 IF (p_debug_mode = 'Y')
5457 THEN
5458 pa_debug.debug('Validate_project_info PVT: probability member id validation');
5459 END IF;
5460
5461 If (p_project_value is not null and p_project_value < 0) then
5462 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5463 p_msg_name =>'PA_BU_NEED_POS_NUM');
5464 end if;
5465
5466 IF nvl(p_probability_member_id, -999) <>
5467 nvl(l_project_old_rec.probability_member_id,-999)
5468 THEN
5469
5470 PA_PROJECTS_MAINT_UTILS.CHECK_PROBABILITY_CAN_CHANGE(
5471 l_project_old_rec.project_status_code,
5472 l_return_status,
5473 l_error_msg_code);
5474 IF l_return_status = 'E'
5475 THEN
5476 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5477 p_msg_name =>l_error_msg_code);
5478 END IF;
5479 END IF;
5480 open prob_per (p_probability_member_id);
5481 fetch prob_per into l_probability_member_id;
5482 close prob_per;
5483 IF l_probability_member_id is not null and p_expected_approval_date is null Then
5484 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5485 p_msg_name =>'PA_EXP_APP_DATE_REQUIRED');
5486 End if;
5487
5488 IF l_probability_member_id is null and p_expected_approval_date is not null Then
5489 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5490 p_msg_name =>'PA_PROBA_PERCENT_REQUIRED');
5491 End if;
5492
5493 end if; -- p_calling_module like ('PIPELINE')
5494
5495 end if; -- p_validation_level > 0
5496
5497 close l_project_details_csr;
5498
5499 EXCEPTION WHEN OTHERS THEN
5500 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PVT',
5501 p_procedure_name => 'VALIDATE_PROJECT_INFO',
5502 p_error_text => SUBSTRB(SQLERRM,1,240));
5503 raise;
5504
5505 END VALIDATE_PROJECT_INFO;
5506
5507
5508 -- API name : Update_project_staffing_info
5509 -- Type : Public
5510 -- Pre-reqs : None.
5511 -- Parameters :
5512 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
5513 -- p_validate_only IN VARCHAR2 Optional Default = FND_API.G_TRUE
5514 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
5515 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
5516 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
5517 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
5518 -- p_project_id IN NUMBER Required
5519 -- p_comp_match_weighting IN pa_projects_all.COMPETENCE_MATCH_WT%TYPE Optional Default = FND_API.G_MISS_NUM
5520 -- p_avail_match_weighting IN pa_projects_all.availability_match_wt%TYPE Optional Default = FND_API.G_MISS_NUM
5521 -- p_job_level_match_weighting IN pa_projects_all.job_level_match_wt%TYPE Optional Default = FND_API.G_MISS_NUM
5522 -- p_search_min_availability IN pa_projects_all.search_min_availability%TYPE Optional Default = FND_API.G_MISS_NUM
5523 -- p_search_country_code IN pa_projects_all.search_country_code%TYPE Optional Default = FND_API.G_MISS_CHAR
5524 -- p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE Optional Default = FND_API.G_MISS_CHAR,
5525 -- p_search_exp_org_struct_ver_id IN pa_projects_all.search_org_hier_id%TYPE Optional Default = FND_API.G_MISS_NUM
5526 -- p_search_exp_org_hier_name IN per_organization_structures.name%TYPE Optional Default = FND_API.G_MISS_CHAR,
5527 -- p_search_exp_start_org_id IN pa_projects_all.search_starting_org_id%TYPE Optional Default = FND_API.G_MISS_NUM
5528 -- p_search_exp_start_org_name IN hr_organization_units.name%TYPE Optional Default = FND_API.G_MISS_CHAR,
5529 -- p_search_min_candidate_score IN pa_projects_all.min_cand_score_reqd_for_nom%TYPE Optional Default = FND_API.G_MISS_NUM
5530 -- p_enable_auto_cand_nom_flag IN pa_projects_all.enable_automated_search%TYPE Optional Default = FND_API.G_MISS_CHAR
5531 -- p_record_version_number IN NUMBER Required
5532 -- x_return_status OUT VARCHAR2 Required
5533 -- x_msg_count OUT NUMBER Required
5534 -- x_msg_data OUT VARCHAR2 Required
5535 --
5536 -- History
5537 --
5538 -- 28-SEP-2000 -- hyau - Created.
5539 --
5540 --
5541 PROCEDURE UPDATE_PROJECT_STAFFING_INFO
5542 (
5543 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
5544 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
5545 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5546 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
5547 p_debug_mode IN VARCHAR2 := 'N' ,
5548 p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM ,
5549 p_project_id IN NUMBER ,
5550 p_comp_match_weighting IN pa_projects_all.COMPETENCE_MATCH_WT%TYPE := FND_API.G_MISS_NUM,
5551 p_avail_match_weighting IN pa_projects_all.availability_match_wt%TYPE := FND_API.G_MISS_NUM,
5552 p_job_level_match_weighting IN pa_projects_all.job_level_match_wt%TYPE := FND_API.G_MISS_NUM,
5553 p_search_min_availability IN pa_projects_all.search_min_availability%TYPE := FND_API.G_MISS_NUM,
5554 p_search_country_code IN pa_projects_all.search_country_code%TYPE := FND_API.G_MISS_CHAR,
5555 p_search_exp_org_struct_ver_id IN pa_projects_all.search_org_hier_id%TYPE := FND_API.G_MISS_NUM,
5556 p_search_exp_start_org_id IN pa_projects_all.search_starting_org_id%TYPE := FND_API.G_MISS_NUM,
5557 p_search_min_candidate_score IN pa_projects_all.min_cand_score_reqd_for_nom%TYPE := FND_API.G_MISS_NUM,
5558 p_enable_auto_cand_nom_flag IN pa_projects_all.enable_automated_search%TYPE := FND_API.G_MISS_CHAR,
5559 p_record_version_number IN NUMBER ,
5560 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
5561 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
5562 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
5563 IS
5564
5565 l_msg_count NUMBER;
5566 l_msg_index_out NUMBER;
5567 l_msg_data VARCHAR2(250);
5568 l_data VARCHAR2(250);
5569 l_dummy VARCHAR2(1);
5570
5571
5572 l_comp_match_weighting pa_projects_all.COMPETENCE_MATCH_WT%TYPE;
5573 l_avail_match_weighting pa_projects_all.availability_match_wt%TYPE;
5574 l_job_level_match_weighting pa_projects_all.job_level_match_wt%TYPE;
5575 l_search_min_availability pa_projects_all.search_min_availability%TYPE;
5576 l_search_country_code pa_projects_all.search_country_code%TYPE;
5577 l_search_exp_org_struct_ver_id pa_projects_all.search_org_hier_id%TYPE;
5578 l_search_exp_start_org_id pa_projects_all.search_starting_org_id%TYPE;
5579 l_search_min_candidate_score pa_projects_all.min_cand_score_reqd_for_nom%TYPE;
5580 l_enable_auto_cand_nom_flag pa_projects_all.enable_automated_search%TYPE;
5581
5582 BEGIN
5583
5584 IF p_commit = FND_API.G_TRUE THEN
5585 SAVEPOINT update_project_staffing_info;
5586 END IF;
5587
5588 x_return_status := FND_API.G_RET_STS_SUCCESS;
5589
5590 if (p_debug_mode = 'Y') then
5591 pa_debug.debug('Update_project_staffing_info PVT: locking record');
5592 end if;
5593 -- write your program logic from here
5594 if p_validate_only <> FND_API.G_TRUE then
5595 BEGIN
5596 SELECT 'x' INTO l_dummy
5597 FROM pa_projects
5598 WHERE project_id = p_project_id
5599 AND record_version_number = p_record_version_number
5600 FOR UPDATE OF record_version_number NOWAIT;
5601 EXCEPTION WHEN TIMEOUT_ON_RESOURCE THEN
5602 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5603 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
5604 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
5605 x_return_status := FND_API.G_RET_STS_ERROR ;
5606 WHEN NO_DATA_FOUND THEN
5607 if p_calling_module = 'FORM' then
5608 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
5609 p_msg_name => 'FORM_RECORD_CHANGED');
5610 x_msg_data := 'FORM_RECORD_CHANGED';
5611 else
5612 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5613 p_msg_name => 'PA_XC_RECORD_CHANGED');
5614 x_msg_data := 'PA_XC_RECORD_CHANGED';
5615 end if;
5616 x_return_status := FND_API.G_RET_STS_ERROR ;
5617 WHEN OTHERS THEN
5618 IF SQLCODE = -54 THEN
5619 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5620 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
5621 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
5622 x_return_status := FND_API.G_RET_STS_ERROR ;
5623 ELSE
5624 raise;
5625 END IF;
5626 END;
5627 else
5628 BEGIN
5629 SELECT 'x' INTO l_dummy
5630 FROM pa_projects
5631 WHERE project_id = p_project_id
5632 AND record_version_number = p_record_version_number;
5633 EXCEPTION
5634 WHEN NO_DATA_FOUND THEN
5635 if p_calling_module = 'FORM' then
5636 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
5637 p_msg_name => 'FORM_RECORD_CHANGED');
5638 x_msg_data := 'FORM_RECORD_CHANGED';
5639 else
5640 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5641 p_msg_name => 'PA_XC_RECORD_CHANGED');
5642 x_msg_data := 'PA_XC_RECORD_CHANGED';
5643 end if;
5644 x_return_status := FND_API.G_RET_STS_ERROR ;
5645 WHEN OTHERS THEN
5646 IF SQLCODE = -54 THEN
5647 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
5648 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
5649 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
5650 x_return_status := FND_API.G_RET_STS_ERROR ;
5651 ELSE
5652 raise;
5653 END IF;
5654 END;
5655 end if;
5656 l_msg_count := FND_MSG_PUB.count_msg;
5657
5658 IF l_msg_count > 0 THEN
5659 x_msg_count := l_msg_count;
5660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5661 RAISE FND_API.G_EXC_ERROR;
5662 END IF;
5663
5664
5665 IF NOT FND_API.TO_BOOLEAN(p_validate_only)
5666 THEN
5667 if (p_debug_mode = 'Y') then
5668 pa_debug.debug('Update_project_staffing_info PVT: update pa_projects table');
5669 end if;
5670 if p_comp_match_weighting = FND_API.G_MISS_NUM then
5671 l_comp_match_weighting := null;
5672 else
5673 l_comp_match_weighting := p_comp_match_weighting;
5674 end if;
5675
5676 if p_avail_match_weighting = FND_API.G_MISS_NUM then
5677 l_avail_match_weighting := null;
5678 else
5679 l_avail_match_weighting := p_avail_match_weighting;
5680 end if;
5681
5682 if p_job_level_match_weighting = FND_API.G_MISS_NUM then
5683 l_job_level_match_weighting := null;
5684 else
5685 l_job_level_match_weighting := p_job_level_match_weighting;
5686 end if;
5687
5688 if p_search_min_availability = FND_API.G_MISS_NUM then
5689 l_search_min_availability := null;
5690 else
5691 l_search_min_availability := p_search_min_availability;
5692 end if;
5693
5694 if p_search_country_code = FND_API.G_MISS_CHAR then
5695 l_search_country_code := null;
5696 else
5697 l_search_country_code := p_search_country_code;
5698 end if;
5699
5700
5701 if p_search_exp_org_struct_ver_id = FND_API.G_MISS_NUM then
5702 l_search_exp_org_struct_ver_id := null;
5703 else
5704 l_search_exp_org_struct_ver_id := p_search_exp_org_struct_ver_id;
5705 end if;
5706
5707 if p_search_exp_start_org_id = FND_API.G_MISS_NUM then
5708 l_search_exp_start_org_id := null;
5709 else
5710 l_search_exp_start_org_id := p_search_exp_start_org_id;
5711 end if;
5712
5713 if p_search_min_candidate_score = FND_API.G_MISS_NUM then
5714 l_search_min_candidate_score := null;
5715 else
5716 l_search_min_candidate_score := p_search_min_candidate_score;
5717 end if;
5718
5719 if p_enable_auto_cand_nom_flag = FND_API.G_MISS_CHAR then
5720 l_enable_auto_cand_nom_flag := null;
5721 else
5722 l_enable_auto_cand_nom_flag := p_enable_auto_cand_nom_flag;
5723 end if;
5724
5725 UPDATE pa_projects_all
5726 SET record_version_number = record_version_number +1 ,
5727 COMPETENCE_MATCH_WT = p_comp_match_weighting,
5728 AVAILABILITY_MATCH_WT = p_avail_match_weighting,
5729 JOB_LEVEL_MATCH_WT = p_job_level_match_weighting,
5730 ENABLE_AUTOMATED_SEARCH = p_enable_auto_cand_nom_flag,
5731 MIN_CAND_SCORE_REQD_FOR_NOM = p_search_min_candidate_score,
5732 SEARCH_MIN_AVAILABILITY = p_search_min_availability,
5733 SEARCH_ORG_HIER_ID = p_search_exp_org_struct_ver_id,
5734 SEARCH_STARTING_ORG_ID = p_search_exp_start_org_id,
5735 SEARCH_COUNTRY_CODE = p_search_country_code
5736 WHERE project_id = p_project_id;
5737 END IF;
5738
5739 IF FND_API.TO_BOOLEAN(P_COMMIT)
5740 THEN
5741 COMMIT WORK;
5742 END IF;
5743
5744 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5745 IF p_commit = FND_API.G_TRUE THEN
5746 ROLLBACK TO update_project_staffing_info;
5747 END IF;
5748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5749 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
5750 p_procedure_name => 'UPDATE_PROJECT_STAFFING_INFO',
5751 p_error_text => SUBSTRB(SQLERRM,1,240));
5752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5753
5754 WHEN FND_API.G_EXC_ERROR THEN
5755 IF p_commit = FND_API.G_TRUE THEN
5756 ROLLBACK TO update_project_staffing_info;
5757 END IF;
5758 x_return_status := FND_API.G_RET_STS_ERROR;
5759
5760 WHEN OTHERS THEN
5761 IF p_commit = FND_API.G_TRUE THEN
5762 ROLLBACK TO update_project_staffing_info;
5763 END IF;
5764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5765 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_PUB',
5766 p_procedure_name => 'UPDATE_PROJECT_STAFFING_INFO',
5767 p_error_text => SUBSTRB(SQLERRM,1,240));
5768 raise;
5769
5770 END UPDATE_PROJECT_STAFFING_INFO;
5771
5772 END PA_PROJECTS_MAINT_PVT;