[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_DATES_PVT
Source
1 PACKAGE BODY PA_PROJECT_DATES_PVT AS
2 /* $Header: PARMPDVB.pls 120.4 2008/06/26 09:55:37 jravisha ship $ */
3
4 -- Global constant
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROJECT_DATES_PVT';
6
7
8 -- API name : Update_Project_Dates
9 -- Type : Public
10 -- Pre-reqs : None.
11 -- Parameters :
12 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
13 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
14 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
15 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
16 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
17 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
18 -- p_project_id IN NUMBER Required
19 -- p_date_type IN VARCHAR2 Required
20 -- p_start_date IN DATE Optional Default = FND_API.G_MISS_DATE
21 -- p_finish_date IN DATE Optional Default = FND_API.G_MISS_DATE
22 -- p_record_version_number IN NUMBER Optional Default = FND_API.G_MISS_NUM
23 -- x_return_status OUT VARCHAR2 Required
24 -- x_msg_count OUT NUMBER Required
25 -- x_msg_data OUT VARCHAR2 Optional
26
27 PROCEDURE UPDATE_PROJECT_DATES
28 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
29 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
30 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
31 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
32 ,p_debug_mode IN VARCHAR2 := 'N'
33 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
34 ,p_project_id IN NUMBER
35 ,p_date_type IN VARCHAR2
36 ,p_start_date IN DATE := FND_API.G_MISS_DATE
37 ,p_finish_date IN DATE := FND_API.G_MISS_DATE
38 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
39 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
40 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
41 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
42 )
43 IS
44
45 l_return_status VARCHAR2(1);
46 l_msg_count NUMBER;
47 l_msg_data VARCHAR2(250);
48 l_dummy VARCHAR2(1);
49 l_start_date DATE;
50 l_finish_date DATE;
51 l_duration_days NUMBER;
52 l_duration NUMBER;
53 l_calendar_id NUMBER;
54
55 /*Bug 6860603*/
56 l_validate varchar(100);
57 l_start_date_status varchar(100);
58 l_end_date_status varchar(100);
59 l_version_enabled varchar(100);
60 validate varchar(100);
61 l_alwd_start_date DATE;
62 l_alwd_end_date DATE;
63 l_res_min_date DATE;
64 l_res_max_date DATE;
65
66 CURSOR get_cal_id IS
67 select calendar_id
68 from pa_projects_all
69 where project_id = p_project_id;
70
71 BEGIN
72 if (p_debug_mode = 'Y') then
73 pa_debug.debug('PA_PROJECT_DATES_PVT.Update_Project_Dates BEGIN');
74 end if;
75
76 if p_commit = FND_API.G_TRUE then
77 savepoint update_project_dates_pvt;
78 end if;
79
80 if p_validate_only <> FND_API.G_TRUE then
81 BEGIN
82 SELECT 'x' INTO l_dummy
83 FROM pa_projects_all
84 WHERE project_id = p_project_id
85 AND record_version_number = p_record_version_number
86 FOR UPDATE OF record_version_number NOWAIT;
87 EXCEPTION
88 when TIMEOUT_ON_RESOURCE then
89 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
90 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
91 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
92 when NO_DATA_FOUND then
93 if p_calling_module = 'FORM' then
94 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
95 p_msg_name => 'FORM_RECORD_CHANGED');
96 l_msg_data := 'FORM_RECORD_CHANGED';
97 else
98 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
99 p_msg_name => 'PA_XC_RECORD_CHANGED');
100 l_msg_data := 'PA_XC_RECORD_CHANGED';
101 end if;
102 when OTHERS then
103 if SQLCODE = -54 then
104 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
105 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
106 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
107 else
108 raise;
109 end if;
110 END;
111 else
112 BEGIN
113 SELECT 'x' INTO l_dummy
114 FROM pa_projects_all
115 WHERE project_id = p_project_id
116 AND record_version_number = p_record_version_number;
117 EXCEPTION
118 when NO_DATA_FOUND then
119 if p_calling_module = 'FORM' then
120 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
121 p_msg_name => 'FORM_RECORD_CHANGED');
122 l_msg_data := 'FORM_RECORD_CHANGED'; else
123 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
124 p_msg_name => 'PA_XC_RECORD_CHANGED');
125 l_msg_data := 'PA_XC_RECORD_CHANGED';
126 end if;
127 when OTHERS then
128 raise;
129 END;
130 end if;
131
132 l_msg_count := FND_MSG_PUB.count_msg;
133 if l_msg_count > 0 then
134 x_msg_count := l_msg_count;
135 if x_msg_count = 1 then
136 x_msg_data := l_msg_data;
137 end if;
138 raise FND_API.G_EXC_ERROR;
139 end if;
140
141 if p_start_date = FND_API.G_MISS_DATE then
142 l_start_date := NULL;
143 else
144 l_start_date := p_start_date;
145 end if;
146
147 if p_finish_date = FND_API.G_MISS_DATE then
148 l_finish_date := NULL;
149 else
150 l_finish_date := p_finish_date;
151 end if;
152
153 if(p_date_type IN ('BASELINE', 'ACTUAL', 'SCHEDULED')) then
154 -- Bug 3657808 Remove duration calculation using calendar
155 -- Duration is in days
156 --
157 l_duration := trunc(l_finish_date) - trunc(l_start_date) + 1;
158 --
159 -- Added to calculate duration
160 /* OPEN get_cal_id;
161 FETCH get_cal_id INTO l_calendar_id;
162 CLOSE get_cal_id;
163
164 PA_DURATION_UTILS.GET_DURATION(
165 p_calendar_id => l_calendar_id
166 ,p_start_date => l_start_date
167 ,p_end_date => l_finish_date
168 ,x_duration_days => l_duration_days
169 ,x_duration_hours => l_duration
170 ,x_return_status => l_return_status
171 ,x_msg_count => l_msg_count
172 ,x_msg_data => l_msg_data );
173
174 IF (l_return_status <> 'S') THEN
175 l_msg_count := FND_MSG_PUB.count_msg;
176 IF l_msg_count > 0 THEN
177 x_msg_count := l_msg_count;
178 IF x_msg_count = 1 THEN
179 x_msg_data := l_msg_data;
180 END IF;
181 END IF;
182
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;*/
185
186 END IF;
187
188 if p_validate_only <> FND_API.G_TRUE then
189 if p_date_type = 'TRANSACTION' then
190
191 /*Bug 6860603 Begin*/
192
193 /*Bug 7203870*/
194 l_validate := FND_PROFILE.value('PA_VALIDATE_ASSIGN_DATES');
195 select Upper(PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED(p_project_id))
196 into l_version_enabled
197 from dual;
198
199
200 IF (l_validate='Y' AND l_version_enabled='Y') then
201 PA_PROJECT_DATES_UTILS.WPP_Validate_Project_Dates (p_project_id => p_project_id,
202 p_start_date => l_start_date,
203 p_end_date => l_finish_date,
204 p_alwd_start_date => l_alwd_start_date,
205 p_alwd_end_date => l_alwd_end_date,
206 p_res_min_date => l_res_min_date,
207 p_res_max_date=> l_res_max_date,
208 x_validate => validate,
209 x_start_date_status=>l_start_date_status,
210 x_end_date_status => l_end_date_status);
211
212 IF (l_start_date_status='I') then
213 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
214 p_msg_name => 'PA_PJR_ASG_DATE_END_ERROR',
215 p_token1 => 'DATE1',
216 P_value1 => l_alwd_start_date,
217 p_token2 => 'DATE2',
218 P_value2 => l_res_min_date,
219 p_token3 => 'DATE3',
220 P_value3 => l_res_max_date
221 );
222
223 end if;
224
225 IF (l_end_date_status='I') then
226
227 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
228 p_msg_name => 'PA_PJR_ASG_DATE_START_ERROR',
229 p_token1 => 'DATE1',
230 P_value1 => l_alwd_end_date,
231 p_token2 => 'DATE2',
232 P_value2 => l_res_min_date,
233 p_token3 => 'DATE3',
234 P_value3 => l_res_max_date
235 );
236
237 end if;
238
239 IF(l_start_date_status='I' OR l_end_date_status='I') then
240 Raise FND_API.G_EXC_ERROR;
241 end if;
242
243 End if; -- PA_VALIDATE_ASSIGN_DATES='Y' and version_enabled='Y'
244 /*Bug 6860603 End*/
245
246 UPDATE PA_PROJECTS_ALL
247 SET start_date = l_start_date,
248 completion_date = l_finish_date,
249 last_update_date = sysdate,
250 record_version_number = p_record_version_number + 1,
251 last_updated_by = fnd_global.user_id,
252 last_update_login = fnd_global.login_id
253 WHERE project_id = p_project_id;
254 elsif p_date_type = 'TARGET' then
255 UPDATE PA_PROJECTS_ALL
256 SET target_start_date = l_start_date,
257 target_finish_date = l_finish_date,
258 last_update_date = sysdate,
259 record_version_number = p_record_version_number + 1,
260 last_updated_by = fnd_global.user_id,
261 last_update_login = fnd_global.login_id
262 WHERE project_id = p_project_id;
263 elsif p_date_type = 'ACTUAL' then
264 UPDATE PA_PROJECTS_ALL
265 SET actual_start_date = l_start_date,
266 actual_finish_date = l_finish_date,
267 actual_duration = l_duration,
268 last_update_date = sysdate,
269 record_version_number = p_record_version_number + 1,
270 last_updated_by = fnd_global.user_id,
271 last_update_login = fnd_global.login_id
272 WHERE project_id = p_project_id;
273 elsif p_date_type = 'BASELINE' then
274 UPDATE PA_PROJECTS_ALL
275 SET baseline_start_date = l_start_date,
276 baseline_finish_date = l_finish_date,
277 baseline_duration = l_duration,
278 baseline_as_of_date = sysdate,
279 last_update_date = sysdate,
280 record_version_number = p_record_version_number + 1,
281 last_updated_by = fnd_global.user_id,
282 last_update_login = fnd_global.login_id
283 WHERE project_id = p_project_id;
284 elsif p_date_type = 'SCHEDULED' then
285 UPDATE PA_PROJECTS_ALL
286 SET scheduled_start_date = l_start_date,
287 scheduled_finish_date = l_finish_date,
288 scheduled_duration = l_duration,
289 scheduled_as_of_date = sysdate,
290 last_update_date = sysdate,
291 record_version_number = p_record_version_number + 1,
292 last_updated_by = fnd_global.user_id,
293 last_update_login = fnd_global.login_id
294 WHERE project_id = p_project_id;
295 end if;
296 end if;
297
298 x_return_status := FND_API.G_RET_STS_SUCCESS;
299
300 if p_commit = FND_API.G_TRUE then
301 commit work;
302 end if;
303
304 if (p_debug_mode = 'Y') then
305 pa_debug.debug('PA_PROJECT_DATES_PVT.Update_Project_Dates END');
306 end if;
307
308 EXCEPTION
309 when FND_API.G_EXC_ERROR then
310 if p_commit = FND_API.G_TRUE then
311 rollback to update_project_dates_pvt;
312 end if;
313 x_return_status := FND_API.G_RET_STS_ERROR;
314 when FND_API.G_EXC_UNEXPECTED_ERROR then
315 if p_commit = FND_API.G_TRUE then
316 rollback to update_project_dates_pvt;
317 end if;
318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
319 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_DATES_PVT',
320 p_procedure_name => 'Update_Project_Dates',
321 p_error_text => SUBSTRB(SQLERRM,1,240));
322 when OTHERS then
323 if p_commit = FND_API.G_TRUE then
324 rollback to update_project_dates_pvt;
325 end if;
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_DATES_PVT',
328 p_procedure_name => 'Update_Project_Dates',
329 p_error_text => SUBSTRB(SQLERRM,1,240));
330 raise;
331 END UPDATE_PROJECT_DATES;
332
333
334 END PA_PROJECT_DATES_PVT;