[Home] [Help]
PACKAGE BODY: APPS.PA_MU_BATCHES_V_PKG
Source
1 package body PA_MU_BATCHES_V_PKG as
2 -- $Header: PAXBAUPB.pls 120.3 2007/02/06 12:11:32 rshaik ship $
3
4 -- -------------------------------------------------------------
5 -- Insert_Row
6 -- INSERT table handler
7 -- -------------------------------------------------------------
8
9 PROCEDURE Insert_Row( X_Rowid IN OUT NOCOPY VARCHAR2, -- 4537865
10 X_Batch_ID IN OUT NOCOPY NUMBER, -- 4537865
11 X_Org_Id IN NUMBER DEFAULT NULL, --R12 MOAC Changes: Bug 4363093
12 X_Creation_Date DATE,
13 X_Created_By NUMBER,
14 X_Last_Updated_By NUMBER,
15 X_Last_Update_Date DATE,
16 X_Last_Update_Login NUMBER,
17 X_Batch_Name VARCHAR2,
18 X_Batch_status_Code VARCHAR2,
19 X_Description VARCHAR2,
20 X_Project_Attribute VARCHAR2,
21 X_Effective_Date DATE,
22 X_Attribute_Category VARCHAR2,
23 X_Attribute1 VARCHAR2,
24 X_Attribute2 VARCHAR2,
25 X_Attribute3 VARCHAR2,
26 X_Attribute4 VARCHAR2,
27 X_Attribute5 VARCHAR2,
28 X_Attribute6 VARCHAR2,
29 X_Attribute7 VARCHAR2,
30 X_Attribute8 VARCHAR2,
31 X_Attribute9 VARCHAR2,
32 X_Attribute10 VARCHAR2,
33 X_Attribute11 VARCHAR2,
34 X_Attribute12 VARCHAR2,
35 X_Attribute13 VARCHAR2,
36 X_Attribute14 VARCHAR2,
37 X_Attribute15 VARCHAR2 )
38 IS
39 l_batch_id NUMBER;
40 l_org_Id NUMBER := nvl(X_Org_Id, pa_moac_utils.get_current_org_id); -- R12 MOAC changes
41 BEGIN
42
43 SELECT PA_MASS_UPDATE_BATCHES_S.NextVal
44 INTO l_batch_id
45 FROM dual;
46
47 INSERT INTO PA_MASS_UPDATE_BATCHES
48 ( Batch_ID,
49 Batch_Name,
50 Description,
51 Batch_Status_Code,
52 Project_Attribute,
53 Effective_Date,
54 org_id, --R12 MOAC Changes: Bug 4363093
55 Attribute_Category,
56 Attribute1,
57 Attribute2,
58 Attribute3,
59 Attribute4,
60 Attribute5,
61 Attribute6,
62 Attribute7,
63 Attribute8,
64 Attribute9,
65 Attribute10,
66 Attribute11,
67 Attribute12,
68 Attribute13,
69 Attribute14,
70 Attribute15,
71 Creation_Date,
72 Created_By,
73 Last_Update_Date,
74 Last_Updated_By,
75 Last_Update_Login )
76 VALUES
77 ( l_batch_id,
78 X_Batch_Name,
79 X_Description,
80 X_Batch_Status_Code,
81 X_Project_Attribute,
82 trunc(X_Effective_Date),
83 l_org_Id, --R12 MOAC Changes: Bug 4363093
84 X_Attribute_Category,
85 X_Attribute1,
86 X_Attribute2,
87 X_Attribute3,
88 X_Attribute4,
89 X_Attribute5,
90 X_Attribute6,
91 X_Attribute7,
92 X_Attribute8,
93 X_Attribute9,
94 X_Attribute10,
95 X_Attribute11,
96 X_Attribute12,
97 X_Attribute13,
98 X_Attribute14,
99 X_Attribute15,
100 X_Creation_Date,
101 X_Created_By,
102 X_Last_Update_Date,
103 X_Last_Updated_By,
104 X_Last_Update_Login
105 );
106
107 X_Batch_ID := l_batch_id;
108
109 SELECT rowid INTO X_Rowid
110 FROM PA_MASS_UPDATE_BATCHES
111 WHERE batch_id = l_batch_id;
112
113 EXCEPTION -- 4537865
114 WHEN OTHERS THEN
115
116 X_Rowid := NULL;
117 X_Batch_ID := NULL;
118 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_MU_BATCHES_V_PKG'
119 ,p_procedure_name => 'Insert_Row'
120 ,p_error_text => SUBSTRB(SQLERRM,1,240));
121 RAISE;
122 END Insert_Row;
123
124
125 -- -------------------------------------------------------------
126 -- Update_Row
127 -- UPDATE table handler
128 -- -------------------------------------------------------------
129
130 PROCEDURE Update_Row( X_Rowid VARCHAR2,
131 X_Last_Updated_By NUMBER,
132 X_Last_Update_Date DATE,
133 X_Last_Update_Login NUMBER,
134 X_Batch_Name VARCHAR2,
135 X_Batch_status_Code VARCHAR2,
136 X_Rejection_Code VARCHAR2,
137 X_Description VARCHAR2,
138 X_Project_Attribute VARCHAR2,
139 X_Effective_Date DATE,
140 X_Process_Run_By NUMBER,
141 X_Process_Run_Date DATE,
142 X_Attribute_Category VARCHAR2,
143 X_Attribute1 VARCHAR2,
144 X_Attribute2 VARCHAR2,
145 X_Attribute3 VARCHAR2,
146 X_Attribute4 VARCHAR2,
147 X_Attribute5 VARCHAR2,
148 X_Attribute6 VARCHAR2,
149 X_Attribute7 VARCHAR2,
150 X_Attribute8 VARCHAR2,
151 X_Attribute9 VARCHAR2,
152 X_Attribute10 VARCHAR2,
153 X_Attribute11 VARCHAR2,
154 X_Attribute12 VARCHAR2,
155 X_Attribute13 VARCHAR2,
156 X_Attribute14 VARCHAR2,
157 X_Attribute15 VARCHAR2 )
158 IS
159
160 BEGIN
161
162 UPDATE pa_mass_update_batches
163 SET
164 Batch_Name = X_Batch_Name,
165 Description = X_Description,
166 Batch_Status_Code = X_Batch_Status_Code,
167 Rejection_Code = X_Rejection_Code,
168 Project_Attribute = X_Project_Attribute,
169 Process_Run_Date = X_Process_Run_Date,
170 Process_Run_By = X_Process_Run_By,
171 Effective_Date = trunc(X_Effective_Date),
172 Attribute_Category = X_Attribute_Category,
173 Attribute1 = X_Attribute1,
174 Attribute2 = X_Attribute2,
175 Attribute3 = X_Attribute3,
176 Attribute4 = X_Attribute4,
177 Attribute5 = X_Attribute5,
178 Attribute6 = X_Attribute6,
179 Attribute7 = X_Attribute7,
180 Attribute8 = X_Attribute8,
181 Attribute9 = X_Attribute9,
182 Attribute10 = X_Attribute10,
183 Attribute11 = X_Attribute11,
184 Attribute12 = X_Attribute12,
185 Attribute13 = X_Attribute13,
186 Attribute14 = X_Attribute14,
187 Attribute15 = X_Attribute15,
188 Last_Update_Date = X_Last_Update_Date,
189 Last_Updated_By = X_Last_Updated_By,
190 Last_Update_Login = X_Last_Update_Login
191 WHERE
192 rowid = X_Rowid;
193
194 IF (SQL%NOTFOUND) THEN
195 raise NO_DATA_FOUND;
196 END IF;
197
198 END Update_Row;
199
200
201 -- -------------------------------------------------------------
202 -- Lock_Row
203 -- LOCK table handler
204 -- -------------------------------------------------------------
205
206 PROCEDURE Lock_Row( X_Rowid VARCHAR2,
207 X_Batch_Name VARCHAR2,
208 X_Batch_status_Code VARCHAR2,
209 X_Description VARCHAR2,
210 X_Project_Attribute VARCHAR2,
211 X_Process_Run_By NUMBER,
212 X_Process_Run_Date DATE,
213 X_Effective_Date DATE,
214 X_Rejection_Code VARCHAR2,
215 X_Attribute_Category VARCHAR2,
216 X_Attribute1 VARCHAR2,
217 X_Attribute2 VARCHAR2,
218 X_Attribute3 VARCHAR2,
219 X_Attribute4 VARCHAR2,
220 X_Attribute5 VARCHAR2,
221 X_Attribute6 VARCHAR2,
222 X_Attribute7 VARCHAR2,
223 X_Attribute8 VARCHAR2,
224 X_Attribute9 VARCHAR2,
225 X_Attribute10 VARCHAR2,
226 X_Attribute11 VARCHAR2,
227 X_Attribute12 VARCHAR2,
228 X_Attribute13 VARCHAR2,
229 X_Attribute14 VARCHAR2,
230 X_Attribute15 VARCHAR2 )
231 IS
232
233 CURSOR l_batch_csr IS
234 SELECT Batch_Name,
235 Description,
236 Batch_Status_Code,
237 Process_Run_Date,
238 Process_Run_By,
239 Project_Attribute,
240 Effective_Date,
241 Rejection_Code,
242 Attribute_Category,
243 Attribute1,
244 Attribute2,
245 Attribute3,
246 Attribute4,
247 Attribute5,
248 Attribute6,
249 Attribute7,
250 Attribute8,
251 Attribute9,
252 Attribute10,
253 Attribute11,
254 Attribute12,
255 Attribute13,
256 Attribute14,
257 Attribute15
258 FROM pa_mass_update_batches
259 WHERE rowid = X_rowid
260 FOR UPDATE NOWAIT;
261
262 l_batch_rec l_batch_csr%ROWTYPE;
263
264 BEGIN
265
266 OPEN l_batch_csr;
267 FETCH l_batch_csr into l_batch_rec;
268 IF (l_batch_csr%NOTFOUND) THEN
269 CLOSE l_batch_csr;
270 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
271 APP_EXCEPTION.Raise_Exception;
272 END IF;
273 CLOSE l_batch_csr;
274
275 IF (
276 (l_batch_rec.batch_name = X_Batch_Name)
277 AND (l_batch_rec.batch_status_code = X_Batch_Status_Code)
278 AND (l_batch_rec.project_attribute = X_Project_Attribute)
279 AND ( (l_batch_rec.description = X_Description)
280 OR ( l_batch_rec.description IS NULL
281 AND X_Description IS NULL ) )
282 AND ( (l_batch_rec.process_run_date = X_Process_Run_Date)
283 OR ( l_batch_rec.process_run_date IS NULL
284 AND X_Process_Run_Date IS NULL) )
285 AND ( (l_batch_rec.process_run_by = X_Process_Run_by)
286 OR ( l_batch_rec.process_run_by IS NULL
287 AND X_Process_Run_By IS NULL) )
288 AND ( (l_batch_rec.effective_date = X_Effective_Date)
289 OR ( l_batch_rec.effective_date IS NULL
290 AND X_Effective_Date IS NULL) )
291 AND ( (l_batch_rec.rejection_code = X_Rejection_Code)
292 OR ( l_batch_rec.rejection_code IS NULL
293 AND X_Rejection_Code IS NULL) )
294 AND ( (l_batch_rec.attribute_category = X_Attribute_Category)
295 OR ( l_batch_rec.attribute_category IS NULL
296 AND X_Attribute_Category IS NULL) )
297 AND ( (l_batch_rec.attribute1 = X_Attribute1)
298 OR ( l_batch_rec.attribute1 IS NULL
299 AND X_Attribute1 IS NULL) )
300 AND ( (l_batch_rec.attribute2 = X_Attribute2)
301 OR ( l_batch_rec.attribute2 IS NULL
302 AND X_Attribute2 IS NULL) )
303 AND ( (l_batch_rec.attribute3 = X_Attribute3)
304 OR ( l_batch_rec.attribute3 IS NULL
305 AND X_Attribute3 IS NULL) )
306 AND ( (l_batch_rec.attribute4 = X_Attribute4)
307 OR ( l_batch_rec.attribute4 IS NULL
308 AND X_Attribute4 IS NULL) )
309 AND ( (l_batch_rec.attribute5 = X_Attribute5)
310 OR ( l_batch_rec.attribute5 IS NULL
311 AND X_Attribute5 IS NULL) )
312 AND ( (l_batch_rec.attribute6 = X_Attribute6)
313 OR ( l_batch_rec.attribute6 IS NULL
314 AND X_Attribute6 IS NULL) )
315 AND ( (l_batch_rec.attribute7 = X_Attribute7)
316 OR ( l_batch_rec.attribute7 IS NULL
317 AND X_Attribute7 IS NULL) )
318 AND ( (l_batch_rec.attribute8 = X_Attribute8)
319 OR ( l_batch_rec.attribute8 IS NULL
320 AND X_Attribute8 IS NULL) )
321 AND ( (l_batch_rec.attribute9 = X_Attribute9)
322 OR ( l_batch_rec.attribute9 IS NULL
323 AND X_Attribute9 IS NULL) )
324 AND ( (l_batch_rec.attribute10 = X_Attribute10)
325 OR ( l_batch_rec.attribute10 IS NULL
326 AND X_Attribute10 IS NULL) )
327 AND ( (l_batch_rec.attribute11 = X_Attribute11)
328 OR ( l_batch_rec.attribute11 IS NULL
329 AND X_Attribute11 IS NULL) )
330 AND ( (l_batch_rec.attribute12 = X_Attribute12)
331 OR ( l_batch_rec.attribute12 IS NULL
332 AND X_Attribute12 IS NULL) )
333 AND ( (l_batch_rec.attribute13 = X_Attribute13)
334 OR ( l_batch_rec.attribute13 IS NULL
335 AND X_Attribute13 IS NULL) )
336 AND ( (l_batch_rec.attribute14 = X_Attribute14)
337 OR ( l_batch_rec.attribute14 IS NULL
338 AND X_Attribute14 IS NULL) )
339 AND ( (l_batch_rec.attribute15 = X_Attribute15)
340 OR ( l_batch_rec.attribute15 IS NULL
341 AND X_Attribute15 IS NULL) ) ) THEN
342 return;
343
344 ELSE
345
346 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
347 APP_EXCEPTION.Raise_Exception;
348
349 END IF;
350
351 END Lock_Row;
352
353
354 -- -------------------------------------------------------------
355 -- Delete_Row
356 -- DELETE table handler
357 -- -------------------------------------------------------------
358
359 PROCEDURE Delete_Row( X_Rowid VARCHAR2 )
360 IS
361
362 l_batch_id NUMBER;
363
364 BEGIN
365
366 SELECT batch_id INTO l_batch_id
367 FROM pa_mass_update_batches
368 WHERE rowid = X_Rowid;
369
370 --
371 -- First, delete all the lines for this batch
372 --
373 BEGIN
374
375 DELETE FROM pa_mass_update_details
376 WHERE batch_id = l_batch_id;
377
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 -- This is okay
381 null;
382 END;
383
384 DELETE FROM pa_mass_update_batches
385 WHERE rowid = X_Rowid;
386
387 IF (SQL%NOTFOUND) THEN
388 Raise NO_DATA_FOUND;
389 END IF;
390
391 END Delete_Row;
392
393
394 -- -------------------------------------------------------------
395 -- Process_Conc
396 -- Called by the concurrent program to process batches. This
397 -- procedure serves as a wrapper to the Process procedure.
398 -- -------------------------------------------------------------
399
400 PROCEDURE Proc_Conc( ERRBUF OUT NOCOPY VARCHAR2, -- 4537865
401 RETCODE OUT NOCOPY VARCHAR2, -- 4537865
402 X_Batch_ID IN NUMBER DEFAULT NULL,
403 X_Request_ID OUT NOCOPY NUMBER ) --4537865
404 IS
405 l_errbuf VARCHAR2(2000);
406 l_retcode VARCHAR2(1);
407 l_NO_EMP_RECORD EXCEPTION;
408
409 CURSOR l_batch_csr IS
410 SELECT batch_id
411 FROM pa_mass_update_batches
412 WHERE batch_status_code = 'S'
413 AND trunc(sysdate) >= trunc(nvl(effective_date, sysdate));
414
415 BEGIN
416 RETCODE := '0';
417 X_Request_ID := FND_GLOBAL.Conc_Request_ID;
418
419 IF (PA_UTILS.GetEmpIdFromUser(FND_GLOBAL.user_id) IS NULL) THEN
420 raise l_NO_EMP_RECORD;
421 END IF;
422
423 IF (X_Batch_ID IS NOT NULL) THEN
424
425 Process( ERRBUF => l_errbuf,
426 RETCODE => l_retcode,
427 X_Batch_ID => X_Batch_ID,
428 X_Concurrent => 'Y',
429 X_All_Batches => 'N' );
430 ELSE
431
432 FOR l_batch_rec IN l_batch_csr LOOP
433
434 Process( ERRBUF => l_errbuf,
435 RETCODE => l_retcode,
436 X_Batch_ID => l_batch_rec.batch_id,
437 X_Concurrent => 'Y',
438 X_All_Batches => 'Y' );
439
440 END LOOP;
441
442 END IF;
443
444 EXCEPTION
445 WHEN l_NO_EMP_RECORD THEN
446 RETCODE := '2';
447 fnd_message.set_name('PA', 'PA_ALL_WARN_NO_EMPL_REC');
448 ERRBUF := fnd_message.get;
449
450 WHEN OTHERS THEN
451 RETCODE := '2';
452 ERRBUF := SQLERRM;
453
454 END Proc_Conc;
455
456
457 -- -------------------------------------------------------------
458 -- Process
459 -- This procedure can be called online using the Mass Update
460 -- Batches form or as a concurrent program. The parameter
461 -- X_Concurrent indicates how this procedure is being called.
462 -- -------------------------------------------------------------
463
464 PROCEDURE Process( ERRBUF OUT NOCOPY VARCHAR2, -- 4537865
465 RETCODE OUT NOCOPY VARCHAR2, -- 4537865
466 X_Batch_ID IN NUMBER,
467 X_Concurrent IN VARCHAR2 DEFAULT 'Y',
468 X_All_Batches IN VARCHAR2 DEFAULT 'N' )
469
470 IS
471
472 l_batch_status_code VARCHAR2(1);
473 l_project_attribute VARCHAR2(30);
474 l_mass_adj_outcome VARCHAR2(30) := NULL;
475 l_dummy1 NUMBER;
476 l_dummy2 NUMBER;
477 l_proc_status VARCHAR2(1) := 'C';
478 l_effective_date DATE;
479 l_err_code VARCHAR2(30);
480 l_err_stage VARCHAR2(30);
481 l_err_stack VARCHAR2(2000);
482 l_org_func_security VARCHAR2(1);
483 l_INVALID_STATUS EXCEPTION;
484 l_VALIDATION_ERROR EXCEPTION;
485 l_INVALID_DATE EXCEPTION;
486 l_PROJECT_CLOSED EXCEPTION;
487 l_UPDATE_NOT_ALLOWED EXCEPTION;
488 --
489 -- We create a new record type to store the error information
490 -- for each batch line that fails to validate. A PL/SQL table
491 -- will be used to store all the failed lines. We need to do
492 -- this because we want to process all the lines even if an
493 -- error has occured in one of the lines
494 --
495 TYPE ErrorRecTyp IS RECORD(
496 line_id NUMBER,
497 rejection_reason VARCHAR2(150) );
498
499 TYPE ErrorTabTyp IS TABLE OF ErrorRecTyp
500 INDEX BY BINARY_INTEGER;
501
502 l_Error_Tab ErrorTabTyp; -- error table
503 l_error_tab_index NUMBER := 0; -- table index counter
504
505 -- -----------------------
506 -- Cursor declarations
507 -- -----------------------
508 CURSOR l_batch_csr IS
509 SELECT batch_status_code, project_attribute, effective_date
510 FROM pa_mass_update_batches b
511 WHERE b.batch_id = X_Batch_ID
512 FOR UPDATE;
513
514 CURSOR l_BatchLines_csr IS
515 SELECT line_id,
516 project_id,
517 task_id,
518 old_attribute_value,
519 new_attribute_value,
520 update_flag,
521 recalculate_flag,
522 pa_security.allow_update(project_id) allow_update
523 FROM pa_mass_update_details
524 WHERE batch_id = X_Batch_ID
525 FOR UPDATE;
526
527 --
528 -- This cursor is used to retrieve the necessary project/
529 -- task information for attribute change validation.
530 -- Note that in the case where the task ID is NULL, all
531 -- the tasks for the project will be selected by the cursor.
532 -- However, we'll only fetch the first record and decode all
533 -- the task fields to NULL
534 --
535 CURSOR l_ProjTask_csr (x_project_id NUMBER,
536 x_task_id NUMBER ) IS
537 SELECT x_project_id PROJECT_ID,
538 x_task_id TASK_ID,
539 decode(x_task_id,
540 NULL, p.carrying_out_organization_id,
541 t.carrying_out_organization_id) OLD_VALUE,
542 p.project_type PROJECT_TYPE,
543 p.start_date PROJECT_START_DATE,
544 p.completion_date PROJECT_END_DATE,
545 pa_project_stus_utils.is_project_status_closed(
546 p.project_status_code) PROJECT_CLOSED,
547 p.public_sector_flag,
548 decode(x_task_id,
549 NULL, NULL,
550 t.task_manager_person_id) TASK_MANAGER_PERSON_ID,
551 decode(x_task_id,
552 NULL, NULL,
553 t.service_type_code) SERVICE_TYPE_CODE,
554 decode(x_task_id,
555 NULL, NULL,
556 t.start_date) TASK_START_DATE,
557 decode(x_task_id,
558 NULL, NULL,
559 t.completion_date) TASK_END_DATE,
560 decode(x_task_id,
561 NULL, p.attribute_category,
562 t.attribute_category) ATTRIBUTE_CATEGORY,
563 decode(x_task_id,
564 NULL, p.attribute1,
565 t.attribute1) ATTRIBUTE1,
566 decode(x_task_id,
567 NULL, p.attribute2,
568 t.attribute2) ATTRIBUTE2,
569 decode(x_task_id,
570 NULL, p.attribute3,
571 t.attribute3) ATTRIBUTE3,
572 decode(x_task_id,
573 NULL, p.attribute4,
574 t.attribute4) ATTRIBUTE4,
575 decode(x_task_id,
576 NULL, p.attribute5,
577 t.attribute5) ATTRIBUTE5,
578 decode(x_task_id,
579 NULL, p.attribute6,
580 t.attribute6) ATTRIBUTE6,
581 decode(x_task_id,
582 NULL, p.attribute7,
583 t.attribute7) ATTRIBUTE7,
584 decode(x_task_id,
585 NULL, p.attribute8,
586 t.attribute8) ATTRIBUTE8,
587 decode(x_task_id,
588 NULL, p.attribute9,
589 t.attribute9) ATTRIBUTE9,
590 decode(x_task_id,
591 NULL, p.attribute10,
592 t.attribute10) ATTRIBUTE10,
593 decode(x_task_id,
594 NULL, p.pm_product_code,
595 t.pm_product_code) PM_PRODUCT_CODE,
596 p.pm_project_reference,
597 decode(x_task_id,
598 NULL, NULL,
599 t.pm_task_reference) PM_TASK_REFERENCE
600 FROM pa_projects_all p,
601 pa_tasks t
602 WHERE p.project_id = x_project_id
603 AND t.project_id = p.project_id
604 AND ( x_task_id IS NULL
605 OR t.task_id = x_task_id );
606
607 l_ProjTask_rec l_ProjTask_csr%ROWTYPE;
608 l_warnings_only_flag VARCHAR2(1) := 'N'; --bug3134205
609 -- ---------------------------------------------------
610 -- Set_Error
611 -- This local procedure takes in an error code and
612 -- tries to retrieve the error message. The error
613 -- information is then stored in the error table.
614 -- ---------------------------------------------------
615 PROCEDURE Set_Error(p_err_code VARCHAR2,
616 p_line_id NUMBER) IS
617 l_error_msg VARCHAR2(150):= NULL;
618 l_error_number NUMBER := 0;
619 BEGIN
620 --
621 -- First check to see if this is a PA error
622 --
623 IF (SUBSTRB(p_err_code, 1, 3) = 'PA_') THEN
624 fnd_message.set_name('PA', p_err_code);
625 l_error_msg := SUBSTRB(fnd_message.get, 1, 150);
626 ELSE
627 --
628 -- Not a PA error; check to see if it's a SQL error
629 --
630 BEGIN
631 l_error_number := to_number(p_err_code);
632 l_error_msg := SUBSTRB(SQLERRM(l_error_number), 1, 150);
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 --
637 -- Not a SQL error either; just set the error msg to
638 -- the error code
639 --
640 l_error_msg := p_err_code;
641 END;
642 END IF;
643
644 l_error_tab_index := l_error_tab_index + 1;
645 l_Error_Tab(l_error_tab_index).line_id := p_line_id;
646 l_Error_Tab(l_error_tab_index).rejection_reason := l_error_msg;
647
648 END Set_Error;
649
650 BEGIN
651 ERRBUF := NULL;
652 RETCODE := '0';
653
654 OPEN l_batch_csr;
655 FETCH l_batch_csr
656 INTO l_batch_status_code, l_project_attribute, l_effective_date;
657 IF (l_batch_csr%NOTFOUND) THEN
658 CLOSE l_batch_csr;
659 raise NO_DATA_FOUND;
660 END IF;
661
662 --
663 -- If this procedure is being run as a concurrent process,
664 -- we need to make sure that the the status of the batch is
665 -- 'Submitted' and the effective date is before sysdate
666 --
667 IF (X_Concurrent = 'Y') THEN
668 IF (l_batch_status_code = 'S') THEN
669 IF (not (trunc(sysdate) >= trunc(nvl(l_effective_date, sysdate)))) THEN
670 CLOSE l_batch_csr;
671 raise l_INVALID_DATE;
672 END IF;
673 --
674 -- Change the status to 'Running'
675 --
676 UPDATE pa_mass_update_batches
677 SET batch_status_code = 'P',
678 rejection_code = NULL,
679 process_run_date = sysdate,
680 process_run_by = FND_GLOBAL.user_id,
681 request_id = FND_GLOBAL.Conc_Request_ID,
682 program_application_id = FND_GLOBAL.Prog_Appl_ID,
683 program_id = FND_GLOBAL.Conc_Program_ID,
684 last_update_login = FND_GLOBAL.Conc_Login_ID,
685 program_update_date = sysdate
686 WHERE CURRENT OF l_batch_csr;
687 --
688 -- Commit to release the lock
689 --
690 COMMIT;
691 ELSE
692 CLOSE l_batch_csr;
693 raise l_INVALID_STATUS;
694 END IF;
695 END IF;
696
697 CLOSE l_batch_csr;
698
699 -- Reset the rejection reason for all the lines
700 UPDATE pa_mass_update_details
701 SET rejection_reason = NULL,
702 last_updated_by = FND_GLOBAL.user_id,
703 last_update_date = sysdate,
704 last_update_login = FND_GLOBAL.login_id
705 WHERE batch_id = X_Batch_ID
706 AND rejection_reason IS NOT NULL;
707
708 --
709 -- Test the function security for Org changes
710 --
711 IF (fnd_function.test('PA_PAXPREPR_UPDATE_ORG') = TRUE) THEN
712 l_org_func_security := 'Y';
713 ELSE
714 l_org_func_security := 'N';
715 END IF;
716
717 --
718 -- We need to establish a savepoint here. Once all the
719 -- lines have been processed, if any error has occurred
720 -- all the changes will have to be rolled back because
721 -- we only apply changes on a ALL or NONE basis
722 --
723 SAVEPOINT Process_Batch;
724
725 --
726 -- Loop through all the lines to validate and process
727 --
728 FOR l_BatchLine_rec IN l_BatchLines_csr LOOP
729 BEGIN
730 IF (l_BatchLine_rec.update_flag = 'Y') THEN
731 --
732 -- Make sure user has proper security to update the project
733 --
734 IF (l_BatchLine_rec.allow_update = 'Y') THEN
735 null;
736 ELSE
737 raise l_UPDATE_NOT_ALLOWED;
738 END IF;
739
740 OPEN l_ProjTask_csr(l_BatchLine_rec.project_id,
741 l_BatchLine_rec.task_id);
742 FETCH l_ProjTask_csr INTO l_ProjTask_rec;
743 IF (l_ProjTask_csr%NOTFOUND) THEN
744 CLOSE l_ProjTask_csr;
745 raise NO_DATA_FOUND;
746 END IF;
747 CLOSE l_ProjTask_Csr;
748
749 --
750 -- Make sure project is not closed
751 --
752 IF (l_ProjTask_rec.project_closed = 'Y') THEN
753 raise l_PROJECT_CLOSED;
754 END IF;
755
756 --
757 -- Validate the attribute change to make sure that it is allowed
758 --
759 l_err_code := 0;
760 l_err_stage := NULL;
761 PA_PROJECT_UTILS2.Validate_Attribute_Change(
762 X_Context => 'ORGANIZATION_VALIDATION',
763 X_insert_update_mode => 'UPDATE',
764 X_calling_module => 'PAXBAUPD',
765 X_project_id => l_ProjTask_rec.project_id,
766 X_task_id => l_ProjTask_rec.task_id,
767 X_old_value => l_ProjTask_rec.old_value,
768 X_new_value => l_BatchLine_rec.new_attribute_value,
769 X_project_type => l_ProjTask_rec.project_type,
770 X_project_start_date => l_ProjTask_rec.project_start_date,
771 X_project_end_date => l_ProjTask_rec.project_end_date,
772 X_public_sector_flag => l_ProjTask_rec.public_sector_flag,
773 X_task_manager_person_id => l_ProjTask_rec.task_manager_person_id,
774 X_Service_type => l_ProjTask_rec.service_type_code,
775 X_task_start_date => l_ProjTask_rec.task_start_date,
776 X_task_end_date => l_ProjTask_rec.task_end_date,
777 X_entered_by_user_id => FND_GLOBAL.user_id,
778 X_attribute_category => l_ProjTask_rec.attribute_category,
779 X_attribute1 => l_ProjTask_rec.attribute1,
780 X_attribute2 => l_ProjTask_rec.attribute2,
781 X_attribute3 => l_ProjTask_rec.attribute3,
782 X_attribute4 => l_ProjTask_rec.attribute4,
783 X_attribute5 => l_ProjTask_rec.attribute5,
784 X_attribute6 => l_ProjTask_rec.attribute6,
785 X_attribute7 => l_ProjTask_rec.attribute7,
786 X_attribute8 => l_ProjTask_rec.attribute8,
787 X_attribute9 => l_ProjTask_rec.attribute9,
788 X_attribute10 => l_ProjTask_rec.attribute10,
789 X_pm_product_code => l_ProjTask_rec.pm_product_code,
790 X_pm_project_reference => l_ProjTask_rec.pm_project_reference,
791 X_pm_task_reference => l_ProjTask_rec.pm_task_reference,
792 X_functional_security_flag => l_org_func_security,
793 x_warnings_only_flag => l_warnings_only_flag, --bug3134205
794 X_err_code => l_err_code,
795 X_err_stage => l_err_stage,
796 X_err_stack => l_err_stack );
797
798 IF (l_err_stage IS NOT NULL) THEN
799 raise l_VALIDATION_ERROR;
800 END IF;
801
802 --
803 -- Now we can process the line
804 --
805 IF (l_project_attribute = 'ORGANIZATION') THEN
806
807 IF (l_BatchLine_rec.task_id IS NULL) THEN
808 IF (X_Concurrent = 'Y') THEN
809
810 UPDATE pa_projects_all
811 SET carrying_out_organization_id =
812 to_number(l_BatchLine_rec.new_attribute_value),
813 request_id = FND_GLOBAL.Conc_Request_ID,
814 program_application_id = FND_GLOBAL.Prog_Appl_ID,
815 program_id = FND_GLOBAL.Conc_Program_ID,
816 last_update_login = FND_GLOBAL.Conc_Login_ID,
817 program_update_date = sysdate
818 WHERE project_id = l_BatchLine_rec.project_id;
819
820 ELSE
821
822 UPDATE pa_projects_all
823 SET carrying_out_organization_id =
824 to_number(l_BatchLine_rec.new_attribute_value),
825 last_updated_by = FND_GLOBAL.User_ID,
826 last_update_login = FND_GLOBAL.Login_ID,
827 last_update_date = sysdate
828 WHERE project_id = l_BatchLine_rec.project_id;
829
830 END IF; -- (X_Concurrent = 'Y')
831 ELSE
832 IF (X_Concurrent = 'Y') THEN
833
834 UPDATE pa_tasks
835 SET carrying_out_organization_id =
836 to_number(l_BatchLine_rec.new_attribute_value),
837 request_id = FND_GLOBAL.Conc_Request_ID,
838 program_application_id = FND_GLOBAL.Prog_Appl_ID,
839 program_id = FND_GLOBAL.Conc_Program_ID,
840 last_update_login = FND_GLOBAL.Conc_Login_ID,
841 program_update_date = sysdate
842 WHERE task_id = l_BatchLine_rec.task_id;
843
844 /* Added for bug#5718668 by anuagraw */
845 UPDATE pa_proj_elements
846 SET carrying_out_organization_id =
847 to_number(l_BatchLine_rec.new_attribute_value),
848 request_id = FND_GLOBAL.Conc_Request_ID,
849 program_application_id = FND_GLOBAL.Prog_Appl_ID,
850 program_id = FND_GLOBAL.Conc_Program_ID,
851 last_update_login = FND_GLOBAL.Conc_Login_ID,
852 program_update_date = sysdate
853 WHERE proj_element_id = l_BatchLine_rec.task_id;
854 /* Added for bug#5718668 by anuagraw */
855
856 ELSE
857
858 UPDATE pa_tasks
859 SET carrying_out_organization_id =
860 to_number(l_BatchLine_rec.new_attribute_value),
861 last_updated_by = FND_GLOBAL.User_ID,
862 last_update_login = FND_GLOBAL.Login_ID,
863 last_update_date = sysdate
864 WHERE task_id = l_BatchLine_rec.task_id;
865
866 /* Added for bug#5718668 by anuagraw */
867 UPDATE pa_proj_elements
868 SET carrying_out_organization_id =
869 to_number(l_BatchLine_rec.new_attribute_value),
870 last_updated_by = FND_GLOBAL.User_ID,
871 last_update_login = FND_GLOBAL.Login_ID,
872 last_update_date = sysdate
873 WHERE proj_element_id = l_BatchLine_rec.task_id;
874 /* Added for bug#5718668 by anuagraw */
875
876
877 END IF; -- (X_Concurrent = 'Y')
878 END IF; -- (l_BatchLine_rec.task_id IS NULL)
879 END IF; -- (l_project_attribute = 'ORGANIZATION')
880
881 --
882 -- See if we need to perform recalculation
883 --
884 IF (l_BatchLine_rec.recalculate_flag = 'Y') THEN
885
886 PA_ADJUSTMENTS.MassAdjust(
887 X_adj_action => 'COST AND REV RECALC',
888 X_module => 'PAXBAUPD',
889 X_user => FND_GLOBAL.User_ID,
890 X_login => FND_GLOBAL.Login_ID,
891 X_project_id => l_BatchLine_rec.project_id,
892 X_task_id => l_BatchLine_rec.task_id,
893 X_ei_date_low => l_effective_date,
894 X_outcome => l_mass_adj_outcome,
895 X_num_processed => l_dummy1,
896 X_num_rejected => l_dummy2 );
897
898
899 /* Added the following call to process Cross Charge Txn */
900 /* Code Starts here */
901
902 PA_ADJUSTMENTS.MassAdjust(
903 X_adj_action => 'REPROCESS CROSS CHARGE',
904 X_module => 'PAXBAUPD',
905 X_user => FND_GLOBAL.User_ID,
906 X_login => FND_GLOBAL.Login_ID,
907 X_project_id => l_BatchLine_rec.project_id,
908 X_task_id => l_BatchLine_rec.task_id,
909 X_ei_date_low => l_effective_date,
910 X_outcome => l_mass_adj_outcome,
911 X_num_processed => l_dummy1,
912 X_num_rejected => l_dummy2 );
913
914 /* Code ends here */
915
916
917 /* Bug 3421201 :Added the following call to process Recalculate
918 burden cost for 'VI' transactions */
919 /* Code Starts here */
920
921 PA_ADJUSTMENTS.MassAdjust(
922 X_adj_action => 'INDIRECT COST RECALC',
923 X_module => 'PAXBAUPD',
924 X_user => FND_GLOBAL.User_ID,
925 X_login => FND_GLOBAL.Login_ID,
926 X_project_id => l_BatchLine_rec.project_id,
927 X_task_id => l_BatchLine_rec.task_id,
928 X_system_linkage => 'VI',
929 X_ei_date_low => l_effective_date,
930 X_outcome => l_mass_adj_outcome,
931 X_num_processed => l_dummy1,
932 X_num_rejected => l_dummy2 );
933
934 /* Code ends here */
935
936
937
938
939
940 END IF; -- (l_BatchLine_rec.recalculate_flag = 'Y')
941 END IF; -- (l_BatchLine_rec.update_flag = 'Y')
942
943 EXCEPTION
944 WHEN l_PROJECT_CLOSED THEN
945 l_err_stage := 'PA_MU_PROJECT_CLOSED';
946 Set_Error(l_err_stage, l_BatchLine_rec.line_id);
947
948 WHEN l_VALIDATION_ERROR THEN
949 Set_Error(l_err_stage, l_BatchLine_rec.line_id);
950
951 WHEN l_UPDATE_NOT_ALLOWED THEN
952 l_err_stage := 'PA_PR_UPDATE_NOT_ALLOWED';
953 Set_Error(l_err_stage, l_BatchLine_rec.line_id);
954
955 WHEN OTHERS THEN
956 l_error_tab_index := l_error_tab_index + 1;
957 l_Error_Tab(l_error_tab_index).line_id := l_BatchLine_rec.line_id;
958 l_Error_Tab(l_error_tab_index).rejection_reason := SUBSTRB(SQLERRM, 1, 150);
959
960 END;
961 END LOOP;
962
963 --
964 -- Check to see if there are any errors
965 --
966 IF (l_error_tab_index > 0) THEN
967 --
968 -- Roll back the previous changes and update the error
969 -- information in each of the batch lines that failed
970 --
971 ROLLBACK TO Process_Batch;
972
973 FOR i IN l_Error_Tab.FIRST .. l_Error_Tab.LAST LOOP
974 UPDATE pa_mass_update_details
975 SET rejection_reason = l_Error_Tab(i).rejection_reason,
976 last_updated_by = FND_GLOBAL.user_id,
977 last_update_date = sysdate,
978 last_update_login = FND_GLOBAL.login_id
979 WHERE batch_id = X_Batch_ID
980 AND line_id = l_Error_Tab(i).line_id;
981 END LOOP;
982 --
983 -- Set the status to 'Rejected'
984 --
985 l_proc_status := 'R';
986 RETCODE := '1';
987 ERRBUF := 'LINES_REJECTED';
988 END IF;
989
990 IF (X_Concurrent = 'Y') THEN
991 --
992 -- Update the batch status
993 --
994 UPDATE pa_mass_update_batches
995 SET batch_status_code = l_proc_status,
996 rejection_code = ERRBUF,
997 process_run_by = FND_GLOBAL.user_id,
998 process_run_date = sysdate,
999 program_application_id = FND_GLOBAL.Prog_Appl_ID,
1000 program_id = FND_GLOBAL.Conc_Program_ID,
1001 last_update_login = FND_GLOBAL.Conc_Login_ID,
1002 program_update_date = sysdate
1003 WHERE batch_id = X_Batch_ID;
1004
1005 COMMIT;
1006
1007 END IF;
1008
1009 EXCEPTION
1010 WHEN l_INVALID_STATUS THEN
1011 --
1012 -- Should only happen when run as a concurrent program
1013 -- Reset the status back to submitted
1014 --
1015 IF (X_All_Batches = 'N') THEN
1016 RETCODE := '1';
1017 ERRBUF := 'INVALID_STATUS';
1018 UPDATE pa_mass_update_batches
1019 SET batch_status_code = 'R',
1020 rejection_code = 'INVALID_STATUS',
1021 process_run_by = FND_GLOBAL.user_id,
1022 process_run_date = sysdate,
1023 program_application_id = FND_GLOBAL.Prog_Appl_ID,
1024 program_id = FND_GLOBAL.Conc_Program_ID,
1025 last_update_login = FND_GLOBAL.Conc_Login_ID,
1026 program_update_date = sysdate
1027 WHERE batch_id = X_Batch_ID;
1028 COMMIT;
1029 END IF;
1030
1031 WHEN l_INVALID_DATE THEN
1032 --
1033 -- Should only happen when run as a concurrent program
1034 -- Reset the status back to submitted
1035 --
1036 IF (X_All_Batches = 'N') THEN
1037 RETCODE := '1';
1038 ERRBUF := 'EFFECTIVE_DATE';
1039 UPDATE pa_mass_update_batches
1040 SET batch_status_code = 'R',
1041 rejection_code = 'EFFECTIVE_DATE',
1042 process_run_by = FND_GLOBAL.user_id,
1043 process_run_date = sysdate,
1044 program_application_id = FND_GLOBAL.Prog_Appl_ID,
1045 program_id = FND_GLOBAL.Conc_Program_ID,
1046 last_update_login = FND_GLOBAL.Conc_Login_ID,
1047 program_update_date = sysdate
1048 WHERE batch_id = X_Batch_ID;
1049 COMMIT;
1050 END IF;
1051
1052 WHEN OTHERS THEN
1053 IF (l_batch_csr%ISOPEN) THEN
1054 CLOSE l_batch_csr;
1055 END IF;
1056
1057 RETCODE := '1';
1058 ERRBUF := 'SQL_ERROR';
1059 IF (X_Concurrent = 'Y') THEN
1060 UPDATE pa_mass_update_batches
1061 SET batch_status_code = 'R',
1062 rejection_code = 'SQL_ERROR',
1063 process_run_by = FND_GLOBAL.user_id,
1064 process_run_date = sysdate,
1065 program_application_id = FND_GLOBAL.Prog_Appl_ID,
1066 program_id = FND_GLOBAL.Conc_Program_ID,
1067 last_update_login = FND_GLOBAL.Conc_Login_ID,
1068 program_update_date = sysdate
1069 WHERE batch_id = X_Batch_ID;
1070 COMMIT;
1071 END IF;
1072 END Process;
1073
1074
1075 END PA_MU_BATCHES_V_PKG;