[Home] [Help]
PACKAGE BODY: APPS.PA_PURGE_PJR_TXNS
Source
1 Package body PA_PURGE_PJR_TXNS AS
2 /* $Header: PAXPJRPB.pls 120.1 2005/08/19 17:16:54 mwasowic noship $ */
3 -- Start of comments
4 -- API name : PA_REQUIREMENTS_PURGE
5 -- Type : Public
6 -- Pre-reqs : None
7 -- Function : Main purge procedure for Purging records related to Requirements for project
8
9 Procedure PA_REQUIREMENTS_PURGE ( p_purge_batch_id in NUMBER,
10 p_project_id in NUMBER,
11 p_purge_release in VARCHAR2,
12 p_txn_to_date in DATE,
13 p_archive_flag in VARCHAR2,
14 p_commit_size in NUMBER,
15 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
17 x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
18
19
20
21
22 l_requirement_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
23 I PLS_INTEGER;
24 l_last_fetch VARCHAR2(1):='N';
25 l_this_fetch NUMBER:=0;
26 l_totally_fetched NUMBER:=0;
27
28 /*This cursor will select all the assignments, which are either cancelled or filled
29 for Project passed. The other conditions are not checked as in closed project
30 purge, purging of requirements will start only if all requirements are cancelled
31 or filled*/
32
33 CURSOR CUR_REQUIREMENTS_CLOSED IS
34 SELECT assignment_id
35 FROM pa_project_assignments pa WHERE pa.assignment_type = 'OPEN_ASSIGNMENT'
36 AND pa.project_id = P_PROJECT_ID;
37
38
39 /*This cursor will select all the assignments, which are either cancelled or filled
40 for Project passed and whose end dates are before purge till date .This is
41 applicable for open indirect project purge.*/
42
43 CURSOR CUR_REQUIREMENTS_ACTIVE IS
44 SELECT pa.assignment_id
45 FROM pa_project_assignments pa, pa_project_statuses ps
46 WHERE pa.assignment_type = 'OPEN_ASSIGNMENT'
47 AND pa.status_code = ps.project_status_code
48 AND ps.status_type='OPEN_ASGMT'
49 AND ps.project_system_status_code <> 'OPEN_ASGMT'
50 AND pa.project_id = P_PROJECT_ID
51 AND pa.end_date <= P_txn_to_date
52 AND NOT EXISTS (Select NULL
53 From pa_project_assignments pa1,pa_project_statuses pps
54 where pa1.source_assignment_id=pa.assignment_id
55 AND pa1.end_date >P_txn_to_date
56 AND pa1.status_code = pps.project_status_code
57 AND pps.status_type = 'STAFFED_ASGMT'
58 AND pps.project_system_status_code <> 'STAFFED_ASGMT_CANCEL');
59
60 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
61
62 BEGIN
63
64 l_old_err_stack := x_err_stack; -- Added for bug 4227589
65 x_err_stack := x_err_stack || ' ->Before call to purge Requirements ';
66
67 If p_txn_to_date IS NULL THEN
68
69 pa_debug.debug('Purging requirements for Closed project purge');
70 x_err_stage := 'About to purge requirements for project '||to_char(p_project_id) ;
71
72 OPEN CUR_REQUIREMENTS_CLOSED;
73 LOOP
74
75 /*Clear PL/SQL table before start */
76 l_requirement_id_tab.DELETE;
77
78 FETCH cur_requirements_closed
79 BULK COLLECT
80 INTO l_requirement_id_tab LIMIT p_commit_size;
81
82 /* To check the rows fetched in this fetch */
83
84 l_this_fetch := cur_requirements_closed%ROWCOUNT - l_totally_fetched;
85 l_totally_fetched := cur_requirements_closed%ROWCOUNT;
86
87 /*
88 * Check if this fetch has 0 rows returned (ie last fetch was even p_commit_size)
89 * This could happen in 2 cases
90 * 1) this fetch is the very first fetch with 0 rows returned
91 * OR 2) the last fetch returned an even p_commit_size rows
92 * If either then EXIT without any processing
93 */
94 IF l_this_fetch = 0 then
95 EXIT;
96 END IF;
97
98 /*
99 * Check if this fetch is the last fetch
100 * If so then set the flag l_last_fetch so as to exit after processing
101 */
102 IF l_this_fetch < p_commit_size then
103 l_last_fetch := 'Y';
104 ELSE
105 l_last_fetch := 'N';
106 END IF;
107
108 /* Call forecast API to archive/purge data from pa_forecast_items and detail table */
109
110 pa_debug.debug(' About to purge Forecast Items and Forecast Item Details ') ;
111 x_err_stage := 'About to purge forecast items for project '||to_char(p_project_id) ;
112
113
114 pa_purge_pjr_txns.pa_forecast_items_purge( p_purge_batch_id =>p_purge_batch_id,
115 p_project_id =>p_project_id,
116 p_purge_release =>p_purge_release,
117 p_assignment_id_tab =>l_requirement_id_tab,
118 p_archive_flag =>p_archive_flag,
119 x_err_stack =>x_err_stack,
120 x_err_stage =>x_err_stage,
121 x_err_code =>x_err_code);
122
123 /* Call schedules API to archive/purge data from pa_schedules, pa_schedule_history and pa_schedule_except_history */
124
125 pa_debug.debug(' About to purge schedules, schedule history and schedule exceptions data ') ;
126 x_err_stage := 'About to purge schedules data for project '||to_char(p_project_id) ;
127
128
129 pa_purge_pjr_txns.pa_schedules_purge( p_purge_batch_id =>p_purge_batch_id,
130 p_project_id =>p_project_id,
131 p_purge_release =>p_purge_release,
132 p_assignment_id_tab =>l_requirement_id_tab,
133 p_archive_flag =>p_archive_flag,
134 x_err_stack =>x_err_stack,
135 x_err_stage =>x_err_stage,
136 x_err_code =>x_err_code);
137
138
139 /* Call API to archive/purge data from pa_candidates and pa_candidate_reviews */
140
141 pa_debug.debug(' About to purge candidate and candidate reviews data ') ;
142 x_err_stage := 'About to purge candidate and candidate reviws for project '||to_char(p_project_id) ;
143
144 pa_purge_pjr_txns.pa_candidates_purge( p_purge_batch_id =>p_purge_batch_id,
145 p_project_id =>p_project_id,
146 p_purge_release =>p_purge_release,
147 p_assignment_id_tab =>l_requirement_id_tab,
148 p_archive_flag =>p_archive_flag,
149 x_err_stack =>x_err_stack,
150 x_err_stage =>x_err_stage,
151 x_err_code =>x_err_code);
152
153 /* Call API to archive/purge data advertisements records */
154
155 pa_debug.debug(' About to purge advertisements data ') ;
156 x_err_stage := 'About to purge advertisements data for project '||to_char(p_project_id) ;
157
158 pa_purge_pjr_txns.pa_advertisements_purge( p_purge_batch_id =>p_purge_batch_id,
159 p_project_id =>p_project_id,
160 p_purge_release =>p_purge_release,
161 p_assignment_id_tab =>l_requirement_id_tab,
162 p_archive_flag =>p_archive_flag,
163 x_err_stack =>x_err_stack,
164 x_err_stage =>x_err_stage,
165 x_err_code =>x_err_code);
166
167 /* Call API to archive/purge assignment and assignment history records records */
168
169 pa_debug.debug(' About to purge requirement and its history data ') ;
170 x_err_stage := 'About to purge requirement and its history '||to_char(p_project_id) ;
171
172 pa_purge_pjr_txns.pa_project_assignment_purge( p_purge_batch_id =>p_purge_batch_id,
173 p_project_id =>p_project_id,
174 p_purge_release =>p_purge_release,
175 p_assignment_id_tab =>l_requirement_id_tab,
176 p_archive_flag =>p_archive_flag,
177 x_err_stack =>x_err_stack,
178 x_err_stage =>x_err_stage,
179 x_err_code =>x_err_code);
180
181
182
183
184 /* Check if this loop is the last set of p_commit_size If so then EXIT; */
185
186 IF l_last_fetch='Y' THEN
187 EXIT;
188 END IF;
189
190 END LOOP;
191
192 CLOSE cur_requirements_closed;
193
194 ELSE
195
196 pa_debug.debug(' About to purge Requirements for Open Indirect Project ') ;
197 x_err_stage := 'Opening cursor Cur_Requirements_Active' ;
198
199 OPEN CUR_REQUIREMENTS_ACTIVE;
200 LOOP
201
202 /*Clear PL/SQL table before start */
203 l_requirement_id_tab.DELETE;
204
205 FETCH cur_requirements_active
206 BULK COLLECT
207 INTO l_requirement_id_tab LIMIT p_commit_size;
208
209 /* To check the rows fetched in this fetch */
210
211 l_this_fetch := cur_requirements_active%ROWCOUNT - l_totally_fetched;
212 l_totally_fetched := cur_requirements_active%ROWCOUNT;
213
214 /*
215 * Check if this fetch has 0 rows returned (ie last fetch was even p_commit_size)
216 * This could happen in 2 cases
217 * 1) this fetch is the very first fetch with 0 rows returned
218 * OR 2) the last fetch returned an even p_commit_size rows
219 * If either then EXIT without any processing
220 */
221 IF l_this_fetch = 0 then
222 EXIT;
223 END IF;
224
225 /*
226 * Check if this fetch is the last fetch
227 * If so then set the flag l_last_fetch so as to exit after processing
228 */
229 IF l_this_fetch < p_commit_size then
230 l_last_fetch := 'Y';
231 ELSE
232 l_last_fetch := 'N';
233 END IF;
234
235 /* Call forecast API to archive/purge data from pa_forecast_items and detail table */
236
237 pa_debug.debug(' About to purge Forecast Items and Forecast Item Details ') ;
238 x_err_stage := 'About to purge forecast items for project '||to_char(p_project_id) ;
239
240 pa_purge_pjr_txns.pa_forecast_items_purge( p_purge_batch_id =>p_purge_batch_id,
241 p_project_id =>p_project_id,
242 p_purge_release =>p_purge_release,
243 p_assignment_id_tab =>l_requirement_id_tab,
244 p_archive_flag =>p_archive_flag,
245 x_err_stack =>x_err_stack,
246 x_err_stage =>x_err_stage,
247 x_err_code =>x_err_code);
248
249 /* Call schedules API to archive/purge data from pa_schedules, pa_schedule_history and pa_schedule_except_history */
250
251 pa_debug.debug(' About to purge schedules, schedule history and schedule exceptions data ') ;
252 x_err_stage := 'About to purge sschedules data for project '||to_char(p_project_id) ;
253
254
255 pa_purge_pjr_txns.pa_schedules_purge( p_purge_batch_id =>p_purge_batch_id,
256 p_project_id =>p_project_id,
257 p_purge_release =>p_purge_release,
258 p_assignment_id_tab =>l_requirement_id_tab,
259 p_archive_flag =>p_archive_flag,
260 x_err_stack =>x_err_stack,
261 x_err_stage =>x_err_stage,
262 x_err_code =>x_err_code);
263
264
265 /* Call API to archive/purge data from pa_candidates and pa_candidate_reviews */
266
267 pa_debug.debug(' About to purge candidate and candidate reviews data ') ;
268 x_err_stage := 'About to purge candidate and candidate reviws for project '||to_char(p_project_id) ;
269
270 pa_purge_pjr_txns.pa_candidates_purge( p_purge_batch_id =>p_purge_batch_id,
271 p_project_id =>p_project_id,
272 p_purge_release =>p_purge_release,
273 p_assignment_id_tab =>l_requirement_id_tab,
274 p_archive_flag =>p_archive_flag,
275 x_err_stack =>x_err_stack,
276 x_err_stage =>x_err_stage,
277 x_err_code =>x_err_code);
278
279 /* Call API to archive/purge data advertisements records */
280
281 pa_debug.debug(' About to purge advertisements data ') ;
282 x_err_stage := 'About to purge advertisements data for project '||to_char(p_project_id) ;
283
284 pa_purge_pjr_txns.pa_advertisements_purge( p_purge_batch_id =>p_purge_batch_id,
285 p_project_id =>p_project_id,
286 p_purge_release =>p_purge_release,
287 p_assignment_id_tab =>l_requirement_id_tab,
288 p_archive_flag =>p_archive_flag,
289 x_err_stack =>x_err_stack,
290 x_err_stage =>x_err_stage,
291 x_err_code =>x_err_code);
292
293 /* Call API to archive/purge assignment and assignment history records records */
294
295 pa_debug.debug(' About to purge requirement and its history data ') ;
296 x_err_stage := 'About to purge requirement and its history '||to_char(p_project_id) ;
297
298 pa_purge_pjr_txns.pa_project_assignment_purge( p_purge_batch_id =>p_purge_batch_id,
299 p_project_id =>p_project_id,
300 p_purge_release =>p_purge_release,
301 p_assignment_id_tab =>l_requirement_id_tab,
302 p_archive_flag =>p_archive_flag,
303 x_err_stack =>x_err_stack,
304 x_err_stage =>x_err_stage,
305 x_err_code =>x_err_code);
306
307
308
309 /* Check if this loop is the last set of p_commit_size If so then EXIT; */
310
311 IF l_last_fetch='Y' THEN
312 EXIT;
313 END IF;
314
315 END LOOP;
316
317 CLOSE cur_requirements_active;
318
319 END IF;
320
321 x_err_stack := l_old_err_stack; -- Added for bug 4227589
322
323 EXCEPTION
324 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
325 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
326
327 WHEN OTHERS THEN
328 pa_debug.debug('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_REQUIREMENTS_PURGE' );
329 pa_debug.debug('Error stage is '||x_err_stage );
330 pa_debug.debug('Error stack is '||x_err_stack );
331 pa_debug.debug(SQLERRM);
332 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
333
334 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
335
336
337 END PA_REQUIREMENTS_PURGE;
338
339 -- Start of comments
340 -- API name : PA_ASSIGNMENTS_PURGE
341 -- Type : Public
342 -- Pre-reqs : None
343 -- Function : Main purge procedure for Purging records related to Assignments for project
344
345 Procedure PA_ASSIGNMENTS_PURGE ( p_purge_batch_id in NUMBER,
346 p_project_id in NUMBER,
347 p_purge_release in VARCHAR2,
348 p_txn_to_date in DATE,
349 p_archive_flag in VARCHAR2,
350 p_commit_size in NUMBER,
351 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
352 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
353 x_err_code in OUT NOCOPY NUMBER ) AS --File.Sql.39 bug 4440895
354
355 l_assignment_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
356 I PLS_INTEGER;
357 l_last_fetch VARCHAR2(1):='N';
358 l_this_fetch NUMBER:=0;
359 l_totally_fetched NUMBER:=0;
360
361 /*This cursor will select all the assignments for project passed(Closed Project Purge.
362 All the assignments belong to project id passed will be purged. No other
363 conditions are mentioned as the purge program will purge closed project
364 assignments only if the all the assignmnets are eligible for purging.*/
365
366 CURSOR CUR_ASSIGNMENTS_CLOSED IS
367 SELECT assignment_id FROM Pa_project_assignments pa
368 WHERE pa.project_id = P_Project_Id
369 AND pa.assignment_type <>'OPEN_ASSIGNMENT';
370
371
372 /*This cursor will select all the assignments for project passed whose end date is
373 falling before purge till date. This cursor is applicable in case of open project
374 purge. In this case, we purge all the assignments falling before purge till date.*/
375
376 CURSOR CUR_ASSIGNMENTS_ACTIVE IS
377 SELECT assignment_id FROM Pa_project_assignments pa
378 WHERE pa.project_id = P_Project_Id
379 AND pa.assignment_type <>'OPEN_ASSIGNMENT'
380 AND p_txn_to_date >= pa.end_date;
381
382 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
383
384 Begin
385
386 l_old_err_stack := x_err_stack; -- Added for bug 4227589
387 x_err_stack := x_err_stack || ' ->Before call to purge assignments ';
388
389 If p_txn_to_date IS NULL THEN
390
391 pa_debug.debug(' About to purge Assignments for closed project purge ') ;
392 x_err_stage := 'About to start purge for assignments for project '||to_char(p_project_id) ;
393
394 OPEN CUR_ASSIGNMENTS_CLOSED;
395 LOOP
396
397 /*Clear PL/SQL table before start */
398 l_assignment_id_tab.DELETE;
399
400 FETCH cur_assignments_closed
401 BULK COLLECT
402 INTO l_assignment_id_tab LIMIT p_commit_size;
403
404 /* To check the rows fetched in this fetch */
405
406 l_this_fetch := cur_assignments_closed%ROWCOUNT - l_totally_fetched;
407 l_totally_fetched := cur_assignments_closed%ROWCOUNT;
408
409 /*
410 * Check if this fetch has 0 rows returned (ie last fetch was even p_commit_size)
411 * This could happen in 2 cases
412 * 1) this fetch is the very first fetch with 0 rows returned
413 * OR 2) the last fetch returned an even p_commit_size rows
414 * If either then EXIT without any processing
415 */
416 IF l_this_fetch = 0 then
417 EXIT;
418 END IF;
419
420 /*
421 * Check if this fetch is the last fetch
422 * If so then set the flag l_last_fetch so as to exit after processing
423 */
424 IF l_this_fetch < p_commit_size then
425 l_last_fetch := 'Y';
426 ELSE
427 l_last_fetch := 'N';
428 END IF;
429
430 /* Call forecast API to archive/purge data from pa_forecast_items and detail table */
431
432 pa_debug.debug(' About to purge Forecast Items and Forecast Item Details ') ;
433 x_err_stage := 'About to purge forecast items for project '||to_char(p_project_id) ;
434
435 pa_purge_pjr_txns.pa_forecast_items_purge( p_purge_batch_id =>p_purge_batch_id,
436 p_project_id =>p_project_id,
437 p_purge_release =>p_purge_release,
438 p_assignment_id_tab =>l_assignment_id_tab,
439 p_archive_flag =>p_archive_flag,
440 x_err_stack =>x_err_stack,
441 x_err_stage =>x_err_stage,
442 x_err_code =>x_err_code);
443
444 /* Call schedules API to archive/purge data from pa_schedules, pa_schedule_history and pa_schedule_except_history */
445
446 pa_debug.debug(' About to purge schedules, schedule history and schedule exceptions data ') ;
447 x_err_stage := 'About to purge sschedules data for project '||to_char(p_project_id) ;
448
449
450 pa_purge_pjr_txns.pa_schedules_purge( p_purge_batch_id =>p_purge_batch_id,
451 p_project_id =>p_project_id,
452 p_purge_release =>p_purge_release,
453 p_assignment_id_tab =>l_assignment_id_tab,
454 p_archive_flag =>p_archive_flag,
455 x_err_stack =>x_err_stack,
456 x_err_stage =>x_err_stage,
457 x_err_code =>x_err_code);
458
459
460 /* Call conflicts API to archive/purge data from pa_assignment_conflict_hist table */
461
462 pa_debug.debug(' About to purge assignment conflicts history data ') ;
463 x_err_stage := 'About to purge assignment conflicts history '||to_char(p_project_id) ;
464
465
466 pa_purge_pjr_txns.pa_assignment_conflicts_purge( p_purge_batch_id =>p_purge_batch_id,
467 p_project_id =>p_project_id,
468 p_purge_release =>p_purge_release,
469 p_assignment_id_tab =>l_assignment_id_tab,
470 p_archive_flag =>p_archive_flag,
471 x_err_stack =>x_err_stack,
472 x_err_stage =>x_err_stage,
473 x_err_code =>x_err_code);
474
475
476 /* Call conflicts API to archive/purge data from pa_project_parties table */
477
478 pa_debug.debug(' About to purge pa_project_parties data related to assignments') ;
479 x_err_stage := 'About to purge assignment related project parties data'||to_char(p_project_id) ;
480
481 pa_purge_pjr_txns.pa_project_parties_purge( p_purge_batch_id =>p_purge_batch_id,
482 p_project_id =>p_project_id,
483 p_purge_release =>p_purge_release,
484 p_assignment_id_tab =>l_assignment_id_tab,
485 p_archive_flag =>p_archive_flag,
486 x_err_stack =>x_err_stack,
487 x_err_stage =>x_err_stage,
488 x_err_code =>x_err_code);
489
490 /* Call API to archive/purge assignment and assignment history records records */
491
492 pa_debug.debug(' About to purge assignments and its history data ') ;
493 x_err_stage := 'About to purge assignments and its history '||to_char(p_project_id) ;
494
495 pa_purge_pjr_txns.pa_project_assignment_purge( p_purge_batch_id =>p_purge_batch_id,
496 p_project_id =>p_project_id,
497 p_purge_release =>p_purge_release,
498 p_assignment_id_tab =>l_assignment_id_tab,
499 p_archive_flag =>p_archive_flag,
500 x_err_stack =>x_err_stack,
501 x_err_stage =>x_err_stage,
502 x_err_code =>x_err_code);
503
504 /* Check if this loop is the last set of p_commit_size If so then EXIT; */
505
506 IF l_last_fetch='Y' THEN
507 EXIT;
508 END IF;
509
510 END LOOP;
511
512
513 CLOSE cur_assignments_closed;
514
515 ELSE
516 pa_debug.debug(' About to purge Assignments for Open Indirect Project ') ;
517 x_err_stage := 'About to purge assignments for project '||to_char(p_project_id) ;
518
519 OPEN CUR_ASSIGNMENTS_ACTIVE;
520 LOOP
521
522 /*Clear PL/SQL table before start */
523 l_assignment_id_tab.DELETE;
524
525 FETCH cur_assignments_active
526 BULK COLLECT
527 INTO l_assignment_id_tab LIMIT p_commit_size;
528
529 /* To check the rows fetched in this fetch */
530
531 l_this_fetch := cur_assignments_active%ROWCOUNT - l_totally_fetched;
532 l_totally_fetched := cur_assignments_active%ROWCOUNT;
533
534 /*
535 * Check if this fetch has 0 rows returned (ie last fetch was even p_commit_size)
536 * This could happen in 2 cases
537 * 1) this fetch is the very first fetch with 0 rows returned
538 * OR 2) the last fetch returned an even p_commit_size rows
539 * If either then EXIT without any processing
540 */
541 IF l_this_fetch = 0 then
542 EXIT;
543 END IF;
544
545 /*
546 * Check if this fetch is the last fetch
547 * If so then set the flag l_last_fetch so as to exit after processing
548 */
549 /*
550 * Check if this fetch is the last fetch
551 * If so then set the flag l_last_fetch so as to exit after processing
552 */
553 IF l_this_fetch < p_commit_size then
554 l_last_fetch := 'Y';
555 ELSE
556 l_last_fetch := 'N';
557 END IF;
558
559 /* Call forecast API to archive/purge data from pa_forecast_items and detail table */
560
561 pa_debug.debug(' About to purge Forecast Items and Forecast Item Details ') ;
562 x_err_stage := 'About to purge forecast items for project '||to_char(p_project_id) ;
563
564 pa_purge_pjr_txns.pa_forecast_items_purge( p_purge_batch_id =>p_purge_batch_id,
565 p_project_id =>p_project_id,
566 p_purge_release =>p_purge_release,
567 p_assignment_id_tab =>l_assignment_id_tab,
568 p_archive_flag =>p_archive_flag,
569 x_err_stack =>x_err_stack,
570 x_err_stage =>x_err_stage,
571 x_err_code =>x_err_code);
572
573 /* Call schedules API to archive/purge data from pa_schedules, pa_schedule_history and pa_schedule_except_history */
574
575 pa_debug.debug(' About to purge schedules, schedule history and schedule exceptions data ') ;
576 x_err_stage := 'About to purge sschedules data for project '||to_char(p_project_id) ;
577
578
579 pa_purge_pjr_txns.pa_schedules_purge( p_purge_batch_id =>p_purge_batch_id,
580 p_project_id =>p_project_id,
581 p_purge_release =>p_purge_release,
582 p_assignment_id_tab =>l_assignment_id_tab,
583 p_archive_flag =>p_archive_flag,
584 x_err_stack =>x_err_stack,
585 x_err_stage =>x_err_stage,
586 x_err_code =>x_err_code);
587
588
589 /* Call conflicts API to archive/purge data from pa_assignment_conflict_hist table */
590
591 pa_debug.debug(' About to purge assignment conflicts history data ') ;
592 x_err_stage := 'About to purge assignment conflicts history '||to_char(p_project_id) ;
593
594
595 pa_purge_pjr_txns.pa_assignment_conflicts_purge( p_purge_batch_id =>p_purge_batch_id,
596 p_project_id =>p_project_id,
597 p_purge_release =>p_purge_release,
598 p_assignment_id_tab =>l_assignment_id_tab,
599 p_archive_flag =>p_archive_flag,
600 x_err_stack =>x_err_stack,
601 x_err_stage =>x_err_stage,
602 x_err_code =>x_err_code);
603
604
605 /* Call conflicts API to archive/purge data from pa_project_parties table */
606
607 pa_debug.debug(' About to purge pa_project_parties data related to assignments') ;
608 x_err_stage := 'About to purge assignment related project parties data'||to_char(p_project_id) ;
609
610 pa_purge_pjr_txns.pa_project_parties_purge( p_purge_batch_id =>p_purge_batch_id,
611 p_project_id =>p_project_id,
612 p_purge_release =>p_purge_release,
613 p_assignment_id_tab =>l_assignment_id_tab,
614 p_archive_flag =>p_archive_flag,
615 x_err_stack =>x_err_stack,
616 x_err_stage =>x_err_stage,
617 x_err_code =>x_err_code);
618
619 /* Call API to archive/purge assignment and assignment history records records */
620
621 pa_debug.debug(' About to purge assignments and its history data ') ;
622 x_err_stage := 'About to purge assignments and its history '||to_char(p_project_id) ;
623
624 pa_purge_pjr_txns.pa_project_assignment_purge( p_purge_batch_id =>p_purge_batch_id,
625 p_project_id =>p_project_id,
626 p_purge_release =>p_purge_release,
627 p_assignment_id_tab =>l_assignment_id_tab,
628 p_archive_flag =>p_archive_flag,
629 x_err_stack =>x_err_stack,
630 x_err_stage =>x_err_stage,
631 x_err_code =>x_err_code);
632
633
634
635 /* Check if this loop is the last set of p_commit_size If so then EXIT; */
636
637 IF l_last_fetch='Y' THEN
638 EXIT;
639 END IF;
640
641 END LOOP;
642
643 CLOSE cur_assignments_active;
644
645 END IF;
646
647 x_err_stack := l_old_err_stack; -- Added for bug 4227589
648
649 EXCEPTION
650 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
651 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
652
653 WHEN OTHERS THEN
654 pa_debug.debug('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_ASSIGNMENTS_PURGE' );
655 pa_debug.debug('Error stage is '||x_err_stage );
656 pa_debug.debug('Error stack is '||x_err_stack );
657 pa_debug.debug(SQLERRM);
658 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
659
660 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
661
662 end PA_ASSIGNMENTS_PURGE;
663
664 -- Start of comments
665 -- API name : PA_FORECAST_ITEMS_PURGE
666 -- Type : Public
667 -- Pre-reqs : None
668 -- Function : Purge procedure for Purging records from tables PA_FORECAST_ITEMS and PA_FORECAST_ITEM_DETAILS
669
670
671 Procedure PA_FORECAST_ITEMS_PURGE ( p_purge_batch_id in NUMBER,
672 p_project_id in NUMBER,
673 p_purge_release in VARCHAR2,
674 p_assignment_id_tab in PA_PLSQL_DATATYPES.IdTabTyp,
675 p_archive_flag in VARCHAR2,
676 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
677 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
678 x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
679
680 l_forecast_item_id Pa_forecast_items.forecast_item_id%TYPE;
681 I PLS_INTEGER;
682 l_assignment_id pa_forecast_items.assignment_id%TYPE;
683 l_nos_fi_inserted NUMBER ;
684 l_nos_fid_inserted NUMBER ;
685 l_nos_fi_deleted NUMBER ;
686 l_nos_fid_deleted NUMBER ;
687 l_nos_fi_amt_inserted NUMBER;
688 l_nos_fi_amt_deleted NUMBER;
689
690 CURSOR Cur_forecast_items(x_assignment_id In NUMBER) IS
691 SELECT forecast_item_id from pa_forecast_items
692 WHERE assignment_id =x_assignment_id;
693
694 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
695
696 Begin
697
698 l_old_err_stack := x_err_stack; -- Added for bug 4227589
699
700 x_err_stack := x_err_stack || ' ->Before call to purge Forecast Item records ';
701
702 /*Initialize the no of record variables for each call */
703
704 pa_debug.debug(' Inside Procedure to purge Forecast Items and Forecast Item Details ') ;
705 x_err_stage := 'Start purging forecast items for project '||to_char(p_project_id) ;
706
707
708 l_nos_fi_inserted :=0;
709 l_nos_fid_inserted :=0;
710 l_nos_fi_deleted :=0;
711 l_nos_fid_deleted :=0;
712 l_nos_fi_amt_deleted :=0;
713 l_nos_fi_amt_inserted :=0;
714
715
716 -----------------Begin Logic added to purge data from Workflow related tables-----------------
717 /* Call Workflow API to archive/purge data from pa_wf_processes and detail table
718 and also from pa_wf_ntf_performers table*/
719
720 Pa_Debug.DEBUG(' About to purge workflow process and details ') ;
721 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
722
723 Pa_Purge_Pjr_Txns.pa_wf_purge ( p_purge_batch_id =>p_purge_batch_id,
724 p_project_id =>p_project_id,
725 p_purge_release =>p_purge_release,
726 p_entity_key_tab =>p_assignment_id_tab,
727 p_wf_type_code =>'FORECAST_GENERATION',
728 p_item_type =>'PARFIGEN',
729 p_archive_flag =>p_archive_flag,
730 x_err_stack =>x_err_stack,
731 x_err_stage =>x_err_stage,
732 x_err_code =>x_err_code);
733
734 -----------------End of Logic added to purge data from Workflow related tables-----------------
735
736 /* Fetch the assignments from assignment tab. */
737
738 FOR I in p_assignment_id_tab.FIRST .. p_assignment_id_tab.LAST LOOP
739 l_assignment_id :=p_assignment_id_tab(I);
740
741 pa_debug.debug(' Fetching Forecast Items for assignments Id passed. ') ;
742 x_err_stage := 'Fetching forecast items for Assignment '||to_char(l_assignment_id) ;
743
744 OPEN cur_forecast_items(l_assignment_id);
745 LOOP
746 FETCH cur_forecast_items INTO l_forecast_item_id;
747 IF cur_forecast_items%NOTFOUND THEN
748 EXIT;
749 END IF;
750
751 /* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
752
753 IF p_archive_flag='Y' THEN
754
755 pa_debug.debug('Inserting Records into pa_forecast_items_AR table ') ;
756 x_err_stage := 'Inserting Records into pa_forecast_items_AR table for forecast item '||to_char(l_forecast_item_id) ;
757
758 INSERT INTO pa_frcst_items_AR
759 (PURGE_BATCH_ID,
760 PURGE_RELEASE,
761 PURGE_PROJECT_ID,
762 FORECAST_ITEM_ID,
763 FORECAST_ITEM_TYPE,
764 PROJECT_ORG_ID,
765 EXPENDITURE_ORG_ID,
766 EXPENDITURE_ORGANIZATION_ID,
767 PROJECT_ORGANIZATION_ID,
768 PROJECT_ID,
769 PROJECT_TYPE_CLASS,
770 PERSON_ID,
771 RESOURCE_ID,
772 BORROWED_FLAG,
773 ASSIGNMENT_ID,
774 ITEM_DATE,
775 ITEM_UOM,
776 ITEM_QUANTITY,
777 PVDR_PERIOD_SET_NAME,
778 PVDR_PA_PERIOD_NAME,
779 PVDR_GL_PERIOD_NAME,
780 RCVR_PERIOD_SET_NAME,
781 RCVR_PA_PERIOD_NAME,
782 RCVR_GL_PERIOD_NAME,
783 GLOBAL_EXP_PERIOD_END_DATE,
784 EXPENDITURE_TYPE,
785 EXPENDITURE_TYPE_CLASS,
786 COST_REJECTION_CODE,
787 REV_REJECTION_CODE,
788 TP_REJECTION_CODE,
789 BURDEN_REJECTION_CODE,
790 OTHER_REJECTION_CODE,
791 DELETE_FLAG,
792 ERROR_FLAG,
793 PROVISIONAL_FLAG,
794 CREATION_DATE,
795 CREATED_BY,
796 LAST_UPDATE_DATE,
797 LAST_UPDATED_BY,
798 LAST_UPDATE_LOGIN,
799 REQUEST_ID,
800 PROGRAM_APPLICATION_ID,
801 PROGRAM_ID,
802 PROGRAM_UPDATE_DATE,
803 ASGMT_SYS_STATUS_CODE,
804 CAPACITY_QUANTITY,
805 OVERCOMMITMENT_QUANTITY,
806 AVAILABILITY_QUANTITY,
807 OVERCOMMITMENT_FLAG,
808 AVAILABILITY_FLAG,
809 TP_AMOUNT_TYPE,
810 FORECAST_AMT_CALC_FLAG,
811 COST_TXN_CURRENCY_CODE,
812 TXN_RAW_COST,
813 TXN_BURDENED_COST,
814 REVENUE_TXN_CURRENCY_CODE,
815 TXN_REVENUE,
816 TP_TXN_CURRENCY_CODE,
817 TXN_TRANSFER_PRICE,
818 PROJECT_CURRENCY_CODE,
819 PROJECT_RAW_COST,
820 PROJECT_BURDENED_COST,
821 PROJECT_REVENUE,
822 PROJECT_TRANSFER_PRICE,
823 PROJFUNC_CURRENCY_CODE,
824 PROJFUNC_RAW_COST,
825 PROJFUNC_BURDENED_COST,
826 PROJFUNC_REVENUE,
827 PROJFUNC_TRANSFER_PRICE,
828 EXPFUNC_CURRENCY_CODE,
829 EXPFUNC_RAW_COST,
830 EXPFUNC_BURDENED_COST,
831 EXPFUNC_TRANSFER_PRICE,
832 OVERPROVISIONAL_QTY,
833 OVER_PROV_CONF_QTY,
834 CONFIRMED_QTY,
835 PROVISIONAL_QTY,
836 JOB_ID)
837
838 SELECT p_purge_batch_id,
839 p_purge_release,
840 p_project_id,
841 Forecast_Item_Id,
842 Forecast_Item_Type,
843 Project_Org_Id,
844 Expenditure_Org_Id,
845 Expenditure_Organization_Id,
846 Project_Organization_Id,
847 Project_Id,
848 Project_Type_Class,
849 Person_Id,
850 Resource_Id,
851 Borrowed_Flag,
852 Assignment_Id,
853 Item_Date,
854 Item_Uom,
855 Item_Quantity,
856 Pvdr_Period_Set_Name,
857 Pvdr_Pa_Period_Name,
858 Pvdr_Gl_Period_Name,
859 Rcvr_Period_Set_Name,
860 Rcvr_Pa_Period_Name,
861 Rcvr_Gl_Period_Name,
862 Global_Exp_Period_End_Date,
863 Expenditure_Type,
864 Expenditure_Type_Class,
865 Cost_Rejection_Code,
866 Rev_Rejection_Code,
867 Tp_Rejection_Code,
868 Burden_Rejection_Code,
869 Other_Rejection_Code,
870 Delete_Flag,
871 Error_Flag,
872 Provisional_Flag,
873 Creation_Date,
874 Created_By,
875 Last_Update_Date,
876 Last_Updated_By,
877 Last_Update_Login,
878 Request_Id,
879 Program_Application_Id,
880 Program_Id,
881 Program_Update_Date,
882 Asgmt_Sys_Status_Code,
883 Capacity_Quantity,
884 Overcommitment_Quantity,
885 Availability_Quantity,
886 Overcommitment_Flag,
887 Availability_Flag,
888 Tp_Amount_Type,
889 Forecast_Amt_Calc_Flag,
890 Cost_Txn_Currency_Code,
891 Txn_Raw_Cost,
892 Txn_Burdened_Cost,
893 Revenue_Txn_Currency_Code,
894 Txn_Revenue,
895 Tp_Txn_Currency_Code,
896 Txn_Transfer_Price,
897 Project_Currency_Code,
898 Project_Raw_Cost,
899 Project_Burdened_Cost,
900 Project_Revenue,
901 Project_Transfer_Price,
902 Projfunc_Currency_Code,
903 projfunc_Raw_Cost,
904 Projfunc_Burdened_Cost,
905 Projfunc_Revenue,
906 Projfunc_Transfer_Price,
907 Expfunc_Currency_Code,
908 Expfunc_Raw_Cost,
909 Expfunc_Burdened_Cost,
910 Expfunc_Transfer_Price,
911 Overprovisional_Qty,
912 Over_Prov_Conf_Qty,
913 Confirmed_Qty,
914 Provisional_Qty,
915 Job_Id
916 FROM pa_forecast_items
917 WHERE forecast_item_id = l_forecast_item_id;
918
919 /*Increase the value of l_nos_fi_inserted to indicate number of records inserted in forecast_items table.
920 The value will increase for each loop(forecast item id*/
921 l_nos_fi_inserted := l_nos_fi_inserted + SQL%ROWCOUNT;
922
923 pa_debug.debug('Inserting Records into pa_forecast_item_DETAILS_AR table ') ;
924 x_err_stage := 'Inserting Records into forecast_item_detail table for forecast item '||to_char(l_forecast_item_id) ;
925
926 INSERT INTO PA_FRCST_ITEM_DTLS_AR
927 (PURGE_BATCH_ID,
928 PURGE_RELEASE,
929 PURGE_PROJECT_ID,
930 FORECAST_ITEM_ID,
931 AMOUNT_TYPE_ID,
932 LINE_NUM,
933 RESOURCE_TYPE_CODE,
934 PERSON_BILLABLE_FLAG,
935 ITEM_DATE,
936 ITEM_UOM,
937 ITEM_QUANTITY,
938 EXPENDITURE_ORG_ID,
939 PROJECT_ORG_ID,
940 PVDR_ACCT_CURR_CODE,
941 PVDR_ACCT_AMOUNT,
942 RCVR_ACCT_CURR_CODE,
943 RCVR_ACCT_AMOUNT,
944 PROJ_CURRENCY_CODE,
945 PROJ_AMOUNT,
946 DENOM_CURRENCY_CODE,
947 DENOM_AMOUNT,
948 TP_AMOUNT_TYPE,
949 BILLABLE_FLAG,
950 FORECAST_SUMMARIZED_CODE,
951 UTIL_SUMMARIZED_CODE,
952 WORK_TYPE_ID,
953 RESOURCE_UTIL_CATEGORY_ID,
954 ORG_UTIL_CATEGORY_ID,
955 RESOURCE_UTIL_WEIGHTED,
956 ORG_UTIL_WEIGHTED,
957 PROVISIONAL_FLAG,
958 REVERSED_FLAG,
959 NET_ZERO_FLAG,
960 REDUCE_CAPACITY_FLAG,
961 LINE_NUM_REVERSED,
962 CREATION_DATE,
963 CREATED_BY,
964 LAST_UPDATE_DATE,
965 LAST_UPDATED_BY,
966 LAST_UPDATE_LOGIN,
967 REQUEST_ID,
968 PROGRAM_APPLICATION_ID,
969 PROGRAM_ID,
970 PROGRAM_UPDATE_DATE,
971 CAPACITY_QUANTITY,
972 OVERCOMMITMENT_QTY,
973 OVERPROVISIONAL_QTY,
974 OVER_PROV_CONF_QTY,
975 CONFIRMED_QTY,
976 PROVISIONAL_QTY,
977 JOB_ID,
978 PROJECT_ID,
979 RESOURCE_ID,
980 EXPENDITURE_ORGANIZATION_ID,
981 PJI_SUMMARIZED_FLAG)
982
983 SELECT p_purge_batch_id,
984 p_Purge_Release,
985 p_Project_Id,
986 Forecast_Item_Id,
987 Amount_Type_Id,
988 Line_Num,
989 Resource_Type_Code,
990 Person_Billable_Flag,
991 Item_Date,
992 Item_Uom,
993 Item_Quantity,
994 Expenditure_Org_Id,
995 Project_Org_Id,
996 Pvdr_Acct_Curr_Code,
997 Pvdr_Acct_Amount,
998 Rcvr_Acct_Curr_Code,
999 Rcvr_Acct_Amount,
1000 Proj_Currency_Code,
1001 Proj_Amount,
1002 Denom_Currency_Code,
1003 Denom_Amount,
1004 Tp_Amount_Type,
1005 Billable_Flag,
1006 Forecast_Summarized_Code,
1007 Util_Summarized_Code,
1008 Work_Type_Id,
1009 Resource_Util_Category_Id,
1010 Org_Util_Category_Id,
1011 Resource_Util_Weighted,
1012 Org_Util_Weighted,
1013 Provisional_Flag,
1014 Reversed_Flag,
1015 Net_Zero_Flag,
1016 Reduce_Capacity_Flag,
1017 Line_Num_Reversed,
1018 Creation_Date,
1019 Created_By,
1020 Last_Update_Date,
1021 Last_Updated_By,
1022 Last_Update_Login,
1023 Request_Id,
1024 Program_Application_Id,
1025 Program_Id,
1026 Program_Update_Date,
1027 CAPACITY_QUANTITY,
1028 OVERCOMMITMENT_QTY,
1029 OVERPROVISIONAL_QTY,
1030 OVER_PROV_CONF_QTY,
1031 CONFIRMED_QTY,
1032 PROVISIONAL_QTY,
1033 JOB_ID,
1034 PROJECT_ID,
1035 RESOURCE_ID,
1036 EXPENDITURE_ORGANIZATION_ID,
1037 PJI_SUMMARIZED_FLAG
1038 FROM PA_forecast_item_details
1039 WHERE forecast_item_id=l_forecast_item_id;
1040
1041 /*Increase the value of l_nos_fis_inserted to indicate number of records inserted in forecast_items detail table.
1042 The value will increase for each loop(forecast item id*/
1043 l_nos_fid_inserted := l_nos_fid_inserted + SQL%ROWCOUNT;
1044
1045 INSERT INTO PA_FI_AMOUNT_DETAILS_AR
1046 (PURGE_BATCH_ID,
1047 PURGE_RELEASE,
1048 PURGE_PROJECT_ID,
1049 FORECAST_ITEM_ID,
1050 LINE_NUM,
1051 ITEM_DATE,
1052 ITEM_UOM,
1053 ITEM_QUANTITY,
1054 REVERSED_FLAG,
1055 NET_ZERO_FLAG,
1056 LINE_NUM_REVERSED,
1057 CREATION_DATE,
1058 CREATED_BY,
1059 LAST_UPDATE_DATE,
1060 LAST_UPDATED_BY,
1061 LAST_UPDATE_LOGIN,
1062 REQUEST_ID,
1063 PROGRAM_APPLICATION_ID,
1064 PROGRAM_ID,
1065 PROGRAM_UPDATE_DATE,
1066 COST_TXN_CURRENCY_CODE,
1067 TXN_RAW_COST,
1068 TXN_BURDENED_COST,
1069 REVENUE_TXN_CURRENCY_CODE,
1070 TXN_REVENUE,
1071 TP_TXN_CURRENCY_CODE,
1072 TXN_TRANSFER_PRICE,
1073 PROJECT_CURRENCY_CODE,
1074 PROJECT_COST_RATE_DATE,
1075 PROJECT_COST_RATE_TYPE,
1076 PROJECT_COST_EXCHANGE_RATE,
1077 PROJECT_RAW_COST,
1078 PROJECT_BURDENED_COST,
1079 PROJECT_REVENUE_RATE_DATE,
1080 PROJECT_REVENUE_RATE_TYPE,
1081 PROJECT_REVENUE_EXCHANGE_RATE,
1082 PROJECT_REVENUE,
1083 PROJECT_TP_RATE_DATE,
1084 PROJECT_TP_RATE_TYPE,
1085 PROJECT_TP_EXCHANGE_RATE,
1086 PROJECT_TRANSFER_PRICE,
1087 PROJFUNC_CURRENCY_CODE,
1088 PROJFUNC_COST_RATE_DATE,
1089 PROJFUNC_COST_RATE_TYPE,
1090 PROJFUNC_COST_EXCHANGE_RATE,
1091 PROJFUNC_RAW_COST,
1092 PROJFUNC_BURDENED_COST,
1093 PROJFUNC_REVENUE,
1094 PROJFUNC_TRANSFER_PRICE,
1095 --PROJFUNC_RATE_DATE,
1096 --PROJFUNC_RATE_TYPE,
1097 --PROJFUNC_EXCHANGE_RATE,
1098 EXPFUNC_CURRENCY_CODE,
1099 EXPFUNC_COST_RATE_DATE,
1100 EXPFUNC_COST_RATE_TYPE,
1101 EXPFUNC_COST_EXCHANGE_RATE,
1102 EXPFUNC_RAW_COST,
1103 EXPFUNC_BURDENED_COST,
1104 EXPFUNC_TP_RATE_DATE,
1105 EXPFUNC_TP_RATE_TYPE,
1106 EXPFUNC_TP_EXCHANGE_RATE,
1107 EXPFUNC_TRANSFER_PRICE)
1108
1109 SELECT P_purge_batch_id,
1110 P_purge_release,
1111 P_project_id,
1112 Forecast_Item_Id,
1113 Line_Num,
1114 Item_Date,
1115 Item_Uom,
1116 Item_Quantity,
1117 Reversed_Flag,
1118 Net_Zero_Flag,
1119 Line_Num_Reversed,
1120 Creation_Date,
1121 Created_By,
1122 Last_Update_Date,
1123 Last_Updated_By,
1124 Last_Update_Login,
1125 Request_Id,
1126 Program_Application_Id,
1127 Program_Id,
1128 Program_Update_Date,
1129 Cost_Txn_Currency_Code,
1130 Txn_Raw_Cost,
1131 Txn_Burdened_Cost,
1132 Revenue_Txn_Currency_Code,
1133 Txn_Revenue,
1134 Tp_Txn_Currency_Code,
1135 Txn_Transfer_Price,
1136 Project_Currency_Code,
1137 Project_Cost_Rate_Date,
1138 Project_Cost_Rate_Type,
1139 Project_Cost_Exchange_Rate,
1140 Project_Raw_Cost,
1141 Project_Burdened_Cost,
1142 Project_Revenue_Rate_Date,
1143 Project_Revenue_Rate_Type,
1144 Project_Revenue_Exchange_Rate,
1145 Project_Revenue,
1146 Project_Tp_Rate_Date,
1147 Project_Tp_Rate_Type,
1148 Project_Tp_Exchange_Rate,
1149 Project_Transfer_Price,
1150 Projfunc_Currency_Code,
1151 Projfunc_Cost_Rate_Date,
1152 Projfunc_Cost_Rate_Type,
1153 Projfunc_Cost_Exchange_Rate,
1154 Projfunc_Raw_Cost,
1155 Projfunc_Burdened_Cost,
1156 Projfunc_Revenue,
1157 Projfunc_Transfer_Price,
1158 --Projfunc_Rate_Date,
1159 --Projfunc_Rate_Type,
1160 --Projfunc_Exchange_Rate,
1161 Expfunc_Currency_Code,
1162 Expfunc_Cost_Rate_Date,
1163 Expfunc_Cost_Rate_Type,
1164 Expfunc_Cost_Exchange_Rate,
1165 Expfunc_Raw_Cost,
1166 Expfunc_Burdened_Cost,
1167 Expfunc_Tp_Rate_Date,
1168 Expfunc_Tp_Rate_Type,
1169 Expfunc_Tp_Exchange_Rate,
1170 Expfunc_Transfer_Price
1171 FROM PA_FI_AMOUNT_DETAILS Where forecast_item_id=l_forecast_item_id;
1172
1173 /*Increase the value of l_nos_fi_amt_inserted to reflct the number of records inserted */
1174
1175 l_nos_fi_amt_inserted := l_nos_fi_amt_inserted + SQL%ROWCOUNT;
1176
1177 END IF;
1178
1179 /*To keep the count of no os records deleted from pa_forecast_items and pa_forecast_item_details, manipulate the
1180 count of l_nos_of fi_deleted and l_nos_fis_deleted. */
1181
1182 pa_debug.debug('Deleting Records from pa_fi_amount_details table ') ;
1183 x_err_stage := 'Deleting Records from pa_fi_amount_details table for id '||to_char(l_forecast_item_id) ;
1184
1185 DELETE PA_FI_AMOUNT_DETAILS
1186 WHERE forecast_item_id =l_forecast_item_id;
1187
1188 l_nos_fi_amt_deleted := l_nos_fi_amt_deleted + SQL%ROWCOUNT;
1189
1190 pa_debug.debug('Deleting Records from pa_forecast_item_details table ') ;
1191 x_err_stage := 'Deleting Records from pa_forecast_item_details table for id '||to_char(l_forecast_item_id) ;
1192
1193 DELETE PA_FORECAST_ITEM_DETAILS
1194 WHERE forecast_item_id =l_forecast_item_id;
1195
1196 l_nos_fid_deleted :=l_nos_fid_deleted + SQL%ROWCOUNT;
1197
1198 pa_debug.debug('Deleting Records from pa_forecast_items table ') ;
1199 x_err_stage := 'Deleting Records from pa_forecast_items table for id '||to_char(l_forecast_item_id) ;
1200
1201
1202 DELETE PA_FORECAST_ITEMS
1203 WHERE forecast_item_id=l_forecast_item_id;
1204
1205 l_nos_fi_deleted :=l_nos_fi_deleted + SQL%ROWCOUNT;
1206
1207
1208 END LOOP;
1209 Close cur_forecast_items;
1210 END LOOP;
1211
1212 /*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
1213 The procedure is called once for pa_forecast_items and once for pa_forecast_item_details */
1214
1215 pa_purge.CommitProcess(p_purge_batch_id,
1216 p_project_id,
1217 'PA_FORECAST_ITEMS',
1218 l_nos_fi_inserted,
1219 l_nos_fi_deleted,
1220 x_err_code,
1221 x_err_stack,
1222 x_err_stage
1223 ) ;
1224
1225 pa_purge.CommitProcess(p_purge_batch_id,
1226 p_project_id,
1227 'PA_FORECAST_ITEM_DETAILS',
1228 l_nos_fid_inserted,
1229 l_nos_fid_deleted,
1230 x_err_code,
1231 x_err_stack,
1232 x_err_stage
1233 ) ;
1234
1235 pa_purge.CommitProcess(p_purge_batch_id,
1236 p_project_id,
1237 'PA_FI_AMOUNT_DETAILS',
1238 /* l_nos_fid_inserted, bug 2480653 */
1239 /* l_nos_fid_deleted, bug 2480653 */
1240 l_nos_fi_amt_inserted,
1241 l_nos_fi_amt_deleted,
1242 x_err_code,
1243 x_err_stack,
1244 x_err_stage
1245 ) ;
1246
1247
1248 x_err_stack := l_old_err_stack; -- Added for bug 4227589
1249
1250 EXCEPTION
1251 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1252 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1253
1254 WHEN OTHERS THEN
1255 pa_debug.debug('Error Procedure Name := PA_PURGE_COSTING.PA_COSTING_MAIN_PURGE' );
1256 pa_debug.debug('Error stage is '||x_err_stage );
1257 pa_debug.debug('Error stack is '||x_err_stack );
1258 pa_debug.debug(SQLERRM);
1259 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1260
1261 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1262
1263
1264 end pa_forecast_items_purge;
1265
1266
1267 -- Start of comments
1268 -- API name : PA_SCHEDULES_PURGE
1269 -- Type : Public
1270 -- Pre-reqs : None
1271 -- Function : Purge procedure for Purging records from tables PA_Schedules, pa_schedules_history and
1272 -- pa_schedule_except_history tables.
1273
1274 Procedure PA_SCHEDULES_PURGE ( p_purge_batch_id in NUMBER,
1275 p_project_id in NUMBER,
1276 p_purge_release in VARCHAR2,
1277 p_assignment_id_tab in PA_PLSQL_DATATYPES.IdTabTyp,
1278 p_archive_flag in VARCHAR2,
1279 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1280 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1281 x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
1282
1283 l_schedule_id pa_schedules.schedule_id%TYPE;
1284 l_schedule_exception_id pa_schedule_except_history.schedule_exception_id%TYPE;
1285 I PLS_INTEGER;
1286 l_assignment_id pa_forecast_items.assignment_id%TYPE;
1287 l_nos_schedule_inserted NUMBER ;
1288 l_nos_schedule_his_inserted NUMBER ;
1289 l_nos_schedule_exp_inserted NUMBER ;
1290 l_nos_schedule_deleted NUMBER ;
1291 l_nos_schedule_his_deleted NUMBER ;
1292 l_nos_schedule_exp_deleted NUMBER ;
1293
1294
1295 CURSOR Cur_schedules(x_assignment_id In NUMBER) IS
1296 SELECT schedule_id from pa_schedules
1297 WHERE assignment_id =x_assignment_id;
1298
1299 Cursor Cur_Schedule_except (a_assignment_id In NUMBER) IS
1300 SELECT schedule_exception_id from pa_schedule_except_history
1301 WHERE assignment_id =a_assignment_id;
1302
1303 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
1304
1305 Begin
1306
1307 l_old_err_stack := x_err_stack; -- Added for bug 4227589
1308
1309 x_err_stack := x_err_stack || ' ->Before call to purge schedule related records ';
1310
1311 /*Initialize the no of record variables for each call */
1312
1313 pa_debug.debug(' Inside Procedure to purge schedule, schedule exceptions and schedule history records ') ;
1314 x_err_stage := 'Start purging schedules for project '||to_char(p_project_id) ;
1315
1316 l_nos_schedule_inserted :=0;
1317 l_nos_schedule_his_inserted :=0;
1318 l_nos_schedule_exp_inserted :=0;
1319 l_nos_schedule_deleted :=0;
1320 l_nos_schedule_his_deleted :=0;
1321 l_nos_schedule_exp_deleted :=0;
1322
1323
1324 /* Fetch the assignments from assignment tab. */
1325
1326 FOR I in p_assignment_id_tab.FIRST .. p_assignment_id_tab.LAST LOOP
1327 l_assignment_id :=p_assignment_id_tab(I);
1328
1329 pa_debug.debug(' Fetching schedule records for assignments Id passed. ') ;
1330 x_err_stage := 'Fetching schedule records for Assignment '||to_char(l_assignment_id) ;
1331
1332 OPEN cur_schedules(l_assignment_id);
1333 LOOP
1334 FETCH cur_schedules INTO l_schedule_id;
1335 IF cur_schedules%NOTFOUND THEN
1336 EXIT;
1337 END IF;
1338
1339 /* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
1340
1341 IF p_archive_flag='Y' THEN
1342
1343 pa_debug.debug('Inserting Records into pa_schedules_AR table ') ;
1344 x_err_stage := 'Inserting Records into pa_schedules_AR table for schedule id '||to_char(l_schedule_id) ;
1345
1346 INSERT INTO PA_SCHEDULES_AR
1347 (PURGE_BATCH_ID,
1348 PURGE_RELEASE,
1349 PURGE_PROJECT_ID,
1350 SCHEDULE_ID,
1351 CALENDAR_ID,
1352 ASSIGNMENT_ID,
1353 PROJECT_ID,
1354 SCHEDULE_TYPE_CODE,
1355 STATUS_CODE,
1356 START_DATE,
1357 END_DATE,
1358 MONDAY_HOURS,
1359 TUESDAY_HOURS,
1360 WEDNESDAY_HOURS,
1361 THURSDAY_HOURS,
1362 FRIDAY_HOURS,
1363 SATURDAY_HOURS,
1364 SUNDAY_HOURS,
1365 REQUEST_ID,
1366 PROGRAM_APPLICATION_ID,
1367 PROGRAM_ID,
1368 PROGRAM_UPDATE_DATE,
1369 CREATION_DATE,
1370 CREATED_BY,
1371 LAST_UPDATE_DATE,
1372 LAST_UPDATE_BY,
1373 LAST_UPDATE_LOGIN,
1374 FORECAST_TXN_VERSION_NUMBER,
1375 FORECAST_TXN_GENERATED_FLAG)
1376
1377 SELECT p_purge_batch_id,
1378 p_Purge_Release,
1379 p_Project_Id,
1380 Schedule_Id,
1381 Calendar_Id,
1382 Assignment_Id,
1383 Project_Id,
1384 Schedule_Type_Code,
1385 Status_Code,
1386 Start_Date,
1387 End_Date,
1388 Monday_Hours,
1389 Tuesday_Hours,
1390 Wednesday_Hours,
1391 Thursday_Hours,
1392 Friday_Hours,
1393 Saturday_Hours,
1394 Sunday_Hours,
1395 Request_Id,
1396 Program_Application_Id,
1397 Program_Id,
1398 Program_Update_Date,
1399 Creation_Date,
1400 Created_By,
1401 Last_Update_Date,
1402 Last_Update_By,
1403 Last_Update_Login,
1404 Forecast_Txn_Version_Number,
1405 Forecast_Txn_Generated_Flag
1406 FROM pa_schedules WHERE schedule_id=l_schedule_id;
1407
1408 /*Increase the value of l_nos_schedule_inserted to indicate number of records inserted in schedule_ar table.
1409 The value will increase for each loop*/
1410
1411 l_nos_schedule_inserted := l_nos_schedule_inserted + SQL%ROWCOUNT;
1412
1413 pa_debug.debug('Inserting Records into pa_schedule_except_history_AR table ') ;
1414 x_err_stage := 'Inserting Records into pa_schedules_except_history_AR table for schedule id '||to_char(l_schedule_id) ;
1415
1416 INSERT INTO PA_SCHEDULES_HSTRY_AR
1417 (PURGE_BATCH_ID,
1418 PURGE_RELEASE,
1419 PURGE_PROJECT_ID,
1420 SCHEDULE_ID,
1421 CALENDAR_ID,
1422 ASSIGNMENT_ID,
1423 PROJECT_ID,
1424 SCHEDULE_TYPE_CODE,
1425 STATUS_CODE,
1426 START_DATE,
1427 END_DATE,
1428 MONDAY_HOURS,
1429 TUESDAY_HOURS,
1430 WEDNESDAY_HOURS,
1431 THURSDAY_HOURS,
1432 FRIDAY_HOURS,
1433 SATURDAY_HOURS,
1434 SUNDAY_HOURS,
1435 CHANGE_ID,
1436 LAST_APPROVED_FLAG,
1437 REQUEST_ID,
1438 PROGRAM_APPLICATION_ID,
1439 PROGRAM_ID,
1440 PROGRAM_UPDATE_DATE,
1441 CREATION_DATE,
1442 CREATED_BY,
1443 LAST_UPDATE_DATE,
1444 LAST_UPDATE_BY,
1445 LAST_UPDATE_LOGIN)
1446
1447 SELECT p_purge_batch_id,
1448 p_purge_release,
1449 p_project_id,
1450 Schedule_Id,
1451 Calendar_Id,
1452 Assignment_Id,
1453 Project_Id,
1454 Schedule_Type_Code,
1455 Status_Code,
1456 Start_Date,
1457 End_Date,
1458 Monday_Hours,
1459 Tuesday_Hours,
1460 Wednesday_Hours,
1461 Thursday_Hours,
1462 Friday_Hours,
1463 Saturday_Hours,
1464 Sunday_Hours,
1465 Change_Id,
1466 Last_Approved_Flag,
1467 Request_Id,
1468 Program_Application_Id,
1469 Program_Id,
1470 Program_Update_Date,
1471 Creation_Date,
1472 Created_By,
1473 Last_Update_Date,
1474 Last_Update_By,
1475 Last_Update_Login
1476 FROM pa_schedules_history WHERE schedule_id=l_schedule_id;
1477
1478 /*Increase the value of l_nos_schedule_his_inserted to indicate number of records inserted in schedule_history_ar
1479 table. The value will increase for each loop*/
1480
1481 l_nos_schedule_his_inserted := l_nos_schedule_his_inserted + SQL%ROWCOUNT;
1482
1483 END IF;
1484
1485 /*To keep the count of no os records deleted from pa_forecast_items and pa_forecast_item_details, manipulate the
1486 count of l_nos_of fi_deleted and l_nos_fis_deleted. */
1487
1488 pa_debug.debug('Deleting Records from pa_schedule_history table ') ;
1489 x_err_stage := 'Deleting Records from pa_schedules_history table for id '||to_char(l_schedule_id) ;
1490
1491 DELETE PA_SCHEDULES_HISTORY
1492 WHERE schedule_id =l_schedule_id;
1493
1494 l_nos_schedule_his_deleted :=l_nos_schedule_his_deleted + SQL%ROWCOUNT;
1495
1496 pa_debug.debug('Deleting Records from pa_schedules table ') ;
1497 x_err_stage := 'Deleting Records from pa_schedules table for id '||to_char(l_schedule_id) ;
1498
1499
1500 DELETE PA_SCHEDULES
1501 WHERE schedule_id=l_schedule_id;
1502
1503 l_nos_schedule_deleted :=l_nos_schedule_deleted + SQL%ROWCOUNT;
1504
1505
1506 END LOOP;
1507 CLOSE Cur_schedules;
1508
1509 pa_debug.debug(' Fetching schedule exception records for assignments Id passed. ') ;
1510 x_err_stage := 'Fetching schedule exception records for Assignment '||to_char(l_assignment_id) ;
1511
1512 OPEN cur_schedule_except(l_assignment_id);
1513 LOOP
1514 FETCH cur_schedule_except INTO l_schedule_exception_id;
1515 IF cur_schedule_except%NOTFOUND THEN
1516 EXIT;
1517 END IF;
1518
1519 /* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
1520
1521 IF p_archive_flag='Y' THEN
1522
1523 pa_debug.debug('inserting records into pa_schedules_except_history_ar table ') ;
1524 x_err_stage := 'Inserting Records into pa_schedules_except_history_AR table for schedule id '||to_char(l_schedule_id) ;
1525
1526 INSERT INTO PA_SCH_EXCPT_HSTRY_AR
1527 (PURGE_BATCH_ID,
1528 PURGE_RELEASE,
1529 PURGE_PROJECT_ID,
1530 SCHEDULE_EXCEPTION_ID,
1531 CALENDAR_ID,
1532 ASSIGNMENT_ID,
1533 PROJECT_ID,
1534 SCHEDULE_TYPE_CODE,
1535 STATUS_CODE,
1536 EXCEPTION_TYPE_CODE,
1537 RESOURCE_CALENDAR_PERCENT,
1538 NON_WORKING_DAY_FLAG,
1539 CHANGE_HOURS_TYPE_CODE,
1540 START_DATE,
1541 END_DATE,
1542 MONDAY_HOURS,
1543 TUESDAY_HOURS,
1544 WEDNESDAY_HOURS,
1545 THURSDAY_HOURS,
1546 FRIDAY_HOURS,
1547 SATURDAY_HOURS,
1548 SUNDAY_HOURS ,
1549 CREATION_DATE,
1550 CREATED_BY,
1551 LAST_UPDATE_DATE,
1552 LAST_UPDATE_BY,
1553 LAST_UPDATE_LOGIN,
1554 CHANGE_ID,
1555 DURATION_SHIFT_TYPE_CODE,
1556 DURATION_SHIFT_UNIT_CODE,
1557 NUMBER_OF_SHIFT,
1558 CHANGE_CALENDAR_TYPE_CODE,
1559 CHANGE_CALENDAR_ID)
1560
1561 SELECT p_purge_batch_id,
1562 p_purge_release,
1563 p_project_id,
1564 schedule_Exception_Id,
1565 Calendar_Id,
1566 Assignment_Id,
1567 Project_Id,
1568 Schedule_Type_Code,
1569 Status_Code,
1570 Exception_Type_Code,
1571 Resource_Calendar_Percent,
1572 Non_Working_Day_Flag,
1573 Change_Hours_Type_Code,
1574 Start_Date,
1575 End_Date,
1576 Monday_Hours,
1577 Tuesday_Hours,
1578 Wednesday_Hours,
1579 Thursday_Hours,
1580 Friday_Hours,
1581 Saturday_Hours,
1582 Sunday_Hours ,
1583 Creation_Date,
1584 Created_By,
1585 Last_Update_Date,
1586 Last_Update_By,
1587 Last_Update_Login,
1588 Change_Id,
1589 Duration_Shift_Type_Code,
1590 Duration_Shift_Unit_Code,
1591 Number_Of_Shift,
1592 Change_Calendar_Type_Code,
1593 Change_Calendar_Id
1594 FROM pa_schedule_except_history WHERE schedule_exception_id=l_schedule_exception_id;
1595
1596 /*Increase the value of l_nos_schedule_his_inserted to indicate number of records inserted inschedule_except_history_ar
1597 table. The value will increase for each loop*/
1598
1599 l_nos_schedule_exp_inserted := l_nos_schedule_exp_inserted + SQL%ROWCOUNT;
1600
1601 END IF;
1602
1603 /*To keep the count of no os records deleted from pa_schedules_except_history , manipulate the
1604 count of l_nos_schedule_exp_deleted . */
1605
1606 pa_debug.debug('Deleting Records from pa_scheduleexcept_history table ') ;
1607 x_err_stage := 'Deleting Records from pa_schedules_except_history table for id '||to_char(l_schedule_exception_id) ;
1608
1609 DELETE PA_SCHEDULE_EXCEPT_HISTORY
1610 WHERE schedule_exception_id =l_schedule_exception_id;
1611
1612 l_nos_schedule_exp_deleted := l_nos_schedule_exp_deleted + SQL%ROWCOUNT;
1613 END LOOP;
1614 Close cur_schedule_except;
1615
1616 END LOOP;
1617
1618 /*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in
1619 the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
1620 The procedure is called pa_schedules, pa_schedule_except_history and pa_schedules_history tables */
1621
1622 pa_purge.CommitProcess(p_purge_batch_id,
1623 p_project_id,
1624 'PA_SCHEDULES',
1625 l_nos_schedule_inserted,
1626 l_nos_schedule_deleted,
1627 x_err_code,
1628 x_err_stack,
1629 x_err_stage
1630 ) ;
1631
1632 pa_purge.CommitProcess(p_purge_batch_id,
1633 p_project_id,
1634 'PA_SCHEDULES_HISTORY',
1635 l_nos_schedule_his_inserted,
1636 l_nos_schedule_his_deleted,
1637 x_err_code,
1638 x_err_stack,
1639 x_err_stage
1640 ) ;
1641
1642 pa_purge.CommitProcess(p_purge_batch_id,
1643 p_project_id,
1644 'PA_SCHEDULE_EXCEPT_HISTORY',
1645 l_nos_schedule_exp_inserted,
1646 l_nos_schedule_exp_deleted,
1647 x_err_code,
1648 x_err_stack,
1649 x_err_stage
1650 ) ;
1651
1652
1653 x_err_stack := l_old_err_stack; -- Added for bug 4227589
1654
1655 EXCEPTION
1656 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1657 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1658
1659 WHEN OTHERS THEN
1660 pa_debug.debug('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_SCHEDULES_PURGE' );
1661 pa_debug.debug('Error stage is '||x_err_stage );
1662 pa_debug.debug('Error stack is '||x_err_stack );
1663 pa_debug.debug(SQLERRM);
1664 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1665
1666 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1667
1668
1669 end Pa_schedules_purge;
1670
1671
1672 -- Start of comments
1673 -- API name : PA_CANDIDATES_PURGE
1674 -- Type : Public
1675 -- Pre-reqs : None
1676 -- Function : Main purge procedure for Purging records from PA_CANDIDATES and PA_CANDIDATE_REVIEWS table
1677
1678
1679 Procedure PA_CANDIDATES_PURGE ( p_purge_batch_id in NUMBER,
1680 p_project_id in NUMBER,
1681 p_purge_release in VARCHAR2,
1682 p_assignment_id_tab in PA_PLSQL_DATATYPES.IdTabTyp,
1683 p_archive_flag in VARCHAR2,
1684 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1685 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1686 x_err_code in OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
1687
1688
1689 l_candidate_id pa_candidates.candidate_id%TYPE;
1690 I PLS_INTEGER;
1691 l_assignment_id pa_forecast_items.assignment_id%TYPE;
1692 l_nos_candidate_inserted NUMBER ;
1693 l_nos_candidate_rev_inserted NUMBER ;
1694 l_nos_candidate_deleted NUMBER ;
1695 l_nos_candidate_rev_deleted NUMBER ;
1696
1697 CURSOR Cur_candidates (x_assignment_id In NUMBER) IS
1698 SELECT candidate_id from pa_candidates
1699 WHERE assignment_id =x_assignment_id;
1700
1701 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
1702
1703 Begin
1704
1705 l_old_err_stack := x_err_stack; -- Added for bug 4227589
1706
1707 x_err_stack := x_err_stack || ' ->Before call to purge candidates records ';
1708
1709 /*Initialize the no of record variables for each call */
1710
1711 pa_debug.debug(' Inside Procedure to purge candidates and candidate review log ') ;
1712 x_err_stage := 'Inside Procedure to purge candidates and candidate review log fro project '||to_char(p_project_id) ;
1713
1714 l_nos_candidate_inserted :=0;
1715 l_nos_candidate_rev_inserted :=0;
1716 l_nos_candidate_deleted :=0;
1717 l_nos_candidate_rev_deleted :=0;
1718
1719 -----------------Begin Logic added to purge data from Workflow related tables-----------------
1720 /* Call Workflow API to archive/purge data from pa_wf_processes and detail table
1721 and also from pa_wf_ntf_performers table*/
1722
1723 Pa_Debug.DEBUG(' About to purge workflow process and details ') ;
1724 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
1725
1726 Pa_Purge_Pjr_Txns.pa_wf_purge ( p_purge_batch_id =>p_purge_batch_id,
1727 p_project_id =>p_project_id,
1728 p_purge_release =>p_purge_release,
1729 p_entity_key_tab =>p_assignment_id_tab,
1730 p_wf_type_code =>'CANDIDATE',
1731 p_item_type =>'PACANDID',
1732 p_archive_flag =>p_archive_flag,
1733 x_err_stack =>x_err_stack,
1734 x_err_stage =>x_err_stage,
1735 x_err_code =>x_err_code);
1736
1737 -----------------End of Logic added to purge data from Workflow related tables-----------------
1738
1739 /* Fetch the assignments from assignment tab. */
1740
1741 FOR I in p_assignment_id_tab.FIRST .. p_assignment_id_tab.LAST LOOP
1742 l_assignment_id :=p_assignment_id_tab(I);
1743
1744 pa_debug.debug(' Fetching schedule records for assignments Id passed. ') ;
1745 x_err_stage := 'Fetching schedule records for Assignment '||to_char(l_assignment_id) ;
1746
1747 OPEN cur_candidates(l_assignment_id);
1748 LOOP
1749 FETCH cur_candidates INTO l_candidate_id;
1750 IF cur_candidates%NOTFOUND THEN
1751 EXIT;
1752 END IF;
1753
1754 /* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
1755
1756 IF p_archive_flag='Y' THEN
1757
1758 pa_debug.debug('Inserting Records into pa_candidates_AR table ') ;
1759 x_err_stage := 'Inserting Records into pa_candidates_AR table for candidate id '||to_char(l_candidate_id) ;
1760
1761 INSERT INTO PA_CANDIDATES_AR
1762 ( PURGE_BATCH_ID,
1763 PURGE_RELEASE,
1764 PURGE_PROJECT_ID,
1765 CANDIDATE_ID,
1766 ASSIGNMENT_ID,
1767 RESOURCE_ID,
1768 RECORD_VERSION_NUMBER,
1769 STATUS_CODE,
1770 NOMINATED_BY_PERSON_ID,
1771 NOMINATION_DATE,
1772 NOMINATION_COMMENTS,
1773 CANDIDATE_RANKING,
1774 ATTRIBUTE_CATEGORY,
1775 ATTRIBUTE1,
1776 ATTRIBUTE2,
1777 ATTRIBUTE3,
1778 ATTRIBUTE4,
1779 ATTRIBUTE5,
1780 ATTRIBUTE6,
1781 ATTRIBUTE7,
1782 ATTRIBUTE8,
1783 ATTRIBUTE9,
1784 ATTRIBUTE10,
1785 ATTRIBUTE11,
1786 ATTRIBUTE12,
1787 ATTRIBUTE13,
1788 ATTRIBUTE14,
1789 ATTRIBUTE15,
1790 CREATION_DATE,
1791 CREATED_BY,
1792 LAST_UPDATE_DATE,
1793 LAST_UPDATED_BY,
1794 LAST_UPDATE_LOGIN)
1795
1796 SELECT p_purge_batch_id,
1797 p_purge_release,
1798 p_project_id,
1799 Candidate_Id,
1800 Assignment_Id,
1801 Resource_Id,
1802 Record_Version_Number,
1803 Status_Code,
1804 Nominated_By_Person_Id,
1805 Nomination_Date,
1806 Nomination_Comments,
1807 Candidate_Ranking,
1808 Attribute_Category,
1809 Attribute1,
1810 Attribute2,
1811 Attribute3,
1812 Attribute4,
1813 Attribute5,
1814 Attribute6,
1815 Attribute7,
1816 Attribute8,
1817 Attribute9,
1818 Attribute10,
1819 Attribute11,
1820 Attribute12,
1821 Attribute13,
1822 Attribute14,
1823 Attribute15,
1824 Creation_Date,
1825 Created_By,
1826 Last_Update_Date,
1827 Last_Updated_By,
1828 Last_Update_Login
1829 FROM PA_CANDIDATES WHERE candidate_id=l_candidate_id;
1830
1831 /*Increase the value of l_nos_candidate_inserted to indicate number of records inserted in candidates_ar table.
1832 The value will increase for each loop*/
1833
1834 l_nos_candidate_inserted := l_nos_candidate_inserted + SQL%ROWCOUNT;
1835
1836 pa_debug.debug('Inserting Records into pa_candidate_reviews_AR table ') ;
1837 x_err_stage := 'Inserting Records into pa_candidate_reviews_AR table for candidate id '||to_char(l_candidate_id) ;
1838
1839 INSERT INTO PA_CANDIDATES_REV_AR
1840 (PURGE_BATCH_ID,
1841 PURGE_RELEASE,
1842 PURGE_PROJECT_ID,
1843 CANDIDATE_REVIEW_ID,
1844 CANDIDATE_ID,
1845 RECORD_VERSION_NUMBER,
1846 STATUS_CODE,
1847 REVIEWER_PERSON_ID,
1848 REVIEW_DATE,
1849 CHANGE_REASON_CODE,
1850 REVIEW_COMMENTS,
1851 ATTRIBUTE_CATEGORY,
1852 ATTRIBUTE1,
1853 ATTRIBUTE2,
1854 ATTRIBUTE3,
1855 ATTRIBUTE4,
1856 ATTRIBUTE5,
1857 ATTRIBUTE6,
1858 ATTRIBUTE7,
1859 ATTRIBUTE8,
1860 ATTRIBUTE9,
1861 ATTRIBUTE10,
1862 ATTRIBUTE11,
1863 ATTRIBUTE12,
1864 ATTRIBUTE13,
1865 ATTRIBUTE14,
1866 ATTRIBUTE15,
1867 CREATION_DATE,
1868 CREATED_BY,
1869 LAST_UPDATE_DATE,
1870 LAST_UPDATED_BY,
1871 LAST_UPDATE_LOGIN)
1872
1873 SELECT p_purge_batch_id,
1874 p_purge_release,
1875 p_project_id,
1876 Candidate_Review_Id,
1877 Candidate_Id,
1878 Record_Version_Number,
1879 Status_Code,
1880 Reviewer_Person_Id,
1881 Review_Date,
1882 Change_Reason_Code,
1883 Review_Comments,
1884 Attribute_Category,
1885 Attribute1,
1886 Attribute2,
1887 Attribute3,
1888 Attribute4,
1889 Attribute5,
1890 Attribute6,
1891 Attribute7,
1892 Attribute8,
1893 Attribute9,
1894 Attribute10,
1895 Attribute11,
1896 Attribute12,
1897 Attribute13,
1898 Attribute14,
1899 Attribute15,
1900 Creation_Date,
1901 Created_By,
1902 Last_Update_Date,
1903 Last_Updated_By,
1904 Last_Update_Login
1905 FROM PA_CANDIDATE_REVIEWS WHERE candidate_id = l_candidate_id;
1906
1907 /*Increase the value of l_nos_candidate_rev_inserted to indicate number of records inserted in candidate_reviews_ar
1908 table. The value will increase for each loop*/
1909
1910 l_nos_candidate_rev_inserted := l_nos_candidate_rev_inserted + SQL%ROWCOUNT;
1911
1912 END IF;
1913
1914 /*To keep the count of no os records deleted from pa_candidates and pa_candidate_reviews, manipulate the
1915 count of l_nos_candidate_deleted and l_nos_candidate_rev_deleted. */
1916
1917 pa_debug.debug('Deleting Records from pa_candidate_reviews table ') ;
1918 x_err_stage := 'Deleting Records from pa_candidate_reviews table for id '||to_char(l_candidate_id) ;
1919
1920 DELETE PA_CANDIDATE_REVIEWS
1921 WHERE candidate_id =l_candidate_id;
1922
1923 l_nos_candidate_rev_deleted :=l_nos_candidate_rev_deleted + SQL%ROWCOUNT;
1924
1925 pa_debug.debug('Deleting Records from pa_candidates table ') ;
1926 x_err_stage := 'Deleting Records from pa_candidates table for id '||to_char(l_candidate_id) ;
1927
1928 DELETE PA_CANDIDATES
1929 WHERE candidate_id =l_candidate_id;
1930
1931 l_nos_candidate_deleted :=l_nos_candidate_deleted + SQL%ROWCOUNT;
1932
1933 END LOOP;
1934
1935 CLOSE Cur_candidates;
1936
1937 END LOOP;
1938
1939 /*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in
1940 the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
1941 The procedure is called pa_schedules, pa_schedule_except_history and pa_schedules_history tables */
1942
1943 pa_purge.CommitProcess(p_purge_batch_id,
1944 p_project_id,
1945 'PA_CANDIDATES',
1946 l_nos_candidate_inserted,
1947 l_nos_candidate_deleted,
1948 x_err_code,
1949 x_err_stack,
1950 x_err_stage
1951 ) ;
1952
1953 pa_purge.CommitProcess(p_purge_batch_id,
1954 p_project_id,
1955 'PA_CANDIDATE_REVIEWS',
1956 l_nos_candidate_rev_inserted,
1957 l_nos_candidate_rev_deleted,
1958 x_err_code,
1959 x_err_stack,
1960 x_err_stage
1961 ) ;
1962
1963 x_err_stack := l_old_err_stack; -- Added for bug 4227589
1964
1965 EXCEPTION
1966 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
1967 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1968
1969 WHEN OTHERS THEN
1970 pa_debug.debug('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_CANDIDATES_PURGE');
1971 pa_debug.debug('Error stage is '||x_err_stage );
1972 pa_debug.debug('Error stack is '||x_err_stack );
1973 pa_debug.debug(SQLERRM);
1974 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
1975
1976 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
1977
1978 End pa_candidates_purge;
1979
1980
1981 -- Start of comments
1982 -- API name : PA_ASSIGNMENT_CONFLICTS_PURGE
1983 -- Type : Public
1984 -- Pre-reqs : None
1985 -- Function : Main purge procedure for Purging records from PA_ASSIGNMENT_CONFLICT_HIST table
1986
1987
1988 Procedure PA_ASSIGNMENT_CONFLICTS_PURGE ( p_purge_batch_id in NUMBER,
1989 p_project_id in NUMBER,
1990 p_purge_release in VARCHAR2,
1991 p_assignment_id_tab in PA_PLSQL_DATATYPES.IdTabTyp,
1992 p_archive_flag in VARCHAR2,
1993 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1994 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1995 x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
1996
1997 l_assignment_id pa_forecast_items.assignment_id%TYPE;
1998 l_nos_conflicts_inserted NUMBER ;
1999 l_nos_conflicts_deleted NUMBER ;
2000
2001 ----------------------------------------------------------------------------------
2002 -- Cursor to fetch all the conflicting projects based on the assignment_id passed.
2003 -- Added for Bug 2979944
2004 ----------------------------------------------------------------------------------
2005 CURSOR c1 (x_assignment_id pa_project_assignments.assignment_id%TYPE) IS
2006 SELECT DISTINCT asgn.project_id
2007 FROM pa_project_assignments asgn,
2008 pa_assignment_conflict_hist hist
2009 WHERE asgn.assignment_id = hist.conflict_assignment_id
2010 AND hist.assignment_id = x_assignment_id;
2011
2012 l_project_id_tab Pa_Plsql_Datatypes.IdTabTyp;
2013 l_count NUMBER;
2014 l_overcommitment_flag VARCHAR2(1) := 'N';
2015
2016 ----------------------------------------------------------------------------------
2017
2018 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
2019
2020 Begin
2021
2022 l_old_err_stack := x_err_stack; -- Added for bug 4227589
2023
2024 x_err_stack := x_err_stack || ' ->Before call to purge assignment conflicts records ';
2025
2026 /*Initialize the no of record variables for each call */
2027
2028 pa_debug.debug(' Inside Procedure to purge assignment conflicts records ') ;
2029 x_err_stage := 'Start purging assignment conflicts for project '||to_char(p_project_id) ;
2030
2031 l_nos_conflicts_inserted :=0;
2032 l_nos_conflicts_deleted :=0;
2033
2034
2035 FOR I in p_assignment_id_tab.FIRST .. p_assignment_id_tab.LAST LOOP
2036 l_assignment_id :=p_assignment_id_tab(I);
2037
2038 -----------------------------------------------------------------------------------------------
2039 -- Added for Bug 2979944
2040
2041 FOR j IN c1(l_assignment_id) LOOP
2042
2043 l_overcommitment_flag := 'Y';
2044
2045 l_count := l_project_id_tab.COUNT + 1;
2046
2047 l_project_id_tab(l_count) := j.project_id;
2048
2049 END LOOP;
2050 -----------------------------------------------------------------------------------------------
2051
2052 pa_debug.debug(' Fetching conflicts records for assignments Id passed. ') ;
2053 x_err_stage := 'Fetching conflicts records for Assignment '||to_char(l_assignment_id) ;
2054
2055 IF p_archive_flag='Y' THEN
2056
2057 INSERT INTO PA_ASGMT_CNFLT_HIST_AR
2058 (PURGE_BATCH_ID,
2059 PURGE_RELEASE,
2060 PURGE_PROJECT_ID,
2061 CONFLICT_GROUP_ID,
2062 ASSIGNMENT_ID,
2063 CONFLICT_ASSIGNMENT_ID,
2064 RESOLVE_CONFLICTS_ACTION_CODE,
2065 INTRA_TXN_CONFLICT_FLAG,
2066 PROCESSED_FLAG,
2067 SELF_CONFLICT_FLAG,
2068 CREATION_DATE,
2069 CREATED_BY,
2070 LAST_UPDATE_DATE,
2071 LAST_UPDATED_BY,
2072 LAST_UPDATE_LOGIN)
2073
2074 SELECT P_purge_batch_id,
2075 P_purge_release,
2076 P_project_id,
2077 Conflict_Group_Id,
2078 Assignment_Id,
2079 Conflict_Assignment_Id,
2080 Resolve_Conflicts_Action_Code,
2081 Intra_Txn_Conflict_Flag,
2082 Processed_Flag,
2083 Self_Conflict_Flag,
2084 Creation_Date,
2085 Created_By,
2086 Last_Update_Date,
2087 Last_Updated_By,
2088 Last_Update_Login
2089 FROM PA_ASSIGNMENT_CONFLICT_HIST WHERE assignment_id = l_assignment_id;
2090
2091 /*Increase the value of l_nos_conflicts_inserted to indicate number of records inserted in assignment_conflict_hist
2092 table. The value will increase for each loop*/
2093
2094 l_nos_conflicts_inserted := l_nos_conflicts_inserted + SQL%ROWCOUNT;
2095
2096 END IF;
2097
2098 /*To keep the count of no of records deleted from pa_assignment_conflict_hist, manipulate the
2099 count of l_nos_conflicts. */
2100
2101 pa_debug.debug('Deleting Records from pa_assignment_conflict_hist table ') ;
2102 x_err_stage := 'Deleting Records from pa_assignment_conflict_hist table for id '||to_char(l_assignment_id) ;
2103
2104 DELETE PA_ASSIGNMENT_CONFLICT_HIST
2105 WHERE assignment_id =l_assignment_id;
2106
2107 l_nos_conflicts_deleted :=l_nos_conflicts_deleted + SQL%ROWCOUNT;
2108 END LOOP;
2109
2110 /*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in
2111 the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
2112 The procedure is called pa_schedules, pa_schedule_except_history and pa_schedules_history tables */
2113
2114 pa_purge.CommitProcess(p_purge_batch_id,
2115 p_project_id,
2116 'PA_ASSIGNMENT_CONFLICT_HIST',
2117 l_nos_conflicts_inserted,
2118 l_nos_conflicts_deleted,
2119 x_err_code,
2120 x_err_stack,
2121 x_err_stage);
2122
2123 -----------------Begin Logic added to purge data from Workflow related tables-----------------
2124
2125 IF l_overcommitment_flag = 'Y' THEN
2126
2127 /* Call Workflow API to archive/purge data from pa_wf_processes and detail table
2128 and also from pa_wf_ntf_performers table*/
2129
2130 Pa_Debug.DEBUG(' About to purge workflow process and details ') ;
2131 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
2132
2133 Pa_Purge_Pjr_Txns.pa_wf_purge ( p_purge_batch_id =>p_purge_batch_id,
2134 p_project_id =>p_project_id,
2135 p_purge_release =>p_purge_release,
2136 p_entity_key_tab =>l_project_id_tab,
2137 p_wf_type_code =>'OVERCOMMITMENT',
2138 p_item_type =>'PAROVCNT',
2139 p_archive_flag =>p_archive_flag,
2140 x_err_stack =>x_err_stack,
2141 x_err_stage =>x_err_stage,
2142 x_err_code =>x_err_code);
2143
2144 END IF;
2145
2146 /* Call Workflow API to archive/purge data from pa_wf_processes table*/
2147
2148 Pa_Debug.DEBUG(' About to purge workflow process ') ;
2149 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
2150
2151 Pa_Purge_Pjr_Txns.pa_wf_key_purge ( p_purge_batch_id =>p_purge_batch_id,
2152 p_project_id =>p_project_id,
2153 p_purge_release =>p_purge_release,
2154 p_entity_key2 =>'SELF_OVERCOMMITMENT',
2155 p_wf_type_code =>'OVERCOMMITMENT',
2156 p_item_type =>'PAROVCNT',
2157 p_archive_flag =>p_archive_flag,
2158 x_err_stack =>x_err_stack,
2159 x_err_stage =>x_err_stage,
2160 x_err_code =>x_err_code);
2161
2162 -----------------End of Logic added to purge data from Workflow related tables-----------------
2163
2164 x_err_stack := l_old_err_stack; -- Added for bug 4227589
2165 EXCEPTION
2166 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2167 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2168
2169 WHEN OTHERS THEN
2170 pa_debug.debug('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_ASSIGNMENT_CONFLICTS_PURGE' );
2171 pa_debug.debug('Error stage is '||x_err_stage );
2172 pa_debug.debug('Error stack is '||x_err_stack );
2173 pa_debug.debug(SQLERRM);
2174 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2175
2176 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2177
2178 end PA_ASSIGNMENT_CONFLICTS_PURGE;
2179
2180
2181
2182 -- Start of comments
2183 -- API name : PA_PROJECT_ASSIGNMENT_PURGE
2184 -- Type : Public
2185 -- Pre-reqs : None
2186 -- Function : Main purge procedure for Purging records from PA_PROJECT_ASSIGNMENTS table
2187
2188
2189 Procedure PA_PROJECT_ASSIGNMENT_PURGE ( p_purge_batch_id in NUMBER,
2190 p_project_id in NUMBER,
2191 p_purge_release in VARCHAR2,
2192 p_assignment_id_tab in PA_PLSQL_DATATYPES.IdTabTyp,
2193 p_archive_flag in VARCHAR2,
2194 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2195 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2196 x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
2197
2198
2199 l_assignment_id pa_forecast_items.assignment_id%TYPE;
2200 l_nos_assignments_inserted NUMBER ;
2201 l_nos_assignments_his_inserted NUMBER ;
2202 l_nos_assignments_deleted NUMBER ;
2203 l_nos_assignments_his_deleted NUMBER ;
2204 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
2205
2206 BEGIN
2207
2208 l_old_err_stack := x_err_stack; -- Added for bug 4227589
2209
2210 x_err_stack := x_err_stack || ' ->Before call to purge assignments records ';
2211
2212 /*Initialize the no of record variables for each call */
2213
2214 pa_debug.debug(' Inside Procedure to purge assignments records ') ;
2215 x_err_stage := 'Start purging assignments for project '||to_char(p_project_id) ;
2216
2217 l_nos_assignments_inserted :=0;
2218 l_nos_assignments_his_inserted :=0;
2219 l_nos_assignments_deleted :=0;
2220 l_nos_assignments_his_deleted :=0;
2221
2222 -----------------Begin Logic added to purge data from Workflow related tables-----------------
2223 /* Call Workflow API to archive/purge data from pa_wf_processes and detail table */
2224
2225 Pa_Debug.DEBUG(' About to purge workflow process and details ') ;
2226 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
2227
2228 Pa_Purge_Pjr_Txns.pa_wf_mass_purge ( p_purge_batch_id =>p_purge_batch_id,
2229 p_project_id =>p_project_id,
2230 p_purge_release =>p_purge_release,
2231 p_object_id_tab =>p_assignment_id_tab,
2232 p_item_type =>'PARMATRX',
2233 p_archive_flag =>p_archive_flag,
2234 x_err_stack =>x_err_stack,
2235 x_err_stage =>x_err_stage,
2236 x_err_code =>x_err_code);
2237
2238 /* Call Workflow API to archive/purge data from pa_wf_processes and detail table
2239 and also from pa_wf_ntf_performers table*/
2240
2241 Pa_Debug.DEBUG(' About to purge workflow process and details ') ;
2242 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
2243
2244 Pa_Purge_Pjr_Txns.pa_wf_purge ( p_purge_batch_id =>p_purge_batch_id,
2245 p_project_id =>p_project_id,
2246 p_purge_release =>p_purge_release,
2247 p_entity_key_tab =>p_assignment_id_tab,
2248 p_wf_type_code =>'ASSIGNMENT_APPROVAL',
2249 p_item_type =>'PAWFAAP',
2250 p_archive_flag =>p_archive_flag,
2251 x_err_stack =>x_err_stack,
2252 x_err_stage =>x_err_stage,
2253 x_err_code =>x_err_code);
2254
2255 /* Call Workflow API to archive/purge data from pa_wf_processes table*/
2256
2257 Pa_Debug.DEBUG(' About to purge workflow process and details ') ;
2258 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
2259
2260 Pa_Purge_Pjr_Txns.pa_wf_key_purge ( p_purge_batch_id =>p_purge_batch_id,
2261 p_project_id =>p_project_id,
2262 p_purge_release =>p_purge_release,
2263 p_entity_key2 =>'0',
2264 p_wf_type_code =>'APPLY_TEAM_TEMPLATE',
2265 p_item_type =>'PARAPTEM',
2266 p_archive_flag =>p_archive_flag,
2267 x_err_stack =>x_err_stack,
2268 x_err_stage =>x_err_stage,
2269 x_err_code =>x_err_code);
2270
2271 /* Call Workflow API to archive/purge data from pa_wf_processes table*/
2272
2273 Pa_Debug.DEBUG(' About to purge workflow process and details ') ;
2274 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
2275
2276 Pa_Purge_Pjr_Txns.pa_wf_mass_asgn_purge ( p_purge_batch_id =>p_purge_batch_id,
2277 p_project_id =>p_project_id,
2278 p_purge_release =>p_purge_release,
2279 p_object_id_tab =>p_assignment_id_tab,
2280 p_wf_type_code =>'MASS_ASSIGNMENT_APPROVAL',
2281 p_archive_flag =>p_archive_flag,
2282 x_err_stack =>x_err_stack,
2283 x_err_stage =>x_err_stage,
2284 x_err_code =>x_err_code);
2285
2286 -----------------End of Logic added to purge data from Workflow related tables-----------------
2287
2288 FOR I in p_assignment_id_tab.FIRST .. p_assignment_id_tab.LAST LOOP
2289 l_assignment_id :=p_assignment_id_tab(I);
2290
2291 IF p_archive_flag='Y' THEN
2292 pa_debug.debug('Inserting Records into pa_project_asgmts_AR table ') ;
2293 x_err_stage := 'Inserting Records into pa_project_asgmts_AR table for assignment id '||to_char(l_assignment_id) ;
2294
2295 INSERT INTO PA_PROJECT_ASGMTS_AR
2296 ( PURGE_BATCH_ID,
2297 PURGE_RELEASE,
2298 PURGE_PROJECT_ID,
2299 ASSIGNMENT_ID,
2300 ASSIGNMENT_NAME,
2301 ASSIGNMENT_TYPE,
2302 MULTIPLE_STATUS_FLAG,
2303 RECORD_VERSION_NUMBER,
2304 STATUS_CODE,
2305 PROJECT_ID,
2306 PROJECT_ROLE_ID,
2307 RESOURCE_ID,
2308 PROJECT_PARTY_ID,
2309 DESCRIPTION,
2310 START_DATE,
2311 END_DATE,
2312 ASSIGNMENT_EFFORT,
2313 EXTENSION_POSSIBLE,
2314 SOURCE_ASSIGNMENT_ID,
2315 ASSIGNMENT_TEMPLATE_ID,
2316 MIN_RESOURCE_JOB_LEVEL,
2317 MAX_RESOURCE_JOB_LEVEL,
2318 ASSIGNMENT_NUMBER,
2319 ADDITIONAL_INFORMATION,
2320 WORK_TYPE_ID,
2321 REVENUE_CURRENCY_CODE,
2322 REVENUE_BILL_RATE,
2323 EXPENSE_OWNER,
2324 EXPENSE_LIMIT,
2325 EXPENSE_LIMIT_CURRENCY_CODE,
2326 FCST_TP_AMOUNT_TYPE,
2327 FCST_JOB_ID,
2328 LOCATION_ID,
2329 CALENDAR_TYPE,
2330 CALENDAR_ID,
2331 RESOURCE_CALENDAR_PERCENT,
2332 ATTRIBUTE_CATEGORY,
2333 ATTRIBUTE1,
2334 ATTRIBUTE2,
2335 ATTRIBUTE3,
2336 ATTRIBUTE4,
2337 ATTRIBUTE5,
2338 ATTRIBUTE6,
2339 ATTRIBUTE7,
2340 ATTRIBUTE8,
2341 ATTRIBUTE9,
2342 ATTRIBUTE10,
2343 ATTRIBUTE11,
2344 ATTRIBUTE12,
2345 ATTRIBUTE13,
2346 ATTRIBUTE14,
2347 ATTRIBUTE15 ,
2348 CREATION_DATE,
2349 CREATED_BY,
2350 LAST_UPDATE_DATE,
2351 LAST_UPDATED_BY,
2352 LAST_UPDATE_LOGIN,
2353 PENDING_APPROVAL_FLAG,
2354 FCST_JOB_GROUP_ID,
2355 EXPENDITURE_ORG_ID,
2356 EXPENDITURE_ORGANIZATION_ID,
2357 EXPENDITURE_TYPE_CLASS,
2358 EXPENDITURE_TYPE,
2359 APPRVL_STATUS_CODE,
2360 NOTE_TO_APPROVER,
2361 STAFFING_PRIORITY_CODE,
2362 STAFFING_OWNER_PERSON_ID,
2363 TEMPLATE_FLAG,
2364 NO_OF_ACTIVE_CANDIDATES,
2365 MASS_WF_IN_PROGRESS_FLAG,
2366 BILL_RATE_OVERRIDE,
2367 BILL_RATE_CURR_OVERRIDE,
2368 MARKUP_PERCENT_OVERRIDE,
2369 TP_RATE_OVERRIDE,
2370 TP_CURRENCY_OVERRIDE,
2371 TP_CALC_BASE_CODE_OVERRIDE,
2372 TP_PERCENT_APPLIED_OVERRIDE,
2373 MARKUP_PERCENT,
2374 SEARCH_MIN_AVAILABILITY,
2375 SEARCH_COUNTRY_CODE,
2376 SEARCH_EXP_ORG_STRUCT_VER_ID,
2377 SEARCH_EXP_START_ORG_ID,
2378 SEARCH_MIN_CANDIDATE_SCORE,
2379 LAST_AUTO_SEARCH_DATE,
2380 ENABLE_AUTO_CAND_NOM_FLAG,
2381 COMPETENCE_MATCH_WEIGHTING,
2382 AVAILABILITY_MATCH_WEIGHTING,
2383 JOB_LEVEL_MATCH_WEIGHTING,
2384 DISCOUNT_PERCENTAGE, -- bug 2686889
2385 RATE_DISC_REASON_CODE,
2386 TRANSFER_PRICE_RATE, --Added for bug 3051110
2387 TRANSFER_PR_RATE_CURR,
2388 RESOURCE_LIST_MEMBER_ID)
2389
2390 SELECT P_purge_batch_id,
2391 P_purge_release,
2392 P_project_id,
2393 Assignment_Id,
2394 Assignment_Name,
2395 Assignment_Type,
2396 Multiple_Status_Flag,
2397 Record_Version_Number,
2398 Status_Code,
2399 Project_Id,
2400 Project_Role_Id,
2401 Resource_Id,
2402 Project_Party_Id,
2403 Description,
2404 Start_Date,
2405 End_Date,
2406 Assignment_Effort,
2407 Extension_Possible,
2408 Source_Assignment_Id,
2409 Assignment_Template_Id,
2410 Min_Resource_Job_Level,
2411 Max_Resource_Job_Level,
2412 Assignment_Number,
2413 Additional_Information,
2414 Work_Type_Id,
2415 Revenue_Currency_Code,
2416 Revenue_Bill_Rate,
2417 Expense_Owner,
2418 Expense_Limit,
2419 Expense_Limit_Currency_Code,
2420 Fcst_Tp_Amount_Type,
2421 Fcst_Job_Id,
2422 Location_Id,
2423 Calendar_Type,
2424 Calendar_Id,
2425 Resource_Calendar_Percent,
2426 Attribute_Category,
2427 Attribute1,
2428 Attribute2,
2429 Attribute3,
2430 Attribute4,
2431 Attribute5,
2432 Attribute6,
2433 Attribute7,
2434 Attribute8,
2435 Attribute9,
2436 Attribute10,
2437 Attribute11,
2438 Attribute12,
2439 Attribute13,
2440 Attribute14,
2441 Attribute15 ,
2442 Creation_Date,
2443 Created_By,
2444 Last_Update_Date,
2445 Last_Updated_By,
2446 Last_Update_Login,
2447 Pending_Approval_Flag,
2448 Fcst_Job_Group_Id,
2449 Expenditure_Org_Id,
2450 Expenditure_Organization_Id,
2451 Expenditure_Type_Class,
2452 Expenditure_Type,
2453 Apprvl_Status_Code,
2454 Note_To_Approver,
2455 Staffing_Priority_Code,
2456 Staffing_Owner_Person_Id,
2457 Template_Flag,
2458 No_Of_Active_Candidates,
2459 Mass_Wf_In_Progress_Flag,
2460 Bill_Rate_Override,
2461 Bill_Rate_Curr_Override,
2462 Markup_Percent_Override,
2463 Tp_Rate_Override,
2464 Tp_Currency_Override,
2465 Tp_Calc_Base_Code_Override,
2466 Tp_Percent_Applied_Override,
2467 Markup_Percent,
2468 Search_Min_Availability,
2469 Search_Country_Code,
2470 Search_Exp_Org_Struct_Ver_Id,
2471 Search_Exp_Start_Org_Id,
2472 Search_Min_Candidate_Score,
2473 Last_Auto_Search_Date,
2474 Enable_Auto_Cand_Nom_Flag,
2475 Competence_Match_Weighting,
2476 Availability_Match_Weighting,
2477 Job_Level_Match_Weighting,
2478 discount_percentage,
2479 rate_disc_reason_Code,
2480 Transfer_price_Rate, -- Added for bug 3051110
2481 Transfer_pr_rate_curr,
2482 Resource_List_Member_Id
2483 FROM pa_project_assignments where assignment_id=l_assignment_id;
2484
2485 /*Increase the value of l_nos_assignments_inserted to indicate number of records inserted in assignments_ar table.
2486 The value will increase for each loop(assignment id)*/
2487 l_nos_assignments_inserted := l_nos_assignments_inserted + SQL%ROWCOUNT;
2488
2489 pa_debug.debug('Inserting Records into pa_assignment_history ar table ') ;
2490
2491 INSERT INTO PA_ASGMTS_HSTRY_AR
2492 (PURGE_BATCH_ID,
2493 PURGE_RELEASE,
2494 PURGE_PROJECT_ID,
2495 ASSIGNMENT_ID,
2496 ASSIGNMENT_NAME,
2497 ASSIGNMENT_TYPE,
2498 MULTIPLE_STATUS_FLAG,
2499 RECORD_VERSION_NUMBER,
2500 CHANGE_ID,
2501 APPRVL_STATUS_CODE,
2502 STATUS_CODE,
2503 PROJECT_ID,
2504 PROJECT_ROLE_ID,
2505 RESOURCE_ID,
2506 PROJECT_PARTY_ID,
2507 DESCRIPTION,
2508 NOTE_TO_APPROVER,
2509 START_DATE,
2510 END_DATE,
2511 ASSIGNMENT_EFFORT,
2512 EXTENSION_POSSIBLE,
2513 SOURCE_ASSIGNMENT_ID,
2514 ASSIGNMENT_TEMPLATE_ID,
2515 MIN_RESOURCE_JOB_LEVEL,
2516 MAX_RESOURCE_JOB_LEVEL,
2517 ASSIGNMENT_NUMBER,
2518 ADDITIONAL_INFORMATION,
2519 WORK_TYPE_ID,
2520 REVENUE_CURRENCY_CODE,
2521 REVENUE_BILL_RATE,
2522 EXPENSE_OWNER,
2523 EXPENSE_LIMIT,
2524 EXPENSE_LIMIT_CURRENCY_CODE ,
2525 FCST_TP_AMOUNT_TYPE,
2526 FCST_JOB_ID,
2527 FCST_JOB_GROUP_ID,
2528 EXPENDITURE_ORG_ID,
2529 EXPENDITURE_ORGANIZATION_ID,
2530 EXPENDITURE_TYPE_CLASS,
2531 EXPENDITURE_TYPE,
2532 LOCATION_ID,
2533 CALENDAR_TYPE,
2534 CALENDAR_ID,
2535 RESOURCE_CALENDAR_PERCENT,
2536 PENDING_APPROVAL_FLAG,
2537 LAST_APPROVED_FLAG,
2538 ATTRIBUTE_CATEGORY,
2539 ATTRIBUTE1,
2540 ATTRIBUTE2,
2541 ATTRIBUTE3,
2542 ATTRIBUTE4,
2543 ATTRIBUTE5,
2544 ATTRIBUTE6,
2545 ATTRIBUTE7,
2546 ATTRIBUTE8,
2547 ATTRIBUTE9,
2548 ATTRIBUTE10,
2549 ATTRIBUTE11,
2550 ATTRIBUTE12,
2551 ATTRIBUTE13,
2552 ATTRIBUTE14,
2553 ATTRIBUTE15,
2554 CREATION_DATE,
2555 CREATED_BY,
2556 LAST_UPDATE_DATE,
2557 LAST_UPDATED_BY,
2558 LAST_UPDATE_LOGIN,
2559 STAFFING_PRIORITY_CODE,
2560 STAFFING_OWNER_PERSON_ID,
2561 PROJECT_SUBTEAM_ID,
2562 NO_OF_ACTIVE_CANDIDATES,
2563 TEMPLATE_FLAG,
2564 COMPETENCE_MATCH_WEIGHTING,
2565 AVAILABILITY_MATCH_WEIGHTING,
2566 JOB_LEVEL_MATCH_WEIGHTING,
2567 SEARCH_MIN_AVAILABILITY,
2568 SEARCH_COUNTRY_CODE,
2569 SEARCH_EXP_ORG_STRUCT_VER_ID,
2570 SEARCH_EXP_START_ORG_ID,
2571 SEARCH_MIN_CANDIDATE_SCORE,
2572 LAST_AUTO_SEARCH_DATE,
2573 ENABLE_AUTO_CAND_NOM_FLAG,
2574 MASS_WF_IN_PROGRESS_FLAG,
2575 BILL_RATE_OVERRIDE,
2576 BILL_RATE_CURR_OVERRIDE,
2577 MARKUP_PERCENT_OVERRIDE,
2578 TP_RATE_OVERRIDE,
2579 TP_CURRENCY_OVERRIDE,
2580 TP_CALC_BASE_CODE_OVERRIDE,
2581 TP_PERCENT_APPLIED_OVERRIDE,
2582 MARKUP_PERCENT,
2583 TRANSFER_PRICE_RATE, -- Added for bug 3051110
2584 TRANSFER_PR_RATE_CURR,
2585 DISCOUNT_PERCENTAGE, -- Added for bug 3041583
2586 RATE_DISC_REASON_CODE) -- Added for bug 3041583
2587 SELECT P_purge_batch_id,
2588 p_purge_release,
2589 p_project_id,
2590 Assignment_Id,
2591 Assignment_Name,
2592 Assignment_Type,
2593 Multiple_Status_Flag,
2594 Record_Version_Number,
2595 Change_Id,
2596 Apprvl_Status_Code,
2597 Status_Code,
2598 Project_Id,
2599 Project_Role_Id,
2600 Resource_Id,
2601 Project_Party_Id,
2602 Description,
2603 Note_To_Approver,
2604 Start_Date,
2605 End_Date,
2606 Assignment_Effort,
2607 Extension_Possible,
2608 Source_Assignment_Id,
2609 Assignment_Template_Id,
2610 Min_Resource_Job_Level,
2611 Max_Resource_Job_Level,
2612 Assignment_Number,
2613 Additional_Information,
2614 Work_Type_Id,
2615 Revenue_Currency_Code,
2616 Revenue_Bill_Rate,
2617 Expense_Owner,
2618 Expense_Limit,
2619 Expense_Limit_Currency_Code ,
2620 Fcst_Tp_Amount_Type,
2621 Fcst_Job_Id,
2622 Fcst_Job_Group_Id,
2623 Expenditure_Org_Id,
2624 Expenditure_Organization_Id,
2625 Expenditure_Type_Class,
2626 Expenditure_Type,
2627 Location_Id,
2628 Calendar_Type,
2629 Calendar_Id,
2630 Resource_Calendar_Percent,
2631 Pending_Approval_Flag,
2632 Last_Approved_Flag,
2633 Attribute_Category,
2634 Attribute1,
2635 Attribute2,
2636 Attribute3,
2637 Attribute4,
2638 Attribute5,
2639 Attribute6,
2640 Attribute7,
2641 Attribute8,
2642 Attribute9,
2643 Attribute10,
2644 Attribute11,
2645 Attribute12,
2646 Attribute13,
2647 Attribute14,
2648 Attribute15,
2649 Creation_Date,
2650 Created_By,
2651 Last_Update_Date,
2652 Last_Updated_By,
2653 Last_Update_Login,
2654 Staffing_Priority_Code,
2655 Staffing_Owner_Person_Id,
2656 Project_Subteam_Id,
2657 No_Of_Active_Candidates,
2658 Template_Flag,
2659 Competence_Match_Weighting,
2660 Availability_Match_Weighting,
2661 Job_Level_Match_Weighting,
2662 Search_Min_Availability,
2663 Search_Country_Code,
2664 Search_Exp_Org_Struct_Ver_Id,
2665 Search_Exp_Start_Org_Id,
2666 Search_Min_Candidate_Score,
2667 Last_Auto_Search_Date,
2668 Enable_Auto_Cand_Nom_Flag,
2669 Mass_Wf_In_Progress_Flag,
2670 Bill_Rate_Override,
2671 Bill_Rate_Curr_Override,
2672 Markup_Percent_Override,
2673 Tp_Rate_Override,
2674 Tp_Currency_Override,
2675 Tp_Calc_Base_Code_Override,
2676 Tp_Percent_Applied_Override,
2677 Markup_Percent,
2678 Transfer_price_rate, -- Added for bug 3051110
2679 Transfer_pr_rate_curr,
2680 discount_percentage, -- Added for bug 3041583
2681 rate_disc_reason_code -- Added for bug 3041583
2682 From pa_assignments_history where assignment_id=l_assignment_id;
2683
2684 /*Increase the value of l_assignments_his_inserted to indicate number of records inserted in assignments_history_ar
2685 table. The value will increase for each loop*/
2686
2687 l_nos_assignments_his_inserted := l_nos_assignments_his_inserted + SQL%ROWCOUNT;
2688
2689 END IF;
2690
2691 /*To keep the count of no of records deleted from pa_project_assignments and pa_assignments_history, manipulate the
2692 count of l_nos_assignments_deleted and l_nos_assignments_his_deleted. */
2693
2694 pa_debug.debug('Deleting Records from pa_assignments_history table ') ;
2695 x_err_stage := 'Deleting Records from pa_assignments_history table for id '||to_char(l_assignment_id) ;
2696
2697 DELETE PA_ASSIGNMENTS_HISTORY
2698 WHERE assignment_id =l_assignment_id;
2699
2700 l_nos_assignments_his_deleted :=l_nos_assignments_his_deleted + SQL%ROWCOUNT;
2701
2702 pa_debug.debug('Deleting Records from pa_assignments table ') ;
2703 x_err_stage := 'Deleting Records from pa_assignments table for id '||to_char(l_assignment_id) ;
2704
2705 DELETE PA_PROJECT_ASSIGNMENTS
2706 WHERE assignment_id =l_assignment_id;
2707
2708 l_nos_assignments_deleted :=l_nos_assignments_deleted + SQL%ROWCOUNT;
2709
2710 END LOOP;
2711
2712
2713 pa_purge.CommitProcess(p_purge_batch_id,
2714 p_project_id,
2715 'PA_PROJECT_ASSIGNMENTS',
2716 l_nos_assignments_inserted,
2717 l_nos_assignments_deleted,
2718 x_err_code,
2719 x_err_stack,
2720 x_err_stage
2721 ) ;
2722
2723 pa_purge.CommitProcess(p_purge_batch_id,
2724 p_project_id,
2725 'PA_ASSIGNMENTS_HISTORY',
2726 l_nos_assignments_his_inserted,
2727 l_nos_assignments_his_deleted,
2728 x_err_code,
2729 x_err_stack,
2730 x_err_stage
2731 ) ;
2732
2733 x_err_stack := l_old_err_stack; -- Added for bug 4227589
2734
2735 EXCEPTION
2736 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2737 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2738
2739 WHEN OTHERS THEN
2740 pa_debug.debug('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_PROJECT_ASSIGNMENT_PURGE' );
2741 pa_debug.debug('Error stage is '||x_err_stage );
2742 pa_debug.debug('Error stack is '||x_err_stack );
2743 pa_debug.debug(SQLERRM);
2744 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2745
2746 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2747
2748
2749 END PA_PROJECT_ASSIGNMENT_PURGE ;
2750
2751
2752
2753 -- Start of comments
2754 -- API name : PA_PROJECT_PARTIES_PURGE
2755 -- Type : Public
2756 -- Pre-reqs : None
2757 -- Function : Main purge procedure for Purging records from PA_PROJECT_PARTIES table
2758
2759
2760 Procedure PA_PROJECT_PARTIES_PURGE ( p_purge_batch_id in NUMBER,
2761 p_project_id in NUMBER,
2762 p_purge_release in VARCHAR2,
2763 p_assignment_id_tab in PA_PLSQL_DATATYPES.IdTabTyp,
2764 p_archive_flag in VARCHAR2,
2765 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2766 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2767 x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
2768
2769 l_assignment_id pa_forecast_items.assignment_id%TYPE;
2770 l_nos_parties_inserted NUMBER ;
2771 l_nos_parties_deleted NUMBER ;
2772 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
2773
2774 Begin
2775
2776 l_old_err_stack := x_err_stack; -- Added for bug 4227589
2777 x_err_stack := x_err_stack || ' ->Before call to purge Project Parties records ';
2778
2779 /*Initialize the no of record variables for each call */
2780
2781 pa_debug.debug(' Inside Procedure to purge project parties records ') ;
2782 x_err_stage := 'Start purging project parties records for project '||to_char(p_project_id) ;
2783
2784 l_nos_parties_inserted:=0;
2785 l_nos_parties_deleted :=0 ;
2786
2787 FOR I in p_assignment_id_tab.FIRST .. p_assignment_id_tab.LAST LOOP
2788 l_assignment_id :=p_assignment_id_tab(I);
2789
2790 IF p_archive_flag='Y' THEN
2791 pa_debug.debug('Inserting Records into PA_project_parties_AR table ') ;
2792 x_err_stage := 'Inserting Records into pa_project_parties_AR table for assignment id '||to_char(l_assignment_id);
2793
2794 INSERT INTO PA_PROJECT_PARTIES_AR
2795 (PURGE_BATCH_ID,
2796 PURGE_RELEASE,
2797 PURGE_PROJECT_ID,
2798 PROJECT_PARTY_ID,
2799 OBJECT_ID,
2800 OBJECT_TYPE,
2801 PROJECT_ID,
2802 RESOURCE_ID,
2803 RESOURCE_TYPE_ID,
2804 RESOURCE_SOURCE_ID,
2805 PROJECT_ROLE_ID,
2806 START_DATE_ACTIVE,
2807 END_DATE_ACTIVE,
2808 SCHEDULED_FLAG,
2809 RECORD_VERSION_NUMBER,
2810 CREATION_DATE,
2811 CREATED_BY,
2812 LAST_UPDATE_DATE,
2813 LAST_UPDATED_BY,
2814 LAST_UPDATE_LOGIN,
2815 GRANT_ID)
2816
2817 SELECT p_purge_batch_id,
2818 p_purge_release,
2819 p_project_id,
2820 Project_Party_Id,
2821 Object_Id,
2822 Object_Type,
2823 Project_Id,
2824 Resource_Id,
2825 Resource_Type_Id,
2826 Resource_Source_Id,
2827 Project_Role_Id,
2828 Start_Date_Active,
2829 End_Date_Active,
2830 Scheduled_Flag,
2831 Record_Version_Number,
2832 Creation_Date,
2833 Created_By,
2834 Last_Update_Date,
2835 Last_Updated_By,
2836 Last_Update_Login,
2837 Grant_Id
2838 FROM PA_PROJECT_PARTIES WHERE project_party_id = (SELECT project_party_id from pa_project_assignments
2839 WHERE assignment_id=l_assignment_id);
2840
2841 /*Increase the value of l_assignments_his_inserted to indicate number of records inserted in assignments_history_ar
2842 table. The value will increase for each loop*/
2843
2844 l_nos_parties_inserted := l_nos_parties_inserted + SQL%ROWCOUNT;
2845
2846 END IF;
2847
2848 /*To keep the count of no of records deleted from pa_project_parties, manipulate the
2849 count of l_nos_parties_deleted */
2850
2851 pa_debug.debug('Deleting Records from pa_project_parties table ') ;
2852 x_err_stage := 'Deleting Records from pa_project_parties table for id '||to_char(l_assignment_id) ;
2853
2854 DELETE PA_PROJECT_PARTIES
2855 WHERE project_party_id =(SELECT project_party_id FROM pa_project_assignments
2856 WHERE assignment_id=l_assignment_id);
2857
2858 l_nos_parties_deleted :=l_nos_parties_deleted + SQL%ROWCOUNT;
2859
2860 END LOOP;
2861
2862 pa_purge.CommitProcess(p_purge_batch_id,
2863 p_project_id,
2864 'PA_PROJECT_PARTIES',
2865 l_nos_parties_inserted,
2866 l_nos_parties_deleted,
2867 x_err_code,
2868 x_err_stack,
2869 x_err_stage
2870 ) ;
2871
2872 x_err_stack := l_old_err_stack; -- Added for bug 4227589
2873
2874 EXCEPTION
2875 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
2876 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2877
2878 WHEN OTHERS THEN
2879 pa_debug.debug('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_PROJECT_PARTIES_PURGE' );
2880 pa_debug.debug('Error stage is '||x_err_stage );
2881 pa_debug.debug('Error stack is '||x_err_stack );
2882 pa_debug.debug(SQLERRM);
2883 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
2884
2885 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
2886
2887 End Pa_Project_Parties_Purge;
2888
2889
2890 -- Start of comments
2891 -- API name : PA_ADVERTISEMENTS_PURGE
2892 -- Type : Public
2893 -- Pre-reqs : None
2894 -- Function : Main purge procedure for Purging records from Advertisements related tables:
2895 -- PA_ACTION_SETS,
2896 -- PA_ACTION_SET_LINES,
2897 -- PA_ACTION_SET_LINE_COND
2898 -- PA_ACTION_SET_LINE_AUD
2899
2900
2901 Procedure PA_ADVERTISEMENTS_PURGE ( p_purge_batch_id in NUMBER,
2902 p_project_id in NUMBER,
2903 p_purge_release in VARCHAR2,
2904 p_assignment_id_tab in PA_PLSQL_DATATYPES.IdTabTyp,
2905 p_archive_flag in VARCHAR2,
2906 x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2907 x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2908 x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
2909
2910 l_assignment_id pa_forecast_items.assignment_id%TYPE;
2911 l_action_set_id pa_action_sets.action_set_id%TYPE;
2912 l_nos_action_sets_inserted NUMBER;
2913 l_nos_set_lines_inserted NUMBER;
2914 l_nos_lines_cond_inserted NUMBER;
2915 l_nos_lines_aud_inserted NUMBER;
2916 l_nos_action_sets_deleted NUMBER;
2917 l_nos_set_lines_deleted NUMBER;
2918 l_nos_lines_cond_deleted NUMBER;
2919 l_nos_lines_aud_deleted NUMBER;
2920
2921 CURSOR Cur_action_sets (x_assignment_id IN pa_project_assignments.assignment_id%TYPE) IS
2922 SELECT action_set_id From Pa_action_sets
2923 WHERE action_set_type_code = 'ADVERTISEMENT'
2924 AND object_type ='OPEN_ASSIGNMENT'
2925 AND object_id=x_assignment_id;
2926
2927 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
2928 Begin
2929
2930 l_old_err_stack := x_err_stack; -- Added for bug 4227589
2931 x_err_stack := x_err_stack || ' ->Before call to purge Requirement advertisements records ';
2932
2933 /*Initialize the no of record variables for each call */
2934
2935 pa_debug.debug(' Inside Procedure to purge requirement Advertisements records ') ;
2936 x_err_stage := 'Start purging requirement Advertisements records for project '||to_char(p_project_id) ;
2937
2938 l_nos_action_sets_inserted :=0;
2939 l_nos_set_lines_inserted :=0;
2940 l_nos_lines_cond_inserted :=0;
2941 l_nos_lines_aud_inserted :=0;
2942 l_nos_action_sets_deleted :=0;
2943 l_nos_set_lines_deleted :=0;
2944 l_nos_lines_cond_deleted :=0;
2945 l_nos_lines_aud_deleted :=0;
2946
2947 -----------------Begin Logic added to purge data from Workflow related tables-----------------
2948 /* Call Workflow API to archive/purge data from pa_wf_processes table*/
2949
2950 Pa_Debug.DEBUG(' About to purge workflow process ') ;
2951 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
2952
2953 Pa_Purge_Pjr_Txns.pa_wf_key_purge ( p_purge_batch_id =>p_purge_batch_id,
2954 p_project_id =>p_project_id,
2955 p_purge_release =>p_purge_release,
2956 p_entity_key2 => '-999',
2957 p_wf_type_code =>'ADVERTISEMENTS_NTF_WF',
2958 p_item_type =>'PARADVWF',
2959 p_archive_flag =>p_archive_flag,
2960 x_err_stack =>x_err_stack,
2961 x_err_stage =>x_err_stage,
2962 x_err_code =>x_err_code);
2963
2964 /* Call Workflow API to archive/purge data from pa_wf_processes and detail table
2965 and also from pa_wf_ntf_performers table*/
2966
2967 Pa_Debug.DEBUG(' About to purge workflow process and details ') ;
2968 x_err_stage := 'About to purge workflow data for project '||TO_CHAR(p_project_id) ;
2969
2970 Pa_Purge_Pjr_Txns.pa_wf_purge ( p_purge_batch_id =>p_purge_batch_id,
2971 p_project_id =>p_project_id,
2972 p_purge_release =>p_purge_release,
2973 p_entity_key_tab =>p_assignment_id_tab,
2974 p_wf_type_code =>'ADVERTISEMENTS_NTF_WF',
2975 p_item_type =>'PARADVWF',
2976 p_archive_flag =>p_archive_flag,
2977 x_err_stack =>x_err_stack,
2978 x_err_stage =>x_err_stage,
2979 x_err_code =>x_err_code);
2980
2981 -----------------End of Logic added to purge data from Workflow related tables-----------------
2982
2983 FOR I in p_assignment_id_tab.FIRST .. p_assignment_id_tab.LAST LOOP
2984 l_assignment_id :=p_assignment_id_tab(I);
2985
2986 OPEN cur_action_sets(l_assignment_id);
2987 LOOP
2988 FETCH cur_action_sets INTO l_action_set_id;
2989 IF cur_action_sets%NOTFOUND THEN
2990 EXIT;
2991 END IF;
2992
2993
2994 IF p_archive_flag='Y' THEN
2995 pa_debug.debug('Inserting Records into PA_ACTION_SETS_AR table ') ;
2996 x_err_stage := 'Inserting Records into P_ACTION_SETS_AR table for assignment id '||to_char(l_assignment_id);
2997
2998 INSERT INTO PA_ACTION_SETS_AR
2999 (PURGE_BATCH_ID,
3000 PURGE_RELEASE,
3001 PURGE_PROJECT_ID,
3002 ACTION_SET_ID,
3003 ACTION_SET_NAME,
3004 ACTION_SET_TYPE_CODE,
3005 OBJECT_TYPE,
3006 OBJECT_ID,
3007 START_DATE_ACTIVE,
3008 END_DATE_ACTIVE,
3009 ACTUAL_START_DATE,
3010 DESCRIPTION,
3011 RECORD_VERSION_NUMBER,
3012 SOURCE_ACTION_SET_ID,
3013 STATUS_CODE,
3014 ACTION_SET_TEMPLATE_FLAG,
3015 MOD_SOURCE_ACTION_SET_FLAG,
3016 ATTRIBUTE_CATEGORY,
3017 ATTRIBUTE1,
3018 ATTRIBUTE2,
3019 ATTRIBUTE3,
3020 ATTRIBUTE4,
3021 ATTRIBUTE5,
3022 ATTRIBUTE6,
3023 ATTRIBUTE7,
3024 ATTRIBUTE8,
3025 ATTRIBUTE9,
3026 ATTRIBUTE10,
3027 ATTRIBUTE11,
3028 ATTRIBUTE12,
3029 ATTRIBUTE13,
3030 ATTRIBUTE14,
3031 ATTRIBUTE15,
3032 CREATED_BY,
3033 CREATION_DATE,
3034 LAST_UPDATE_DATE,
3035 LAST_UPDATED_BY,
3036 LAST_UPDATE_LOGIN)
3037
3038 SELECT p_purge_batch_id,
3039 p_purge_release,
3040 p_project_id,
3041 Action_Set_Id,
3042 Action_Set_Name,
3043 Action_Set_Type_Code,
3044 Object_Type,
3045 Object_Id,
3046 Start_Date_Active,
3047 End_Date_Active,
3048 Actual_Start_Date,
3049 Description,
3050 Record_Version_Number,
3051 Source_Action_Set_Id,
3052 Status_Code,
3053 Action_Set_Template_Flag,
3054 Mod_Source_Action_Set_Flag,
3055 Attribute_Category,
3056 Attribute1,
3057 Attribute2,
3058 Attribute3,
3059 Attribute4,
3060 Attribute5,
3061 Attribute6,
3062 Attribute7,
3063 Attribute8,
3064 Attribute9,
3065 Attribute10,
3066 Attribute11,
3067 Attribute12,
3068 Attribute13,
3069 Attribute14,
3070 Attribute15,
3071 Created_By,
3072 Creation_Date,
3073 Last_Update_Date,
3074 Last_Updated_By,
3075 Last_Update_Login
3076 From pa_action_sets where action_set_id=l_action_set_id;
3077
3078 /*Increase the value of l_nos_action_sets_inserted to indicate number of records inserted in action_sets_ar table.
3079 The value will increase for each loop*/
3080
3081 l_nos_action_sets_inserted := l_nos_action_sets_inserted + SQL%ROWCOUNT;
3082
3083 pa_debug.debug('Inserting Records into pa_action_set_lines_AR table ') ;
3084 x_err_stage := 'Inserting Records into pa_action_setl_lines_AR table for action set id '||to_char(l_action_set_id) ;
3085
3086
3087 INSERT INTO PA_ACTION_SET_LINES_AR
3088 ( PURGE_BATCH_ID,
3089 PURGE_RELEASE,
3090 PURGE_PROJECT_ID,
3091 ACTION_SET_LINE_ID,
3092 ACTION_SET_ID,
3093 ACTION_SET_LINE_NUMBER,
3094 STATUS_CODE,
3095 DESCRIPTION,
3096 RECORD_VERSION_NUMBER,
3097 LINE_DELETED_FLAG,
3098 ACTION_CODE,
3099 ACTION_ATTRIBUTE1,
3100 ACTION_ATTRIBUTE2,
3101 ACTION_ATTRIBUTE3,
3102 ACTION_ATTRIBUTE4,
3103 ACTION_ATTRIBUTE5,
3104 ACTION_ATTRIBUTE6,
3105 ACTION_ATTRIBUTE7,
3106 ACTION_ATTRIBUTE8,
3107 ACTION_ATTRIBUTE9,
3108 ACTION_ATTRIBUTE10,
3109 CREATION_DATE,
3110 CREATED_BY,
3111 LAST_UPDATE_DATE,
3112 LAST_UPDATED_BY,
3113 LAST_UPDATE_LOGIN)
3114
3115 SELECT P_purge_batch_id,
3116 P_purge_release,
3117 P_project_id,
3118 Action_Set_Line_Id,
3119 Action_Set_Id,
3120 Action_Set_Line_Number,
3121 Status_Code,
3122 Description,
3123 Record_Version_Number,
3124 Line_Deleted_Flag,
3125 Action_Code,
3126 Action_Attribute1,
3127 Action_Attribute2,
3128 Action_Attribute3,
3129 Action_Attribute4,
3130 Action_Attribute5,
3131 Action_Attribute6,
3132 Action_Attribute7,
3133 Action_Attribute8,
3134 Action_Attribute9,
3135 Action_Attribute10,
3136 Creation_Date,
3137 Created_By,
3138 Last_Update_Date,
3139 Last_Updated_By,
3140 Last_Update_Login
3141 FROM Pa_Action_Set_Lines WHERE action_set_id=l_action_set_id;
3142
3143 /*Increase the value of l_nos_set_lines_inserted to indicate number of records inserted in action_set_lines_ar table.
3144 The value will increase for each loop*/
3145
3146 l_nos_set_lines_inserted := l_nos_set_lines_inserted + SQL%ROWCOUNT;
3147
3148 pa_debug.debug('Inserting Records into pa_action_set_line_cond_AR table ') ;
3149 x_err_stage := 'Inserting Records into pa_action_set_line__cond_AR table for lines of action set id '||to_char(l_action_set_id) ;
3150
3151 INSERT INTO PA_ACTN_SET_LN_COND_AR
3152 ( PURGE_BATCH_ID,
3153 PURGE_RELEASE,
3154 PURGE_PROJECT_ID,
3155 ACTION_SET_LINE_ID,
3156 ACTION_SET_LINE_CONDITION_ID,
3157 DESCRIPTION,
3158 CONDITION_DATE,
3159 CONDITION_CODE,
3160 CONDITION_ATTRIBUTE1,
3161 CONDITION_ATTRIBUTE2,
3162 CONDITION_ATTRIBUTE3,
3163 CONDITION_ATTRIBUTE4,
3164 CONDITION_ATTRIBUTE5,
3165 CONDITION_ATTRIBUTE6,
3166 CONDITION_ATTRIBUTE7,
3167 CONDITION_ATTRIBUTE8,
3168 CONDITION_ATTRIBUTE9,
3169 CONDITION_ATTRIBUTE10,
3170 CREATION_DATE,
3171 CREATED_BY,
3172 LAST_UPDATE_DATE,
3173 LAST_UPDATED_BY,
3174 LAST_UPDATE_LOGIN)
3175
3176 SELECT P_purge_batch_id,
3177 P_purge_release,
3178 P_project_id,
3179 Action_Set_Line_Id,
3180 Action_Set_Line_Condition_Id,
3181 Description,
3182 Condition_Date,
3183 Condition_Code,
3184 Condition_Attribute1,
3185 Condition_Attribute2,
3186 Condition_Attribute3,
3187 Condition_Attribute4,
3188 Condition_Attribute5,
3189 Condition_Attribute6,
3190 Condition_Attribute7,
3191 Condition_Attribute8,
3192 Condition_Attribute9,
3193 Condition_Attribute10,
3194 Creation_Date,
3195 Created_By,
3196 Last_Update_Date,
3197 Last_Updated_By,
3198 Last_Update_Login
3199 FROM PA_ACTION_SET_LINE_COND WHERE action_set_line_id IN (SELECT action_set_line_id
3200 From pa_action_set_lines where action_set_id=l_action_set_id);
3201
3202 /*Increase the value of l_l_nos_lines_cond_inserted to indicate number of records inserted in action_set_lines_acond_ar table.
3203 The value will increase for each loop*/
3204
3205 l_nos_lines_cond_inserted := l_nos_lines_cond_inserted + SQL%ROWCOUNT;
3206
3207 pa_debug.debug('Inserting Records into pa_action_set_lines_audit table ') ;
3208 x_err_stage := 'Inserting Records into pa_action_set_lines_audit table for lines of action set id '||to_char(l_action_set_id) ;
3209
3210 INSERT INTO PA_ACTN_SETLN_AUD_AR
3211 ( PURGE_BATCH_ID,
3212 PURGE_RELEASE,
3213 PURGE_PROJECT_ID,
3214 ACTION_SET_LINE_ID,
3215 OBJECT_TYPE,
3216 OBJECT_ID,
3217 ACTION_SET_TYPE_CODE,
3218 STATUS_CODE,
3219 REASON_CODE,
3220 ACTION_CODE,
3221 AUDIT_DISPLAY_ATTRIBUTE,
3222 AUDIT_ATTRIBUTE,
3223 ACTION_DATE,
3224 ACTIVE_FLAG,
3225 REVERSED_ACTION_SET_LINE_ID,
3226 OBJECT_NAME,
3227 ENCODED_ERROR_MESSAGE,
3228 CREATION_DATE,
3229 CREATED_BY,
3230 LAST_UPDATE_DATE,
3231 LAST_UPDATED_BY,
3232 LAST_UPDATE_LOGIN,
3233 REQUEST_ID,
3234 PROGRAM_APPLICATION_ID,
3235 PROGRAM_ID,
3236 PROGRAM_UPDATE_DATE)
3237
3238 SELECT P_purge_batch_id,
3239 P_purge_release,
3240 P_project_id,
3241 Action_Set_Line_Id,
3242 Object_Type,
3243 Object_Id,
3244 Action_Set_Type_Code,
3245 Status_Code,
3246 Reason_Code,
3247 Action_Code,
3248 Audit_Display_Attribute,
3249 Audit_Attribute,
3250 Action_Date,
3251 Active_Flag,
3252 Reversed_Action_Set_Line_Id,
3253 Object_Name,
3254 Encoded_Error_Message,
3255 Creation_Date,
3256 Created_By,
3257 Last_Update_Date,
3258 Last_Updated_By,
3259 Last_Update_Login,
3260 Request_Id,
3261 Program_Application_Id,
3262 Program_Id,
3263 Program_Update_Date
3264 From PA_ACTION_SET_LINE_AUD WHERE action_set_line_ID IN (SELECT action_set_line_id From pa_action_set_lines
3265 WHERE action_set_id=l_action_set_id);
3266
3267
3268 /*Increase the value of l_nos_lines_aud_inserted to indicate number of records inserted in action_set_lines_aud_ar
3269 table. The value will increase for each loop*/
3270
3271 l_nos_lines_aud_inserted := l_nos_lines_aud_inserted + SQL%ROWCOUNT;
3272
3273 END IF;
3274
3275 /*To keep the count of no of records deleted from adcertisement related tables, manipulate the
3276 count of variables: */
3277
3278 pa_debug.debug('Deleting Records from pa_action_set_lines_aud table ') ;
3279 x_err_stage := 'Deleting Records from pa_action_set_lines_aud for id for lines of action set id'||to_char(l_action_set_id) ;
3280
3281 DELETE PA_ACTION_SET_LINE_AUD
3282 WHERE action_set_line_ID IN (SELECT action_set_line_id From pa_action_set_lines
3283 WHERE action_set_id=l_action_set_id);
3284
3285 l_nos_lines_aud_deleted := l_nos_lines_aud_deleted + SQL%ROWCOUNT;
3286
3287 pa_debug.debug('Deleting Records from PA_ACTION_SET_LINE_COND table ') ;
3288 x_err_stage := 'Deleting Records from PA_ACTION_SET_LINE_COND for id for lines of action set id'||to_char(l_action_set_id) ;
3289
3290 DELETE PA_ACTION_SET_LINE_COND
3291 WHERE action_set_line_ID IN (SELECT action_set_line_id From pa_action_set_lines
3292 WHERE action_set_id=l_action_set_id);
3293
3294 l_nos_lines_cond_deleted := l_nos_lines_cond_deleted + SQL%ROWCOUNT;
3295
3296 pa_debug.debug('Deleting Records from PA_ACTION_SET_LINES table ') ;
3297 x_err_stage := 'Deleting Records from PA_ACTION_SET_LINES for id for lines of action set id'||to_char(l_action_set_id) ;
3298
3299 DELETE PA_ACTION_SET_LINES
3300 WHERE action_set_id=l_action_set_id;
3301
3302 l_nos_set_lines_deleted := l_nos_set_lines_deleted + SQL%ROWCOUNT;
3303
3304 pa_debug.debug('Deleting Records from PA_ACTION_SETS table ') ;
3305 x_err_stage := 'Deleting Records from PA_ACTION_SETS for id for lines of action set id'||to_char(l_action_set_id) ;
3306
3307 DELETE PA_ACTION_SETS
3308 WHERE action_set_id=l_action_set_id;
3309
3310 l_nos_action_sets_deleted := l_nos_action_sets_deleted + SQL%ROWCOUNT;
3311
3312
3313
3314 END LOOP;
3315 CLOSE Cur_Action_sets;
3316
3317 END LOOP;
3318
3319 pa_purge.CommitProcess(p_purge_batch_id,
3320 p_project_id,
3321 'PA_ACTION_SETS',
3322 l_nos_action_sets_inserted,
3323 l_nos_action_sets_deleted,
3324 x_err_code,
3325 x_err_stack,
3326 x_err_stage
3327 ) ;
3328
3329 pa_purge.CommitProcess(p_purge_batch_id,
3330 p_project_id,
3331 'PA_ACTION_SET_LINES',
3332 l_nos_set_lines_inserted,
3333 l_nos_set_lines_deleted,
3334 x_err_code,
3335 x_err_stack,
3336 x_err_stage
3337 ) ;
3338
3339 pa_purge.CommitProcess(p_purge_batch_id,
3340 p_project_id,
3341 /* 'PA_ACTN_SET_LN_COND_AR', bug 2480653 */
3342 'PA_ACTION_SET_LINE_COND',
3343 l_nos_lines_cond_inserted,
3344 l_nos_lines_cond_deleted,
3345 x_err_code,
3346 x_err_stack,
3347 x_err_stage
3348 ) ;
3349
3350 pa_purge.CommitProcess(p_purge_batch_id,
3351 p_project_id,
3352 /* 'PA_ACTN_SETLN_AUD_AR', bug 2480653 */
3353 'PA_ACTION_SET_LINE_AUD',
3354 l_nos_lines_aud_inserted,
3355 l_nos_lines_aud_deleted,
3356 x_err_code,
3357 x_err_stack,
3358 x_err_stage
3359 ) ;
3360
3361 x_err_stack := l_old_err_stack; -- Added for bug 4227589
3362
3363 EXCEPTION
3364 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
3365 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3366
3367 WHEN OTHERS THEN
3368 pa_debug.debug('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_ADVERTISEMENTS_PURGE' );
3369 pa_debug.debug('Error stage is '||x_err_stage );
3370 pa_debug.debug('Error stack is '||x_err_stack );
3371 pa_debug.debug(SQLERRM);
3372 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
3373
3374 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
3375
3376
3377
3378
3379 End Pa_Advertisements_purge;
3380
3381
3382 -- Start of comments
3383 -- API name : PA_WF_PURGE
3384 -- Type : Public
3385 -- Pre-reqs : None
3386 -- Function : Main purge procedure for Purging records from Workflow related tables:
3387 -- Note : Argument p_entity_key2_tab can have the following values-
3388 -- pa_wf_processes.entity_key2
3389 -- pa_wf_process-details.object_id1.
3390 -- pa_wf_ntf_performers.object_id1.
3391
3392 PROCEDURE PA_WF_PURGE ( p_purge_batch_id IN NUMBER,
3393 p_project_id IN NUMBER,
3394 p_purge_release IN VARCHAR2,
3395 p_entity_key_tab IN Pa_Plsql_Datatypes.IdTabTyp,
3396 p_wf_type_code IN VARCHAR2,
3397 p_item_type IN VARCHAR2,
3398 p_archive_flag IN VARCHAR2,
3399 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3400 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3401 x_err_code IN OUT NOCOPY NUMBER ) AS --File.Sql.39 bug 4440895
3402
3403 CURSOR cur_workflow (x_entity_key VARCHAR2) IS
3404 SELECT item_key
3405 FROM pa_wf_processes
3406 WHERE entity_key1 = TO_CHAR(p_project_id)
3407 AND entity_key2 = x_entity_key
3408 AND wf_type_code = p_wf_type_code
3409 AND item_type = p_item_type;
3410
3411 l_item_key pa_wf_processes.item_key%TYPE;
3412 I PLS_INTEGER;
3413 l_entity_key pa_wf_processes.entity_key2%TYPE;
3414 l_nos_wf_process_inserted NUMBER ;
3415 l_nos_wf_process_dtls_inserted NUMBER ;
3416 l_nos_wf_ntf_perf_inserted NUMBER ;
3417 l_nos_wf_process_deleted NUMBER ;
3418 l_nos_wf_process_dtls_deleted NUMBER ;
3419 l_nos_wf_ntf_perf_deleted NUMBER ;
3420
3421 l_status VARCHAR2(100);
3422 l_result VARCHAR2(100);
3423 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
3424
3425 BEGIN
3426
3427 l_old_err_stack := x_err_stack; -- Added for bug 4227589
3428 x_err_stack := x_err_stack || ' ->Before call to purge workflow records ';
3429
3430 /*Initialize the no of record variables for each call */
3431
3432 Pa_Debug.DEBUG(' Inside Procedure to purge workflow process details and notification ') ;
3433 x_err_stage := 'Inside Procedure to purge workflow process details and notification for project '||TO_CHAR(p_project_id) ;
3434
3435 l_nos_wf_process_inserted := 0;
3436 l_nos_wf_process_dtls_inserted := 0;
3437 l_nos_wf_ntf_perf_inserted := 0;
3438 l_nos_wf_process_deleted := 0;
3439 l_nos_wf_process_dtls_deleted := 0;
3440 l_nos_wf_ntf_perf_deleted := 0;
3441
3442 /* Fetch the records from PLSQL table. */
3443
3444 FOR I IN p_entity_key_tab.FIRST .. p_entity_key_tab.LAST LOOP
3445 l_entity_key := TO_CHAR(p_entity_key_tab(I));
3446
3447 OPEN cur_workflow(l_entity_key);
3448 LOOP
3449 FETCH cur_workflow INTO l_item_key;
3450 IF cur_workflow%NOTFOUND THEN
3451 EXIT;
3452 END IF;
3453
3454 /* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
3455
3456 IF p_archive_flag='Y' THEN
3457
3458 Pa_Debug.DEBUG('Inserting Records into pa_wf_processes_AR table ') ;
3459 x_err_stage := 'Inserting Records into pa_wf_processes_AR table for item key '||l_item_key ;
3460
3461 INSERT INTO pa_wf_processes_ar
3462 (purge_batch_id,
3463 purge_release,
3464 purge_project_id,
3465 wf_type_code,
3466 item_type,
3467 item_key,
3468 entity_key1,
3469 entity_key2,
3470 description,
3471 last_updated_by,
3472 last_update_date,
3473 creation_date,
3474 created_by,
3475 last_update_login)
3476 SELECT p_purge_batch_id,
3477 p_purge_release,
3478 p_project_id,
3479 wf_type_code,
3480 item_type,
3481 item_key,
3482 entity_key1,
3483 entity_key2,
3484 description,
3485 last_updated_by,
3486 last_update_date,
3487 creation_date,
3488 created_by,
3489 last_update_login
3490 FROM pa_wf_processes
3491 WHERE entity_key1 = TO_CHAR(p_project_id)
3492 AND entity_key2 = l_entity_key
3493 AND wf_type_code = p_wf_type_code
3494 AND item_type = p_item_type;
3495
3496 /*Increase the value of l_nos_wf_process_inserted to indicate number of records inserted in pa_wf_processes_ar table.
3497 The value will increase for each loop(item_key)*/
3498 l_nos_wf_process_inserted := l_nos_wf_process_inserted + SQL%ROWCOUNT;
3499
3500 Pa_Debug.DEBUG('Inserting Records into pa_wf_process_DETAILS_AR table ') ;
3501 x_err_stage := 'Inserting Records into pa_wf_process_DETAILS_AR table for item key '|| l_item_key ;
3502
3503
3504 INSERT INTO pa_wf_process_details_ar
3505 (purge_batch_id,
3506 purge_release,
3507 purge_project_id,
3508 wf_type_code,
3509 item_type,
3510 item_key,
3511 object_id1,
3512 object_id2,
3513 process_status_code,
3514 source_attribute1,
3515 source_attribute2,
3516 source_attribute3,
3517 source_attribute4,
3518 source_attribute5,
3519 source_attribute6,
3520 source_attribute7,
3521 source_attribute8,
3522 source_attribute9,
3523 source_attribute10,
3524 last_updated_by,
3525 last_update_date,
3526 creation_date,
3527 created_by,
3528 last_update_login)
3529 SELECT p_purge_batch_id,
3530 p_purge_release,
3531 p_project_id,
3532 wf_type_code,
3533 item_type,
3534 item_key,
3535 object_id1,
3536 object_id2,
3537 process_status_code,
3538 source_attribute1,
3539 source_attribute2,
3540 source_attribute3,
3541 source_attribute4,
3542 source_attribute5,
3543 source_attribute6,
3544 source_attribute7,
3545 source_attribute8,
3546 source_attribute9,
3547 source_attribute10,
3548 last_updated_by,
3549 last_update_date,
3550 creation_date,
3551 created_by,
3552 last_update_login
3553 FROM pa_wf_process_details
3554 WHERE item_key = l_item_key
3555 AND object_id1 = l_entity_key
3556 AND wf_type_code = p_wf_type_code
3557 AND item_type = p_item_type;
3558
3559 /* Increase the value of l_nos_wf_process_dtls_inserted to indicate number of records inserted in
3560 pa_wf_process_details_ar table. The value will increase for each loop(item_key)*/
3561 l_nos_wf_process_dtls_inserted := l_nos_wf_process_dtls_inserted + SQL%ROWCOUNT;
3562
3563 Pa_Debug.DEBUG('Inserting Records into pa_wf_ntf_performers_AR table ') ;
3564 x_err_stage := 'Inserting Records into pa_wf_ntf_performers_AR table for item key '|| l_item_key ;
3565
3566
3567 INSERT INTO pa_wf_ntf_performers_ar
3568 (purge_batch_id,
3569 purge_release,
3570 purge_project_id,
3571 wf_type_code,
3572 item_type,
3573 item_key,
3574 object_id1,
3575 object_id2,
3576 user_name,
3577 user_type,
3578 routing_order,
3579 current_approver_flag,
3580 approver_comments,
3581 group_id,
3582 approver_group_id,
3583 approval_status,
3584 sequence_number,
3585 action_code,
3586 action_date,
3587 display_flag,
3588 last_updated_by,
3589 last_update_date,
3590 creation_date,
3591 created_by,
3592 last_update_login)
3593 SELECT p_purge_batch_id,
3594 p_purge_release,
3595 p_project_id,
3596 wf_type_code,
3597 item_type,
3598 item_key,
3599 object_id1,
3600 object_id2,
3601 user_name,
3602 user_type,
3603 routing_order,
3604 current_approver_flag,
3605 approver_comments,
3606 group_id,
3607 approver_group_id,
3608 approval_status,
3609 sequence_number,
3610 action_code,
3611 action_date,
3612 display_flag,
3613 Fnd_Global.user_id,
3614 SYSDATE,
3615 SYSDATE,
3616 Fnd_Global.user_id,
3617 Fnd_Global.login_id
3618 FROM pa_wf_ntf_performers
3619 WHERE object_id2 = TO_CHAR(p_project_id)
3620 AND object_id1 = l_entity_key
3621 AND wf_type_code = p_wf_type_code
3622 AND item_type = p_item_type;
3623
3624 /*Increase the value of l_nos_wf_ntf_perf_inserted to indicate number of records inserted in pa_wf_process_details_ar table.
3625 The value will increase for each loop(item_key)*/
3626 l_nos_wf_ntf_perf_inserted := l_nos_wf_ntf_perf_inserted + SQL%ROWCOUNT;
3627
3628 END IF; -- p_archive_flag='Y'
3629
3630 /* To keep the count of no of records deleted from pa_wf_processes, pa_wf_process_details and pa_wf_ntf_performers,
3631 manipulate the count of l_nos_wf_process_deleted, l_nos_wf_process_dtls_deleted and l_nos_wf_ntf_perf_deleted. */
3632
3633 Pa_Debug.DEBUG('Deleting Records from pa_wf_ntf_performers table ') ;
3634 x_err_stage := 'Deleting Records from pa_wf_ntf_performers table for item key '|| l_item_key ;
3635
3636 DELETE pa_wf_ntf_performers
3637 WHERE object_id2 = TO_CHAR(p_project_id)
3638 AND object_id1 = l_entity_key
3639 AND wf_type_code = p_wf_type_code
3640 AND item_type = p_item_type;
3641
3642 l_nos_wf_ntf_perf_deleted := l_nos_wf_ntf_perf_deleted + SQL%ROWCOUNT;
3643
3644 Pa_Debug.DEBUG('Deleting Records from pa_wf_process_details table ') ;
3645 x_err_stage := 'Deleting Records from pa_wf_process_details table for item key '|| l_item_key ;
3646
3647 DELETE pa_wf_process_details
3648 WHERE object_id2 = TO_CHAR(p_project_id)
3649 AND object_id1 = l_entity_key
3650 AND wf_type_code = p_wf_type_code
3651 AND item_type = p_item_type;
3652
3653 l_nos_wf_process_dtls_deleted := l_nos_wf_process_dtls_deleted + SQL%ROWCOUNT;
3654
3655 Pa_Debug.DEBUG('Deleting Records from pa_wf_processes table ') ;
3656 x_err_stage := 'Deleting Records from pa_wf_processes table for item key '|| l_item_key ;
3657
3658
3659 DELETE pa_wf_processes
3660 WHERE entity_key1 = TO_CHAR(p_project_id)
3661 AND entity_key2 = l_entity_key
3662 AND wf_type_code = p_wf_type_code
3663 AND item_type = p_item_type;
3664
3665 l_nos_wf_process_deleted := l_nos_wf_process_deleted + SQL%ROWCOUNT;
3666
3667 -------------------Delete the records from WORKFLOW tables by calling suitable APIs----------------------
3668
3669 /* Check the item status for the workflow process */
3670 Wf_Engine.itemstatus ( itemtype => p_item_type,
3671 itemkey => l_item_key,
3672 status => l_status,
3673 result => l_result );
3674 /* If the status of the item is ACTIVE, the process need to be aborted before purging */
3675 IF l_status = 'ACTIVE' THEN
3676
3677 Wf_Engine.abortprocess ( itemtype => p_item_type,
3678 itemkey => l_item_key );
3679 END IF;
3680
3681 /* Purge the record from workflow tables.*/
3682 Wf_Purge.total( itemtype => p_item_type,
3683 itemkey => l_item_key );
3684
3685
3686 END LOOP;
3687
3688 CLOSE cur_workflow;
3689
3690 END LOOP;
3691
3692 /*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in
3693 the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
3694 The procedure is called once for ppa_wf_processes, pa_wf_process_details and pa_wf_ntf_performers tables */
3695
3696 Pa_Purge.CommitProcess(p_purge_batch_id,
3697 p_project_id,
3698 'PA_WF_NTF_PERFORMERS',
3699 l_nos_wf_ntf_perf_inserted,
3700 l_nos_wf_ntf_perf_deleted,
3701 x_err_code,
3702 x_err_stack,
3703 x_err_stage
3704 ) ;
3705
3706 Pa_Purge.CommitProcess(p_purge_batch_id,
3707 p_project_id,
3708 'PA_WF_PROCESS_DETAILS',
3709 l_nos_wf_process_dtls_inserted,
3710 l_nos_wf_process_dtls_deleted,
3711 x_err_code,
3712 x_err_stack,
3713 x_err_stage
3714 ) ;
3715
3716 Pa_Purge.CommitProcess(p_purge_batch_id,
3717 p_project_id,
3718 'PA_WF_PROCESSES',
3719 l_nos_wf_process_inserted,
3720 l_nos_wf_process_deleted,
3721 x_err_code,
3722 x_err_stack,
3723 x_err_stage
3724 ) ;
3725
3726 x_err_stack := l_old_err_stack; -- Added for bug 4227589
3727 EXCEPTION
3728 WHEN Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error THEN
3729 RAISE Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error ;
3730
3731 WHEN OTHERS THEN
3732 Pa_Debug.DEBUG('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_WF_PURGE');
3733 Pa_Debug.DEBUG('Error stage is '||x_err_stage );
3734 Pa_Debug.DEBUG('Error stack is '||x_err_stack );
3735 Pa_Debug.DEBUG(SQLERRM);
3736 Pa_Project_Utils2.g_sqlerrm := SQLERRM ;
3737
3738 RAISE Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error ;
3739
3740
3741 END PA_WF_PURGE;
3742
3743
3744 PROCEDURE PA_WF_MASS_PURGE ( p_purge_batch_id IN NUMBER,
3745 p_project_id IN NUMBER,
3746 p_purge_release IN VARCHAR2,
3747 p_object_id_tab IN Pa_Plsql_Datatypes.IdTabTyp,
3748 p_item_type IN VARCHAR2,
3749 p_archive_flag IN VARCHAR2,
3750 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3751 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3752 x_err_code IN OUT NOCOPY NUMBER ) AS --File.Sql.39 bug 4440895
3753
3754 CURSOR cur_workflow (x_object_id VARCHAR2) IS
3755 SELECT item_key
3756 FROM pa_wf_process_details
3757 WHERE NVL(object_id2, object_id1) = x_object_id
3758 AND item_type = p_item_type;
3759
3760 l_item_key pa_wf_processes.item_key%TYPE;
3761 I PLS_INTEGER;
3762 l_object_id pa_wf_process_details.object_id1%TYPE;
3763 l_nos_wf_process_inserted NUMBER ;
3764 l_nos_wf_process_dtls_inserted NUMBER ;
3765 l_nos_wf_process_deleted NUMBER ;
3766 l_nos_wf_process_dtls_deleted NUMBER ;
3767
3768 l_status VARCHAR2(100);
3769 l_result VARCHAR2(100);
3770 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
3771
3772 BEGIN
3773
3774 l_old_err_stack := x_err_stack; -- Added for bug 4227589
3775 x_err_stack := x_err_stack || ' ->Before call to purge workflow records ';
3776
3777 /*Initialize the no of record variables for each call */
3778
3779 Pa_Debug.DEBUG(' Inside Procedure to purge workflow process details ') ;
3780 x_err_stage := 'Inside Procedure to purge workflow process details for project '||TO_CHAR(p_project_id) ;
3781
3782 l_nos_wf_process_inserted := 0;
3783 l_nos_wf_process_dtls_inserted := 0;
3784 l_nos_wf_process_deleted := 0;
3785 l_nos_wf_process_dtls_deleted := 0;
3786
3787 /* Fetch the records from PLSQL table. */
3788
3789 FOR I IN p_object_id_tab.FIRST .. p_object_id_tab.LAST LOOP
3790 l_object_id := p_object_id_tab(I);
3791
3792 OPEN cur_workflow(l_object_id);
3793 LOOP
3794 FETCH cur_workflow INTO l_item_key;
3795 IF cur_workflow%NOTFOUND THEN
3796 EXIT;
3797 END IF;
3798
3799 /* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
3800
3801 IF p_archive_flag='Y' THEN
3802
3803 Pa_Debug.DEBUG('Inserting Records into pa_wf_processes_AR table ') ;
3804 x_err_stage := 'Inserting Records into pa_wf_processes_AR table for item key '||l_item_key ;
3805
3806 INSERT INTO pa_wf_processes_ar
3807 (purge_batch_id,
3808 purge_release,
3809 purge_project_id,
3810 wf_type_code,
3811 item_type,
3812 item_key,
3813 entity_key1,
3814 entity_key2,
3815 description,
3816 last_updated_by,
3817 last_update_date,
3818 creation_date,
3819 created_by,
3820 last_update_login)
3821 SELECT p_purge_batch_id,
3822 p_purge_release,
3823 p_project_id,
3824 wf_type_code,
3825 item_type,
3826 item_key,
3827 entity_key1,
3828 entity_key2,
3829 description,
3830 last_updated_by,
3831 last_update_date,
3832 creation_date,
3833 created_by,
3834 last_update_login
3835 FROM pa_wf_processes
3836 WHERE item_key = l_item_key
3837 AND entity_key1 = TO_CHAR(p_project_id)
3838 AND item_type = p_item_type;
3839
3840 /*Increase the value of l_nos_wf_process_inserted to indicate number of records inserted in pa_wf_processes_ar table.
3841 The value will increase for each loop(item_key)*/
3842 l_nos_wf_process_inserted := l_nos_wf_process_inserted + SQL%ROWCOUNT;
3843
3844 Pa_Debug.DEBUG('Inserting Records into pa_wf_process_DETAILS_AR table ') ;
3845 x_err_stage := 'Inserting Records into pa_wf_process_DETAILS_AR table for item key '|| l_item_key ;
3846
3847
3848 INSERT INTO pa_wf_process_details_ar
3849 (purge_batch_id,
3850 purge_release,
3851 purge_project_id,
3852 wf_type_code,
3853 item_type,
3854 item_key,
3855 object_id1,
3856 object_id2,
3857 process_status_code,
3858 source_attribute1,
3859 source_attribute2,
3860 source_attribute3,
3861 source_attribute4,
3862 source_attribute5,
3863 source_attribute6,
3864 source_attribute7,
3865 source_attribute8,
3866 source_attribute9,
3867 source_attribute10,
3868 last_updated_by,
3869 last_update_date,
3870 creation_date,
3871 created_by,
3872 last_update_login)
3873 SELECT p_purge_batch_id,
3874 p_purge_release,
3875 p_project_id,
3876 wf_type_code,
3877 item_type,
3878 item_key,
3879 object_id1,
3880 object_id2,
3881 process_status_code,
3882 source_attribute1,
3883 source_attribute2,
3884 source_attribute3,
3885 source_attribute4,
3886 source_attribute5,
3887 source_attribute6,
3888 source_attribute7,
3889 source_attribute8,
3890 source_attribute9,
3891 source_attribute10,
3892 last_updated_by,
3893 last_update_date,
3894 creation_date,
3895 created_by,
3896 last_update_login
3897 FROM pa_wf_process_details
3898 WHERE item_key = l_item_key
3899 AND NVL(object_id2, object_id1) = TO_CHAR(l_object_id)
3900 AND item_type = p_item_type;
3901
3902 /* Increase the value of l_nos_wf_process_dtls_inserted to indicate number of records inserted in
3903 pa_wf_process_details_ar table. The value will increase for each loop(item_key)*/
3904 l_nos_wf_process_dtls_inserted := l_nos_wf_process_dtls_inserted + SQL%ROWCOUNT;
3905
3906 END IF; -- p_archive_flag='Y'
3907
3908 /* To keep the count of no of records deleted from pa_wf_processes and pa_wf_process_details, manipulate the
3909 count of l_nos_wf_process_deleted and l_nos_wf_process_dtls_deleted. */
3910
3911 Pa_Debug.DEBUG('Deleting Records from pa_wf_process_details table ') ;
3912 x_err_stage := 'Deleting Records from pa_wf_process_details table for item key '|| l_item_key ;
3913
3914 DELETE pa_wf_process_details
3915 WHERE item_key = l_item_key
3916 AND NVL(object_id2, object_id1) = TO_CHAR(l_object_id)
3917 AND item_type = p_item_type;
3918
3919 l_nos_wf_process_dtls_deleted := l_nos_wf_process_dtls_deleted + SQL%ROWCOUNT;
3920
3921 Pa_Debug.DEBUG('Deleting Records from pa_wf_processes table ') ;
3922 x_err_stage := 'Deleting Records from pa_wf_processes table for item key '|| l_item_key ;
3923
3924
3925 DELETE pa_wf_processes
3926 WHERE item_key = l_item_key
3927 AND entity_key1 = TO_CHAR(p_project_id)
3928 AND item_type = p_item_type;
3929
3930 l_nos_wf_process_deleted := l_nos_wf_process_deleted + SQL%ROWCOUNT;
3931
3932 -------------------Delete the records from WORKFLOW tables by calling suitable APIs----------------------
3933
3934 /* Check the item status for the workflow process */
3935 Wf_Engine.itemstatus ( itemtype => p_item_type,
3936 itemkey => l_item_key,
3937 status => l_status,
3938 result => l_result );
3939 /* If the status of the item is ACTIVE, the process need to be aborted before purging */
3940 IF l_status = 'ACTIVE' THEN
3941
3942 Wf_Engine.abortprocess ( itemtype => p_item_type,
3943 itemkey => l_item_key );
3944 END IF;
3945
3946 /* Purge the record from workflow tables.*/
3947 Wf_Purge.total( itemtype => p_item_type,
3948 itemkey => l_item_key );
3949
3950
3951 END LOOP;
3952
3953 CLOSE cur_workflow;
3954
3955 END LOOP;
3956
3957 /*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in
3958 the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
3959 The procedure is called once for pa_wf_processes, pa_wf_process_details and pa_wf_ntf_performers tables */
3960
3961 Pa_Purge.CommitProcess(p_purge_batch_id,
3962 p_project_id,
3963 'PA_WF_PROCESS_DETAILS',
3964 l_nos_wf_process_dtls_inserted,
3965 l_nos_wf_process_dtls_deleted,
3966 x_err_code,
3967 x_err_stack,
3968 x_err_stage
3969 ) ;
3970
3971 Pa_Purge.CommitProcess(p_purge_batch_id,
3972 p_project_id,
3973 'PA_WF_PROCESSES',
3974 l_nos_wf_process_inserted,
3975 l_nos_wf_process_deleted,
3976 x_err_code,
3977 x_err_stack,
3978 x_err_stage
3979 ) ;
3980
3981 x_err_stack := l_old_err_stack; -- Added for bug 4227589
3982 EXCEPTION
3983 WHEN Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error THEN
3984 RAISE Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error ;
3985
3986 WHEN OTHERS THEN
3987 Pa_Debug.DEBUG('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_WF_MASS_PURGE');
3988 Pa_Debug.DEBUG('Error stage is '||x_err_stage );
3989 Pa_Debug.DEBUG('Error stack is '||x_err_stack );
3990 Pa_Debug.DEBUG(SQLERRM);
3991 Pa_Project_Utils2.g_sqlerrm := SQLERRM ;
3992
3993 RAISE Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error ;
3994
3995
3996 END PA_WF_MASS_PURGE;
3997
3998 -- Start of comments
3999 -- API name : PA_WF_MASS_ASGN_PURGE
4000 -- Type : Public
4001 -- Pre-reqs : None
4002 -- Function : Procedure for Purging records from Workflow related tables for item_type 'PARMAAP':
4003
4004 PROCEDURE PA_WF_MASS_ASGN_PURGE ( p_purge_batch_id IN NUMBER,
4005 p_project_id IN NUMBER,
4006 p_purge_release IN VARCHAR2,
4007 p_object_id_tab IN Pa_Plsql_Datatypes.IdTabTyp,
4008 p_wf_type_code IN VARCHAR2,
4009 p_archive_flag IN VARCHAR2,
4010 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4011 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4012 x_err_code IN OUT NOCOPY NUMBER ) AS --File.Sql.39 bug 4440895
4013
4014 CURSOR cur_workflow (x_object_id VARCHAR2) IS
4015 SELECT item_key, group_id
4016 FROM pa_wf_ntf_performers
4017 WHERE object_id1 = x_object_id
4018 AND wf_type_code = p_wf_type_code
4019 AND item_type <> 'PAWFAAP';
4020
4021 l_item_key pa_wf_processes.item_key%TYPE;
4022 l_group_id pa_wf_ntf_performers.group_id%TYPE;
4023 I PLS_INTEGER;
4024 l_object_id pa_wf_ntf_performers.object_id1%TYPE;
4025 l_nos_wf_process_inserted NUMBER ;
4026 l_nos_wf_ntf_perf_inserted NUMBER ;
4027 l_nos_wf_process_deleted NUMBER ;
4028 l_nos_wf_ntf_perf_deleted NUMBER ;
4029
4030 l_status VARCHAR2(100);
4031 l_result VARCHAR2(100);
4032 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
4033
4034 BEGIN
4035
4036 l_old_err_stack := x_err_stack; -- Added for bug 4227589
4037 x_err_stack := x_err_stack || ' ->Before call to purge workflow records ';
4038
4039 /*Initialize the no of record variables for each call */
4040
4041 Pa_Debug.DEBUG(' Inside Procedure to purge workflow process and notification ') ;
4042 x_err_stage := 'Inside Procedure to purge workflow process and notification for project '||TO_CHAR(p_project_id) ;
4043
4044 l_nos_wf_process_inserted := 0;
4045 l_nos_wf_ntf_perf_inserted := 0;
4046 l_nos_wf_process_deleted := 0;
4047 l_nos_wf_ntf_perf_deleted := 0;
4048
4049 /* Fetch the records from PLSQL table. */
4050
4051 FOR I IN p_object_id_tab.FIRST .. p_object_id_tab.LAST LOOP
4052 l_object_id := p_object_id_tab(I);
4053
4054 OPEN cur_workflow(l_object_id);
4055 LOOP
4056 FETCH cur_workflow INTO l_item_key,l_group_id;
4057 IF cur_workflow%NOTFOUND THEN
4058 EXIT;
4059 END IF;
4060
4061 /* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
4062
4063 IF p_archive_flag='Y' THEN
4064
4065 Pa_Debug.DEBUG('Inserting Records into pa_wf_processes_AR table ') ;
4066 x_err_stage := 'Inserting Records into pa_wf_processes_AR table for assignment_id/group_id '||l_group_id ;
4067
4068 INSERT INTO pa_wf_processes_ar
4069 (purge_batch_id,
4070 purge_release,
4071 purge_project_id,
4072 wf_type_code,
4073 item_type,
4074 item_key,
4075 entity_key1,
4076 entity_key2,
4077 description,
4078 last_updated_by,
4079 last_update_date,
4080 creation_date,
4081 created_by,
4082 last_update_login)
4083 SELECT p_purge_batch_id,
4084 p_purge_release,
4085 p_project_id,
4086 wf_type_code,
4087 item_type,
4088 item_key,
4089 entity_key1,
4090 entity_key2,
4091 description,
4092 last_updated_by,
4093 last_update_date,
4094 creation_date,
4095 created_by,
4096 last_update_login
4097 FROM pa_wf_processes
4098 WHERE ( entity_key2 = TO_CHAR(l_object_id)
4099 OR entity_key2 = TO_CHAR(l_group_id ))
4100 AND entity_key1 = TO_CHAR(p_project_id)
4101 AND wf_type_code = p_wf_type_code
4102 AND item_type <> 'PAWFAAP';
4103
4104 /*Increase the value of l_nos_wf_process_inserted to indicate number of records inserted in pa_wf_processes_ar table.
4105 The value will increase for each loop(item_key)*/
4106 l_nos_wf_process_inserted := l_nos_wf_process_inserted + SQL%ROWCOUNT;
4107
4108 Pa_Debug.DEBUG('Inserting Records into pa_wf_ntf_performers_AR table ') ;
4109 x_err_stage := 'Inserting Records into pa_wf_ntf_performers_AR table for assignment_id/group_id '|| l_group_id ;
4110
4111
4112 INSERT INTO pa_wf_ntf_performers_ar
4113 (purge_batch_id,
4114 purge_release,
4115 purge_project_id,
4116 wf_type_code,
4117 item_type,
4118 item_key,
4119 object_id1,
4120 object_id2,
4121 user_name,
4122 user_type,
4123 routing_order,
4124 current_approver_flag,
4125 approver_comments,
4126 group_id,
4127 approver_group_id,
4128 approval_status,
4129 sequence_number,
4130 action_code,
4131 action_date,
4132 display_flag,
4133 last_updated_by,
4134 last_update_date,
4135 creation_date,
4136 created_by,
4137 last_update_login)
4138 SELECT p_purge_batch_id,
4139 p_purge_release,
4140 p_project_id,
4141 wf_type_code,
4142 item_type,
4143 item_key,
4144 object_id1,
4145 object_id2,
4146 user_name,
4147 user_type,
4148 routing_order,
4149 current_approver_flag,
4150 approver_comments,
4151 group_id,
4152 approver_group_id,
4153 approval_status,
4154 sequence_number,
4155 action_code,
4156 action_date,
4157 display_flag,
4158 Fnd_Global.user_id,
4159 SYSDATE,
4160 SYSDATE,
4161 Fnd_Global.user_id,
4162 Fnd_Global.login_id
4163 FROM pa_wf_ntf_performers
4164 WHERE object_id1 = TO_CHAR(l_object_id)
4165 AND wf_type_code = p_wf_type_code
4166 AND item_type <> 'PAWFAAP';
4167
4168 /*Increase the value of l_nos_wf_ntf_perf_inserted to indicate number of records inserted in pa_wf_process_details_ar table.
4169 The value will increase for each loop(item_key)*/
4170 l_nos_wf_ntf_perf_inserted := l_nos_wf_ntf_perf_inserted + SQL%ROWCOUNT;
4171
4172 END IF; -- p_archive_flag='Y'
4173
4174 /* To keep the count of no of records deleted from pa_wf_processes, pa_wf_process_details and pa_wf_ntf_performers,
4175 manipulate the count of l_nos_wf_process_deleted, l_nos_wf_process_dtls_deleted and l_nos_wf_ntf_perf_deleted. */
4176
4177 Pa_Debug.DEBUG('Deleting Records from pa_wf_ntf_performers table ') ;
4178 x_err_stage := 'Deleting Records from pa_wf_ntf_performers table for assignment_id/group_id '|| l_group_id;
4179
4180 DELETE pa_wf_ntf_performers
4181 WHERE object_id1 = TO_CHAR(l_object_id)
4182 AND wf_type_code = p_wf_type_code
4183 AND item_type <> 'PAWFAAP';
4184
4185 l_nos_wf_ntf_perf_deleted := l_nos_wf_ntf_perf_deleted + SQL%ROWCOUNT;
4186
4187 Pa_Debug.DEBUG('Deleting Records from pa_wf_processes table ') ;
4188 x_err_stage := 'Deleting Records from pa_wf_processes table for assignment_id/group_id '|| l_group_id;
4189
4190
4191 DELETE pa_wf_processes
4192 WHERE entity_key1 = TO_CHAR(p_project_id)
4193 aND (entity_key2 = TO_CHAR(l_object_id)
4194 OR entity_key2 = TO_CHAR(l_group_id) )
4195 AND wf_type_code = p_wf_type_code
4196 AND item_type <> 'PAWFAAP';
4197
4198 l_nos_wf_process_deleted := l_nos_wf_process_deleted + SQL%ROWCOUNT;
4199
4200 -------------------Delete the records from WORKFLOW tables by calling suitable APIs----------------------
4201
4202 /* Check the item status for the workflow process */
4203 Wf_Engine.itemstatus ( itemtype => 'PARMAAP',
4204 itemkey => l_item_key,
4205 status => l_status,
4206 result => l_result );
4207 /* If the status of the item is ACTIVE, the process need to be aborted before purging */
4208 IF l_status = 'ACTIVE' THEN
4209
4210 Wf_Engine.abortprocess ( itemtype => 'PARMAAP',
4211 itemkey => l_item_key );
4212 END IF;
4213
4214 /* Purge the record from workflow tables.*/
4215 Wf_Purge.total( itemtype => 'PARMAAP',
4216 itemkey => l_item_key );
4217
4218
4219 END LOOP;
4220
4221 CLOSE cur_workflow;
4222
4223 END LOOP;
4224
4225 /*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in
4226 the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
4227 The procedure is called once for pa_wf_processes and pa_wf_ntf_performers tables */
4228
4229 Pa_Purge.CommitProcess(p_purge_batch_id,
4230 p_project_id,
4231 'PA_WF_NTF_PERFORMERS',
4232 l_nos_wf_ntf_perf_inserted,
4233 l_nos_wf_ntf_perf_deleted,
4234 x_err_code,
4235 x_err_stack,
4236 x_err_stage
4237 ) ;
4238
4239 Pa_Purge.CommitProcess(p_purge_batch_id,
4240 p_project_id,
4241 'PA_WF_PROCESSES',
4242 l_nos_wf_process_inserted,
4243 l_nos_wf_process_deleted,
4244 x_err_code,
4245 x_err_stack,
4246 x_err_stage
4247 ) ;
4248
4249 x_err_stack := l_old_err_stack; -- Added for bug 4227589
4250 EXCEPTION
4251 WHEN Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error THEN
4252 RAISE Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error ;
4253
4254 WHEN OTHERS THEN
4255 Pa_Debug.DEBUG('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_WF_MASS_ASGN_PURGE');
4256 Pa_Debug.DEBUG('Error stage is '||x_err_stage );
4257 Pa_Debug.DEBUG('Error stack is '||x_err_stack );
4258 Pa_Debug.DEBUG(SQLERRM);
4259 Pa_Project_Utils2.g_sqlerrm := SQLERRM ;
4260
4261 RAISE Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error ;
4262
4263
4264 END PA_WF_MASS_ASGN_PURGE;
4265
4266 -- Start of comments
4267 -- API name : PA_WF_KEY_PURGE
4268 -- Type : Public
4269 -- Pre-reqs : None
4270 -- Function : Procedure for Purging records from Workflow related tables where
4271 -- pa_wf_processes.entity_key2 does not store assignment_id
4272
4273 PROCEDURE PA_WF_KEY_PURGE ( p_purge_batch_id IN NUMBER,
4274 p_project_id IN NUMBER,
4275 p_purge_release IN VARCHAR2,
4276 p_entity_key2 IN VARCHAR2,
4277 p_wf_type_code IN VARCHAR2,
4278 p_item_type IN VARCHAR2,
4279 p_archive_flag IN VARCHAR2,
4280 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4281 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4282 x_err_code IN OUT NOCOPY NUMBER ) AS --File.Sql.39 bug 4440895
4283
4284 CURSOR cur_workflow (x_entity_key VARCHAR2) IS
4285 SELECT item_key
4286 FROM pa_wf_processes
4287 WHERE entity_key1 = TO_CHAR(p_project_id)
4288 AND entity_key2 = x_entity_key
4289 AND wf_type_code = p_wf_type_code
4290 AND item_type = p_item_type;
4291
4292 l_item_key pa_wf_processes.item_key%TYPE;
4293 I PLS_INTEGER;
4294 l_entity_key pa_wf_processes.entity_key2%TYPE;
4295 l_nos_wf_process_inserted NUMBER ;
4296 l_nos_wf_process_deleted NUMBER ;
4297
4298 l_status VARCHAR2(100);
4299 l_result VARCHAR2(100);
4300 l_old_err_stack VARCHAR2(2000); -- Added for bug 4227589
4301
4302 BEGIN
4303
4304 l_old_err_stack := x_err_stack; -- Added for bug 4227589
4305 x_err_stack := x_err_stack || ' ->Before call to purge workflow records ';
4306
4307 /*Initialize the no of record variables for each call */
4308
4309 Pa_Debug.DEBUG(' Inside Procedure to purge workflow processn ') ;
4310 x_err_stage := 'Inside Procedure to purge workflow process for project '||TO_CHAR(p_project_id) ;
4311
4312 l_nos_wf_process_inserted := 0;
4313 l_nos_wf_process_deleted := 0;
4314
4315 OPEN cur_workflow(p_entity_key2);
4316 LOOP
4317 FETCH cur_workflow INTO l_item_key;
4318 IF cur_workflow%NOTFOUND THEN
4319 EXIT;
4320 END IF;
4321
4322 /* If archive flag is YES, archiving of data needs to be done. Insert data into correspodning AR tables */
4323
4324 IF p_archive_flag='Y' THEN
4325
4326 Pa_Debug.DEBUG('Inserting Records into pa_wf_processes_AR table ') ;
4327 x_err_stage := 'Inserting Records into pa_wf_processes_AR table for item key '||l_item_key ;
4328
4329 INSERT INTO pa_wf_processes_ar
4330 (purge_batch_id,
4331 purge_release,
4332 purge_project_id,
4333 wf_type_code,
4334 item_type,
4335 item_key,
4336 entity_key1,
4337 entity_key2,
4338 description,
4339 last_updated_by,
4340 last_update_date,
4341 creation_date,
4342 created_by,
4343 last_update_login)
4344 SELECT p_purge_batch_id,
4345 p_purge_release,
4346 p_project_id,
4347 wf_type_code,
4348 item_type,
4349 item_key,
4350 entity_key1,
4351 entity_key2,
4352 description,
4353 last_updated_by,
4354 last_update_date,
4355 creation_date,
4356 created_by,
4357 last_update_login
4358 FROM pa_wf_processes
4359 WHERE entity_key1 = TO_CHAR(p_project_id)
4360 AND entity_key2 = p_entity_key2
4361 AND wf_type_code = p_wf_type_code
4362 AND item_type = p_item_type;
4363
4364 /*Increase the value of l_nos_wf_process_inserted to indicate number of records inserted in pa_wf_processes_ar table.
4365 The value will increase for each loop(item_key)*/
4366 l_nos_wf_process_inserted := l_nos_wf_process_inserted + SQL%ROWCOUNT;
4367
4368
4369 END IF; -- p_archive_flag='Y'
4370
4371 /* To keep the count of no of records deleted from pa_wf_processes manipulate the count of l_nos_wf_process_deleted. */
4372
4373 Pa_Debug.DEBUG('Deleting Records from pa_wf_processes table ') ;
4374 x_err_stage := 'Deleting Records from pa_wf_processes table for item key '|| l_item_key ;
4375
4376
4377 DELETE pa_wf_processes
4378 WHERE entity_key1 = TO_CHAR(p_project_id)
4379 AND entity_key2 = p_entity_key2
4380 AND wf_type_code = p_wf_type_code
4381 AND item_type = p_item_type;
4382
4383 l_nos_wf_process_deleted := l_nos_wf_process_deleted + SQL%ROWCOUNT;
4384
4385 -------------------Delete the records from WORKFLOW tables by calling suitable APIs----------------------
4386
4387 /* Check the item status for the workflow process */
4388 Wf_Engine.itemstatus ( itemtype => p_item_type,
4389 itemkey => l_item_key,
4390 status => l_status,
4391 result => l_result );
4392 /* If the status of the item is ACTIVE, the process need to be aborted before purging */
4393 IF l_status = 'ACTIVE' THEN
4394
4395 Wf_Engine.abortprocess ( itemtype => p_item_type,
4396 itemkey => l_item_key );
4397 END IF;
4398
4399 /* Purge the record from workflow tables.*/
4400 Wf_Purge.total( itemtype => p_item_type,
4401 itemkey => l_item_key );
4402
4403
4404 END LOOP;
4405
4406 CLOSE cur_workflow;
4407
4408 /*After "deleting" or "deleting and inserting" a set of records the transaction is commited.
4409 This also creates a record in the Pa_Purge_Project_details, which will show the no. of
4410 records that are purged from each table.The procedure is called once for pa_wf_processes tables */
4411
4412 Pa_Purge.CommitProcess(p_purge_batch_id,
4413 p_project_id,
4414 'PA_WF_PROCESSES',
4415 l_nos_wf_process_inserted,
4416 l_nos_wf_process_deleted,
4417 x_err_code,
4418 x_err_stack,
4419 x_err_stage
4420 ) ;
4421
4422 x_err_stack := l_old_err_stack; -- Added for bug 4227589
4423 EXCEPTION
4424 WHEN Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error THEN
4425 RAISE Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error ;
4426
4427 WHEN OTHERS THEN
4428 Pa_Debug.DEBUG('Error Procedure Name := PA_PURGE_PJR_TXNS.PA_WF_KEY_PURGE');
4429 Pa_Debug.DEBUG('Error stage is '||x_err_stage );
4430 Pa_Debug.DEBUG('Error stack is '||x_err_stack );
4431 Pa_Debug.DEBUG(SQLERRM);
4432 Pa_Project_Utils2.g_sqlerrm := SQLERRM ;
4433
4434 RAISE Pa_Project_Utils2.PA_Arch_Pur_Subroutine_Error ;
4435
4436
4437 END PA_WF_KEY_PURGE;
4438
4439 END;