1 package body pa_purge_validate as
2 /* $Header: PAXVALDB.pls 120.2 2005/08/19 17:22:18 mwasowic noship $ */
3
4 -- forward declarations
5
6 -- Start of comments
7 -- API name : BatchVal
8 -- Type : Public
9 -- Pre-reqs : None
10 -- Function : This procedure is the main validate procedure that calls
11 -- the validate_attribute_change procedure. This procedure
12 -- gets all the projects from this batch and pass it one
13 -- by one to validate_attribute_change procedure.
14 --
15 -- Parameters : p_batch_id IN NUMBER
16 -- The purge batch id for which rows have
17 -- to be purged/archived.
18 -- p_project_Id IN NUMBER,
19 -- The project id for which records have
20 -- to be purged/archived.
21 -- p_txn_to_date IN DATE,
22 -- If the purging is being done on projects
23 -- that are active then this parameter is
24 -- determine the date to which the transactions
25 -- need to be purged.
26 -- p_Commit_Size IN NUMBER,
27 -- The number of records that can be allowed to
28 -- remain uncommited. If the number of records
29 -- goes byond this number then the process is
30 -- commited.
31 -- p_Archive_Flag IN OUT VARCHAR2,
32 -- This flag determines if the records need to
33 -- be archived before they are purged
34 -- p_Purge_Release IN OUT VARCHAR2,
35 -- The version of the application on which the
36 -- purge process is run.
37 -- X_Err_Stack IN OUT VARCHAR2,
38 -- Error stack
39 -- X_Err_Stage IN OUT VARCHAR2,
40 -- Stage in the procedure where error occurred
41 -- X_Err_Code IN OUT NUMBER
42 -- Error code returned from the procedure
43 -- End of comments
44
45 procedure BatchVal ( errbuf IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
46 ret_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
47 p_purge_batch_id IN NUMBER)
48
49 is
50
51 -- This cursor fetches the active_closed flag in the batch
52 -- and locks the batch. Locking the batch is necessary because
53 -- one batch should not be picked by two batches simultaneously
54 -- for validation
55 cursor GetBatchDet is
56 select pb.active_closed_flag
57 from pa_purge_batches pb
58 where pb.purge_batch_id = p_purge_batch_id
59 for update of pb.purge_batch_id nowait ;
60
61 -- This cursor fetches all the projects in the batch.
62 cursor GetProjectsInBatch is
63 select pp.project_id,
64 /* pt.project_type_class_code,
65 p.project_status_code, Commented for bug 2715317*/ /* project current status */
66 pp.last_project_status_code,
67 pp.purge_project_status_code, /* Bug#2416385 Added for Phase -III Archive and Purge */
68 pp.purge_summary_flag,
69 pp.purge_capital_flag,
70 pp.purge_actuals_flag,
71 pp.purge_budgets_flag,
72 pp.txn_to_date
73 /* from pa_project_types pt,
74 pa_projects p, Commented for bug 2715317 */
75 from pa_purge_projects pp
76 where pp.purge_batch_id = p_purge_batch_id
77 /* and p.project_type = pt.project_type
78 and nvl(pt.org_id, -99) = nvl(p.org_id, -99)
79 and pp.project_id = p.project_id Commented for bug 2715317 */
80 for update of pp.purge_project_status_code nowait ;
81
82 /* bug 4255353 starts here*/
83 cursor Getpurge_summaryflag(p_project_id IN NUMBER) is
84 select pp.purge_summary_flag
85 from pa_purge_projects pp
86 where pp.purge_batch_id = p_purge_batch_id
87 and pp.project_id=p_project_id;
88
89 l_GetProjectsInBatch_csr GetProjectsInBatch%rowtype;
90 l_GetBatchDet_csr GetBatchDet%rowtype ;
91 l_err_code NUMBER;
92 l_err_stack VARCHAR2(2000);
93 l_err_stage VARCHAR2(500);
94 l_error_msg VARCHAR2(30);
95 l_error_code NUMBER := 0;
96 /* Added l_project_type_class_code ,l_project_status_code for bug 2715317 */
97 l_project_type_class_code pa_project_types_all.project_type_class_code%type;
98 l_project_status_code pa_projects_all.project_status_code%type;
99 /* Bug#2416385 Added the variable l_purge_project_status_code for Phase -III Archive and Purge */
100 l_purge_project_status_code pa_purge_projects.purge_project_status_code%TYPE;
101 l_warnings_only_flag VARCHAR2(1) := 'N'; --bug3134205
102 BEGIN
103 Open GetBatchDet ;
104 Fetch GetBatchDet into l_GetBatchDet_csr ;
105
106
107 g_delete_errors := 'Y'; /* Bug#2416385 Added for Phase -III Archive and Purge */
108 g_active_flag := l_GetBatchDet_csr.active_closed_flag ;
109 pa_purge_validate.g_user := fnd_profile.value('USER_ID');
110 pa_purge_validate.g_request_id := fnd_global.conc_request_id ;
111 pa_purge_validate.g_Program_Application_Id := fnd_global.prog_appl_id ;
112 pa_purge_validate.g_program_id := fnd_global.conc_program_id ;
113
114 Open GetProjectsInBatch ;
115 l_err_stage := 'After open cursor GetProjectsInBatch' ;
116 -- l_err_stack := err_stack || ' ->After open cursor GetProjectsInBatch' ;
117 --
118 pa_debug.debug('Fetching the projects ');
119 LOOP
120
121
122 FND_MSG_PUB.Initialize ;
123
124 -- Fetch the next project from the cursor
125
126 Fetch GetProjectsInBatch into l_GetProjectsInBatch_csr ;
127
128
129 If GetProjectsInBatch%NotFound then
130 l_err_stage := 'No more records to process' ;
131 -- l_err_stack := err_stack || ' ->No more records to process' ;
132 pa_debug.debug('No more projects to process');
133 exit ;
134 End If;
135 -- Check project status has been changed during, project selected in batch to
136 -- validation process.
137
138 /* Bug 2715317 starts */
139 Select pt.project_type_class_code,
140 p.project_status_code
141 into l_project_type_class_code,
142 l_project_status_code
143 from pa_project_types pt,
144 pa_projects p
145 where p.project_type = pt.project_type
146 and p.project_id = l_GetProjectsInBatch_csr.project_id;
147 /* Bug 2715317 ends */
148
149
150 /* bug 4255353 starts here*/
151 open Getpurge_summaryflag(l_GetProjectsInBatch_csr.project_id);
152 Fetch Getpurge_summaryflag into pa_purge_validate_pjrm.g_purge_summary_flag ;
153 close Getpurge_summaryflag;
154 /* bug 4255353 ends here*/
155 /* IF (l_GetProjectsInBatch_csr.project_status_code <> 'PENDING_PURGE') AND
156 (l_GetProjectsInBatch_csr.project_status_code <> Commented for Bug 2715317 */
157 IF (l_project_status_code <> 'PENDING_PURGE') AND /* Added for Bug 2715317 */
158 (l_project_status_code <>
159 l_GetProjectsInBatch_csr.last_project_status_code) THEN
160 fnd_message.set_name('PA','PA_ARPR_PROJ_STATUS_CHANGED');
161 fnd_msg_pub.add;
162 l_err_code := 10;
163 l_err_stage := 'This project status has been changed.';
164 l_err_stack := l_err_stack||'->Project status changed';
165 pa_debug.debug(' This project status changed after selecting a batch '||to_char(l_GetProjectsInBatch_csr.project_id));
166 ELSE
167
168 -- If current project status for project is same as in batch project last project status.
169 -- Then run validation process.
170 g_txn_to_date := l_GetProjectsInBatch_csr.txn_to_date ;
171
172 pa_purge_validate_capital.g_purge_capital_flag :=
173 l_GetProjectsInBatch_csr.purge_capital_flag; /* Bug#2387342 */
174
175 /* g_project_type_class_code := l_GetProjectsInBatch_csr.project_type_class_code ; Commented for Bug 2715317 */
176 g_project_type_class_code := l_project_type_class_code ; /* Added for Bug 2715317 */
177
178 pa_debug.debug('Validating project '||to_char(l_GetProjectsInBatch_csr.project_id));
179 -- Call the validation procedure
180
181 if l_GetProjectsInBatch_csr.last_project_status_code <> 'PARTIALLY_PURGED' then
182
183 pa_project_utils2.validate_attribute_change(
184 x_Context => 'ARCHIVE_PURGE'
185 , x_Insert_Update_Mode => NULL
186 , x_Calling_Module => NULL
187 , x_project_id => l_GetProjectsInBatch_csr.project_id
188 , x_Task_id => NULL
189 , x_old_value => l_GetProjectsInBatch_csr.last_project_status_code
190 , x_new_value => 'PENDING_PURGE'
191 , x_Project_Type => NULL
192 , x_Project_Start_Date => NULL
193 , x_Project_End_Date => NULL
194 , x_Public_Sector_Flag => NULL
195 , x_Task_Manager_Person_Id => NULL
196 , x_Service_Type => NULL
197 , x_Task_Start_Date => NULL
198 , x_Task_End_Date => NULL
199 , x_Entered_By_User_Id => NULL
200 , x_Attribute_Category => NULL
201 , x_Attribute1 => NULL
202 , x_Attribute2 => NULL
203 , x_Attribute3 => NULL
204 , x_Attribute4 => NULL
205 , x_Attribute5 => NULL
206 , x_Attribute6 => NULL
207 , x_Attribute7 => NULL
208 , x_Attribute8 => NULL
209 , x_Attribute9 => NULL
210 , x_Attribute10 => NULL
211 , x_PM_Product_Code => NULL
212 , x_PM_Project_Reference => NULL
213 , x_PM_Task_Reference => NULL
214 , x_Functional_Security_Flag => NULL
215 , x_warnings_only_flag => l_warnings_only_flag --bug3134205
216 , x_err_code => l_err_code
217 , x_err_stage => l_err_stage
218 , x_err_stack => l_err_stack ) ;
219 else
220
221 /* Code changes for Bug 4255353 starts here */
222 pa_purge_validate_pjrm.Validate_Perf_reporting(p_project_id => l_GetProjectsInBatch_csr.project_id,
223 x_err_code => l_err_code,
224 x_err_stack => l_err_stack,
225 x_err_stage => l_err_stage
226 );
227
228 /* Code changes for Bug 4255353 ends here */
229
230 END IF; -- Validation.
231
232 END IF; -- Project status is changed.
233
234 pa_purge_validate.insert_errors(p_Purge_Batch_Id => p_purge_batch_id,
235 p_Project_Id => l_GetProjectsInBatch_csr.project_id,
236 p_Error_Type => 'E',
237 p_User => pa_purge_validate.g_user,
238 x_Err_Stack => l_err_stack,
239 x_Err_Stage => l_err_stage,
240 x_Err_Code => l_err_code
241 );
242
243 end loop ;
244
245 -- Update the batch status to working.
246 update pa_purge_batches pb
247 set pb.batch_status_code = 'W',
248 pb.request_id = pa_purge_validate.g_request_id,
249 pb.program_application_id = pa_purge_validate.g_program_application_id,
250 pb.program_id = pa_purge_validate.g_program_id ,
251 pb.program_update_date = sysdate,
252 pb.last_update_login = -1,
253 pb.last_updated_by = -1,
254 pb.last_update_date = sysdate
255 where pb.purge_batch_id = p_purge_batch_id ;
256
257
258 close GetProjectsInBatch;
259 close GetBatchDet ;
260
261 /* Bug#2416385 Code added for Phase -III Archive and Purge starts here */
262
263 /* If any of the Project which is a Receiver Project (InterProject Setup) is pulled in purge batch,
264 we will NOT pull associated provider projects programmatically. But the code will Invalidate the
265 receiver project prompting user to pull all associated un-purged provider projects in the same
266 batch or to remove receiver project from the batch to make the batch valid for purge.
267 To implement the above logic, the Interproject receiver project validation is called after all
268 the regular checks are completed.
269 Individually receiver and provider projects can be valid for regular checks but after the
270 receiver project validation, the receiver project can be invalid incase,
271 1. if any of its provider projects which is not in purge status and is not included in the
272 purge batch or
273 2. included in the purge batch but is invalid for regular checks
274 */
275
276 FOR c_prj_in_batch in GetProjectsInBatch LOOP
277
278 g_delete_errors := 'N';
279 pa_purge_validate_icip.g_insert_errors_no_duplicate := 'N'; /* Bug# 2431705 */
280
281 FND_MSG_PUB.Initialize;
282
283 if c_prj_in_batch.last_project_status_code <> 'PARTIALLY_PURGED' then
284
285 if pa_purge_validate_icip.Is_InterPrj_Receiver_Project(c_prj_in_batch.project_id) = 'Y' then
286
287 pa_debug.debug(' * Calling validate process for IP receiver project for project '||
288 to_char(c_prj_in_batch.project_id));
289
290 pa_purge_validate_icip.Validate_IP_Rcvr ( c_prj_in_batch.project_id,
291 l_err_code,
292 l_err_stack,
293 l_err_stage );
294
295 pa_purge_validate.insert_errors ( p_Purge_Batch_Id => p_purge_batch_id,
296 p_Project_Id => c_prj_in_batch.project_id,
297 p_Error_Type => 'E',
298 p_User => pa_purge_validate.g_user,
299 x_Err_Stack => l_err_stack,
300 x_Err_Stage => l_err_stage,
301 x_Err_Code => l_err_code );
302
303 select purge_project_status_code
304 into l_purge_project_status_code
305 from pa_purge_projects
306 where project_id = c_prj_in_batch.project_id
307 and purge_batch_id = p_purge_batch_id ;
308
309 if ( (l_purge_project_status_code <> c_prj_in_batch.purge_project_status_code) and
310 (l_purge_project_status_code = 'I') ) then
311
312 update pa_projects_all p
313 set p.project_status_code = c_prj_in_batch.last_project_status_code,
314 p.last_update_date = sysdate,
315 p.last_updated_by = -1,
316 p.last_update_login = -1
317 where p.project_id = c_prj_in_batch.project_id;
318
319 end if;
320
321 end if; /* pa_purge_validate_icip.Is_InterPrj_Receiver_Project check */
322
323 end if; /* c_prj_in_batch.last_project_status_code <> 'PARTIALLY_PURGED' check */
324
325 END LOOP;
326
327 /* Bug#2416385 Code added for Phase -III Archive and Purge ends here */
328
329 commit ;
330
331 EXCEPTION
332 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error THEN
333 errbuf := PA_PROJECT_UTILS2.g_sqlerrm ;
334 ret_code := -1 ;
335 WHEN OTHERS THEN
336 errbuf := SQLERRM ;
337 ret_code := -1 ;
338
339 END BatchVal;
340
341 -- Start of comments
342 -- API name : insert_errors
343 -- Type : Public
344 -- Pre-reqs : None
345 -- Function : This procedure inserts all the errors for a project into
346 -- the error table.
347 --
348 -- Parameters : p_purge_batch_id NUMBER
349 -- The purge batch id for which rows have
350 -- to be purged/archived.
351 -- p_project_Id IN NUMBER,
352 -- The project id for which records have
353 -- to be purged/archived.
354 -- p_error_type IN OUT VARCHAR2,
355 -- This flag indicates if it is an error or
356 -- warning.
357 -- p_user IN OUT VARCHAR2,
358 -- This will get the user_id to the procedure
359 -- X_Err_Stack IN OUT VARCHAR2,
360 -- Error stack
361 -- X_Err_Stage IN OUT VARCHAR2,
362 -- Stage in the procedure where error occurred
363 -- X_Err_Code IN OUT NUMBER
364 -- Error code returned from the procedure
365 -- End of comments
366
367 procedure insert_errors ( p_purge_batch_id in NUMBER,
368 p_project_id in NUMBER,
369 p_error_type in VARCHAR2,
370 p_user in NUMBER,
371 X_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
372 X_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
373 X_err_code in OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
374 ) is
375
376 l_Count NUMBER ;
377 l_MesgCount NUMBER ;
378 l_err_stage VARCHAR2(2000);
379 l_err_stack VARCHAR2(2000);
380 l_err_stack_old VARCHAR2(2000);
381 l_message_code VARCHAR2(50);
382 l_msg_data VARCHAR2(30);
383 l_chr VARCHAR2(3);
384 l_msg_index_Out NUMBER ;
385 l_encoded VARCHAR2(30) := FND_API.G_TRUE ;
386 l_app_name VARCHAR2(3);
387 BEGIN
388
389 l_chr := convert(fnd_global.local_chr(12), substr(userenv('LANGUAGE'),
390 instr(userenv('LANGUAGE'),'.') +1),
391 'WE8ISO8859P1') ;
392 l_err_stack := X_err_stack ;
393 l_err_stack_old := X_err_stack ;
394 l_err_stack := X_err_stack || '-> Inserting errors to the error table ' ;
395 l_MesgCount := FND_MSG_PUB.Count_Msg ;
396
397 if g_delete_errors = 'Y' then /* Bug#2416385 Added for Phase -III Archive and Purge */
398
399 pa_debug.debug('Deleting errors for the project '||to_char(p_project_id)||' in batch '||to_char(p_purge_batch_id));
400 x_err_stage := 'Deleting errors for the project '||to_char(p_project_id)||' in batch '||to_char(p_purge_batch_id);
401 X_err_stack := X_err_stack || '-> Deleting errors for the project '||to_char(p_project_id) ;
402
403 delete from pa_purge_project_errors pe
404 where pe.purge_batch_id = p_purge_batch_id
405 and pe.project_id = p_project_id ;
406
407 end if;
408
409 if l_MesgCount = 0 then
410
411
412 if g_delete_errors = 'Y' then /* Bug#2416385 Added for Phase -III Archive and Purge */
413
414 -- This means there are no errors for this validation run. So delete
415 -- all the errors from the previous run if exists and update the
416 -- project to Valid. Also update the status of the project in
417 -- PA_PROJECTS to 'PENDING_PURGE'.
418
419 pa_debug.debug('Updating purge_project_status_code to valid for project '||to_char(p_project_id)) ;
420 X_err_stage := 'No errors . Updating purge_project_status_code to valid for project '||to_char(p_project_id) ;
421
422 update pa_purge_projects pp
423 set pp.purge_project_status_code = 'V',
424 pp.request_id = pa_purge_validate.g_request_id,
425 pp.program_application_id = pa_purge_validate.g_program_application_id,
426 pp.program_id = pa_purge_validate.g_program_id ,
427 pp.program_update_date = sysdate
428 where pp.project_id = p_project_id
429 and pp.purge_batch_id = p_purge_batch_id ;
430
431 /* Bug#2416385 Modified the pa_projects to pa_projects_all for Phase -III Archive and Purge */
432 update pa_projects_all p
433 set p.project_status_code = 'PENDING_PURGE',
434 p.last_update_date = sysdate,
435 p.last_updated_by = -1,
436 p.last_update_login = -1
437 where p.project_id = p_project_id ;
438
439 end if; /* if g_delete_errors = 'Y check */
440
441 else
442
443 -- If l_MesgCount is greater than 0 then errors exist for the project
444 -- and
445 pa_debug.debug('Inserting validation errors for project '||to_char(p_project_id));
446 X_err_stage := 'Inserting validation errors for project '||to_char(p_project_id) ;
447
448 for i in 1..l_MesgCount
449 LOOP
450 FND_MSG_PUB.Get(p_encoded => l_encoded,
451 p_data => l_message_code,
452 p_msg_index => 1,
453 p_msg_index_out => l_msg_index_out) ;
454 pa_debug.debug('Message is '||replace(replace(l_message_code,'PA'||l_chr), l_chr));
455
456 l_app_name := 'PA';
457 if l_message_code is not null then
458
459 FND_MESSAGE.PARSE_ENCODED(ENCODED_MESSAGE => l_message_code,
460 APP_SHORT_NAME => l_app_name,
461 MESSAGE_NAME => l_msg_data);
462
463 FND_MSG_PUB.DELETE_MSG(p_msg_index => 1);
464
465 pa_debug.debug('l_msg_data:'||l_msg_data);
466
467 end if;
468
469 insert into pa_purge_project_errors
470 ( purge_batch_id,
471 project_id,
472 error_code,
473 error_type,
474 created_by,
475 creation_date,
476 last_updated_by,
477 last_update_date,
478 last_update_login )
479 values (p_purge_batch_id,
480 p_project_id,
481 -- replace(replace(l_message_code,'PA'||l_chr), l_chr),
482 l_msg_data,
483 p_error_type,
484 -1,
485 sysdate,
486 -1,
487 sysdate,
488 -1) ;
489
490 END LOOP ;
491
492 pa_debug.debug('Updating purge_project_status_code to invalid for project '||to_char(p_project_id));
493 X_err_stage := 'Updating purge_project_status_code to invalid for project '||to_char(p_project_id) ;
494 update pa_purge_projects pp
495 set pp.purge_project_status_code = 'I',
496 pp.request_id = pa_purge_validate.g_request_id,
497 pp.program_application_id = pa_purge_validate.g_program_application_id,
498 pp.program_id = pa_purge_validate.g_program_id ,
499 pp.program_update_date = sysdate
500 where pp.project_id = p_project_id
501 and pp.purge_batch_id = p_purge_batch_id ;
502 end if;
503
504 x_err_stack := l_err_stack_old ;
505
506 EXCEPTION
507 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
508 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
509
510 WHEN OTHERS THEN
511 -- x_err_stage := l_err_stage ;
512 pa_debug.debug('Error Procedure Name := PA_PURGE_VALIDATE.INSERT_ERRORS' );
513 pa_debug.debug('Error stage is '||x_err_stage );
514 pa_debug.debug('Error stack is '||x_err_stack );
515 pa_debug.debug(SQLERRM);
516 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
517
518 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
519
520 END insert_errors ;
521
522 END pa_purge_validate;
[Home] [Help]