[Home] [Help]
PACKAGE BODY: APPS.PA_PRJ_PROGRESS_REPORTS_PKG
Source
1 PACKAGE BODY PA_PRJ_PROGRESS_REPORTS_PKG AS
2 /* $Header: PAPJXPRB.pls 120.1 2005/08/19 16:41:25 mwasowic noship $ */
3 /* APIs for Project exchange progress table */
4 Function is_valid_progress_code(p_progress_code IN varchar2)
5 return BOOLEAN
6 is
7 dummy number;
8 Begin
9 select 1 into dummy
10 from dual
11 where exists (select 1
12 from pa_lookups
13 where lookup_type = 'PA_XC_PROGRESS_STATUS'
14 and lookup_code = p_progress_code);
15 return TRUE;
16 Exception
17 when no_data_found then
18 return FALSE;
19 End is_valid_progress_code;
20
21 /* Public API */
22 /** Commented for progress update
23 PROCEDURE update_progress_report(
24 P_USER_ID IN NUMBER
25 ,P_COMMIT_FLAG IN VARCHAR2 default 'N'
26 ,P_DEBUG_MODE IN VARCHAR2 default 'N'
27 ,P_PROJECT_ID_OLD NUMBER := null
28 ,P_TASK_ID_OLD NUMBER := null
29 ,P_PROGRESS_STATUS_CODE_OLD VARCHAR2 := null
30 ,P_SHORT_DESCRIPTION_OLD VARCHAR2 := null
31 ,P_PROGRESS_ASOF_DATE_OLD VARCHAR2 := null
32 ,P_LONG_DESCRIPTION_OLD VARCHAR2 := null
33 ,P_ISSUES_OLD VARCHAR2 := null
34 ,P_ESTIMATED_START_DATE_OLD VARCHAR2 := null
35 ,P_ESTIMATED_END_DATE_OLD VARCHAR2 := null
36 ,P_ACTUAL_START_DATE_OLD VARCHAR2 := null
37 ,P_ACTUAL_END_DATE_OLD VARCHAR2 := null
38 ,P_PERCENT_COMPLETE_OLD NUMBER := null
39 ,P_ESTIMATE_TO_COMPLETE_OLD NUMBER := null
40 ,P_UNIT_TYPE_OLD VARCHAR2 := null
41 ,p_wf_status_code_old VARCHAR2 := null
42 ,p_wf_item_type_old VARCHAR2 := null
43 ,p_wf_item_key_old NUMBER := NULL
44 ,p_wf_process_old VARCHAR2 := null
45 ,P_PROJECT_ID_NEW NUMBER := null
46 ,P_TASK_ID_NEW NUMBER := null
47 ,P_PROGRESS_STATUS_CODE_NEW VARCHAR2 := null
48 ,P_SHORT_DESCRIPTION_NEW VARCHAR2 := null
49 ,P_PROGRESS_ASOF_DATE_NEW VARCHAR2 := null
50 ,P_LONG_DESCRIPTION_NEW VARCHAR2 := null
51 ,P_ISSUES_NEW VARCHAR2 := null
52 ,P_ESTIMATED_START_DATE_NEW VARCHAR2 := null
53 ,P_ESTIMATED_END_DATE_NEW VARCHAR2 := null
54 ,P_ACTUAL_START_DATE_NEW VARCHAR2 := null
55 ,P_ACTUAL_END_DATE_NEW VARCHAR2 := null
56 ,P_PERCENT_COMPLETE_NEW NUMBER := null
57 ,P_ESTIMATE_TO_COMPLETE_NEW NUMBER := null
58 ,P_UNIT_TYPE_NEW VARCHAR2 := null
59 ,p_wf_status_code_new VARCHAR2 := null
60 ,p_wf_item_type_new VARCHAR2 := null
61 ,p_wf_item_key_new NUMBER := null
62 ,p_wf_process_new VARCHAR2 := null
63 ,p_create_item_key_flag VARCHAR2 := 'N'
64 ,x_item_key OUT number
65 ,X_RETURN_STATUS OUT VARCHAR2
66 ,X_MSG_COUNT IN OUT NUMBER
67 ,X_MSG_DATA IN OUT pa_vc_1000_2000
68 )
69 IS
70 CURSOR C IS
71 SELECT *
72 FROM PA_PROJ_PROGRESS_REPORTS
73 WHERE project_id = P_project_id_old
74 AND task_id = p_task_id_old
75 FOR UPDATE of progress_status_code NOWAIT;
76 Recinfo C%ROWTYPE;
77 SAVEPOINT_TAG varchar2(60);
78 l_short_description_old PA_PROJ_PROGRESS_REPORTS.short_description%TYPE;
79 l_progress_asof_date_old date;
80 -- l_progress_asof_date_new date;
81 l_long_description_old PA_PROJ_PROGRESS_REPORTS.long_description%TYPE;
82 l_issues_old PA_PROJ_PROGRESS_REPORTS.issues%TYPE;
83 l_estimated_start_date_old date;
84 l_estimated_end_date_old date;
85 l_actual_start_date_old date;
86 l_actual_end_date_old date;
87 l_percent_complete_old PA_PROJ_PROGRESS_REPORTS.percent_complete%TYPE;
88 l_estimate_to_complete_old PA_PROJ_PROGRESS_REPORTS.estimate_to_complete%TYPE;
89 l_unit_type_old PA_PROJ_PROGRESS_REPORTS.unit_type%TYPE;
90 l_wf_status_code_old PA_PROJ_PROGRESS_REPORTS.wf_status_code%TYPE;
91 --l_wf_item_type_old PA_PROJ_PROGRESS_REPORTS.wf_item_type%TYPE;
92 --l_wf_item_key_old PA_PROJ_PROGRESS_REPORTS.wf_item_key%TYPE;
93 --l_wf_process_old PA_PROJ_PROGRESS_REPORTS.wf_process%TYPE;
94
95 l_wf_item_key_new number;
96
97 BEGIN
98
99 --debug_msg ('In update_progress_report');
100
101 l_wf_item_key_new := p_wf_item_key_new;
102
103 IF p_create_item_key_flag = 'Y' THEN
104 SELECT pa_workflow_itemkey_s.nextval
105 INTO l_wf_item_key_new
106 from dual;
107 x_item_key := l_wf_item_key_new;
108 ELSE
109 l_wf_item_key_new := p_wf_item_key_new;
110 END IF;
111
112 --debug_msg ('In update_progress_report 2');
113
114
115 --debug_msg ('******** In Progress Update Public API **********');
116 x_return_status := 'S';
117 --Validate the parameters - Project ID
118 If ( P_PROJECT_ID_OLD is null
119 OR P_PROJECT_ID_NEW is null
120 OR P_PROJECT_ID_OLD <> P_PROJECT_ID_NEW) then
121 FND_MESSAGE.Set_Name('PA', 'PA_XC_INVALID_PROJECT_ID');
122 x_msg_count := x_msg_count + 1;
123 x_msg_data.extend(1);
124 x_msg_data(x_msg_count) := FND_MESSAGE.get;
125 x_return_status := 'E';
126 end if;
127
128 --Validate the parameters - Task ID
129 If ( P_TASK_ID_OLD is null
130 OR P_TASK_ID_NEW is null
131 OR P_TASK_ID_OLD <> P_TASK_ID_NEW) then
132 FND_MESSAGE.Set_Name('PA', 'PA_XC_INVALID_TASK_ID');
133 x_msg_count := x_msg_count + 1;
134 x_msg_data.extend(1);
135 x_msg_data(x_msg_count) := FND_MESSAGE.get;
136 x_return_status := 'E';
137 end if;
138
139 --Validate Date values are in canonical format
140 if(p_debug_mode = 'Y') then
141 pa_debug.debug('Validating Date Format');
142 end if;
143 Begin
144 if(P_PROGRESS_ASOF_DATE_OLD is not null) then
145 l_progress_asof_date_old := fnd_date.canonical_to_date(P_PROGRESS_ASOF_DATE_OLD);
146 end if;
147 if(P_ESTIMATED_START_DATE_OLD is not null) then
148 l_estimated_start_date_old := fnd_date.canonical_to_date(P_ESTIMATED_START_DATE_OLD);
149 end if;
150 if(P_ESTIMATED_END_DATE_OLD is not null) then
151 l_estimated_end_date_old := fnd_date.canonical_to_date(P_ESTIMATED_END_DATE_OLD);
152 end if;
153 if(P_ACTUAL_START_DATE_OLD is not null) then
154 l_actual_start_date_old := fnd_date.canonical_to_date(P_ACTUAL_START_DATE_OLD);
155 end if;
156 if(P_ACTUAL_END_DATE_OLD is not null) then
157 l_actual_end_date_old := fnd_date.canonical_to_date(P_ACTUAL_END_DATE_OLD);
158 end if;
159 Exception
160 when others then
161 FND_MESSAGE.Set_Name('PA', 'PA_SU_INVALID_DATES');
162 x_msg_count := x_msg_count + 1;
163 x_msg_data.extend(1);
164 x_msg_data(x_msg_count) := FND_MESSAGE.get;
165 x_return_status := 'E';
166 End ;
167 --Validating New progress code
168 if(p_debug_mode = 'Y') then
169 pa_debug.debug('Validating Progress Code');
170 end if;
171 if(NOT is_valid_progress_code(P_PROGRESS_STATUS_CODE_NEW)) then
172 FND_MESSAGE.set_name('PA','PA_XC_INVALID_PROGRESS_CODE');
173 x_msg_count := x_msg_count + 1;
174 x_msg_data.extend(1);
175 x_msg_data(x_msg_count) := FND_MESSAGE.get;
176 x_return_status := 'E';
177 end if;
178 --debug_msg ('Progress : Validating Estimated Start Date');
179 --Validate Estimated Start and End Date
180 if(p_debug_mode = 'Y') then
181 pa_debug.debug('Validating Estimated Start and End Date');
182 end if;
183 if ( P_ESTIMATED_START_DATE_NEW is not null
184 AND P_ESTIMATED_END_DATE_NEW is not null
185 AND trunc(fnd_date.canonical_to_date(P_ESTIMATED_START_DATE_NEW)) > trunc(fnd_date.canonical_to_date(P_ESTIMATED_END_DATE_NEW))) then
186 FND_MESSAGE.set_name('PA','PA_XC_ET_STARTDATE_GT_ENDDATE');
187 x_msg_count := x_msg_count + 1;
188 x_msg_data.extend(1);
189 x_msg_data(x_msg_count) := FND_MESSAGE.get;
190 x_return_status := 'E';
191 end if;
192
193 --Validate Actual Start and End Date
194 if(p_debug_mode = 'Y') then
195 pa_debug.debug('Validating Actual Start and End Date');
196 end if;
197 if(P_ACTUAL_START_DATE_NEW is not null
198 AND P_ACTUAL_END_DATE_NEW is not null
199 AND trunc(fnd_date.canonical_to_date(P_ACTUAL_START_DATE_NEW)) > trunc(fnd_date.canonical_to_date(P_ACTUAL_END_DATE_NEW)) ) then
200 FND_MESSAGE.set_name('PA','PA_XC_AC_STARTDATE_GT_ENDDATE');
201 x_msg_count := x_msg_count + 1;
202 x_msg_data.extend(1);
203 x_msg_data(x_msg_count) := FND_MESSAGE.get;
204 x_return_status := 'E';
205 end if;
206
207 --Validate Percent Comolete
208 if(p_debug_mode = 'Y') then
209 pa_debug.debug('Validating Percent Complete');
210 end if;
211 if(nvl(P_PERCENT_COMPLETE_NEW,0) < 0 OR nvl(P_PERCENT_COMPLETE_NEW,0) > 100) then
212 FND_MESSAGE.set_name('PA','PA_XC_INVALID_PERCENT_COMPLETE');
213 x_msg_count := x_msg_count + 1;
214 x_msg_data.extend(1);
215 x_msg_data(x_msg_count) := FND_MESSAGE.get;
216 x_return_status := 'E';
217 end if;
218 if (P_ACTUAL_START_DATE_NEW is not null
219 AND nvl(P_PERCENT_COMPLETE_NEW,0) > 0
220 AND trunc(fnd_date.canonical_to_date(P_ACTUAL_START_DATE_NEW)) > trunc(fnd_date.canonical_to_date(P_PROGRESS_ASOF_DATE_NEW))) then
221 FND_MESSAGE.set_name('PA','PA_XC_HAVE_NOT_STARTED');
222 x_msg_count := x_msg_count + 1;
223 x_msg_data.extend(1);
224 x_msg_data(x_msg_count) := FND_MESSAGE.get;
225 x_return_status := 'E';
226 end if;
227 if (P_ACTUAL_END_DATE_NEW is not null
228 AND nvl(P_PERCENT_COMPLETE_NEW,0) = 100
229 AND trunc(fnd_date.canonical_to_date(P_ACTUAL_END_DATE_NEW)) > trunc(fnd_date.canonical_to_date(P_PROGRESS_ASOF_DATE_NEW))) then
230 FND_MESSAGE.set_name('PA','PA_XC_HAVE_NOT_COMPLETED');
231 x_msg_count := x_msg_count + 1;
232 x_msg_data.extend(1);
233 x_msg_data(x_msg_count) := FND_MESSAGE.get;
234 x_return_status := 'E';
235 end if;
236 if (P_ACTUAL_END_DATE_NEW is not null
237 AND nvl(P_PERCENT_COMPLETE_NEW,0) < 100
238 AND trunc(fnd_date.canonical_to_date(P_ACTUAL_END_DATE_NEW)) <= trunc(fnd_date.canonical_to_date(P_PROGRESS_ASOF_DATE_NEW))) then
239 FND_MESSAGE.set_name('PA','PA_XC_HAVE_ALREADY_COMPLETED');
240 x_msg_count := x_msg_count + 1;
241 x_msg_data.extend(1);
242 x_msg_data(x_msg_count) := FND_MESSAGE.get;
243 x_return_status := 'E';
244 end if;
245
246
247 --Return the control if there is an error else continue
248 if(x_return_status <> 'S') then
249 return;
250 end if;
251
252 --debug_msg ('In update_progress_report 3');
253
254 -- dbms_output.put_line('NO ERROR');
255 --debug_msg ('Progress : Comparing old and new values');
256 If( P_PROJECT_ID_OLD = P_PROJECT_ID_NEW
257 AND P_TASK_ID_OLD = P_TASK_ID_NEW
258 AND ( P_PROGRESS_STATUS_CODE_OLD <> P_PROGRESS_STATUS_CODE_NEW
259 OR nvl(P_SHORT_DESCRIPTION_OLD,'#!#') <> nvl(P_SHORT_DESCRIPTION_NEW,'#!#')
260 OR nvl(P_PROGRESS_ASOF_DATE_OLD,'#!#') <> nvl(P_PROGRESS_ASOF_DATE_NEW,'#!#')
261 OR nvl(P_LONG_DESCRIPTION_OLD,'#!#') <> nvl(P_LONG_DESCRIPTION_NEW,'#!#')
262 OR nvl(P_ISSUES_OLD,'#!#') <> nvl(P_ISSUES_NEW,'#!#')
263 OR nvl(P_ESTIMATED_START_DATE_OLD,'#!#') <> nvl(P_ESTIMATED_START_DATE_NEW,'#!#')
264 OR nvl(P_ESTIMATED_END_DATE_OLD,'#!#') <> nvl(P_ESTIMATED_END_DATE_NEW,'#!#')
265 OR nvl(P_ACTUAL_START_DATE_OLD,'#!#') <> nvl(P_ACTUAL_START_DATE_NEW,'#!#')
266 OR nvl(P_ACTUAL_END_DATE_OLD,'#!#') <> nvl(P_ACTUAL_END_DATE_NEW,'#!#')
267 OR nvl(P_PERCENT_COMPLETE_OLD,0) <> nvl(P_PERCENT_COMPLETE_NEW,0)
268 OR nvl(P_ESTIMATE_TO_COMPLETE_OLD,0) <> nvl(P_ESTIMATE_TO_COMPLETE_NEW,0)
269 OR nvl(P_UNIT_TYPE_OLD,'#!#') <> nvl(P_UNIT_TYPE_NEW,'#!#')
270 OR nvl(P_wf_status_code_old,'#!#') <> nvl(P_wf_status_code_NEW,'#!#')
271 OR nvl(P_wf_item_type_old,'#!#') <> nvl(P_wf_item_type_NEW,'#!#')
272 OR nvl(P_wf_item_key_old,0) <> nvl(P_wf_item_key_NEW,0)
273 OR nvl(P_wf_process_old,'#!#') <> nvl(P_wf_process_NEW,'#!#')
274 )
275 ) then
276 --debug_msg ('Progress : Change exists in the record');
277
278 -- Issue a save point for the project , task
279 SAVEPOINT_TAG := 'PAXC_'||to_char(p_project_id_old)||to_char(p_task_id_old);
280 SAVEPOINT SAVEPOINT_TAG;
281 if(p_debug_mode = 'Y') then
282 pa_debug.debug('Obtaining Lock for Project:'||to_char(p_project_id_old)||'Task:'||to_char(p_task_id_old));
283 end if;
284 --Obtain the lock
285 OPEN C;
286 FETCH C INTO Recinfo;
287 if (C%NOTFOUND) then
288 CLOSE C;
289 FND_MESSAGE.Set_Name('PA', 'PA_XC_NO_DATA_FOUND');
290 x_msg_count := x_msg_count + 1;
291 x_msg_data.extend(1);
292 x_msg_data(x_msg_count) := FND_MESSAGE.get;
293 x_return_status := 'E';
294 -- dbms_output.put_line('NODATAFOUND Rollback');
295 ROLLBACK TO SAVEPOINT SAVEPOINT_TAG;
296 if(p_debug_mode = 'Y') then
297 pa_debug.debug('Unable to Lock record for Project:'||to_char(p_project_id_old)||'Task:'||to_char(p_task_id_old));
298 end if;
299 return;
300 end if;
301 CLOSE C;
302 --Compare values with DB
303 if(p_debug_mode = 'Y') then
304 pa_debug.debug('Comparing old values with database');
305 end if;
306 --Compare the old and new values and if both are null assign the Data base value
307 -- to old value and then compare with the Data base for changed records.
308 --debug_msg ('Progress : Setting the local variables if null');
309 if(p_short_description_old is null AND p_short_description_new is null) then
310 l_short_description_old := Recinfo.short_description;
311 else
312 l_short_description_old := p_short_description_old;
313 end if;
314 if(p_long_description_old is null AND p_long_description_new is null) then
315 l_long_description_old := Recinfo.long_description;
316 else
317 l_long_description_old := p_long_description_old;
318 end if;
319 if(p_issues_old is null AND p_issues_new is null) then
320 l_issues_old := Recinfo.issues;
321 else
322 l_issues_old :=p_issues_old;
323 end if;
324 if(p_progress_asof_date_old is null AND p_progress_asof_date_new is null) then
325 l_progress_asof_date_old := Recinfo.progress_asof_date;
326 end if;
327 --debug_msg ('Progress : Setting the local variables for dates');
328
332 if(p_estimated_end_date_old is null AND p_estimated_end_date_new is null) then
329 if(p_estimated_start_date_old is null AND p_estimated_start_date_new is null) then
330 l_estimated_start_date_old := Recinfo.estimated_start_date;
331 end if;
333 l_estimated_end_date_old := Recinfo.estimated_end_date;
334 end if;
335 if(p_actual_start_date_old is null AND p_actual_start_date_new is null) then
336 l_actual_start_date_old := Recinfo.actual_start_date;
337 end if;
338 if(p_actual_end_date_old is null AND p_actual_end_date_new is null) then
339 l_actual_end_date_old := Recinfo.actual_end_date;
340 end if;
341 --debug_msg ('Progress : After Setting the local variables for dates');
342 if(p_percent_complete_old is null AND p_percent_complete_new is null) then
343 l_percent_complete_old := Recinfo.percent_complete;
344 else
345 l_percent_complete_old := p_percent_complete_old;
346 end if;
347
348 if(p_estimate_to_complete_old is null AND p_estimate_to_complete_new is null) then
349 l_estimate_to_complete_old := Recinfo.estimate_to_complete;
350 else
351 l_estimate_to_complete_old := p_estimate_to_complete_old;
352 end if;
353
354 if(p_unit_type_old is null AND p_unit_type_new is null) then
355 l_unit_type_old := Recinfo.unit_type;
356 else
357 l_unit_type_old := p_unit_type_old;
358 end if;
359
360 if(p_wf_status_code_old is null AND p_wf_status_code_new is null) then
361 l_wf_status_code_old := Recinfo.wf_status_code;
362 else
363 l_wf_status_code_old := p_wf_status_code_old;
364 end if;
365 ****/
366
367 /*
368 if(p_wf_item_type_old is null AND p_wf_item_type_new is null) then
369 l_wf_item_type_old := Recinfo.wf_item_type;
370 else
371 l_wf_item_type_old := p_wf_item_type_old;
372 end if;
373
374
375 if(p_wf_item_key_old is null AND p_wf_item_key_new is null) then
376 l_wf_item_key_old := Recinfo.wf_item_key;
377 else
378 l_wf_item_key_old := p_wf_item_key_old;
379 end if;
380
381
382 if(p_wf_process_old is null AND p_wf_process_new is null) then
383 l_wf_process_old := Recinfo.wf_process;
384 else
385 l_wf_process_old := p_wf_process_old;
386 end if;
387 */
388
389 --debug_msg ('In update_progress_report 4');
390
391 /*** Commented
392 if ( (Recinfo.PROJECT_id = p_project_Id_old)
393 AND (Recinfo.task_id = p_task_id_old)
394 AND (Recinfo.progress_status_code = p_progress_status_code_old)
395 AND ( (Recinfo.short_description = l_short_description_old)
396 OR ( (Recinfo.short_description IS NULL)
397 AND (l_short_description_old IS NULL)))
398 AND (trunc(Recinfo.progress_asof_date) = trunc(l_progress_asof_date_old))
399 AND ( (Recinfo.long_description = l_long_description_old)
400 OR ( (Recinfo.long_description IS NULL)
401 AND (l_long_description_old IS NULL)))
402 AND ( (Recinfo.issues = l_issues_old)
403 OR ( (Recinfo.issues IS NULL)
404 AND (l_issues_old IS NULL)))
405 AND ( (trunc(Recinfo.estimated_start_date) = trunc(l_estimated_start_date_old))
406 OR ( (Recinfo.estimated_start_date IS NULL)
407 AND (l_estimated_start_date_old IS NULL)) )
408 AND ( (trunc(Recinfo.estimated_end_date) = trunc(l_estimated_end_date_old))
409 OR ( (Recinfo.estimated_end_date IS NULL)
410 AND (l_estimated_end_date_old IS NULL)) )
411 AND ( (trunc(Recinfo.actual_start_date) = trunc(l_actual_start_date_old))
412 OR ( (Recinfo.actual_start_date IS NULL)
413 AND (l_actual_start_date_old IS NULL)) )
414 AND ( (trunc(Recinfo.actual_end_date) = trunc(l_actual_end_date_old))
415 OR ( (Recinfo.actual_end_date IS NULL)
416 AND (l_actual_end_date_old IS NULL)) )
417 AND ( (Recinfo.percent_complete = l_percent_complete_old)
418 OR ( (Recinfo.percent_complete IS NULL)
419 AND (l_percent_complete_old IS NULL)))
420 AND ( (Recinfo.estimate_to_complete = l_estimate_to_complete_old)
421 OR ( (Recinfo.estimate_to_complete IS NULL)
422 AND (l_estimate_to_complete_old IS NULL)))
423
424 AND ( (Recinfo.wf_status_code = l_wf_status_code_old)
425 OR ( (recinfo.wf_status_code IS NULL)
426 AND ( l_wf_status_code_old IS NULL)))
427
428 --AND ( (Recinfo.wf_item_type = l_wf_item_type_old)
429 -- OR ( (Recinfo.wf_item_type IS NULL)
430 -- AND (l_wf_item_type_old IS NULL)))
431 --AND ( (Recinfo.wf_item_key = l_wf_item_key_old)
432 -- OR ( (Recinfo.wf_item_key IS NULL)
433 -- AND (l_wf_item_key_old IS NULL)))
434 --AND ( (Recinfo.new_prog_status_code = l_new_prog_status_code_old)
435 -- OR ( (Recinfo.new_prog_status_code IS NULL)
436 -- AND (l_new_prog_status_code_old IS NULL)))
437
438 --AND ( (Recinfo.unit_type = l_unit_type_old)
439 -- OR ( (Recinfo.unit_type IS NULL)
440 -- AND (l_unit_type_old IS NULL)))
441 ) then
442 --Call the Update
443 if(p_debug_mode = 'Y') then
444 pa_debug.debug('Updating The record');
445 end if;
446 --Call the Update
447
448 --debug_msg ('before update_row');
449
450 PA_XC_PRJ_PROGRESS_REPORTS_PKG.Update_Row(
454 ,P_SHORT_DESCRIPTION_NEW
451 P_PROJECT_ID_NEW
452 ,P_TASK_ID_NEW
453 ,P_PROGRESS_STATUS_CODE_NEW
455 ,fnd_date.canonical_to_date(P_PROGRESS_ASOF_DATE_NEW)
456 ,P_LONG_DESCRIPTION_NEW
457 ,P_ISSUES_NEW
458 ,fnd_date.canonical_to_date(P_ESTIMATED_START_DATE_NEW)
459 ,fnd_date.canonical_to_date(P_ESTIMATED_END_DATE_NEW)
460 ,fnd_date.canonical_to_date(P_ACTUAL_START_DATE_NEW)
461 ,fnd_date.canonical_to_date(P_ACTUAL_END_DATE_NEW)
462 ,P_PERCENT_COMPLETE_NEW
463 ,P_ESTIMATE_TO_COMPLETE_NEW
464 ,p_unit_type_new
465 ,p_wf_status_code_new
466 ,p_wf_item_type_new
467 ,l_wf_item_key_new
468 ,p_wf_process_new
469 ,P_USER_ID
470 ,sysdate
471 ,P_USER_ID
472 );
473
474 --debug_msg ('after update_row');
475 ELSE
476
477 --debug_msg ('In update_progress_report 13');
478
479 FND_MESSAGE.Set_Name('PA', 'PA_XC_RECORD_CHANGED');
480 x_msg_count := x_msg_count + 1;
481 x_msg_data.extend(1);
482 x_msg_data(x_msg_count) := FND_MESSAGE.get;
483 x_return_status := 'E';
484 -- dbms_output.put_line('record has been changed');
485 ROLLBACK TO SAVEPOINT SAVEPOINT_TAG;
486 if(p_debug_mode = 'Y') then
487 pa_debug.debug('Record modified by another user');
488 end if;
489 return;
490 end if;
491
492 if(p_commit_flag = 'Y') THEN
493 commit;
494 end if;
495 end if;
496 EXCEPTION
497 WHEN TIMEOUT_ON_RESOURCE then
498 FND_MESSAGE.Set_Name('PA', 'PA_XC_ROW_ALREADY_LOCKED');
499 --FND_MESSAGE.Set_token('ENTITY', 'PA_PROJ_PROGRESS_REPORTS');
500 --FND_MESSAGE.Set_token('PROJECT',to_char(P_PROJECT_ID_OLD));
501 --FND_MESSAGE.Set_token('TASK',to_char(P_TASK_ID_OLD));
502 x_msg_count := x_msg_count + 1;
503 x_msg_data.extend(1);
504 x_msg_data(x_msg_count) := FND_MESSAGE.get;
505 x_return_status := 'E';
506 -- dbms_output.put_line('timeout on resource');
507 WHEN OTHERS then
508 if(SQLCODE = -54) then
509 FND_MESSAGE.Set_Name('PA', 'PA_XC_ROW_ALREADY_LOCKED');
510 --FND_MESSAGE.Set_token('ENTITY', 'PA_PROJ_PROGRESS_REPORTS');
511 --FND_MESSAGE.Set_token('PROJECT',to_char(P_PROJECT_ID_OLD));
512 --FND_MESSAGE.Set_token('TASK',to_char(P_TASK_ID_OLD));
513
514 x_msg_count := x_msg_count + 1;
515 x_msg_data.extend(1);
516 x_msg_data(x_msg_count) := FND_MESSAGE.get;
517 x_return_status := 'E';
518 -- dbms_output.put_line('row already locked');
519 else
520 x_msg_count := x_msg_count + 1;
521 x_msg_data.extend(1);
522 x_msg_data(x_msg_count) := substr(SQLERRM,1,2000);
523 x_return_status := 'U';
524 end if;
525 END update_progress_report;
526
527 /* Private APIs */
528
529 PROCEDURE Insert_Row(
530 -- P_ROWID IN OUT VARCHAR2
531 P_PROGRESS_REPORT_ID IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
532 ,P_RECORD_VERSION_NUMBER NUMBER DEFAULT 1
533 ,P_PROJECT_ID NUMBER
534 ,P_TASK_ID NUMBER default 0
535 ,P_PROGRESS_STATUS_CODE VARCHAR2 default 'ON_TRACK'
536 ,P_SHORT_DESCRIPTION VARCHAR2 default null
537 ,P_PROGRESS_ASOF_DATE DATE default sysdate
538 ,P_LONG_DESCRIPTION VARCHAR2 default null
539 ,P_ISSUES VARCHAR2 default null
540 ,P_ESTIMATED_START_DATE DATE default null
541 ,P_ESTIMATED_END_DATE DATE default null
542 ,P_ACTUAL_START_DATE DATE default null
543 ,P_ACTUAL_END_DATE DATE default null
544 ,P_PERCENT_COMPLETE NUMBER default null
545 ,P_ESTIMATE_TO_COMPLETE NUMBER default null
546 ,P_UNIT_TYPE VARCHAR2 default null
547 ,P_PLANNED_ACTIVITIES VARCHAR2 DEFAULT NULL
548 ,P_REPORT_STATUS VARCHAR2 DEFAULT 'WIP'
549 ,P_CREATED_BY NUMBER default -1
550 ,P_CREATION_DATE DATE default sysdate
551 ,P_LAST_UPDATED_BY NUMBER default -1
552 ,P_LAST_UPDATE_DATE DATE default sysdate
553 ,P_LAST_UPDATE_LOGIN NUMBER default -1
554 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
555 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
556 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
557
558 )
559 IS
560 l_progress_report_id number;
561 l_row_id varchar2(40);
562 CURSOR C IS SELECT rowid FROM PA_PROJ_PROGRESS_REPORTS
563 WHERE progress_report_id = l_progress_report_id;
564 BEGIN
565 x_return_status := FND_API.G_RET_STS_SUCCESS;
566
567 --Fetch the next sequence number for progres report
568 SELECT PA_PROJ_PROGRESS_REPORTS_S.NEXTVAL
569 INTO l_progress_report_id
570 FROM dual;
571
572 INSERT INTO PA_PROJ_PROGRESS_REPORTS(
573 PROGRESS_REPORT_ID
574 ,RECORD_VERSION_NUMBER
575 ,PROJECT_ID
576 ,TASK_ID
577 ,PROGRESS_STATUS_CODE
578 ,SHORT_DESCRIPTION
579 ,PROGRESS_ASOF_DATE
580 ,LONG_DESCRIPTION
581 ,ISSUES
582 ,ESTIMATED_START_DATE
586 ,PERCENT_COMPLETE
583 ,ESTIMATED_END_DATE
584 ,ACTUAL_START_DATE
585 ,ACTUAL_END_DATE
587 ,ESTIMATE_TO_COMPLETE
588 ,UNIT_TYPE
589 ,PLANNED_ACTIVITIES
590 ,REPORT_STATUS
591 ,CREATED_BY
592 ,CREATION_DATE
593 ,LAST_UPDATED_BY
594 ,LAST_UPDATE_DATE
595 ,LAST_UPDATE_LOGIN
596 ) VALUES (
597 L_PROGRESS_REPORT_ID
598 ,P_RECORD_VERSION_NUMBER
599 ,P_PROJECT_ID
600 ,P_TASK_ID
601 ,P_PROGRESS_STATUS_CODE
602 ,P_SHORT_DESCRIPTION
603 ,trunc(P_PROGRESS_ASOF_DATE)
604 ,P_LONG_DESCRIPTION
605 ,P_ISSUES
606 ,trunc(P_ESTIMATED_START_DATE)
607 ,trunc(P_ESTIMATED_END_DATE)
608 ,trunc(P_ACTUAL_START_DATE)
609 ,trunc(P_ACTUAL_END_DATE)
610 ,P_PERCENT_COMPLETE
611 ,P_ESTIMATE_TO_COMPLETE
612 ,P_UNIT_TYPE
613 ,P_PLANNED_ACTIVITIES
614 ,P_REPORT_STATUS
615 ,P_CREATED_BY
616 ,P_CREATION_DATE
617 ,P_LAST_UPDATED_BY
618 ,P_LAST_UPDATE_DATE
619 ,P_LAST_UPDATE_LOGIN
620 );
621 OPEN C;
622 FETCH C INTO l_Row_id;
623 if (C%NOTFOUND) then
624 CLOSE C;
625 Raise NO_DATA_FOUND;
626 end if;
627 CLOSE C;
628 EXCEPTION
629 WHEN OTHERS THEN
630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
631 RAISE;
632
633 END Insert_Row;
634
635 PROCEDURE Update_Row(
636 P_PROGRESS_REPORT_ID NUMBER
637 ,P_RECORD_VERSION_NUMBER NUMBER
638 ,P_PROJECT_ID NUMBER
639 ,P_TASK_ID NUMBER
640 ,P_PROGRESS_STATUS_CODE VARCHAR2
641 ,P_SHORT_DESCRIPTION VARCHAR2
642 ,P_PROGRESS_ASOF_DATE DATE
643 ,P_LONG_DESCRIPTION VARCHAR2
644 ,P_ISSUES VARCHAR2
645 ,P_ESTIMATED_START_DATE DATE default trunc(to_date('01/01/1851','DD/MM/YYYY'))
646 ,P_ESTIMATED_END_DATE DATE default trunc(to_date('01/01/1851','DD/MM/YYYY'))
647 ,P_ACTUAL_START_DATE DATE default trunc(to_date('01/01/1851','DD/MM/YYYY'))
648 ,P_ACTUAL_END_DATE DATE default trunc(to_date('01/011851','DD/MM/YYYY'))
649 ,P_PERCENT_COMPLETE NUMBER default -9999
650 ,P_ESTIMATE_TO_COMPLETE NUMBER default -9999
651 ,P_UNIT_TYPE VARCHAR2 default '####'
652 ,P_PLANNED_ACTIVITIES VARCHAR2 default '####'
653 ,P_REPORT_STATUS VARCHAR2 default '####'
654 ,p_wf_status_code VARCHAR2 default '####'
655 ,p_wf_item_type VARCHAR2 default '####'
656 ,p_wf_item_key NUMBER default -9999
657 ,p_wf_process VARCHAR2 default '####'
658 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
659 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
660 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
661 )
662
663 IS
664 CURSOR new_wip is
665 select * from
666 pa_proj_progress_reports
667 where progress_report_id = p_progress_report_id;
668 c_rec new_wip%ROWTYPE;
669 l_row_id varchar2(60);
670 l_progress_report_id number := null;
671
672 BEGIN
673 x_return_status := FND_API.G_RET_STS_SUCCESS;
674
675 UPDATE PA_PROJ_PROGRESS_REPORTS
676 SET RECORD_VERSION_NUMBER = P_RECORD_VERSION_NUMBER + 1
677 ,PROJECT_ID = P_PROJECT_ID
678 ,TASK_ID = P_TASK_ID
679 ,PROGRESS_STATUS_CODE = P_PROGRESS_STATUS_CODE
680 ,SHORT_DESCRIPTION = P_SHORT_DESCRIPTION
681 ,PROGRESS_ASOF_DATE = nvl(P_PROGRESS_ASOF_DATE,trunc(sysdate))
682 ,LONG_DESCRIPTION = P_LONG_DESCRIPTION
683 ,ISSUES = P_ISSUES
684 ,ESTIMATED_START_DATE = decode( to_char(P_ESTIMATED_START_DATE,'DD/MM/YYYY'), '01/01/1851', trunc(ESTIMATED_START_DATE), trunc(P_ESTIMATED_START_DATE))
685 ,ESTIMATED_END_DATE = decode( to_char(P_ESTIMATED_END_DATE,'DD/MM/YYYY'), '01/01/1851', trunc(ESTIMATED_END_DATE), trunc(P_ESTIMATED_END_DATE) )
686 ,ACTUAL_START_DATE = decode( to_char(P_ACTUAL_START_DATE,'DD/MM/YYYY'), '01/01/1851', trunc(ACTUAL_START_DATE), trunc(P_ACTUAL_START_DATE) )
687 ,ACTUAL_END_DATE = decode( to_char(P_ACTUAL_END_DATE,'DD/MM/YYYY'), '01/01/1851', trunc(ACTUAL_END_DATE), trunc(P_ACTUAL_END_DATE) )
688 ,PERCENT_COMPLETE = decode( P_PERCENT_COMPLETE, -9999, PERCENT_COMPLETE, P_PERCENT_COMPLETE )
689 ,ESTIMATE_TO_COMPLETE = decode( P_ESTIMATE_TO_COMPLETE, -9999, ESTIMATE_TO_COMPLETE, P_ESTIMATE_TO_COMPLETE )
690 ,UNIT_TYPE = decode( P_UNIT_TYPE, '####', UNIT_TYPE, P_UNIT_TYPE )
691 ,PLANNED_ACTIVITIES = decode(P_PLANNED_ACTIVITIES ,'####',PLANNED_ACTIVITIES,P_PLANNED_ACTIVITIES)
692 ,REPORT_STATUS = decode(P_REPORT_STATUS ,'####',REPORT_STATUS,P_REPORT_STATUS)
693 --,wf_status_code = decode( p_wf_status_code, '####', wf_status_code, p_wf_status_code )
697 ,LAST_UPDATED_BY = -1
694 --,wf_item_type = decode(p_wf_item_type, '####', wf_item_type, p_wf_item_type )
695 --,wf_item_key = decode(p_wf_item_key, -9999, wf_item_key, p_wf_item_key)
696 --,wf_process = decode(p_wf_process,'####', wf_process, p_wf_process)
698 ,LAST_UPDATE_DATE = sysdate
699 ,LAST_UPDATE_LOGIN = -1
700 WHERE PROGRESS_REPORT_ID = P_PROGRESS_REPORT_ID
701 AND NVL(P_RECORD_VERSION_NUMBER,RECORD_VERSION_NUMBER) = RECORD_VERSION_NUMBER;
702 IF (SQL%NOTFOUND) THEN
703 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
704 --PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
705 x_return_status := FND_API.G_RET_STS_ERROR;
706 x_msg_data := 'PA_XC_RECORD_CHANGED';
707 return;
708 END IF;
709 /**
710 IF (P_REPORT_STATUS='PUBLISHED') then
711 open new_wip;
712 fetch new_wip into c_rec;
713 close new_wip;
714 Insert_row(
715 --P_ROWID => l_row_id
716 P_PROGRESS_REPORT_ID => l_progress_report_id
717 ,P_PROJECT_ID => c_rec.project_id
718 ,P_TASK_ID => c_rec.TASK_ID
719 ,P_PROGRESS_STATUS_CODE => c_rec.PROGRESS_STATUS_CODE
720 ,P_SHORT_DESCRIPTION => c_rec.SHORT_DESCRIPTION
721 --,P_PROGRESS_ASOF_DATE DATE default sysdate
722 ,P_LONG_DESCRIPTION => c_rec.LONG_DESCRIPTION
723 ,P_ISSUES => c_rec.ISSUES
724 ,P_ESTIMATED_START_DATE => trunc(c_rec.ESTIMATED_START_DATE)
725 ,P_ESTIMATED_END_DATE => trunc(c_rec.ESTIMATED_END_DATE)
726 ,P_ACTUAL_START_DATE => trunc(c_rec.ACTUAL_START_DATE)
727 ,P_ACTUAL_END_DATE => trunc(c_rec.ACTUAL_END_DATE)
728 ,P_PERCENT_COMPLETE => c_rec.PERCENT_COMPLETE
729 ,P_ESTIMATE_TO_COMPLETE => c_rec.ESTIMATE_TO_COMPLETE
730 ,P_UNIT_TYPE => c_rec.UNIT_TYPE
731 ,P_PLANNED_ACTIVITIES => c_rec.PLANNED_ACTIVITIES
732 --,P_REPORT_STATUS VARCHAR2 DEFAULT 'WIP'
733 ,P_CREATED_BY => -1
734 ,P_CREATION_DATE => sysdate
735 ,P_LAST_UPDATED_BY => -1
736 ,P_LAST_UPDATE_DATE => sysdate
737 ,P_LAST_UPDATE_LOGIN => -1
738 ,x_return_status => x_return_status
739 ,x_msg_count => x_msg_count
740 ,x_msg_data => x_msg_data );
741 End if;
742 **/
743 --
744 EXCEPTION
745 WHEN OTHERS THEN -- catch the exceptins here
746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
747 RAISE;
748 END Update_row;
749
750
751 PROCEDURE Delete_Row( P_PROGRESS_REPORT_ID NUMBER
752 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
753 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
754 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
755 )
756 is
757 BEGIN
758 x_return_status := FND_API.G_RET_STS_SUCCESS;
759 DELETE FROM PA_PROJ_PROGRESS_REPORTS
760 WHERE progress_report_id = p_progress_report_id;
761 IF (SQL%NOTFOUND) THEN
762 PA_UTILS.Add_Message ( p_app_short_name => 'PA', p_msg_name => 'PA_XC_RECORD_CHANGED');
763 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
764 END IF;
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
769 RAISE;
770 END Delete_Row;
771
772 PROCEDURE Copy_lastpublished_report(
773 -- P_PROGRESS_REPORT_ID IN OUT NUMBER
774 P_PROJECT_ID NUMBER )
775 --,P_TASK_ID NUMBER default 0
776 --,P_CREATED_BY NUMBER default -1
777 --,P_CREATION_DATE DATE default sysdate
778 --,P_LAST_UPDATED_BY NUMBER default -1
779 --,P_LAST_UPDATE_DATE DATE default sysdate
780 --,P_LAST_UPDATE_LOGIN NUMBER default -1
781 --,x_return_status OUT VARCHAR2
782 --,x_msg_count OUT NUMBER
783 --,x_msg_data OUT VARCHAR2 )
784 IS
785 CURSOR new_wip is
786 select * from
787 pa_proj_progress_reports
788 where progress_report_id = (select max(progress_report_id)
789 from pa_proj_progress_reports
790 where project_id = p_project_id
791 and report_status = 'PUBLISHED');
792 c_rec new_wip%ROWTYPE;
793 l_row_id varchar2(60);
794 l_progress_report_id number := null;
795 l_return_status varchar2(10);
796 l_msg_count number := 0;
797 l_msg_data varchar2(200);
798 BEGIN
799 --debug_msu('In copy progress Report'||to_char(p_project_id));
800 open new_wip;
801 fetch new_wip into c_rec;
802 if(new_wip%NOTFOUND) then
803 --debug_msu('No data found to copy progress Report');
804 close new_wip;
805 return;
806 end if;
807 close new_wip;
808 --debug_msu(' copying New progress Report');
809 Insert_row(
810 --P_ROWID => l_row_id
811 P_PROGRESS_REPORT_ID => l_progress_report_id
812 ,P_PROJECT_ID => c_rec.project_id
813 ,P_TASK_ID => c_rec.TASK_ID
814 ,P_PROGRESS_STATUS_CODE => c_rec.PROGRESS_STATUS_CODE
815 ,P_SHORT_DESCRIPTION => c_rec.SHORT_DESCRIPTION
816 ,P_PROGRESS_ASOF_DATE => sysdate
817 ,P_LONG_DESCRIPTION => c_rec.LONG_DESCRIPTION
818 ,P_ISSUES => c_rec.ISSUES
819 ,P_ESTIMATED_START_DATE => trunc(c_rec.ESTIMATED_START_DATE)
820 ,P_ESTIMATED_END_DATE => trunc(c_rec.ESTIMATED_END_DATE)
821 ,P_ACTUAL_START_DATE => trunc(c_rec.ACTUAL_START_DATE)
822 ,P_ACTUAL_END_DATE => trunc(c_rec.ACTUAL_END_DATE)
823 ,P_PERCENT_COMPLETE => c_rec.PERCENT_COMPLETE
824 ,P_ESTIMATE_TO_COMPLETE => c_rec.ESTIMATE_TO_COMPLETE
825 ,P_UNIT_TYPE => c_rec.UNIT_TYPE
826 ,P_PLANNED_ACTIVITIES => c_rec.PLANNED_ACTIVITIES
827 ,P_REPORT_STATUS => 'WIP'
828 ,P_CREATED_BY => -1
829 ,P_CREATION_DATE => sysdate
830 ,P_LAST_UPDATED_BY => -1
831 ,P_LAST_UPDATE_DATE => sysdate
832 ,P_LAST_UPDATE_LOGIN => -1
833 ,x_return_status => l_return_status
834 ,x_msg_count => l_msg_count
835 ,x_msg_data => l_msg_data );
836 commit;
837 --dbms_output.put_line('In package End of Copy last published report');
838 --debug_msu('In package End of Copy last published report');
839 END Copy_lastpublished_report;
840 END PA_PRJ_PROGRESS_REPORTS_PKG;