1 package body pa_purge_validate_pjrm as
2 /* $Header: PAXRMVTB.pls 120.2 2005/08/19 17:19:18 mwasowic noship $ */
3
4 -- Start of comments
5 -- API name : Validate_pjrm
6 -- Type : Public
7 -- Pre-reqs : None
8 -- Function : Validates the project resource management details
9 -- and a project is not purged if there exists any
10 -- PJRM transactions for a project.
11 -- Following validations are performed.
12 -- 1. If there exists any assignment or requirement.
13 -- 2. If the project is of unassigned time or
14 -- an administrative type.
15 --
16 -- Parameters
17 -- p_project_Id IN NUMBER,
18 -- The project id for which records have
19 -- to be purged/archived.
20 -- p_Active_Flag IN VARCHAR2,
21 -- Indicates if batch contains ACTIVE or CLOSED projects
22 -- ( 'A' - Active , 'C' - Closed)
23 -- p_Txn_To_Date IN DATE,
24 -- Date on or before which all transactions are to be purged
25 -- (Will be used by Costing only)
26 -- X_Err_Stack IN OUT VARCHAR2,
27 -- Error stack
28 -- X_Err_Stage IN OUT VARCHAR2,
29 -- Stage in the procedure where error occurred
30 -- X_Err_Code IN OUT NUMBER
31 -- Error code returned from the procedure
32 -- = 0 SUCCESS
33 -- > 0 Application error
34 -- < 0 Oracle error
35 -- End of comments
36
37 procedure validate_pjrm ( p_project_id in NUMBER,
38 p_txn_to_date in DATE,
39 p_active_flag in VARCHAR2,
40 x_err_code in OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
41 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
42 x_err_stage in OUT NOCOPY VARCHAR2 ) is --File.Sql.39 bug 4440895
43
44 -- Cursor for pjrm validaton before purge
45 --
46 cursor IsAPJRMProject is
47
48 select 'Assignment or Requirement Exists' , 'PA_ARPR_ASG_REQ_EXISTS'
49 from dual
50 where exists ( select NULL
51 from pa_project_assignments pa
52 where nvl(pa.project_id, 0) = p_project_id
53 )
54 UNION
55 select 'Administrative or Unassigned Time Type' , 'PA_ARPR_ADM_UNASS_PRJ_TYP'
56 from dual
57 where exists ( select pt.project_type
58 from pa_project_types_all pt,
59 pa_projects_all p
60 where p.project_id = p_project_id
61 and pt.project_type = p.project_type
62 and ( nvl(pt.administrative_flag, 'N') = 'Y'
63 or nvl(pt.unassigned_time, 'N') = 'Y' ));
64
65 l_err_stack VARCHAR2(2000);
66 l_err_stage VARCHAR2(500);
67 l_err_code NUMBER ;
68 l_dummy VARCHAR2(500);
69 l_msg_name VARCHAR2(50);
70
71 BEGIN
72
73 /*
74 l_err_code := 0 ;
75 l_err_stage := x_err_stage;
76 l_err_stack := x_err_stack;
77 pa_debug.debug('-- Performing pjrm validation for project '||to_char(p_project_id));
78
79 -- Open cursor
80 -- If cursor returns one or more rows , indicates that
81 -- project is not valid for purge as far as pjrm is concerned
82 --
83
84 Open IsAPJRMProject ;
85
86 pa_debug.debug('-- After Open cursor IsAPJRMProject');
87
88 LOOP
89
90 -- Fetch a row for each validation that failed
91 -- and set the appropriate message
92 --
93 Fetch IsAPJRMProject into l_dummy , l_msg_name ;
94 Exit When IsAPJRMProject%Notfound;
95 fnd_message.set_name('PA',l_msg_name );
96 fnd_msg_pub.add;
97 x_err_stack := x_err_stack || ' ->After open cursor ' ||l_dummy ;
98 pa_debug.debug(' * ' || l_dummy|| ' for ' || to_char(p_project_id));
99
100
101 END LOOP;
102
103 close IsAPJRMProject;
104
105 x_err_stage := l_err_stage ;
106 x_err_stack := l_err_stack ;
107
108 EXCEPTION
109 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
110 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
111
112 WHEN OTHERS THEN
113 x_err_stage := l_err_stage ;
114 pa_debug.debug('Error Procedure Name := PA_PURGE_VALIDATE_PJRM.VALIDATE_PJRM' );
115 pa_debug.debug('Error stage is '||l_err_stage );
116 pa_debug.debug('Error stack is '||l_err_stack );
117 pa_debug.debug(SQLERRM);
118 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
119
120 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ; */
121
122 NULL;
123
124 END validate_pjrm ;
125
126
127 -- Start of comments
128 -- API name : Validate_Requirement
129 -- Type : Public
130 -- Pre-reqs : None
131 -- Function : Validates the project resource management details for requirements
132 --
133
134
135 PROCEDURE Validate_Requirement( p_project_id in NUMBER,
136 p_txn_to_date in DATE,
137 p_active_flag in VARCHAR2,
138 x_err_code in OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
139 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
140 x_err_stage in OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
141
142
143 /*Bug 2489778:For closed prject purge, even the requirement exist after project closed date,
144 the requirement and project can be purged.So this cursoris not valid anymore.
145
146 The below cursor will select any open requirement for the passed project id. If the
147 Cursor return any row, it means that project contains requirements in open status, so
148 Requirement and project cannot be purged. This curor is applicable in case of Closed
149 Projects.
150
151 CURSOR CUR_REQUIREMENTS_CLOSED IS
152 SELECT 1
153 FROM pa_project_assignments pa, pa_project_statuses ps, pa_projects pr
154 WHERE pa.assignment_type = 'OPEN_ASSIGNMENT'
155 AND pa.status_code = ps.project_status_code
156 AND ps.status_type='OPEN_ASGMT'
157 AND ps.project_system_status_code ='OPEN_ASGMT'
158 AND pa.project_id = P_PROJECT_ID
159 AND pa.end_date > nvl(p_txn_to_date,pr.closed_date)
160 AND pa.project_id=pr.project_id;
161
162 */
163
164 /*The below cursor will select any open requirement for the passed project id which
165 exist before purge Till Date. If the cursor return any row,it means that project
166 contains requirements in open status, so Requirement and project cannot be purged.
167 This cursor is applicable for Open Indirect Project Purge.*/
168
169 CURSOR CUR_REQUIREMENTS_ACTIVE IS
170 SELECT 1
171 FROM pa_project_assignments pa, pa_project_statuses ps
172 WHERE pa.assignment_type = 'OPEN_ASSIGNMENT'
173 AND pa.status_code = ps.project_status_code
174 AND ps.status_type='OPEN_ASGMT'
175 AND ps.project_system_status_code ='OPEN_ASGMT'
176 AND pa.project_id = P_PROJECT_ID
177 AND p_active_flag = 'A'
178 AND pa.end_date <= P_txn_to_date;
179
180
181 l_err_stack VARCHAR2(2000);
182 l_err_stage VARCHAR2(500);
183 l_err_code NUMBER ;
184 l_dummy NUMBER;
185 l_msg_name VARCHAR2(50);
186
187 BEGIN
188
189 l_err_code := 0 ;
190 l_err_stage := x_err_stage;
191 l_err_stack := x_err_stack;
192 pa_debug.debug('Performing Requirement validation for project '||to_char(p_project_id));
193
194
195 /* Indirect project purge validations */
196
197 IF p_active_flag ='A' THEN
198 pa_debug.debug('Opening cursor for Open Indirect Project purge ');
199
200 OPEN CUR_REQUIREMENTS_ACTIVE;
201 FETCH CUR_REQUIREMENTS_ACTIVE INTO l_dummy;
202
203 IF CUR_REQUIREMENTS_ACTIVE%FOUND THEN
204 fnd_message.set_name('PA', 'PA_ARPR_OPEN_REQ_EXIST');
205 fnd_msg_pub.add;
206 l_err_code := 10 ;
207
208 l_err_stage := 'After checking for Requirements for Open Indirect Project';
209 l_err_stack := l_err_stack ||
210 ' ->After checking for Requirements for Open Indirect Project';
211 pa_debug.debug(' The project '||to_char(p_project_id)|| 'has Requirements in open status before purge date');
212
213 END IF;
214 CLOSE CUR_REQUIREMENTS_ACTIVE;
215
216 /* Code commented for bug 2489778
217 ELSE
218 pa_debug.debug('Opening cursor for Closed Project purge ');
219
220 OPEN CUR_REQUIREMENTS_CLOSED;
221 FETCH CUR_REQUIREMENTS_CLOSED INTO l_dummy;
222
223 IF CUR_REQUIREMENTS_CLOSED%FOUND THEN
224 fnd_message.set_name('PA', 'PA_ARPR_CLOSED_REQ_EXIST');
225 fnd_msg_pub.add;
226 l_err_code := 10 ;
227 l_err_stage := 'After checking for Requirements for Closed Project';
228 l_err_stack := l_err_stack ||
229 ' ->After checking for Requirements for Closed Project';
230 pa_debug.debug(' The project '||to_char(p_project_id)|| 'has Requirements in Open Status ');
231
232 END IF;
233 CLOSE CUR_REQUIREMENTS_CLOSED;
234 */
235
236 END IF;
237
238 x_err_code := l_err_code ;
239 x_err_stage := l_err_stage ;
240 x_err_stack := l_err_stack ;
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 x_err_stage := l_err_stage ;
245 pa_debug.debug('Error Procedure Name := PA_PURGE_VALIDATE_COSTING.VALIDATE_COSTING' );
246 pa_debug.debug('Error stage is '||l_err_stage );
247 pa_debug.debug('Error stack is '||l_err_stack );
248 pa_debug.debug(SQLERRM);
249 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
250
251 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
252
253
254 END Validate_Requirement;
255
256
257 -- Start of comments
258 -- API name : Validate_Assignments
259 -- Type : Public
260 -- Pre-reqs : None
261 -- Function : Validates the project resource management details for requirements
262 -- The proceduire do following validations
263 -- 1.In case of closed project purge,if there exist any assignment whose end date
264 -- is greater than project closed date,then pjr assignment and project will not be purged.
265 -- In above validation, the procedure will return error message if validation fails.
266 --
267 --
268
269 PROCEDURE Validate_assignment ( p_project_id in NUMBER,
270 p_txn_to_date in DATE,
271 p_active_flag in VARCHAR2,
272 x_err_code in OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
273 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
274 x_err_stage in OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
275
276
277 /*This cursor will select any assignments for project passed (closed Project)
278 Which is having assignment end date greater than Purge Till Date and NOT cancelled.
279 This is required, as even there exist any assignment with cancelled status with end
280 Date Greater than purge till date, the assignment can be purged.
281 Also, as p_txn_to_date will be NULL in case of closed project purge, the assignment
282 Dates are compared with project Closed Date*/
283
284 CURSOR CUR_ASSIGNMENTS IS
285 SELECT 1
286 FROM Pa_project_assignments pa
287 , pa_project_statuses ps
288 , pa_projects pr
289 WHERE pa.project_id = P_Project_Id
290 AND pa.assignment_type <>'OPEN_ASSIGNMENT'
291 AND nvl(p_txn_to_date,Pr.CLOSED_DATE) < pa.end_date
292 AND pa.status_code = ps.project_status_code
293 AND ps.status_type = 'STAFFED_ASGMT'
294 AND ps.project_system_status_code <> 'STAFFED_ASGMT_CANCEL'
295 AND pr.project_id =pa.project_id;
296
297 l_err_stack VARCHAR2(2000);
298 l_err_stage VARCHAR2(500);
299 l_err_code NUMBER ;
300 l_dummy NUMBER;
301 l_msg_name VARCHAR2(50);
302
303 BEGIN
304
305 l_err_code := 0 ;
306 l_err_stage := x_err_stage;
307 l_err_stack := x_err_stack;
308 pa_debug.debug('Performing validation whether any active assignment exist for the project '||to_char(p_project_id));
309
310 /* Project purge validations for active assignments */
311
312 IF p_active_flag <> 'A' THEN
313 pa_debug.debug('Opening cursor for Active Assignments ');
314
315 OPEN CUR_ASSIGNMENTS;
316 FETCH CUR_ASSIGNMENTS INTO l_dummy;
317
318 IF CUR_ASSIGNMENTS%FOUND THEN
319 fnd_message.set_name('PA', 'PA_ARPR_CLOSED_ASGMT_EXIST');
320 fnd_msg_pub.add;
321 l_err_code := 10 ;
322 l_err_stage := 'After checking for Assignments for Closed Project';
323 l_err_stack := l_err_stack ||
324 ' ->After checking for Assignments for Closed Project';
325 pa_debug.debug(' The project '||to_char(p_project_id)|| 'has assignments either in provisional or confirmed Status ');
326 END IF;
327 CLOSE CUR_ASSIGNMENTS;
328
329 END IF;
330
331 x_err_code := l_err_code ;
332 x_err_stage := l_err_stage ;
333 x_err_stack := l_err_stack ;
334
335 EXCEPTION
336
337 WHEN OTHERS THEN
338 x_err_stage := l_err_stage ;
339 pa_debug.debug('Error Procedure Name := PA_PURGE_VALIDATE_COSTING.VALIDATE_COSTING' );
340 pa_debug.debug('Error stage is '||l_err_stage );
341 pa_debug.debug('Error stack is '||l_err_stack );
342 pa_debug.debug(SQLERRM);
343 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
344
345 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
346
347 END validate_assignment;
348
349
350 /* The below procedure is added for bug 2962582
351 Created By: Vinay */
352
353 -- Start of comments
354 -- API name : Validate_PJI
355 -- Type : Public
356 -- Pre-reqs : None
357 -- Function : Validates the PJI details for the project.
358 -- The procedure does the following validations
359 -- 1. In case PJI is installed and the project has unsummarized transactions, then it returns
360 -- error message.
361 --
362
363 -- Parameters
364 -- p_project_Id IN NUMBER The project id for which records have
365 -- to be purged/archived.
366 -- p_project_end_date IN DATE End date of the project to be purged.
367 --
368 -- X_Err_Stack IN OUT VARCHAR2 Error stack
369 --
370 -- X_Err_Stage IN OUT VARCHAR2 Stage in the procedure where error occurred
371 --
372 -- X_Err_Code IN OUT NUMBER Error code returned from the procedure
373 -- = 0 SUCCESS
374 -- > 0 Application error
375 -- < 0 Oracle error
376 -- End of comments
377
378 Procedure Validate_PJI ( p_project_id IN NUMBER,
379 p_project_end_date IN DATE,
380 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
381 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
382 x_err_stage IN OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
383 IS
384
385 l_count NUMBER := -1;
386 l_install VARCHAR2(1);
387 l_start_date DATE;
388 l_project_status PA_PROJECTS_ALL.project_status_code%TYPE;
389 l_count_fnd NUMBER := 0;
390 l_count_drv NUMBER := 0;
391 l_count_cdl NUMBER := 0;
392 l_count_inv NUMBER := 0;
393 l_count_log NUMBER := 0;
394 l_string VARCHAR2(1000);
395
396 l_err_stack VARCHAR2(2000);
397 l_err_stage VARCHAR2(500);
398 l_err_code NUMBER ;
399
400 CURSOR chk_funding IS
401 SELECT 1
402 FROM DUAL
403 WHERE EXISTS
404 (SELECT NULL
405 FROM pa_project_fundings
406 WHERE project_id = p_project_id
407 AND pji_summarized_flag = 'N');
408
409 CURSOR chk_revenue IS
410 SELECT 1
411 FROM DUAL
412 WHERE EXISTS
413 (SELECT NULL
414 FROM pa_draft_revenues_all
415 WHERE project_id = p_project_id
416 AND released_date IS NOT NULL
417 AND transfer_status_code = 'A'
418 AND pji_summarized_flag = 'N');
419
420 CURSOR chk_cdl IS
421 SELECT 1
422 FROM DUAL
423 WHERE EXISTS
424 (SELECT NULL
425 FROM pa_cost_distribution_lines_all
426 WHERE project_id = p_project_id
427 AND line_type IN ('R', 'I')
428 AND pji_summarized_flag = 'N');
429
430 CURSOR chk_inv IS
431 SELECT 1
432 FROM DUAL
433 WHERE EXISTS
434 (SELECT NULL
435 FROM pa_draft_invoices_all
436 WHERE project_id = p_project_id
437 AND system_reference IS NOT NULL
438 AND system_reference <> 0
439 AND pji_summarized_flag = 'N');
440
441 CURSOR chk_log IS
442 SELECT 1
443 FROM DUAL
444 WHERE EXISTS
445 (SELECT NULL
446 FROM pa_pji_proj_events_log
447 WHERE event_type in ('Projects', 'Classifications', 'DRAFT_REVENUES')
448 and event_object = p_project_id); /* Added this condition for bug 3807671 */
449
450 BEGIN
451
452 l_err_code := 0 ;
453 l_err_stage := x_err_stage;
454 l_err_stack := x_err_stack;
455 pa_debug.debug('Performing validation whether any unsummarized PJI transaction exists for project '||to_char(p_project_id));
456
457 l_install := PA_INSTALL.is_pji_licensed;
458 IF l_install = 'Y' THEN
459 -- Begin. Added for bug 3130009.
460 l_install := '';
461 l_install := PA_INSTALL.is_pji_installed;
462 IF l_install = 'Y' THEN
463 l_string := 'SELECT COUNT(*) FROM pji_system_parameters';
464 EXECUTE IMMEDIATE l_string INTO l_count;
465 ELSE
466 RETURN;
467 END IF;
468 -- End. Added for bug 3130009.
469 ELSE
470 RETURN;
471 END IF;
472
473 IF l_count > 1 THEN
474 l_string := 'SELECT BIS_COMMON_PARAMETERS.get_global_start_date FROM DUAL';
475 EXECUTE IMMEDIATE l_string INTO l_start_date;
476
477 SELECT project_status_code
478 INTO l_project_status
479 FROM pa_projects_all
480 WHERE project_id = p_project_id;
481
482 IF nvl(p_project_end_date, l_start_date ) >= l_start_date
483 AND PA_PROJECT_UTILS.check_prj_stus_action_allowed(l_project_status, 'STATUS_REPORTING') = 'Y' THEN
484
485 OPEN chk_funding;
486 FETCH chk_funding INTO l_count_fnd;
487 CLOSE chk_funding;
488
489 OPEN chk_revenue;
490 FETCH chk_revenue INTO l_count_drv;
491 CLOSE chk_revenue;
492
493 OPEN chk_cdl;
494 FETCH chk_cdl INTO l_count_cdl;
495 CLOSE chk_cdl;
496
497 OPEN chk_inv;
498 FETCH chk_inv INTO l_count_inv;
499 CLOSE chk_inv;
500
501 OPEN chk_log;
502 FETCH chk_log INTO l_count_log;
503 CLOSE chk_log;
504
505 IF nvl(l_count_fnd, 0) = 1 OR nvl(l_count_drv, 0) = 1
506 OR nvl(l_count_cdl, 0) = 1 OR nvl(l_count_inv, 0) = 1
507 OR nvl(l_count_log, 0) = 1 THEN
508
509 fnd_message.set_name('PA', 'PA_PJI_UNSUMMARIZED_EXIST');
510 fnd_msg_pub.add;
511 l_err_code := 10;
512 l_err_stage := 'After checking for PJI details the Project';
513 l_err_stack := l_err_stack ||
514 ' ->After checking for PJI details for the Project';
515 pa_debug.debug(' The project '||to_char(p_project_id)|| 'has transactions that are not summarized by PJI module.');
516 END IF;
517
518 END IF;
519
520 END IF;
521
522 END Validate_PJI;
523
524
525 /*bug 4255353*/
526 Procedure Validate_Perf_reporting ( p_project_id IN NUMBER,
527 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
528 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
529 x_err_stage IN OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
530 cursor chk_pji_migrated IS
531 SELECT 1 from dual
532 where exists (select 1
533 from pa_projects_all
534 where project_id=p_project_id
535 and pji_source_flag='Y');
536 l_count_migrated NUMBER :=0;
537 l_err_stack VARCHAR2(2000);
538 l_err_stage VARCHAR2(500);
539 l_err_code NUMBER ;
540 BEGIN
541 l_err_code := 0 ;
542 l_err_stage := x_err_stage;
543 l_err_stack := x_err_stack;
544 OPEN chk_pji_migrated;
545 FETCH chk_pji_migrated INTO l_count_migrated;
546 CLOSE chk_pji_migrated;
547
548 IF l_count_migrated = 1 AND NVL(pa_purge_validate_pjrm.g_purge_summary_flag,'N')='Y' THEN
549 fnd_message.set_name('PA', 'PA_PJI_MIGRATED_PROJECT');
550 fnd_msg_pub.add;
551 l_err_code := 10;
552 l_err_stage:='After checking for Perf Reporting details the Project';
553 l_err_stack := l_err_stack ||
554 ' ->After checking for Perf Reporting details for the Project';
555 pa_debug.debug('The project '||to_char(p_project_id)|| 'has PJI data migrated to the new Summarization Model');
556
557 END IF;
558 x_err_stack:= l_err_stack;
559 x_err_code := l_err_code;
560 x_err_stage := l_err_stage ;
561 EXCEPTION
562 WHEN OTHERS THEN
563 x_err_stage := l_err_stage ;
564 l_err_stack := l_err_stack ||SUBSTR(SQLERRM,100);
565 pa_debug.debug('Error Procedure Name := pa_purge_validate_pjrm.VALIDATE_perf_reporting' );
566 pa_debug.debug('Error stage is '||l_err_stage );
567 pa_debug.debug('Error stack is '||l_err_stack );
568 pa_debug.debug(SQLERRM);
569 x_err_code:=SQLCODE;
570 x_err_stack:= l_err_stack;
571
572 END Validate_Perf_reporting ;
573
574
575 END ;
[Home] [Help]