1 package body PA_MU_DETAILS_V_PKG as
2 -- $Header: PAXBAULB.pls 120.2 2006/02/13 14:59:53 dlanka noship $
3
4
5 PROCEDURE Insert_Row( X_Rowid IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6 X_Line_ID IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
7 X_Batch_ID NUMBER,
8 X_Creation_Date DATE,
9 X_Created_By NUMBER,
10 X_Last_Updated_By NUMBER,
11 X_Last_Update_Date DATE,
12 X_Last_Update_Login NUMBER,
13 X_Project_ID NUMBER,
14 X_Task_ID NUMBER,
15 X_Old_Attribute_Value VARCHAR2,
16 X_New_Attribute_Value VARCHAR2,
17 X_Update_Flag VARCHAR2,
18 X_Recalculate_Flag VARCHAR2 )
19 IS
20 l_line_id NUMBER;
21 BEGIN
22
23 SELECT PA_MASS_UPDATE_DETAILS_S.NextVal
24 INTO l_line_id
25 FROM dual;
26
27 INSERT INTO PA_MASS_UPDATE_DETAILS
28 ( Batch_ID,
29 Line_ID,
30 Project_ID,
31 Task_ID,
32 Old_Attribute_Value,
33 New_Attribute_Value,
34 Update_Flag,
35 Recalculate_Flag,
36 Creation_Date,
37 Created_By,
38 Last_Update_Date,
39 Last_Updated_By,
40 Last_Update_Login )
41 VALUES
42 ( X_batch_id,
43 l_line_id,
44 X_Project_ID,
45 X_Task_ID,
46 X_Old_Attribute_Value,
47 X_New_Attribute_Value,
48 X_Update_Flag,
49 X_Recalculate_Flag,
50 X_Creation_Date,
51 X_Created_By,
52 X_Last_Update_Date,
53 X_Last_Updated_By,
54 X_Last_Update_Login
55 );
56
57 X_Line_ID := l_line_id;
58
59 SELECT rowid INTO X_Rowid
60 FROM PA_MASS_UPDATE_DETAILS
61 WHERE batch_id = X_batch_id
62 AND line_id = l_line_id;
63
64 END Insert_Row;
65
66
67 PROCEDURE Update_Row( X_Rowid VARCHAR2,
68 X_Last_Updated_By NUMBER,
69 X_Last_Update_Date DATE,
70 X_Last_Update_Login NUMBER,
71 X_Project_ID NUMBER,
72 X_Task_ID NUMBER,
73 X_Old_Attribute_Value VARCHAR2,
74 X_New_Attribute_Value VARCHAR2,
75 X_Update_Flag VARCHAR2,
76 X_Recalculate_Flag VARCHAR2,
77 X_Rejection_Reason VARCHAR2 )
78 IS
79
80 BEGIN
81
82 UPDATE pa_mass_update_details
83 SET
84 Project_ID = X_Project_ID,
85 Task_ID = X_Task_ID,
86 Old_Attribute_Value = X_Old_Attribute_Value,
87 New_Attribute_Value = X_New_Attribute_Value,
88 Update_Flag = X_Update_Flag,
89 Recalculate_Flag = X_Recalculate_Flag,
90 Rejection_Reason = X_Rejection_Reason,
91 Last_Update_Date = X_Last_Update_Date,
92 Last_Updated_By = X_Last_Updated_By,
93 Last_Update_Login = X_Last_Update_Login
94 WHERE
95 rowid = X_Rowid;
96
97 IF (SQL%NOTFOUND) THEN
98 raise NO_DATA_FOUND;
99 END IF;
100
101 END Update_Row;
102
103
104
105 PROCEDURE Lock_Row( X_Rowid VARCHAR2,
106 X_Project_ID NUMBER,
107 X_Task_ID NUMBER,
108 X_Old_Attribute_Value VARCHAR2,
109 X_New_Attribute_Value VARCHAR2,
110 X_Update_Flag VARCHAR2,
111 X_Recalculate_Flag VARCHAR2,
112 X_Rejection_Reason VARCHAR2 )
113 IS
114
115 CURSOR l_line_csr IS
116 SELECT Project_ID,
117 Task_ID,
118 Old_Attribute_Value,
119 New_Attribute_Value,
120 Update_Flag,
121 Recalculate_Flag,
122 Rejection_Reason
123 FROM pa_mass_update_details
124 WHERE rowid = X_rowid
125 FOR UPDATE NOWAIT;
126
127 l_line_rec l_line_csr%ROWTYPE;
128
129 BEGIN
130
131 OPEN l_line_csr;
132 FETCH l_line_csr into l_line_rec;
133 IF (l_line_csr%NOTFOUND) THEN
134 CLOSE l_line_csr;
135 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
136 APP_EXCEPTION.Raise_Exception;
137 END IF;
138 CLOSE l_line_csr;
139
140 IF (
141 (l_line_rec.project_id = X_Project_ID)
142 AND ( (l_line_rec.task_id = X_Task_ID)
143 OR ( l_line_rec.task_id IS NULL
144 AND X_Task_ID IS NULL ) )
145 AND ( (l_line_rec.old_attribute_value = X_Old_Attribute_Value)
146 OR ( l_line_rec.old_attribute_value IS NULL
147 AND X_Old_Attribute_Value IS NULL ) )
148 AND ( (l_line_rec.new_attribute_value = X_New_Attribute_Value)
149 OR ( l_line_rec.new_attribute_value IS NULL
150 AND X_New_Attribute_Value IS NULL ) )
151 AND ( (l_line_rec.update_flag = X_Update_Flag)
155 OR ( l_line_rec.recalculate_flag IS NULL
152 OR ( l_line_rec.update_flag IS NULL
153 AND X_Update_Flag IS NULL ) )
154 AND ( (l_line_rec.recalculate_flag = X_Recalculate_Flag)
156 AND X_Recalculate_Flag IS NULL ) ) ) THEN
157
158 return;
159
160 ELSE
161
162 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
163 APP_EXCEPTION.Raise_Exception;
164
165 END IF;
166
167 END Lock_Row;
168
169
170
171 PROCEDURE Delete_Row( X_Rowid VARCHAR2 )
172 IS
173 BEGIN
174
175 DELETE FROM pa_mass_update_details
176 WHERE rowid = X_Rowid;
177
178 IF (SQL%NOTFOUND) THEN
179 Raise NO_DATA_FOUND;
180 END IF;
181
182 END Delete_Row;
183
184
185 -- ----------------------------------------------------------
186 -- Generate_Lines
187 -- This procedure is used to generate detail lines for the
188 -- given criteria.
189 -- ----------------------------------------------------------
190
191 PROCEDURE Generate_Lines(
192 X_Batch_ID NUMBER,
193 X_Project_Selection VARCHAR2,
194 X_Search_Project_ID NUMBER Default NULL,
195 X_Search_Organization_ID NUMBER Default NULL,
196 X_Task_Selection VARCHAR2,
197 X_New_Organization_ID NUMBER Default NULL,
198 X_Recalculate_Flag VARCHAR2 Default 'Y',
199 X_Err_Code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
200 X_Err_Stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
201 X_Err_Stack IN OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
202 IS
203
204 l_old_stack VARCHAR2(630);
205 l_user_id NUMBER := FND_GLOBAL.User_ID;
206 l_login_id NUMBER := FND_GLOBAL.Login_ID;
207 l_rowid VARCHAR2(18);
208 l_line_id NUMBER;
209 l_date DATE := sysdate;
210 l_proj_organization_id NUMBER;
211 l_update_flag VARCHAR2(1);
212 l_allow_update VARCHAR2(1);
213 l_recalc_flag VARCHAR2(1);
214
215 --
216 -- Cursor for selecting all the tasks for the given project
217 --
218 CURSOR l_ProjTasks_csr (p_project_id NUMBER) IS
219 SELECT task_id, carrying_out_organization_id organization_id
220 FROM pa_tasks
221 WHERE project_id = p_project_id;
222
223 --
224 -- Cursor for selecting all the tasks that belongs to the organization
225 -- of the given project
226 --
227 CURSOR l_OrgTasks_csr (x_project_id NUMBER) IS
228 SELECT t.task_id, t.carrying_out_organization_id organization_id
229 FROM pa_tasks t, pa_projects p
230 WHERE t.carrying_out_organization_id = p.carrying_out_organization_id
231 AND t.project_id = p.project_id
232 AND p.project_id = x_project_id;
233
234 --
235 -- Cursor for selecting all the projects that belongs to the organization
236 -- specified by the X_Search_Organization_ID parameter
237 --
238 CURSOR l_proj_csr IS
239 SELECT p.project_id, p.carrying_out_organization_id organization_id,
240 pa_security.allow_update(p.project_id) allow_update
241 FROM pa_projects p
242 WHERE
243 -- p.project_status_code <> 'CLOSED'. **** Archive Purge changes ****
244 pa_project_stus_utils.is_project_status_closed(p.project_status_code) <> 'Y'
245 AND p.carrying_out_organization_id = X_Search_Organization_ID
246 AND pa_security.Allow_Query(p.project_id) = 'Y';
247
248 --
249 -- Line_Exists
250 -- This is a local function that will return whether or not the given
251 -- Project/Task combination exists in the given batch.
252 --
253 -- Commenting out the function as part of bug fix : 4629597
254 /*
255 FUNCTION Line_Exists(
256 X_Batch_ID NUMBER,
257 X_Project_ID NUMBER,
258 X_Task_ID NUMBER DEFAULT NULL )
259 RETURN BOOLEAN IS
260
261 l_dummy VARCHAR2(1);
262
263 CURSOR l_line_csr IS
264 SELECT 'x'
265 FROM pa_mass_update_details
266 WHERE batch_id = X_Batch_ID
267 AND project_id = X_Project_ID
268 AND ( ( task_id IS NULL
269 AND X_Task_ID IS NULL)
270 OR (task_id = X_Task_ID));
271
272 BEGIN
273
274 OPEN l_line_csr;
275 FETCH l_line_csr INTO l_dummy;
276 IF (l_line_csr%NOTFOUND) THEN
277 CLOSE l_line_csr;
278 return FALSE;
279 END IF;
280 CLOSE l_line_csr;
281 return TRUE;
282
283 END Line_Exists; */
284
285 BEGIN
286 --
287 -- Initialize error information
288 --
289 X_Err_Code := 0;
290 l_old_stack := X_Err_Stack;
291 X_Err_Stage := 'generate detail lines <' || to_char(X_Batch_ID) || '><' ||
292 X_Project_Selection || '><' || to_char(X_Search_Project_ID) ||
293 to_char(X_Search_Organization_ID) || X_Task_Selection || '><' ||
294 to_char(X_New_Organization_ID);
295
296 --Bug 4629597. Delete the cached Lines. Please see the bug for more details
297 DELETE
298 FROM pa_mass_update_details
299 WHERE batch_id=X_Batch_ID;
300
301 IF (X_Project_Selection = 'NAME') THEN
302 --
303 -- We are selecting the project by name (project_id)
304 --
305
306 -- Get the organization_id of the given project
307 SELECT carrying_out_organization_id,
308 pa_security.allow_update(project_id)
309 INTO l_proj_organization_id, l_allow_update
313 --Bug 4629597.
310 FROM pa_projects
311 WHERE project_id = X_Search_Project_ID;
312
314 --IF (NOT (line_exists(X_Batch_ID, X_Search_Project_ID, NULL))) THEN
315
316 --
317 -- If the old and the new organization are the same, we still
318 -- create the line; however, the Update_Flag and the
319 -- Recalculate_Flag will be set to 'N'
320 --
321 IF (l_allow_update = 'N') OR
322 (l_proj_organization_id = X_New_Organization_ID) THEN
323 l_update_flag := 'N';
324 l_recalc_flag := 'N';
325 ELSE
326 l_update_flag := 'Y';
327 l_recalc_flag := X_Recalculate_Flag;
328 END IF;
329
330 INSERT_ROW(
331 X_Rowid => l_rowid,
332 X_Line_ID => l_line_id,
333 X_Batch_ID => X_Batch_ID,
334 X_Creation_Date => l_date,
335 X_Created_By => l_user_id,
336 X_Last_Updated_By => l_user_id,
337 X_Last_Update_Date => l_date,
338 X_Last_Update_Login => l_login_id,
339 X_Project_ID => X_Search_Project_ID,
340 X_Task_ID => NULL,
341 X_Old_Attribute_Value => to_char(l_proj_organization_id),
342 X_New_Attribute_Value => to_char(X_New_Organization_ID),
343 X_Update_Flag => l_update_flag,
344 X_Recalculate_Flag => l_recalc_flag );
345
346 -- END IF; -- Fix for bug : 4629597
347
348 IF (X_Task_Selection = 'ALL') THEN
349 --
350 -- We need to generate a line for all the tasks of the
351 -- given project
352 --
353 FOR l_task_rec IN l_ProjTasks_csr(X_Search_Project_ID) LOOP
354 -- Commenting out the function as part of bug fix : 4629597
355 /* IF (NOT (line_exists(X_Batch_ID,
356 X_Search_Project_ID,
357 l_task_rec.task_id))) THEN */
358
359 --
360 -- If the old and the new organization are the same, we still
361 -- create the line; however, the Update_Flag and the
362 -- Recalculate_Flag will be set to 'N'
363 --
364 IF (l_allow_update = 'N') OR
365 (l_task_rec.organization_id = X_New_Organization_ID) THEN
366 l_update_flag := 'N';
367 l_recalc_flag := 'N';
368 ELSE
369 l_update_flag := 'Y';
370 l_recalc_flag := X_Recalculate_Flag;
371 END IF;
372
373 INSERT_ROW(
374 X_Rowid => l_rowid,
375 X_Line_ID => l_line_id,
376 X_Batch_ID => X_Batch_ID,
377 X_Creation_Date => l_date,
378 X_Created_By => l_user_id,
379 X_Last_Updated_By => l_user_id,
380 X_Last_Update_Date => l_date,
381 X_Last_Update_Login => l_login_id,
382 X_Project_ID => X_Search_Project_ID,
383 X_Task_ID => l_task_rec.task_id,
384 X_Old_Attribute_Value => to_char(l_task_rec.organization_id),
385 X_New_Attribute_Value => to_char(X_New_Organization_ID),
386 X_Update_Flag => l_update_flag,
387 X_Recalculate_Flag => l_recalc_flag );
388
389 -- END IF; -- Fix for bug : 4629597
390
391 END LOOP;
392
393 ELSIF (X_Task_Selection = 'ORG') THEN
394 --
395 -- We need to generate a line for all the tasks with the same
396 -- organization as the given project
397 --
398 FOR l_task_rec IN l_OrgTasks_csr(X_Search_Project_ID) LOOP
399
400 -- Commenting out the function as part of bug fix : 4629597
401 /* IF (NOT (line_exists(X_Batch_ID,
402 X_Search_Project_ID,
403 l_task_rec.task_id))) THEN */
404
405 --
406 -- If the old and the new organization are the same, we still
407 -- create the line; however, the Update_Flag and the
408 -- Recalculate_Flag will be set to 'N'
409 --
410 IF (l_allow_update = 'N') OR
411 (l_task_rec.organization_id = X_New_Organization_ID) THEN
412 l_update_flag := 'N';
413 l_recalc_flag := 'N';
414 ELSE
415 l_update_flag := 'Y';
416 l_recalc_flag := X_Recalculate_Flag;
417 END IF;
418
419 INSERT_ROW(
420 X_Rowid => l_rowid,
421 X_Line_ID => l_line_id,
422 X_Batch_ID => X_Batch_ID,
423 X_Creation_Date => l_date,
424 X_Created_By => l_user_id,
425 X_Last_Updated_By => l_user_id,
426 X_Last_Update_Date => l_date,
427 X_Last_Update_Login => l_login_id,
428 X_Project_ID => X_Search_Project_ID,
429 X_Task_ID => l_task_rec.task_id,
430 X_Old_Attribute_Value => to_char(l_task_rec.organization_id),
431 X_New_Attribute_Value => to_char(X_New_Organization_ID),
432 X_Update_Flag => l_update_flag,
433 X_Recalculate_Flag => l_recalc_flag );
434
435 -- END IF; -- Fix for bug : 4629597
436
437 END LOOP;
438
439 END IF;
440
441 ELSIF (X_Project_Selection = 'ORG') THEN
442 --
443 -- We are selecting the project by organization. Use a cursor
444 -- loop to go through all the projects with the given organization
445 --
446 FOR l_proj_rec IN l_proj_csr LOOP
447 -- Commenting out the function as part of bug fix : 4629597
448 /* IF (NOT (line_exists(X_Batch_ID,
449 l_proj_rec.project_id,
450 NULL))) THEN */
451
452 --
453 -- If the old and the new organization are the same, we still
454 -- create the line; however, the Update_Flag and the
455 -- Recalculate_Flag will be set to 'N'
456 --
457 IF (l_proj_rec.allow_update = 'N') OR
458 (X_Search_Organization_ID = X_New_Organization_ID) THEN
459 l_update_flag := 'N';
460 l_recalc_flag := 'N';
461 ELSE
462 l_update_flag := 'Y';
463 l_recalc_flag := X_Recalculate_Flag;
464 END IF;
465
466 INSERT_ROW(
467 X_Rowid => l_rowid,
468 X_Line_ID => l_line_id,
469 X_Batch_ID => X_Batch_ID,
470 X_Creation_Date => l_date,
471 X_Created_By => l_user_id,
472 X_Last_Updated_By => l_user_id,
473 X_Last_Update_Date => l_date,
474 X_Last_Update_Login => l_login_id,
475 X_Project_ID => l_proj_rec.project_id,
476 X_Task_ID => NULL,
477 X_Old_Attribute_Value => to_char(X_Search_Organization_ID),
478 X_New_Attribute_Value => to_char(X_New_Organization_ID),
479 X_Update_Flag => l_update_flag,
480 X_Recalculate_Flag => l_recalc_flag );
481
482 -- END IF; -- Fix for bug : 4629597
483
484 IF (X_Task_Selection = 'ALL') THEN
485 --
486 -- We need to generate a line for all the tasks of the
487 -- current project in the loop
488 --
489 FOR l_task_rec IN l_ProjTasks_csr(l_proj_rec.project_id) LOOP
490 -- Commenting out the function as part of bug fix : 4629597
491 /* IF (NOT (line_exists(X_Batch_ID,
492 l_proj_rec.project_id,
493 l_task_rec.task_id))) THEN */
494
495 --
496 -- If the old and the new organization are the same, we still
497 -- create the line; however, the Update_Flag and the
498 -- Recalculate_Flag will be set to 'N'
499 --
500 IF (l_proj_rec.allow_update = 'N') OR
501 (l_task_rec.organization_id = X_New_Organization_ID) THEN
502 l_update_flag := 'N';
503 l_recalc_flag := 'N';
504 ELSE
505 l_update_flag := 'Y';
506 l_recalc_flag := X_Recalculate_Flag;
507 END IF;
508
509 INSERT_ROW(
510 X_Rowid => l_rowid,
511 X_Line_ID => l_line_id,
512 X_Batch_ID => X_Batch_ID,
513 X_Creation_Date => l_date,
514 X_Created_By => l_user_id,
515 X_Last_Updated_By => l_user_id,
516 X_Last_Update_Date => l_date,
517 X_Last_Update_Login => l_login_id,
518 X_Project_ID => l_proj_rec.project_id,
519 X_Task_ID => l_task_rec.task_id,
520 X_Old_Attribute_Value => to_char(l_task_rec.organization_id),
521 X_New_Attribute_Value => to_char(X_New_Organization_ID),
522 X_Update_Flag => l_update_flag,
523 X_Recalculate_Flag => l_recalc_flag );
524
525 -- END IF; -- Fix for bug : 4629597
526
527 END LOOP;
528
529 ELSIF (X_Task_Selection = 'ORG') THEN
530 --
531 -- We need to generate a line for all the tasks with the same
532 -- organization as the current project in the loop
533 --
534 FOR l_task_rec IN l_OrgTasks_csr(l_proj_rec.project_id) LOOP
535
536 /* IF (NOT (line_exists(X_Batch_ID,
537 l_proj_rec.project_id,
538 l_task_rec.task_id))) THEN */
539
540 --
541 -- If the old and the new organization are the same, we still
542 -- create the line; however, the Update_Flag and the
543 -- Recalculate_Flag will be set to 'N'
544 --
545 IF (l_proj_rec.allow_update = 'N') OR
546 (l_task_rec.organization_id = X_New_Organization_ID) THEN
547 l_update_flag := 'N';
548 l_recalc_flag := 'N';
549 ELSE
550 l_update_flag := 'Y';
551 l_recalc_flag := X_Recalculate_Flag;
552 END IF;
553
554 INSERT_ROW(
555 X_Rowid => l_rowid,
556 X_Line_ID => l_line_id,
557 X_Batch_ID => X_Batch_ID,
558 X_Creation_Date => l_date,
559 X_Created_By => l_user_id,
560 X_Last_Updated_By => l_user_id,
561 X_Last_Update_Date => l_date,
562 X_Last_Update_Login => l_login_id,
563 X_Project_ID => l_proj_rec.project_id,
564 X_Task_ID => l_task_rec.task_id,
565 X_Old_Attribute_Value => to_char(l_task_rec.organization_id),
566 X_New_Attribute_Value => to_char(X_New_Organization_ID),
567 X_Update_Flag => l_update_flag,
568 X_Recalculate_Flag => l_recalc_flag );
569
570 -- END IF; -- Fix for bug : 4629597
571
572 END LOOP;
573
574 END IF;
575
576 END LOOP;
577
578 END IF;
579
580 X_Err_Stack := l_old_stack;
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 X_Err_Code := SQLCODE;
585
586 END Generate_Lines;
587
588
589 END PA_MU_DETAILS_V_PKG;
[Home] [Help]