[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_UTILS2
Source
1 package body PA_PROJECT_UTILS2 as
2 -- $Header: PAXPUT2B.pls 120.6.12010000.2 2008/09/16 06:39:23 sugupta ship $
3
4 -- ----------------------------------------------------------
5 -- Validate_Attribute_Change
6 -- X_err_code:
7 -- > 0 for application business errors
8 -- < 0 for SQL errors
9 -- = 0 for success
10 -- If X_err_code > 0, X_err_stage contains the message code
11 -- X_err_code < 0, X_err_stage contains SQLCODE
12 -- ----------------------------------------------------------
13
14 PROCEDURE validate_attribute_change(
15 X_Context IN VARCHAR2
16 , X_insert_update_mode IN VARCHAR2
17 , X_calling_module IN VARCHAR2
18 , X_project_id IN NUMBER
19 , X_task_id IN NUMBER
20 , X_old_value IN VARCHAR2
21 , X_new_value IN VARCHAR2
22 , X_project_type IN VARCHAR2
23 , X_project_start_date IN DATE
24 , X_project_end_date IN DATE
25 , X_public_sector_flag IN VARCHAR2
26 , X_task_manager_person_id IN NUMBER
27 , X_Service_type IN VARCHAR2
28 , X_task_start_date IN DATE
29 , X_task_end_date IN DATE
30 , X_entered_by_user_id IN NUMBER
31 , X_attribute_category IN VARCHAR2
32 , X_attribute1 IN VARCHAR2
33 , X_attribute2 IN VARCHAR2
34 , X_attribute3 IN VARCHAR2
35 , X_attribute4 IN VARCHAR2
36 , X_attribute5 IN VARCHAR2
37 , X_attribute6 IN VARCHAR2
38 , X_attribute7 IN VARCHAR2
39 , X_attribute8 IN VARCHAR2
40 , X_attribute9 IN VARCHAR2
41 , X_attribute10 IN VARCHAR2
42 , X_pm_product_code IN VARCHAR2
43 , X_pm_project_reference IN VARCHAR2
44 , X_pm_task_reference IN VARCHAR2
45 , X_functional_security_flag IN VARCHAR2
46 , x_warnings_only_flag OUT NOCOPY varchar2 --bug3134205 --File.Sql.39 bug 4440895
47 , X_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
48 , X_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
49 , X_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
50 IS
51 x_yes_no varchar2(1);
52 x_pt_class_meaning varchar2(80);
53 x_outcome varchar2(80);
54 old_stack varchar2(2000); /* Increased the array size from 630 */
55 l_error_number NUMBER;
56 l_warnings_only_flag VARCHAR2(1) := 'N';
57 l_system_status_code varchar2(30);
58 l_proj_type_class_code varchar2(30);
59 l_err_msgname VARCHAR2(30);
60
61 -- Added by sunkalya for bug:4687520
62 l_return_status VARCHAR2(100);
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(2000);
65 l_start_no_mgr_date DATE;
66 l_end_no_mgr_date DATE;
67 l_error_occured VARCHAR2(100) := 'N';
68 -- End of code added by Sunkalya for Bug:4687520
69
70 -- These two variables will keep track of the very first error that we
71 -- encounter. We need to do this because we'll continue processing
72 -- even though an error has occurred
73 l_error_msg VARCHAR2(30);
74 l_error_code NUMBER := 0;
75
76 --MOAC Changes: Bug 4363092: removed nvl usage with org_id
77 cursor c is
78 select nvl(decode(pt.project_type_class_code,'INDIRECT',org_information1
79 , 'CAPITAL',org_information12
80 , 'CONTRACT',org_information13),'Y')
81 ,meaning
82 from hr_organization_information org
83 , pa_project_types_all pt -- Bug#3807805 : Modified pa_project_types to pa_project_types_all
84 , pa_lookups lps
85 where org.organization_id(+) = nvl(x_new_value,x_old_value)
86 and upper(org.org_information_context(+)) = upper('Project Type Class Information')
87 and pt.project_type = x_project_type
88 and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
89 and lps.lookup_code(+) = pt.project_type_class_code
90 and pt.org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
91
92 Begin
93 x_err_code := 0;
94 old_stack := x_err_stack;
95 x_err_stack := x_err_stack || '->PA_PROJECT_UTILS.VALIDATE_ATTRIBUTE_CHANGE';
96
97 If X_Context = 'ORGANIZATION_VALIDATION' then
98 --
99 -- Validating whether the Org selected allows creating Projects or Tasks
100 -- with the PT class . This info for the Org is specified when
101 -- definining the org in HR.
102 -- PT class for a Project is determined from the project type specified
103 -- for the Project.
104 --
105 if PA_PROJECT_REQUEST_PVT.G_ORG_ID is null and X_project_type is not null then -- Added the if block for Bug#3807805
106 select org_id into PA_PROJECT_REQUEST_PVT.G_ORG_ID from pa_project_types where project_type = X_project_type;
107 end if;
108
109 open c;
110 fetch c into x_yes_no
111 , x_pt_class_meaning;
112 close c;
113
114 if x_yes_no = 'N' then
115 x_err_code := 10;
116 x_err_stage := 'PA_INVALID_PT_CLASS_ORG';
117 return;
118 end if;
119
120 --
121 -- Calling the Client Extension for Org Validation
122 pa_org_client_extn.verify_org_change(
123 X_insert_update_mode =>X_insert_update_mode
124 , X_calling_module =>X_calling_module
125 , X_project_id =>X_project_id
126 , X_task_id =>X_task_id
127 , X_old_organization_id =>to_number(X_old_value)
128 , X_new_organization_id =>to_number(x_new_value)
129 , X_project_type =>X_project_type
130 , X_project_start_date =>X_project_start_date
131 , X_project_end_date =>X_project_end_date
132 , X_public_sector_flag =>X_public_sector_flag
133 , X_task_manager_person_id =>X_task_manager_person_id
134 , X_Service_type =>X_Service_type
135 , X_task_start_date =>X_task_start_date
136 , X_task_end_date =>X_task_end_date
137 , X_entered_by_user_id =>X_entered_by_user_id
138 , X_attribute_category =>X_attribute_category
139 , X_attribute1 =>X_attribute1
140 , X_attribute2 =>X_attribute2
141 , X_attribute3 =>X_attribute3
142 , X_attribute4 =>X_attribute4
143 , X_attribute5 =>X_attribute5
144 , X_attribute6 =>X_attribute6
145 , X_attribute7 =>X_attribute7
146 , X_attribute8 =>X_attribute8
147 , X_attribute9 =>X_attribute9
148 , X_attribute10 =>X_attribute10
149 , X_pm_product_code =>X_pm_product_code
150 , X_pm_project_reference =>X_pm_project_reference
151 , X_pm_task_reference =>X_pm_task_reference
152 , X_functional_security_flag => X_functional_security_flag
153 , X_outcome =>X_outcome );
154 if x_outcome is not null then
155
156 /* Commented for bug 2981386
157 if (SUBSTRB(x_outcome, 1, 3) = 'PA_') then
158 x_err_code := 15; --Changed to 15
159 else
160 BEGIN
161 l_error_number := to_number(x_outcome);
162 x_err_code := -1;
163 EXCEPTION
164 WHEN OTHERS THEN
165 x_err_code := 10;
166 END;
167 end if;
168 */
169 x_err_code := 15; /* Added for bug 2981386 */
170 x_err_stage := X_outcome ;
171 return;
172 end if;
173
174 ELSIF X_Context = 'PROJECT_STATUS_CHANGE' then
175 IF x_new_value IS NULL OR x_project_id IS NULL THEN
176 x_err_code := 0;
177 RETURN;
178 END IF;
179
180 IF x_new_value IS NOT NULL THEN
181 select project_system_status_code
182 into l_system_status_code
183 from pa_project_statuses
184 where project_status_code = X_new_value;
185 END IF;
186
187 IF (l_system_status_code IN ('APPROVED', 'SUBMITTED')) THEN
188
189 PA_PROJECT_VERIFY_PKG.Category_Required(
190 x_project_id => X_Project_Id,
191 x_err_stage => X_err_stage,
192 x_err_code => X_err_code,
193 x_err_stack => X_err_stack,
194 x_err_msgname => l_err_msgname );
195
196 IF (x_err_code > 0) THEN
197 IF (l_err_msgname IS NOT NULL) THEN
198 fnd_message.set_name('PA', l_err_msgname);
199 fnd_msg_pub.add;
200 END IF;
201 l_error_code := X_err_code;
202 l_error_msg := x_err_stage;
203 ELSIF (X_err_code < 0) THEN
204 FND_MSG_PUB.Add_Exc_Msg(
205 p_pkg_name => 'PA_PROJECT_VERIFY_PKG',
206 p_procedure_name => 'CATEGORY_REQUIRED',
207 p_error_text => 'ORA-'||LPAD(substr(to_char(x_err_code),2),5,'0'));
208 l_error_code := -1;
209 l_error_msg := to_char(x_err_code);
210 END IF;
211
212 -- Get the project type class code
213 --MOAC Changes: Bug 4363092: removed nvl usage with org_id
214 IF x_project_id IS NOT NULL THEN
215 select pt.project_type_class_code
216 into l_proj_type_class_code
217 from pa_projects_all p, -- Bug#3807805 : Modified pa_projects to pa_projects_all
218 pa_project_types_all pt -- Bug#3807805 : Modified pa_project_types to pa_project_types_all
219 where p.project_id = X_Project_ID
220 and p.project_type = pt.project_type
221 and p.org_id = pt.org_id; -- Added the and condition for Bug#3807805
222 END IF;
223
224 -- Additional validation is required for Contract projects
225 IF (l_proj_type_class_code = 'CONTRACT') THEN
226
227 PA_PROJECT_VERIFY_PKG.Customer_Exists(
228 x_project_id => X_Project_Id,
229 x_err_stage => X_err_stage,
230 x_err_code => X_err_code,
231 x_err_stack => X_err_stack,
232 x_err_msgname => l_err_msgname );
233
234 IF (x_err_code > 0) THEN
235 IF (l_err_msgname IS NOT NULL) THEN
236 fnd_message.set_name('PA', l_err_msgname);
237 fnd_msg_pub.add;
238 END IF;
239 IF (l_error_code = 0) THEN
240 l_error_code := X_err_code;
241 l_error_msg := x_err_stage;
242 END IF;
243 ELSIF (X_err_code < 0) THEN
244 FND_MSG_PUB.Add_Exc_Msg(
245 p_pkg_name => 'PA_PROJECT_VERIFY_PKG',
246 p_procedure_name => 'CUSTOMER_EXISTS',
247 p_error_text => 'ORA-'||LPAD(substr(to_char(x_err_code),2),5,'0'));
248 IF (l_error_code = 0) THEN
249 l_error_code := -1;
250 l_error_msg := to_char(x_err_code);
251 END IF;
252 END IF;
253 /* Start of code change Done for Bug:4687520. Done by Sunkalya. */
254 /*PA_PROJECT_VERIFY_PKG.Manager_Exists(
255 x_project_id => X_Project_Id,
256 x_err_stage => X_err_stage,
257 x_err_code => X_err_code,
258 x_err_stack => X_err_stack,
259 x_err_msgname => l_err_msgname );
260
261 IF (x_err_code > 0) THEN
262 IF (l_err_msgname IS NOT NULL) THEN
263 fnd_message.set_name('PA', l_err_msgname);
264 fnd_msg_pub.add;
265 END IF;
266 IF (l_error_code = 0) THEN
267 l_error_code := X_err_code;
268 l_error_msg := x_err_stage;
269 END IF;
270 ELSIF (X_err_code < 0) THEN
271 FND_MSG_PUB.Add_Exc_Msg(
272 p_pkg_name => 'PA_PROJECT_VERIFY_PKG',
273 p_procedure_name => 'MANAGER_EXISTS',
274 p_error_text => 'ORA-'||LPAD(substr(to_char(x_err_code),2),5,'0'));
275 IF (l_error_code = 0) THEN
276 l_error_code := -1;
277 l_error_msg := to_char(x_err_code);
278 END IF;
279 END IF; */ --This entire API call is Commented for Bug:4687520
280 IF l_system_status_code = 'APPROVED' THEN
281
282 -- Putting a savepoint and changing project status to 'APPROVED' temporarily
283 -- since PA_PROJECT_PARTIES_UTILS.VALIDATE_ONE_MANAGER_EXISTS and
284 -- PA_PROJECT_PARTIES_UTILS.validate_manager_date_range will do the checks
285 -- only if the project is an approved contract project.
286
287 savepoint checking_manager_validity;
288
289 update pa_projects_all
290 set project_status_code = 'APPROVED'
291 where project_id = x_project_id;
292
293 -- Calling the procedures that do the checks
294
295 PA_PROJECT_PARTIES_UTILS.VALIDATE_ONE_MANAGER_EXISTS( p_project_id => x_project_id
296 ,x_return_status => l_return_status
297 ,x_msg_count => l_msg_count
298 ,x_msg_data => l_msg_data);
299
300 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
301
302 IF (l_error_code = 0) THEN
303 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
304 l_error_code := 10;
305 l_error_msg := l_msg_data;
306 ELSE
307 l_error_code := -1;
308 l_error_msg := l_msg_data;
309 END IF;
310 END IF;
311
312 ELSE
313 l_error_occured := 'N';
314
315 PA_PROJECT_PARTIES_UTILS.validate_manager_date_range( p_mode => 'SS'
316 ,p_project_id => x_project_id
317 ,x_start_no_mgr_date => l_start_no_mgr_date
318 ,x_end_no_mgr_date => l_end_no_mgr_date
319 ,x_error_occured => l_error_occured);
320
321 IF l_error_occured <> 'N' THEN
322
323 IF l_error_occured = 'PA_PR_NO_MGR_DATE_RANGE' THEN
324 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
325 pa_utils.add_message
326 ( p_app_short_name => 'PA'
327 ,p_msg_name => 'PA_PR_NO_MGR_DATE_RANGE'
328 ,p_token1 => 'START_DATE'
329 ,p_value1 => l_start_no_mgr_date
330 ,p_token2 => 'END_DATE'
331 ,p_value2 => l_end_no_mgr_date
332 );
333 END IF;
334
335 IF (l_error_code = 0) THEN
336 l_error_code := 10;
337 l_error_msg := l_error_occured;
338 END IF;
339 ELSE
340 IF (l_error_code = 0) THEN
341 l_error_code := -1;
342 l_error_msg := l_error_occured;
343 END IF;
344 END IF;
345 END IF;
346 END IF;
347
348 rollback to checking_manager_validity;
349
350 END IF; -- Checks for project manager done
351
352 /* End of changes for bug 4687520 Done by Sunkalya */
353
354 -- Commented below code for the bug 4867044
355 /* PA_PROJECT_VERIFY_PKG.Contact_Exists(
356 x_project_id => X_Project_Id,
357 x_err_stage => X_err_stage,
358 x_err_code => X_err_code,
359 x_err_stack => X_err_stack,
360 x_err_msgname => l_err_msgname );
361
362 IF (x_err_code > 0) THEN
363 IF (l_err_msgname IS NOT NULL) THEN
364 fnd_message.set_name('PA', l_err_msgname);
365 fnd_msg_pub.add;
366 END IF;
367 IF (l_error_code = 0) THEN
368 l_error_code := X_err_code;
369 l_error_msg := x_err_stage;
370 END IF;
371 ELSIF (X_err_code < 0) THEN
372 FND_MSG_PUB.Add_Exc_Msg(
373 p_pkg_name => 'PA_PROJECT_VERIFY_PKG',
374 p_procedure_name => 'CONTACT_EXISTS',
375 p_error_text => 'ORA-'||LPAD(substr(to_char(x_err_code),2),5,'0'));
376 IF (l_error_code = 0) THEN
377 l_error_code := -1;
378 l_error_msg := to_char(x_err_code);
379 END IF;
380 END IF;
381 */ -- End of commented code for the bug 4867044
382 END IF; -- (l_proj_type_class_code = 'CONTRACT')
383 END IF; -- (l_system_status_code IN ('APPROVED', 'SUBMITTED'))
384
385 -- Call the client extn to verify project status changes
386 pa_client_extn_proj_status.verify_project_status_change
387 (x_calling_module => x_calling_module
388 ,X_project_id => X_project_id
389 ,X_old_proj_status_code => X_old_value
390 ,X_new_proj_status_code => x_new_value
391 ,X_project_type => x_project_type
392 ,X_project_start_date => X_project_start_date
393 ,X_project_end_date => X_project_end_date
394 ,X_public_sector_flag => X_public_sector_flag
395 ,X_attribute_category => X_attribute_category
396 ,X_attribute1 => X_attribute1
397 ,X_attribute2 => X_attribute2
398 ,X_attribute3 => X_attribute3
399 ,X_attribute4 => X_attribute4
400 ,X_attribute5 => X_attribute5
401 ,X_attribute6 => X_attribute6
402 ,X_attribute7 => X_attribute7
403 ,X_attribute8 => X_attribute8
404 ,X_attribute9 => X_attribute9
405 ,X_attribute10 => X_attribute10
406 ,x_pm_product_code => x_pm_product_code
407 ,x_err_code => l_error_number
408 ,x_warnings_only_flag => l_warnings_only_flag );
409
410 --bug 3134205
411 x_warnings_only_flag := l_warnings_only_flag;
412
413 IF (l_error_number < 0) THEN
414 FND_MSG_PUB.Add_Exc_Msg(
415 p_pkg_name => 'PA_CLIENT_EXTN_PROJ_STATUS',
416 p_procedure_name => 'VERIFY_PROJECT_STATUS_CHANGE',
417 p_error_text => 'ORA-'||LPAD(substr(X_err_stage,2),5,'0'));
418 X_err_code := l_error_number;
419 IF (l_error_code = 0) THEN
420 x_err_code := -1;
421 x_err_stage := to_char(l_error_number);
422 END IF;
423 END IF;
424
425 -- Begin of Fix for error code > 0 ssanckar on 8th Jul 99
426
427 IF (l_error_number > 0) THEN
428 l_error_code := l_error_number;
429 END IF;
430
431 -- End of Fix for error code > 0 ssanckar on 8th Jul 99
432
433 -- Set the return values if errors have occurred
434 IF (l_error_code <> 0) THEN
435 X_err_code := l_error_code;
436 X_err_stage := l_error_msg;
437 END IF;
438
439 ELSIF X_Context = 'ARCHIVE_PURGE' then
440 IF X_new_value IS NULL OR X_project_id IS NULL THEN
441 x_err_code := 0;
442 RETURN;
443 END IF;
444
445 pa_debug.debug('Calling validate process for costing for project '||to_char(X_project_id));
446 x_err_stage := 'Calling validate process for billing for project '||to_char(X_project_id);
447 pa_purge_validate_costing.validate_costing(p_project_id => X_project_id,
448 p_txn_to_date => pa_purge_validate.g_txn_to_date ,
449 p_active_flag => pa_purge_validate.g_active_flag,
450 x_err_code => x_err_code,
451 x_err_stack => x_err_stack,
452 x_err_stage => x_err_stage
453 );
454
455 if pa_purge_validate.g_project_type_class_code = 'CONTRACT' then
456
457 pa_debug.debug('Calling validate process for billing for project '||to_char(X_project_id));
458 x_err_stage := 'Calling validate process for billing for project '||to_char(X_project_id);
459 pa_purge_validate_billing.validate_billing(p_project_id => X_project_id,
460 p_txn_to_date => pa_purge_validate.g_txn_to_date ,
461 p_active_flag => pa_purge_validate.g_active_flag,
462 x_err_code => x_err_code,
463 x_err_stack => x_err_stack,
464 x_err_stage => x_err_stage
465 );
466 end if;
467
468 pa_debug.debug('Calling validate process for capital for project '||to_char(X_project_id));
469
470 x_err_stage := 'Calling validate process for capital for project '||to_char(X_project_id);
471 pa_purge_validate_capital.validate_capital( p_project_id => X_project_id,
472 p_purge_to_date => pa_purge_validate.g_txn_to_date ,
473 p_active_flag => pa_purge_validate.g_active_flag,
474 p_err_code => x_err_code,
475 p_err_stack => x_err_stack,
476 p_err_stage => x_err_stage
477 );
478
479 pa_debug.debug('Calling validate process for PJRM for project '||to_char(X_project_id));
480
481 x_err_stage := 'Calling validate process for PJRM for project '||to_char(X_project_id);
482
483 pa_purge_validate_pjrm.validate_requirement (p_project_id => X_project_id,
484 p_txn_to_date => pa_purge_validate.g_txn_to_date ,
485 p_active_flag => pa_purge_validate.g_active_flag,
486 x_err_code => x_err_code,
487 x_err_stack => x_err_stack,
488 x_err_stage => x_err_stage
489 );
490
491 pa_purge_validate_pjrm.validate_assignment (p_project_id => X_project_id,
492 p_txn_to_date => pa_purge_validate.g_txn_to_date ,
493 p_active_flag => pa_purge_validate.g_active_flag,
494 x_err_code => x_err_code,
495 x_err_stack => x_err_stack,
496 x_err_stage => x_err_stage
497 );
498
499 /* Bug#2416385 Code added for phase III of archive and Purge, starts here */
500
501 pa_debug.debug('Calling validate process for IC and IP Billing for project '||to_char(X_project_id));
502 x_err_stage := 'Calling validate process for IC and IP Billing for project '||to_char(X_project_id);
503 pa_purge_validate_icip.validate_IC_IP(p_project_id => X_project_id,
504 p_txn_to_date => pa_purge_validate.g_txn_to_date ,
505 p_active_flag => pa_purge_validate.g_active_flag,
506 x_err_code => x_err_code,
507 x_err_stack => x_err_stack,
508 x_err_stage => x_err_stage
509 );
510
511 /* Bug#2416385 Code added for phase III of archive and Purge, ends here */
512
513 /* Code changes for Bug 2962582 starts here */
514 pa_purge_validate_pjrm.Validate_PJI(p_project_id => x_project_id,
515 p_project_end_date => x_project_end_date,
516 x_err_code => x_err_code,
517 x_err_stack => x_err_stack,
518 x_err_stage => x_err_stage
519 );
520
521 /* Code changes for Bug 2962582 ends here */
522
523
524 /* Code changes for Bug 4255353 starts here */
525 pa_purge_validate_pjrm.Validate_Perf_reporting(p_project_id => x_project_id,
526 x_err_code => x_err_code,
527 x_err_stack => x_err_stack,
528 x_err_stage => x_err_stage
529 );
530
531 /* Code changes for Bug 4255353 ends here */
532
533 -- Client extension for the user if he wants to put any extra validation.
534
535 pa_debug.debug('Calling validate process for client extn for project '||to_char(X_project_id));
536
537 x_err_stage := 'Calling validate process for client extn for project '||to_char(X_project_id);
538 pa_purge_extn_validate.validate_extn( p_project_id => X_project_id,
539 p_txn_through_date => pa_purge_validate.g_txn_to_date ,
540 p_active_flag => pa_purge_validate.g_active_flag,
541 x_err_code => x_err_code,
542 x_err_stack => x_err_stack,
543 x_err_stage => x_err_stage
544 );
545
546 END IF; -- X_context =
547
548 x_err_stack := old_stack;
549
550 EXCEPTION
551 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
552 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
553
554 WHEN OTHERS THEN
555 /* If X_Context is ARCHIVE_PURGE then pa_debug is used for logging errors in
556 * cocurrent request log */
557
558 IF X_Context='ARCHIVE_PURGE' THEN
559 pa_debug.debug('Procedure Name := PA_PROJECT_UTILS2.VALIDATE_ATTRIBUTE_CHANGE');
560 pa_debug.debug('Error stage is '||x_err_stage );
561 pa_debug.debug('Error stack is '||x_err_stack );
562 pa_debug.debug(SQLERRM);
563 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
564
565 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
566 ELSE
567
568 /* Other X_Context values ORGANIZATION_VALIDATION and PROJECT_STATUS_CHANGE
569 * return to forms so nedd to use fnd calls to handle it. */
570
571 x_err_code := -1;
572 x_err_stage := to_char(SQLCODE);
573 FND_MSG_PUB.Add_Exc_Msg(
574 p_pkg_name => 'PA_PROJECT_UTILS2',
575 p_procedure_name => 'VALIDATE_ATTRIBUTE_CHANGE',
576 p_error_text => 'ORA-'||LPAD(substr(X_err_stage,2),5,'0'));
577 END IF;
578
579 END;
580
581 FUNCTION Get_project_business_group
582 (p_project_id IN pa_projects_all.project_id%TYPE) RETURN NUMBER IS
583 -- This function returns the business group for a project
584 l_bg_id NUMBER := 0;
585 BEGIN
586 SELECT impl.business_group_id
587 INTO l_bg_id
588 FROM pa_implementations_all impl,
589 pa_projects_all pap
590 WHERE pap.project_id = p_project_id
591 AND pap.org_id = impl.org_id; --MOAC Changes: Bug 4363092: removed nvl usage with org_id
592 RETURN l_bg_id ;
593 EXCEPTION
594
595 WHEN OTHERS THEN
596
597 RAISE;
598 END get_project_business_group ;
599
600
601 PROCEDURE Check_Project_Number_Or_Id
602 ( p_project_id IN pa_projects_all.project_id%TYPE
603 ,p_project_number IN pa_projects_all.segment1%TYPE
604 ,p_check_id_flag IN VARCHAR2 := 'A'
605 ,x_project_id OUT NOCOPY pa_projects_all.project_id%TYPE --File.Sql.39 bug 4440895
606 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
607 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
608
609
610 l_current_id NUMBER := NULL;
611 l_num_ids NUMBER := 0;
612 l_id_found_flag VARCHAR(1) := 'N';
613
614 CURSOR c_ids IS
615 SELECT project_id
616 FROM pa_projects_all
617 WHERE segment1 = p_project_number;
618
619 BEGIN
620
621 IF (p_project_id IS NOT NULL) THEN
622 IF (p_check_id_flag = 'Y') THEN
623 -- Validate ID
624 SELECT project_id
625 INTO x_project_id
626 FROM pa_projects_all
627 WHERE project_id = p_project_id;
628 ELSIF (p_check_id_flag = 'N') THEN
629 -- No ID validation necessary
630 x_project_id := p_project_id;
631 ELSIF (p_check_id_flag = 'A') THEN
632 IF (p_project_number IS NULL) THEN
633 -- Return a null ID since the name is null.
634 x_project_id := NULL;
635 ELSE
636 -- Find the ID which matches the Name passed
637 OPEN c_ids;
638 LOOP
639 FETCH c_ids INTO l_current_id;
640 EXIT WHEN c_ids%NOTFOUND;
641 IF (l_current_id = p_project_id) THEN
642 l_id_found_flag := 'Y';
643 x_project_id := p_project_id;
644 END IF;
645 END LOOP;
646 l_num_ids := c_ids%ROWCOUNT;
647 CLOSE c_ids;
648
649 IF (l_num_ids = 0) THEN
650 -- No IDs for name
651 RAISE NO_DATA_FOUND;
652 ELSIF (l_num_ids = 1) THEN
653 -- Since there is only one ID for the name use it.
654 x_project_id := l_current_id;
655 ELSIF (l_id_found_flag = 'N') THEN
656 -- More than one ID for the name and none of the IDs matched
657 -- the ID passed in.
658 RAISE TOO_MANY_ROWS;
659 END IF;
660 END IF;
661 END IF;
662 ELSE -- Find ID since it was not passed.
663 IF (p_project_number IS NOT NULL) THEN
664 SELECT project_id
665 INTO x_project_id
666 FROM pa_projects_all
667 WHERE segment1 = p_project_number;
668 ELSE
669 x_project_id := NULL;
670 END IF;
671 END IF;
672
673 x_return_status:= FND_API.G_RET_STS_SUCCESS;
674
675 EXCEPTION
676 WHEN NO_DATA_FOUND THEN
677 x_project_id := NULL;
678 x_return_status := FND_API.G_RET_STS_ERROR;
679 x_error_message_code := 'PA_PROJECT_NUMBER_INVALID';
680 WHEN TOO_MANY_ROWS THEN
681 x_project_id := NULL;
682 x_return_status := FND_API.G_RET_STS_ERROR;
683 x_error_message_code := 'PA_PROJECT_NUMBER_INVALID';
684 WHEN OTHERS THEN
685 x_project_id := NULL;
686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
687 FND_MSG_PUB.add_exc_msg(p_pkg_name =>'PA_PROJECT_UTILS2',
688 p_procedure_name => 'Check_Project_Number_Or_Id');
689 RAISE;
690 END Check_Project_Number_Or_Id;
691
692 -- Procedure : AbortWorkflow
693 -- Type :
694 -- Purpose : This API will is called when the Abort WorkFlow button is pressed on the project status
695 -- change page
696 -- Note :
697 -- Parameters Type Required Description and Purpose
698 -- --------------------------- ------ -------- --------------------------------------------------------
699 -- p_project_id NUMBER Y The project id
700 -- p_record_version_number NUMBER Y The record version number
701 PROCEDURE AbortWorkflow( p_project_id IN NUMBER,
702 p_record_version_number IN NUMBER,
703 x_return_status OUT NOCOPY VARCHAR2,
704 x_msg_count OUT NOCOPY NUMBER,
705 x_msg_data OUT NOCOPY VARCHAR2 ) IS
706 CURSOR c_wf_type IS
707 SELECT ps.workflow_item_type
708 FROM pa_project_statuses ps,
709 pa_projects_all ppa
710 WHERE ppa.project_id = p_project_id
711 AND ppa.project_status_code = ps.project_status_code
712 AND ps.enable_wf_flag = 'Y'
713 AND ps.wf_success_status_code is NOT NULL
714 AND ps.wf_failure_status_code is NOT NULL;
715
716 CURSOR get_last_workflow_info(p_wf_item_type IN VARCHAR2) IS
717 SELECT MAX(item_key)
718 FROM pa_wf_processes
719 WHERE item_type = p_wf_item_type
720 AND entity_key1 = p_project_id
721 AND wf_type_code = 'PROJECT';
722
723 CURSOR get_prev_status(c_project_id IN VARCHAR2) IS
724 SELECT a.old_project_status_code, a.new_project_status_code
725 FROM ( SELECT obj_status_change_id,
726 old_project_status_code,
727 new_project_status_code
728 FROM pa_obj_status_changes
729 WHERE object_type = 'PA_PROJECTS'
730 AND object_id = p_project_id
731 ORDER BY obj_status_change_id DESC ) a
732 WHERE ROWNUM = 1;
733
734 Invalid_Arg_Exc EXCEPTION;
735
736 l_diagramUrl VARCHAR2(2000);
737 l_wf_item_type pa_project_statuses.workflow_item_type%TYPE;
738 l_wf_process pa_project_statuses.workflow_process%TYPE;
739 l_item_key pa_wf_processes.item_key%TYPE;
740 l_prev_status pa_obj_status_changes.old_project_status_code%TYPE;
741 l_curr_status pa_obj_status_changes.new_project_status_code%TYPE;
742 l_comment pa_ci_comments.comment_text%TYPE;
743
744 l_debug_mode VARCHAR2(1);
745 l_calling_module VARCHAR2(50) := 'SSO_ABORT'; -- for the BUG # 6661144
746
747 l_debug_level2 CONSTANT NUMBER := 2;
748 l_debug_level3 CONSTANT NUMBER := 3;
749 l_debug_level4 CONSTANT NUMBER := 4;
750 l_debug_level5 CONSTANT NUMBER := 5;
751 BEGIN
752 x_msg_count := 0;
753 x_return_status := FND_API.G_RET_STS_SUCCESS;
754 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
755
756 IF l_debug_mode = 'Y' THEN
757 PA_DEBUG.set_curr_function( p_function => 'AbortWorkflow',
758 p_debug_mode => l_debug_mode );
759 END IF;
760
761 IF l_debug_mode = 'Y' THEN
762 Pa_Debug.g_err_stage:= 'Printing Input parameters';
763 Pa_Debug.WRITE('PA_PROJECT_UTILS2',Pa_Debug.g_err_stage,
764 l_debug_level3);
765
766 Pa_Debug.WRITE('PA_PROJECT_UTILS2','p_project_id'||':'||p_project_id,
767 l_debug_level3);
768 END IF;
769
770 IF l_debug_mode = 'Y' THEN
771 Pa_Debug.g_err_stage:= 'Validating Input parameters';
772 Pa_Debug.WRITE('PA_PROJECT_UTILS2',Pa_Debug.g_err_stage,
773 l_debug_level3);
774 END IF;
775
776 IF ( p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM )
777 THEN
778 IF l_debug_mode = 'Y' THEN
779 Pa_Debug.g_err_stage:= 'PA_PROJECT_UTILS2 : AbortWorkflow : p_project_id IS NULL';
780 Pa_Debug.WRITE('PA_PROJECT_UTILS2',Pa_Debug.g_err_stage,
781 l_debug_level3);
782 END IF;
783 RAISE Invalid_Arg_Exc;
784 END IF;
785
786 OPEN c_wf_type;
787 FETCH c_wf_type INTO l_wf_item_type;
788 CLOSE c_wf_type;
789
790 OPEN get_last_workflow_info( l_wf_item_type );
791 FETCH get_last_workflow_info INTO l_item_key;
792 CLOSE get_last_workflow_info;
793
794 OPEN get_prev_status(p_project_id);
795 FETCH get_prev_status INTO l_prev_status, l_curr_status;
796 CLOSE get_prev_status;
797
798
799 --Abort the workflow
800 pa_control_items_workflow.cancel_workflow
801 (l_wf_item_type,
802 l_item_key,
803 x_msg_count,
804 x_msg_data,
805 x_return_status);
806
807 --Retrieve the comment to be put into the status change history
808 fnd_message.set_name('PA', 'PA_CI_ABORT_WF_COMMENT');
809 l_comment := fnd_message.get;
810
811 --Change the project status back to the previous status
812 -- for the BUG # 6661144
813 PA_PROJECTS_MAINT_PUB.project_status_change(
814 p_project_id => p_project_id
815 ,p_new_status_code => l_prev_status
816 ,p_comment => l_comment
817 ,p_calling_module => l_calling_module
818 ,x_return_status => x_return_status
819 ,x_msg_count => x_msg_count
820 ,x_msg_data => x_msg_data );
821
822 EXCEPTION
823 WHEN OTHERS THEN
824
825 x_return_status := 'U';
826 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_UTILS2',
827 p_procedure_name => 'AbortWorkflow',
828 p_error_text => SUBSTRB(SQLERRM,1,240));
829
830 fnd_msg_pub.count_and_get(p_count => x_msg_count,
831 p_data => x_msg_data);
832 END AbortWorkflow;
833
834
835 END PA_PROJECT_UTILS2 ;