[Home] [Help]
PACKAGE BODY: APPS.PA_DELIVERABLE_PROGRESS_PUB
Source
1 PACKAGE BODY PA_DELIVERABLE_PROGRESS_PUB AS
2 /* $Header: PAPRDLPB.pls 120.4 2010/11/15 14:16:54 bifernan ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_DELIVERABLE_PROGRESS_PUB';
5
6 PROCEDURE UPDATE_DELIVERABLE_PROGRESS(
7 p_api_version IN NUMBER := 1.0 ,
8 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
9 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
10 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
11 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
12 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
13 p_action IN VARCHAR2 := 'SAVE' ,
14 p_bulk_load_flag IN VARCHAR2 := 'N' ,
15 p_progress_mode IN VARCHAR2 := 'FUTURE' ,
16 p_percent_complete_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
17 p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
18 p_object_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
19 p_object_type IN VARCHAR2 := 'PA_DELIVERABLES' ,
20 p_object_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
21 p_del_status IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
22 p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
23 p_as_of_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
24 p_percent_complete IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
25 p_progress_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
26 p_progress_comment IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
27 p_brief_overview IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
28 p_actual_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
29 p_deliverable_due_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
30 p_record_version_number IN NUMBER ,
31 p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
32 p_structure_type IN VARCHAR2 := 'WORKPLAN' ,
33 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
34 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
35 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
36 IS
37
38 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_DELIVERABLE_PROGRESS' ;
39 l_api_version CONSTANT NUMBER := 1.0 ;
40
41 l_return_status VARCHAR2(1) ;
42 l_msg_count NUMBER ;
43 l_msg_data VARCHAR2(250) ;
44 l_data VARCHAR2(250) ;
45 l_msg_index_out NUMBER ;
46 l_error_msg_code VARCHAR2(250) ;
47 l_user_id NUMBER := FND_GLOBAL.USER_ID ;
48 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID ;
49
50 l_task_id NUMBER ;
51 l_project_id NUMBER ;
52
53 l_att_pc_id NUMBER ;
54 l_percent_complete_id NUMBER ;
55 l_PROGRESS_ROLLUP_ID NUMBER ;
56
57
58 l_last_progress_date DATE ;
59 l_progress_exists_on_aod VARCHAR2(15) ;
60 l_db_action VARCHAR2(10) ;
61 l_BASE_PERCENT_COMPLETE NUMBER ;
62 l_published_flag VARCHAR2(1) ;
63 l_current_flag VARCHAR2(1) ;
64 l_actual_finish_date DATE ;
65 l_BASE_PROGRESS_STATUS_CODE VARCHAR2(30) ;
66 l_proj_element_id NUMBER ;
67 l_percent_complete NUMBER ;
68
69 l_percent_complete_flag VARCHAR2(1) ;
70 l_rollup_rec_ver_number NUMBER ;
71 l_published_by_party_id NUMBER := PA_UTILS.get_party_id( l_user_id ) ;
72 l_del_status VARCHAR2(150) ;
73 l_del_status2 VARCHAR2(150) ;
74 l_working_aod DATE ;
75 l_aod DATE ;
76 l_progress_entry_enable_flag VARCHAR2(1) ;
77 l_msg VARCHAR2(30) ;
78 l_EFF_ROLLUP_PERCENT_COMP NUMBER ;
79 l_EFF_ROLLUP_PROG_STAT_CODE VARCHAR2(150) ;
80 l_rollup_progress_status VARCHAR2(150) ;
81 l_rollup_completed_percentage NUMBER ;
82 l_pev_schedule_id NUMBER ;
83 l_sch_rec_ver_number NUMBER ;
84 l_del_type_prog_enabled VARCHAR2(1) ;
85 l_task_type_prog_enabled VARCHAR2(1) ;
86 g1_debug_mode VARCHAR2(1) ;
87 L_WQ_ENABLED_FLAG VARCHAR2(1) ;
88 l_del_name VARCHAR2(240); -- Bug 6497559
89 L_EST_REMAINING_EFF_FLAG VARCHAR2(1) ;
90 l_estimated_start_date DATE ;
91 l_estimated_finish_date DATE ;
92 l_actual_start_date DATE ;
93 L_TASK_WEIGHT_BASIS_CODE VARCHAR2(20) ;
94 L_ALLOW_COLLAB_PROG_ENTRY VARCHAR2(1) ;
95 L_ALLW_PHY_PRCNT_CMP_OVERRIDES VARCHAR2(1) ;
96
97
98
99 CURSOR cur_sch_id( c_object_version_id NUMBER )
100 IS
101 SELECT pev_schedule_id, record_version_number, actual_start_date, estimated_start_date, estimated_finish_date
102 FROM pa_proj_elem_ver_schedule
103 WHERE project_id = p_project_id
104 AND element_version_id = c_object_version_id;
105
106
107 CURSOR cur_get_del_type_prog_attr
108 IS
109 SELECT nvl(ptt.PROG_ENTRY_ENABLE_FLAG, 'N'), elem.type_id, elem.status_code, elem.NAME -- Bug 6497559
110 FROM pa_proj_elements elem, pa_task_types ptt
111 WHERE project_id = p_project_id
112 AND proj_element_id = p_object_id
113 AND elem.object_type ='PA_DELIVERABLES'
114 AND elem.type_id = ptt.task_type_id(+)
115 AND ptt.object_type = 'PA_DLVR_TYPES' ;
116
117 CURSOR cur_get_task_type_prog_attr
118 IS
119 SELECT nvl(ptt.PROG_ENTRY_ENABLE_FLAG, 'N')
120 FROM pa_proj_elements elem, pa_task_types ptt
121 WHERE project_id = p_project_id
122 AND proj_element_id = p_task_id
123 AND elem.object_type ='PA_TASKS'
124 AND elem.type_id = ptt.task_type_id(+)
125 AND ptt.object_type = 'PA_TASKS' ;
126
127
128
129
130 l_prog_pa_period_name VARCHAR2(30) ;
131 l_prog_gl_period_name VARCHAR2(30) ;
132 l_deliverable_status VARCHAR2(30) ;
133 l_deliverable_existing_status VARCHAR2(30) ;
134 l_dlvr_type_id NUMBER ;
135 l_pm_product_code pa_percent_completes.pm_product_code%TYPE ; -- FPM Dev CR 1
136 l_PROGRESS_COMMENT pa_percent_completes.progress_comment%TYPE ; -- FPM Dev CR 1
137 l_brief_overview pa_percent_completes.description%TYPE ; -- FPM Dev CR 1
138 l_progress_status_code pa_progress_rollup.progress_status_code%TYPE ; -- FPM Dev CR 3
139 l_record_version_number NUMBER ; -- FPM Dev CR 3
140 --3632883
141 l_dummy VARCHAR2(3);
142 l_rollup_current_flag VARCHAR2(1); -- Bug 3879461
143
144 temp_task_id number; --5194985
145
146 BEGIN
147
148 g1_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
149 IF g1_debug_mode = 'Y' THEN
150 pa_debug.init_err_stack ('PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS');
151 END IF;
152
153 IF g1_debug_mode = 'Y' THEN
154 -- FPM Dev CR 1 : Printed All Parameters
155 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS Passed Parameters Are..', x_Log_Level=> 3);
156 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_api_version='||p_api_version, x_Log_Level=> 3);
157 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_init_msg_list='||p_init_msg_list, x_Log_Level=> 3);
158 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_commit='||p_commit, x_Log_Level=> 3);
159 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_validate_only='||p_validate_only, x_Log_Level=> 3);
160 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_validation_level='||p_validation_level, x_Log_Level=> 3);
161 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_calling_module='||p_calling_module, x_Log_Level=> 3);
162 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_action='||p_action, x_Log_Level=> 3);
163 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_bulk_load_flag='||p_bulk_load_flag, x_Log_Level=> 3);
164 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_progress_mode='||p_progress_mode, x_Log_Level=> 3);
165 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_percent_complete_id='||p_percent_complete_id, x_Log_Level=> 3);
166 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_project_id='||p_project_id, x_Log_Level=> 3);
167 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_object_id='||p_object_id, x_Log_Level=> 3);
168 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_object_type='||p_object_type, x_Log_Level=> 3);
169 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_object_version_id='||p_object_version_id, x_Log_Level=> 3);
170 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_del_status='||p_del_status, x_Log_Level=> 3);
171 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_task_id='||p_task_id, x_Log_Level=> 3);
172 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_as_of_date='||p_as_of_date, x_Log_Level=> 3);
173 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_percent_complete='||p_percent_complete, x_Log_Level=> 3);
174 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_progress_status_code='||p_progress_status_code, x_Log_Level=> 3);
175 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_progress_comment='||p_progress_comment, x_Log_Level=> 3);
176 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_brief_overview='||p_brief_overview, x_Log_Level=> 3);
177 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_actual_finish_date='||p_actual_finish_date, x_Log_Level=> 3);
178 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_deliverable_due_date='||p_deliverable_due_date, x_Log_Level=> 3);
179 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_record_version_number='||p_record_version_number, x_Log_Level=> 3);
180 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_pm_product_code='||p_pm_product_code, x_Log_Level=> 3);
181 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'p_structure_type='||p_structure_type, x_Log_Level=> 3);
182 END IF;
183
184
185 IF (p_commit = FND_API.G_TRUE) THEN
186 savepoint UPDATE_DELIVERABLE_PROGRESS;
187 END IF;
188
189 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END IF;
192
193 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
194 FND_MSG_PUB.initialize;
195 END IF;
196
197 x_return_status := FND_API.G_RET_STS_SUCCESS;
198
199
200 --bug 4185364, start
201 -- if action is SAVE then delete al the present and future working records.
202 --there shud be only one working record.
203 if p_action = 'SAVE'
204 THEN
205 delete from pa_percent_completes
206 where project_id= p_project_id
207 and object_id = p_object_id
208 and published_flag = 'N'
209 and task_id = p_task_id
210 and structure_type = p_structure_type
211 ;
212
213 delete from pa_progress_rollup
214 where project_id= p_project_id
215 and object_id = p_object_id
216 and current_flag = 'W'
217 and proj_element_id = p_task_id
218 and structure_type = p_structure_type
219 and structure_version_id is null
220 ;
221 end if;
222 --bug 4185364, end
223
224
225 --bug 3879461
226 --This code is required is PUBLISH mode to delete working progress records on previous dates
227 if p_action = 'PUBLISH' and p_structure_type = 'WORKPLAN'
228 then
229 delete from pa_percent_completes
230 where project_id= p_project_id
231 and object_id = p_object_id
232 and published_flag = 'N'
233 and date_computed <= p_as_of_date --bug 4247839, modified so that two records are not created for same as of date
234 and structure_type = p_structure_type
235 ;
236
237 delete from pa_progress_rollup
238 where project_id= p_project_id
239 and object_id = p_object_id
240 and current_flag = 'W'
241 and as_of_date < p_as_of_date
242 and structure_type = p_structure_type
243 and structure_version_id is null
244 ;
245 end if;
246 --bug 3879461
247
248
249 -- It is possible to enter deliverable progress records even if it is not associated to a task
250
251 IF p_task_id IS NULL or p_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
252 l_task_id := 0;
253 ELSE
254 l_task_id := p_task_id;
255 END IF;
256 --- 5194985 either task_id should be 0 or that of the task whose % comp deriv method is deliverable.
257 begin
258 SELECT ppe.proj_element_id
259 into temp_task_id
260 FROM pa_proj_elements ppe,
261 pa_object_relationships por,
262 pa_task_types ttype
263 WHERE
264 ppe.object_type = 'PA_TASKS'
265 and por.object_id_to2 = p_object_id
266 and ppe.proj_element_id = por.object_id_from2
267 and por.object_type_to = 'PA_DELIVERABLES'
268 and por.relationship_type = 'A'
269 and por.relationship_subtype = 'TASK_TO_DELIVERABLE'
270 and decode(ppe.base_percent_comp_deriv_code,null, ttype.base_percent_comp_deriv_code, '^', ttype.base_percent_comp_deriv_code, ppe.base_percent_comp_deriv_code)='DELIVERABLE'
271 AND ppe.object_type ='PA_TASKS'
272 AND ppe.type_id = ttype.task_type_id;
273 exception when others then
274 temp_task_id := 0;
275 end;
276 if nvl(temp_task_id,0) <> 0 then
277 l_task_id := temp_task_id;
278 end if;
279
280 --- end 5194985
281 -- Bug 3957792 : Commenting this check, deliverable is a separete entity and progress can be entered even if associated task is cancelled
282 /*
283 --Added for BUG 3762650, by rtarway, added check for task's cancelled status
284 --if associated task status is CANCELLED, dlvr progress cant be entered
285 IF PA_PROGRESS_UTILS.get_system_task_status(PA_PROGRESS_UTILS.get_task_status( p_project_id, l_task_id)) = 'CANCELLED'
286 THEN
287 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
288 ,p_msg_name => 'PA_TP_DLVR_CANT_NTER_PRG_CANCEL');
289 x_msg_data := 'PA_TP_DLVR_CANT_NTER_PRG_CANCEL';
290 x_return_status := 'E';
291 RAISE FND_API.G_EXC_ERROR;
292 END IF;
293 */
294
295 IF g1_debug_mode = 'Y' THEN
296 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_task_id='||l_task_id, x_Log_Level=> 3);
297 END IF;
298
299 -- If deliverable is cancelled then progress can not be entered
300 l_deliverable_status := PA_PROGRESS_UTILS.get_system_task_status(PA_PROGRESS_UTILS.get_task_status( p_project_id, p_object_id, 'PA_DELIVERABLES'),'PA_DELIVERABLES');
301
302 -- Bug 6497559
303 OPEN cur_get_del_type_prog_attr;
304 FETCH cur_get_del_type_prog_attr INTO l_del_type_prog_enabled , l_dlvr_type_id , l_deliverable_existing_status, l_del_name ; --MSP Messages Change(changed position)
305 CLOSE cur_get_del_type_prog_attr;
306
307 --IF ( l_deliverable_status = 'CANCELLED' OR l_deliverable_status = 'ON_HOLD' )
308 -- rtarway Changed If condition, during BUG fix, 3668168 status comparison was wrong
309 --IF ( l_deliverable_status = 'DLVR_CANCELLED' OR l_deliverable_status = 'DLVR_ON_HOLD' )
310 --Changed for BUG 3762650, by rtarway, removed check for DLVR_ON_HOLD status
311 IF ( l_deliverable_status = 'DLVR_CANCELLED')
312 THEN
313 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
314 ,p_msg_name => 'PA_TP_CANT_NTER_DEL_CANCEL_AMG'
315 ,p_token1 => 'DEL_NAME' -- Bug 6497559
316 ,p_value1 => l_del_name
317 );
318 x_msg_data := 'PA_TP_CANT_NTER_DEL_CANCEL_AMG';
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 RAISE FND_API.G_EXC_ERROR;
321 END IF;
322
323 IF g1_debug_mode = 'Y' THEN
324 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'After Checking Deliverable Cancelled/On Hold Status', x_Log_Level=> 3);
325 END IF;
326
327 PA_PROGRESS_UTILS.get_project_progress_defaults(
328 p_project_id => p_project_id
329 ,p_structure_type => 'WORKPLAN'
330 ,x_WQ_ENABLED_FLAG => l_wq_enabled_flag
331 ,x_EFFORT_ENABLED_FLAG => l_est_remaining_eff_flag
332 ,x_PERCENT_COMP_ENABLED_FLAG => l_percent_complete_flag
333 ,x_task_weight_basis_code => l_task_weight_basis_code
334 ,X_ALLOW_COLLAB_PROG_ENTRY => l_ALLOW_COLLAB_PROG_ENTRY
335 ,X_ALLW_PHY_PRCNT_CMP_OVERRIDES => l_ALLW_PHY_PRCNT_CMP_OVERRIDES
336 );
337
338 IF g1_debug_mode = 'Y' THEN
339 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'After getting the progress defaults from project level', x_Log_Level=> 3);
340 END IF;
341
342
343 --OPEN cur_get_del_type_prog_attr;
344 --FETCH cur_get_del_type_prog_attr INTO l_del_type_prog_enabled , l_dlvr_type_id , l_deliverable_existing_status;
345 --CLOSE cur_get_del_type_prog_attr;
346
347
348 --OPEN cur_get_task_type_prog_attr;
349 --FETCH cur_get_task_type_prog_attr INTO l_task_type_prog_enabled;
350 --CLOSE cur_get_task_type_prog_attr;
351
352 IF g1_debug_mode = 'Y' THEN
353 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_del_type_prog_enabled='||l_del_type_prog_enabled, x_Log_Level=> 3);
354 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_task_type_prog_enabled='||l_task_type_prog_enabled, x_Log_Level=> 3);
355 END IF;
356
357 IF (nvl(l_del_type_prog_enabled,'N') = 'N')
358 THEN
359 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
360 ,p_msg_name => 'PA_TP_CANT_ENTER_DEL_PROG_AMG'
361 ,p_token1 => 'DEL_NAME' -- Bug 6497559
362 ,p_value1 => l_del_name);
363 x_msg_data := 'PA_TP_CANT_ENTER_DEL_PROG_AMG';
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 RAISE FND_API.G_EXC_ERROR;
366 END IF;
367
368 IF p_progress_mode <> 'FUTURE'
369 THEN
370 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
371 ,p_msg_name => 'PA_TP_WRONG_PRG_MODE4');
372 x_msg_data := 'PA_TP_WRONG_PRG_MODE4';
373 x_return_status := FND_API.G_RET_STS_ERROR;
374 RAISE FND_API.G_EXC_ERROR;
375 END IF;
376
377 IF p_as_of_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE OR p_as_of_date IS NULL
378 THEN
379 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
380 ,p_msg_name => 'PA_TP_INV_AOD_AMG'
381 ,p_token1 => 'DEL_NAME' -- Bug 6497559
382 ,p_value1 => l_del_name);
383 x_msg_data := 'PA_TP_INV_AOD';
384 x_return_status := FND_API.G_RET_STS_ERROR;
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387
388 /* FPM Dev CR 3
389 IF p_progress_status_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR p_progress_status_code IS NULL
390 THEN
391 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
392 ,p_msg_name => 'PA_TP_INV_PRG_STAT');
393 x_msg_data := 'PA_TP_INV_PRG_STAT';
394 x_return_status := FND_API.G_RET_STS_ERROR;
395 RAISE FND_API.G_EXC_ERROR;
396 END IF;
397 */
398 -- FPM Dev CR 3 : Defaulting Progress Status
399 IF p_progress_status_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR p_progress_status_code IS NULL
400 THEN
401 l_progress_status_code := 'PROGRESS_STAT_ON_TRACK';
402 ELSE
403 l_progress_status_code := p_progress_status_code;
404 END IF;
405
406 IF p_del_status = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR p_del_status IS NULL
407 THEN
408 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
409 ,p_msg_name => 'PA_TP_INV_DLV_STAT_AMG' -- Bug 6497559
410 ,p_token1 => 'DEL_NAME'
411 ,p_value1 => l_del_name
412 ); -- FPM Dev CR 1 : Changed message
413 x_msg_data := 'PA_TP_INV_DLV_STAT_AMG';
414 x_return_status := FND_API.G_RET_STS_ERROR;
415 RAISE FND_API.G_EXC_ERROR;
416 END IF;
417
418 IF g1_debug_mode = 'Y' THEN
419 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'After mode, status, as of date check', x_Log_Level=> 3);
420 END IF;
421
422 -- 3982374 moved below deliverable status change validation after the code to change
423 -- deliverable status based on physical % complete value
424
425 -- changed the location of this api call because
426 -- changing of deliverable status from the existing value to completed is done after this validation and becuase of
427 -- this validation api was not getting the changed deliverable status ( completed status ) as parameter
428 -- and user was able to complete the deliverable by entering physical % as 100 and completion date though deliverable
429 -- completion validations are not satisfied
430
431 /*
432 IF (p_del_status <> l_deliverable_existing_status) THEN
433 -- Check if the Deliverable Status can be changed
434 PA_DELIVERABLE_UTILS.IS_DLV_STATUS_CHANGE_ALLOWED
435 ( p_project_id => p_project_id
436 ,p_dlvr_item_id => p_object_id
437 ,p_dlvr_version_id => p_object_version_id
438 ,p_dlv_type_id => l_dlvr_type_id
439 ,p_dlvr_status_code => p_del_status
440 ,x_return_status => l_return_status
441 ,x_msg_count => l_msg_count
442 ,x_msg_data => l_msg_data
443 );
444
445 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
446 x_return_status := FND_API.G_RET_STS_ERROR;
447 RAISE FND_API.G_EXC_ERROR;
448 END IF;
449 IF g1_debug_mode = 'Y' THEN
450 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'After Checking Deliverable Status Change allowed', x_Log_Level=> 3);
451 END IF;
452 END IF;
453 */
454
455 -- 3982374 end
456
457 IF p_percent_complete = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
458 THEN
459 l_percent_complete := 0;
460 ELSE
461 l_percent_complete := nvl(p_percent_complete,0);
462 END IF;
463
464 IF p_actual_finish_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
465 THEN
466 l_actual_finish_date := null;
467 ELSE
468 l_actual_finish_date := p_actual_finish_date;
469 END IF;
470
471
472 IF p_del_status = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
473 THEN
474 l_del_status := null;
475 ELSE
476 l_del_status := p_del_status;
477 END IF;
478
479 IF p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
480 THEN
481 l_pm_product_code := null;
482 ELSE
483 l_pm_product_code := p_pm_product_code;
484 END IF;
485
486 IF p_PROGRESS_COMMENT = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
487 THEN
488 l_PROGRESS_COMMENT := null;
489 ELSE
490 l_PROGRESS_COMMENT := p_PROGRESS_COMMENT;
491 END IF;
492
493 IF p_brief_overview = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
494 THEN
495 l_brief_overview := null;
496 ELSE
497 l_brief_overview := p_brief_overview;
498 END IF;
499
500 IF (l_percent_complete < 0 or l_percent_complete > 100) THEN
501 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
502 ,p_msg_name => 'PA_PERC_COMP_INV_DLV_AMG' -- Bug 6497559
503 ,p_token1 => 'DEL_NAME'
504 ,p_value1 => l_del_name
505 );
506 x_msg_data := 'PA_PERC_COMP_INV_DLV_AMG';
507 x_return_status := FND_API.G_RET_STS_ERROR;
508 RAISE FND_API.G_EXC_ERROR;
509 END IF;
510
511
512 /* IF (l_percent_complete < 100 AND l_percent_complete_flag = 'Y') THEN
513 l_actual_finish_date := to_date(null);
514 END IF; */ --- 3804420 ,instead throw an error
515
516 -- Changed by rtarway for BUG 3668168
517 --Moved from below , while fixing for 3668168
518 -- Bug 3606627 : AMG Validation
519 IF p_calling_module = 'AMG' THEN
520 BEGIN
521 SELECT 'x' INTO l_dummy
522 FROM pa_project_statuses
523 WHERE status_type = 'DELIVERABLE'
524 AND project_status_code = l_del_status;
525 EXCEPTION
526 WHEN NO_DATA_FOUND THEN
527 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
528 p_msg_name => 'PA_TP_INV_DEL_STATUS',
529 P_TOKEN1 => 'OBJECT_ID',
530 P_VALUE1 => p_object_id);
531 x_msg_data := 'PA_TP_INV_DEL_STATUS';
532 x_return_status := FND_API.G_RET_STS_ERROR;
533 RAISE FND_API.G_EXC_ERROR;
534 END;
535
536 --l_task_id is validated in pa_status_pub itself
537 --l_as_of_date is validated in pa_status_pub
538
539 BEGIN
540 SELECT 'xyz' INTO l_dummy
541 FROM pa_project_statuses
542 WHERE status_type = 'PROGRESS'
543 AND project_status_code = l_progress_status_code;
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
547 p_msg_name => 'PA_INVALID_PROG_STATUS',
548 P_TOKEN1 => 'OBJECT_ID',
549 P_VALUE1 => p_object_id);
550 x_msg_data := 'PA_INVALID_PROG_STATUS';
551 x_return_status := FND_API.G_RET_STS_ERROR;
552 RAISE FND_API.G_EXC_ERROR;
553 END;
554 END IF; --p_calling_module = 'AMG'
555 -- Moved this from below while fixing for 3668168
556 -- Changed by rtarway for BUG 3668168
557
558 -- Changed by rtarway for BUG 3668168, should use l_del_status
559 --l_del_status2 := PA_PROGRESS_UTILS.get_system_task_status( p_del_status, 'PA_DELIVERABLES' );
560 l_del_status2 := PA_PROGRESS_UTILS.get_system_task_status( l_del_status, 'PA_DELIVERABLES' );
561
562 --Added by rtarway for BUG 3668168, if condition
563 IF l_percent_complete_flag = 'Y' THEN
564 /* Bug 3606627 Deleiverable Status Should be defaulted as done for tasks*/
565 IF (
566 (l_percent_complete > 0 AND l_percent_complete < 100)
567 AND
568 ( l_del_status2 IS NULL
569 OR
570 --Added by rtarway for BUG 366168
571 (l_del_status2 = 'DLVR_NOT_STARTED')
572 )
573 --Commented by rtarway for BUG 366168
574 --(l_del_status2 IS NULL OR l_del_status2 <> 'DLVR_IN_PROGRESS')
575 )
576 THEN
577 l_del_status2 := 'DLVR_IN_PROGRESS';
578 l_del_status := 'DLVR_IN_PROGRESS';
579
580 --Commented by rtarway, 3668168, this defaulting is not requird, user will get an error in this case
581 /*ELSIF ( l_percent_complete = 0
582 AND
583 (
584 l_del_status2 IS NULL
585 OR
586 --Added by rtarway for BUG 366168
587 (l_del_status2 <> 'DLVR_NOT_STARTED' AND l_del_status2 <> 'DLVR_ON_HOLD' AND l_del_status2 <> 'DLVR_CANCELLED')
588 --Commented by rtarway for BUG 366168
589 --(l_del_status2 <> 'DLVR_NOT_STARTED')
590 )
591 )
592 THEN
593 l_del_status2 := 'DLVR_NOT_STARTED';
594 l_del_status := 'DLVR_NOT_STARTED';
595 */
596 -- Changed after BUG review 3668168
597 ELSIF ( l_percent_complete = 100 AND (l_del_status2 IS NULL OR l_del_status2 <> 'DLVR_COMPLETED'))
598 THEN
599 l_del_status2 := 'DLVR_COMPLETED';
600 l_del_status := 'DLVR_COMPLETED';
601 END IF;
602
603
604 IF g1_debug_mode = 'Y' THEN
605 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Status and Date Combination check', x_Log_Level=> 3);
606 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_del_status2='||l_del_status2, x_Log_Level=> 3);
607 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_percent_complete='||l_percent_complete, x_Log_Level=> 3);
608 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_actual_finish_date='||l_actual_finish_date, x_Log_Level=> 3);
609 END IF;
610
611
612 IF l_del_status2 = 'DLVR_NOT_STARTED' AND NOT( l_actual_finish_date IS NULL AND l_percent_complete = 0) OR
613 l_del_status2 = 'DLVR_IN_PROGRESS' AND NOT ( l_actual_finish_date IS NULL AND l_percent_complete > 0 AND l_percent_complete < 100 ) OR
614 l_del_status2 = 'DLVR_COMPLETED' AND NOT ( l_actual_finish_date IS NOT NULL AND l_percent_complete = 100 ) OR
615 (l_del_status2 is NULL) OR (l_del_status2 = '')
616 THEN
617 --Commented by rtarway for BUG 3668168, if condition
618 --IF l_percent_complete_flag = 'Y'
619 --THEN
620 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
621 ,p_msg_name => 'PA_DELP_STAT_DTES_PC_COMB_AMG'
622 ,p_token1 => 'DEL_NAME' -- Bug 6497559
623 ,p_value1 => l_del_name);
624 x_msg_data := 'PA_DELP_STAT_DTES_PC_COMB_AMG';
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 RAISE FND_API.G_EXC_ERROR;
627 --END IF;
628 END IF;
629 --Added by rtarway for BUG 3668168, Else if
630 ELSIF l_percent_complete_flag = 'N'
631 THEN
632 IF l_percent_complete > 0
633 THEN
634 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
635 ,p_msg_name => 'PA_TP_PCC_DISABL_AMG'
636 ,p_token1 => 'TASK_NAME' -- Bug 6497559
637 ,p_value1 => PA_TASK_UTILS.get_task_name(p_task_id)
638 ,p_token2 => 'TASK_NUMBER'
639 ,p_value2 => PA_TASK_UTILS.get_task_number(p_task_id)
640 ,p_token3 => 'PROJECT_NAME'
641 ,p_value3 => PA_TASK_UTILS.get_project_name(p_project_id)
642 ,p_token4 => 'PROJECT_NUMBER'
643 ,p_value4 => PA_TASK_UTILS.get_project_number(p_project_id));
644 x_msg_data := 'PA_TP_PCC_DISABL_AMG';
645 x_return_status := FND_API.G_RET_STS_ERROR;
646 RAISE FND_API.G_EXC_ERROR;
647 END IF;
648 -- Added by rtarway , date validation check
649 IF l_del_status2 = 'DLVR_NOT_STARTED' AND NOT( l_actual_finish_date IS NULL ) OR
650 l_del_status2 = 'DLVR_IN_PROGRESS' AND NOT ( l_actual_finish_date IS NULL ) OR
651 l_del_status2 = 'DLVR_COMPLETED' AND NOT ( l_actual_finish_date IS NOT NULL ) OR
652 (l_del_status2 is NULL) OR (l_del_status2 = '')
653 THEN
654 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
655 ,p_msg_name => 'PA_DELP_STAT_DTES_PC_COMB_AMG'
656 ,p_token1 => 'DEL_NAME' -- Bug 6497559
657 ,p_value1 => l_del_name);
658 x_msg_data := 'PA_DELP_STAT_DTES_PC_COMB_AMG';
659 x_return_status := FND_API.G_RET_STS_ERROR;
660 RAISE FND_API.G_EXC_ERROR;
661 END IF;
662 END IF;
663
664 -- 3982374 Calling deliverable status change validation to check deliverable status
665 -- change is allowed or not
666
667 IF (l_del_status <> l_deliverable_existing_status) THEN
668 -- Check if the Deliverable Status can be changed
669 PA_DELIVERABLE_UTILS.IS_DLV_STATUS_CHANGE_ALLOWED
670 ( p_project_id => p_project_id
671 ,p_dlvr_item_id => p_object_id
672 ,p_dlvr_version_id => p_object_version_id
673 ,p_dlv_type_id => l_dlvr_type_id
674 ,p_dlvr_status_code => l_del_status
675 ,x_return_status => l_return_status
676 ,x_msg_count => l_msg_count
677 ,x_msg_data => l_msg_data
678 );
679
680 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
681 x_return_status := FND_API.G_RET_STS_ERROR;
682 RAISE FND_API.G_EXC_ERROR;
683 END IF;
684 IF g1_debug_mode = 'Y' THEN
685 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'After Checking Deliverable Status Change allowed', x_Log_Level=> 3);
686 END IF;
687 END IF;
688
689 -- 3982374 end
690
691 --Commented by rtarway, 3668168
692 --Moved this code up while fixing for BUG 3668168
693 /*
694 -- Bug 3606627 : AMG Validation
695 IF p_calling_module = 'AMG' THEN
696 BEGIN
697 SELECT 'x' INTO l_dummy
698 FROM pa_project_statuses
699 WHERE status_type = 'DELIVERABLE'
700 AND project_status_code = l_del_status;
701 EXCEPTION
702 WHEN NO_DATA_FOUND THEN
703 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
704 p_msg_name => 'PA_TP_INV_DEL_STATUS',
705 P_TOKEN1 => 'OBJECT_ID',
706 P_VALUE1 => p_object_id);
707 x_msg_data := 'PA_TP_INV_DEL_STATUS';
708 x_return_status := FND_API.G_RET_STS_ERROR;
709 RAISE FND_API.G_EXC_ERROR;
710 END;
711
712 --l_task_id is validated in pa_status_pub itself
713 --l_as_of_date is validated in pa_status_pub
714
715 BEGIN
716 SELECT 'xyz' INTO l_dummy
717 FROM pa_project_statuses
718 WHERE status_type = 'PROGRESS'
719 AND project_status_code = l_progress_status_code;
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
723 p_msg_name => 'PA_INVALID_PROG_STATUS',
724 P_TOKEN1 => 'OBJECT_ID',
725 P_VALUE1 => p_object_id);
726 x_msg_data := 'PA_INVALID_PROG_STATUS';
727 x_return_status := FND_API.G_RET_STS_ERROR;
728 RAISE FND_API.G_EXC_ERROR;
729 END;
730 END IF; --p_calling_module = 'AMG'
731 */
732 --Commented by rtarway, 3668168
733
734 l_BASE_PERCENT_COMPLETE := l_percent_complete;
735 l_BASE_PROGRESS_STATUS_CODE := l_progress_status_code;
736
737 IF g1_debug_mode = 'Y' THEN
738 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Updating progress outdated flag', x_Log_Level=> 3);
739 END IF;
740
741 --Update outdated flag back to 'N'
742 UPDATE pa_proj_elements
743 SET progress_outdated_flag = 'N'
744 WHERE proj_element_id = p_object_id
745 AND project_id = p_project_id
746 AND object_type = p_object_type;
747
748 -- FPM Dev CR 1 : Passed more parameters in the following call
749 l_last_progress_date := PA_PROGRESS_UTILS.GET_LATEST_AS_OF_DATE(p_task_id => null, p_project_id=> p_project_id,p_object_id=> p_object_id, p_object_type=>p_object_type, p_structure_type=> p_structure_type );
750
751 l_working_aod := PA_PROGRESS_UTILS.Working_version_exist(
752 --p_task_id => p_object_id --bug# 3764224 Changes for RLM
753 p_task_id => null
754 ,p_project_id => p_project_id
755 ,p_object_type => p_object_type
756 ,p_object_id => p_object_id --bug# 3764224 Added for RLM
757 ,p_as_of_date => p_as_of_date --bug 4185364 get working records upto passed as of date as we dont want to
758 ); -- update future working records
759
760 l_progress_exists_on_aod := PA_PROGRESS_UTILS.check_prog_exists_on_aod(
761 --p_task_id => p_object_id --bug# 3764224 Changes for RLM
762 p_task_id => null
763 ,p_as_of_date => p_as_of_date
764 ,p_project_id => p_project_id
765 ,p_object_version_id => p_object_version_id
766 ,p_object_type => p_object_type
767 ,p_object_id => p_object_id --bug# 3764224 Added for RLM
768 );
769
770 IF g1_debug_mode = 'Y' THEN
771 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_last_progress_date='||l_last_progress_date, x_Log_Level=> 3);
772 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_working_aod='||l_working_aod, x_Log_Level=> 3);
773 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_progress_exists_on_aod='||l_progress_exists_on_aod, x_Log_Level=> 3);
774 END IF;
775
776 -- Percent Complete Progress Rollup Possible Comments
777 -- Insert Update No Applicable for summary level
778 -- Insert Insert Yes Normal Case
779 -- Update Insert Yes When You save on 1st March and Then publish on 8th March
780 -- Update Update Yes Normal Case
781
782 IF trunc(p_as_of_date) < trunc(NVL( l_last_progress_date, p_as_of_date ))
783 --Commented by rtarway for Correction flow
784 --AND l_working_aod IS NULL -- progress exists after as of date
785 THEN
786 --You cannot create a future progress when there exists a progress
787 --after AS_OF_DATE for this deliverable.
788 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
789 p_msg_name => 'PA_TP_INV_AOD2');
790
791 x_msg_data := 'PA_TP_WRONG_DEL_PRG_MODE3';
792 x_return_status := FND_API.G_RET_STS_ERROR;
793 RAISE FND_API.G_EXC_ERROR;
794 --Commented by rtarway for Correction flow
795 -- FPM Dev CR 1 : Added the following IF condition
796 /*ELSIF trunc(p_as_of_date) <= trunc(NVL( l_last_progress_date, p_as_of_date-1)) AND l_progress_exists_on_aod = 'PUBLISHED' THEN
797 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
798 ,p_msg_name => 'PA_TP_INV_AOD2');
799 x_msg_data := 'PA_TP_INV_AOD2';
800 x_return_status := FND_API.G_RET_STS_ERROR;
801 RAISE FND_API.G_EXC_ERROR;*/
802 --End Commented by rtarway for Correction flow
803 ELSE
804 --Validate as of date
805 -- Bug 3627315 : Check valid as of date should not be called from AMG or Task Progress Details page
806 -- Beacuse from both the places we submit progress for all objects against one cycle date
807
808 --bug 3994165, commmenting as as of date validation is not required
809 /*IF p_calling_module <> 'AMG' -- Bug 3627315
810 AND p_calling_module <> 'TASK_PROG_DET_PAGE' -- Bug 3627315
811 AND PA_PROGRESS_UTILS.CHECK_VALID_AS_OF_DATE( p_as_of_date, p_project_id, p_object_id, 'PA_DELIVERABLES' ) = 'N'
812 AND trunc(nvl(l_last_progress_date,p_as_of_date + 1 )) <> trunc(p_as_of_date)
813 THEN
814 --Add message
815 --Invalid as of date
816 --Message Changed by rtarway during Correction flow changes
817 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
818 ,p_msg_name => 'PA_TP_INV_AOD2');
819 x_msg_data := 'PA_TP_INV_AOD2';
820 x_return_status := FND_API.G_RET_STS_ERROR;
821 RAISE FND_API.G_EXC_ERROR;
822
823 END IF;*/
824 --
825 --bug 4185364, not needed
826 /* Begin: Fix for Bug # 3958892. */
827
828 /*IF (p_as_of_date = NVL(l_last_progress_date, p_as_of_date + 1 ) AND p_action = 'SAVE') THEN
829
830 if (l_working_aod = p_as_of_date) then
831 l_db_action := 'UPDATE';
832 else
833 l_db_action := 'CREATE';
834 end if;
835
836 ELSIF (p_as_of_date = NVL(l_last_progress_date, p_as_of_date + 1 ) AND p_action = 'PUBLISH') THEN
837
838 l_db_action := 'CREATE';
839
840 ELSE */
841
842 /* End: Fix for Bug # 3958892. */
843
844
845 IF l_progress_exists_on_aod = 'WORKING'
846 THEN
847 --update the existing working progress record ( publish and roll it only when p_action = 'PUBLISH' )
848 l_db_action := 'UPDATE';
849 ELSIF l_progress_exists_on_aod = 'PUBLISHED'
850 THEN
851 --bug 4185364, if correcting published record then action shud be update as we dont want to maintain history
852 -- of corrected records , if save then we will create new record.
853 IF p_action = 'PUBLISH' THEN
854 l_db_action := 'UPDATE';
855 ELSE
856 l_db_action := 'CREATE';
857 END IF;
858 ELSIF l_progress_exists_on_aod = 'N'
859 THEN
860 --End Add 3595585
861 --Create a new working progress record. ( publish and roll it only when p_action = 'PUBLISH' )
862 --1. if l_progress_exists_on_aod = 'PUBLISHED' then we create new record in ppc
863 --2. if l_progress_exists_on_aod = 'N' then new record in PR otherwise its an update to Progress Rollup
864 l_db_action := 'CREATE';
865 IF l_working_aod IS NOT NULL --now this case will never come
866 THEN
867 l_db_action := 'UPDATE';
868 END IF;
869 END IF;
870
871 /* Begin: Fix for Bug # 3958892. */
872
873 --END IF;
874
875 /* End: Fix for Bug # 3958892. */
876
877
878 END IF;
879
880 IF g1_debug_mode = 'Y' THEN
881 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_db_action='||l_db_action, x_Log_Level=> 3);
882 END IF;
883
884
885 IF ( p_action = 'PUBLISH')
886 THEN
887
888 IF g1_debug_mode = 'Y' THEN
889 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Action is Publish', x_Log_Level=> 3);
890 END IF;
891
892 l_published_flag := 'Y';
893 ----
894 l_rollup_progress_status := l_progress_status_code;
895 l_rollup_completed_percentage := l_percent_complete;
896 l_rollup_current_flag := 'Y'; -- Bug 3879461
897
898 l_current_flag := 'Y';
899 UPDATE pa_percent_completes
900 SET current_flag = 'N'
901 WHERE project_id = p_project_id
902 AND object_id = p_object_id
903 AND current_flag = 'Y'
904 AND object_type = p_object_type
905 AND structure_type = p_structure_type;
906
907 UPDATE pa_progress_rollup
908 SET current_flag = 'N'
909 WHERE project_id = p_project_id
910 AND object_id = p_object_id
911 AND current_flag = 'Y'
912 AND object_type = p_object_type
913 AND structure_type = p_structure_type
914 AND structure_version_id is NULL;
915
916 -- Bug 3879461 Begin
917 -- This case would not be possible for Deliverable, but still code is there to make it in sycn with Assignments Case
918 -- IF l_db_action = 'UPDATE' THEN -- Commented to fix Bug # 3958892.
919 /*
920 -- Delete the published progress record on the same as of date
921 DELETE FROM pa_progress_rollup
922 where project_id = p_project_id
923 and object_id = p_object_id
924 and object_type = p_object_type
925 and structure_version_id is null
926 and structure_type = 'WORKPLAN'
927 and current_flag = 'Y'
928 and trunc(as_of_date) = trunc(p_as_of_date)
929 and exists(select 1
930 from pa_progress_rollup
931 where project_id = p_project_id
932 and object_id = p_object_id
933 and object_type = p_object_type
934 and structure_version_id is null
935 and structure_type = 'WORKPLAN'
936 and current_flag = 'W'
937 and trunc(as_of_date) = trunc(p_as_of_date)
938 );
939 -- Update the working progress record on the same as of date as published progress
940 -- so that while updating rollup record its values can be considered
941 -- Basically this is done so get_prog_rollup_id can return this row for update mode
942
943 Update pa_progress_rollup
944 set current_flag = 'Y'
945 where project_id = p_project_id
946 and object_id = p_object_id
947 and object_type = p_object_type
948 and structure_version_id is null
949 and structure_type = 'WORKPLAN'
950 and current_flag = 'W'
951 and trunc(as_of_date) = trunc(p_as_of_date);
952 */
953 Delete from pa_progress_rollup
954 where project_id = p_project_id
955 and object_id = p_object_id
956 and object_type = p_object_type
957 and structure_version_id is null
958 and structure_type = 'WORKPLAN'
959 and current_flag = 'W'
960 and trunc(as_of_date) <= trunc(p_as_of_date); -- Fix for Bug # 3958892.
961
962 -- END IF; -- Commented to fix Bug # 3958892.
963 -- Bug 3879461 End
964
965 IF g1_debug_mode = 'Y' THEN
966 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'After updating percent complete and progress rollup current flag to N', x_Log_Level=> 3);
967 END IF;
968
969 ELSE
970 IF g1_debug_mode = 'Y' THEN
971 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Action is Save', x_Log_Level=> 3);
972 END IF;
973 l_rollup_progress_status := null;
974 l_rollup_completed_percentage := null;
975 l_published_flag := 'N';
976 l_current_flag := 'N'; -- FPM Dev CR 1
977 l_rollup_current_flag := 'W'; -- Bug 3879461
978 END IF;
979
980 l_prog_pa_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(p_as_of_date),null);
981 l_prog_gl_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(p_as_of_date),null);
982
983
984 IF l_db_action = 'CREATE'
985 THEN
986
987 -- IF p_percent_complete_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR p_percent_complete_id IS NULL
988 --THEN
989 l_percent_complete_id := null;
990 --ELSE
991 -- l_percent_complete_id := p_percent_complete_id;
992 --END IF;
993
994
995 l_att_pc_id := p_percent_complete_id;
996
997 IF g1_debug_mode = 'Y' THEN
998 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'DB Action is Create', x_Log_Level=> 3);
999 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Going to Insert in percent complete table', x_Log_Level=> 3);
1000 END IF;
1001
1002 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Going to Insert in percent complete tables l_percent_complete_id='||l_percent_complete_id, x_Log_Level=> 3);
1003
1004 PA_PERCENT_COMPLETES_PKG.INSERT_ROW(
1005 p_TASK_ID => l_task_id
1006 ,p_DATE_COMPUTED => p_as_of_date
1007 ,p_LAST_UPDATE_DATE => SYSDATE
1008 ,p_LAST_UPDATED_BY => l_user_id
1009 ,p_CREATION_DATE => SYSDATE
1010 ,p_CREATED_BY => l_user_id
1011 ,p_LAST_UPDATE_LOGIN => l_login_id
1012 ,p_COMPLETED_PERCENTAGE => l_percent_complete
1013 ,p_DESCRIPTION => l_brief_overview
1014 ,p_PROJECT_ID => p_project_id
1015 ,p_PM_PRODUCT_CODE => l_pm_product_code
1016 ,p_CURRENT_FLAG => l_current_flag
1017 ,p_OBJECT_TYPE => p_object_type
1018 ,p_OBJECT_ID => p_object_id
1019 ,p_OBJECT_VERSION_ID => p_object_version_id
1020 ,p_PROGRESS_STATUS_CODE => l_progress_status_code
1021 ,p_ACTUAL_START_DATE => null
1022 ,p_ACTUAL_FINISH_DATE => l_actual_finish_date
1023 ,p_ESTIMATED_START_DATE => null
1024 ,p_ESTIMATED_FINISH_DATE => null
1025 ,p_PUBLISHED_FLAG => l_published_flag
1026 ,p_PUBLISHED_BY_PARTY_ID => l_published_by_party_id
1027 ,p_PROGRESS_COMMENT => l_PROGRESS_COMMENT
1028 ,p_HISTORY_FLAG => 'N'
1029 ,p_status_code => l_del_status
1030 ,x_PERCENT_COMPLETE_ID => l_percent_complete_id
1031 ,p_ATTRIBUTE_CATEGORY => null
1032 ,p_ATTRIBUTE1 => null
1033 ,p_ATTRIBUTE2 => null
1034 ,p_ATTRIBUTE3 => null
1035 ,p_ATTRIBUTE4 => null
1036 ,p_ATTRIBUTE5 => null
1037 ,p_ATTRIBUTE6 => null
1038 ,p_ATTRIBUTE7 => null
1039 ,p_ATTRIBUTE8 => null
1040 ,p_ATTRIBUTE9 => null
1041 ,p_ATTRIBUTE10 => null
1042 ,p_ATTRIBUTE11 => null
1043 ,p_ATTRIBUTE12 => null
1044 ,p_ATTRIBUTE13 => null
1045 ,p_ATTRIBUTE14 => null
1046 ,p_ATTRIBUTE15 => null
1047 ,p_structure_type => p_structure_type
1048 );
1049
1050 -- FPM Dev CR 3 : Raising Error
1051 IF Fnd_Msg_Pub.count_msg > 0 THEN
1052 RAISE FND_API.G_EXC_ERROR;
1053 END IF;
1054
1055
1056
1057 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Going to Insert in percent complete tablessaasasas', x_Log_Level=> 3);
1058
1059 IF g1_debug_mode = 'Y' THEN
1060 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'After perrcent complete', x_Log_Level=> 3);
1061 END IF;
1062
1063
1064 l_PROGRESS_ROLLUP_ID := null;
1065 --Create record in progress rollup
1066
1067 IF g1_debug_mode = 'Y' THEN
1068 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Getting rollup id', x_Log_Level=> 3);
1069 END IF;
1070
1071 l_PROGRESS_ROLLUP_ID := PA_PROGRESS_UTILS.get_prog_rollup_id(
1072 p_project_id => p_project_id
1073 ,p_object_id => p_object_id
1074 ,p_object_type => p_object_type
1075 ,p_object_version_id => p_object_version_id
1076 ,p_as_of_date => p_as_of_date
1077 ,p_action => p_action -- Bug 3879461
1078 ,x_record_version_number => l_rollup_rec_ver_number
1079 );
1080
1081 IF g1_debug_mode = 'Y' THEN
1082 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_PROGRESS_ROLLUP_ID='||l_PROGRESS_ROLLUP_ID, x_Log_Level=> 3);
1083 END IF;
1084
1085
1086 IF l_PROGRESS_ROLLUP_ID IS NULL
1087 THEN
1088 l_EFF_ROLLUP_PERCENT_COMP := null;
1089 l_EFF_ROLLUP_PROG_STAT_CODE := null;
1090
1091 IF g1_debug_mode = 'Y' THEN
1092 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'INserting in progress rollup table', x_Log_Level=> 3);
1093 END IF;
1094
1095
1096 PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
1097 X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
1098 ,X_PROJECT_ID => p_project_id
1099 ,X_OBJECT_ID => p_object_id
1100 ,X_OBJECT_TYPE => p_object_type
1101 ,X_AS_OF_DATE => p_as_of_date
1102 ,X_OBJECT_VERSION_ID => p_object_version_id
1103 ,X_LAST_UPDATE_DATE => SYSDATE
1104 ,X_LAST_UPDATED_BY => l_user_id
1105 ,X_CREATION_DATE => SYSDATE
1106 ,X_CREATED_BY => l_user_id
1107 ,X_PROGRESS_STATUS_CODE => l_progress_status_code
1108 ,X_LAST_UPDATE_LOGIN => l_login_id
1109 ,X_INCREMENTAL_WORK_QTY => null
1110 ,X_CUMULATIVE_WORK_QTY => null
1111 ,X_BASE_PERCENT_COMPLETE => l_BASE_PERCENT_COMPLETE
1112 ,X_EFF_ROLLUP_PERCENT_COMP => l_EFF_ROLLUP_PERCENT_COMP
1113 ,X_COMPLETED_PERCENTAGE => l_percent_complete
1114 ,X_ESTIMATED_START_DATE => null
1115 ,X_ESTIMATED_FINISH_DATE => null
1116 ,X_ACTUAL_START_DATE => null
1117 ,X_ACTUAL_FINISH_DATE => l_actual_finish_date
1118 ,X_EST_REMAINING_EFFORT => null
1119 ,X_BASE_PERCENT_COMP_DERIV_CODE => null
1120 ,X_BASE_PROGRESS_STATUS_CODE => l_BASE_PROGRESS_STATUS_CODE
1121 ,X_EFF_ROLLUP_PROG_STAT_CODE => null
1122 ,x_percent_complete_id => l_percent_complete_id
1123 ,X_STRUCTURE_TYPE => p_structure_type
1124 --,X_PROJ_ELEMENT_ID => null --bug# 3799060 For deliverables proj_element_id should be populated. It is a must.
1125 ,X_PROJ_ELEMENT_ID => l_task_id
1126 ,X_STRUCTURE_VERSION_ID => null
1127 ,X_PPL_ACT_EFFORT_TO_DATE => null
1128 ,X_EQPMT_ACT_EFFORT_TO_DATE => null
1129 ,X_EQPMT_ETC_EFFORT => null
1130 ,X_OTH_ACT_COST_TO_DATE_TC => null
1131 ,X_OTH_ACT_COST_TO_DATE_FC => null
1132 ,X_OTH_ACT_COST_TO_DATE_PC => null
1133 ,X_OTH_ETC_COST_TC => null
1134 ,X_OTH_ETC_COST_FC => null
1135 ,X_OTH_ETC_COST_PC => null
1136 ,X_PPL_ACT_COST_TO_DATE_TC => null
1137 ,X_PPL_ACT_COST_TO_DATE_FC => null
1138 ,X_PPL_ACT_COST_TO_DATE_PC => null
1139 ,X_PPL_ETC_COST_TC => null
1140 ,X_PPL_ETC_COST_FC => null
1141 ,X_PPL_ETC_COST_PC => null
1142 ,X_EQPMT_ACT_COST_TO_DATE_TC => null
1143 ,X_EQPMT_ACT_COST_TO_DATE_FC => null
1144 ,X_EQPMT_ACT_COST_TO_DATE_PC => null
1145 ,X_EQPMT_ETC_COST_TC => null
1146 ,X_EQPMT_ETC_COST_FC => null
1147 ,X_EQPMT_ETC_COST_PC => null
1148 ,X_EARNED_VALUE => null
1149 ,X_TASK_WT_BASIS_CODE => null
1150 ,X_SUBPRJ_PPL_ACT_EFFORT => null
1151 ,X_SUBPRJ_EQPMT_ACT_EFFORT => null
1152 ,X_SUBPRJ_PPL_ETC_EFFORT => null
1153 ,X_SUBPRJ_EQPMT_ETC_EFFORT => null
1154 ,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
1155 ,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
1156 ,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
1157 ,X_SUBPRJ_PPL_ACT_COST_TC => null
1158 ,X_SUBPRJ_PPL_ACT_COST_FC => null
1159 ,X_SUBPRJ_PPL_ACT_COST_PC => null
1160 ,X_SUBPRJ_EQPMT_ACT_COST_TC => null
1161 ,X_SUBPRJ_EQPMT_ACT_COST_FC => null
1162 ,X_SUBPRJ_EQPMT_ACT_COST_PC => null
1163 ,X_SUBPRJ_OTH_ETC_COST_TC => null
1164 ,X_SUBPRJ_OTH_ETC_COST_FC => null
1165 ,X_SUBPRJ_OTH_ETC_COST_PC => null
1166 ,X_SUBPRJ_PPL_ETC_COST_TC => null
1167 ,X_SUBPRJ_PPL_ETC_COST_FC => null
1168 ,X_SUBPRJ_PPL_ETC_COST_PC => null
1169 ,X_SUBPRJ_EQPMT_ETC_COST_TC => null
1170 ,X_SUBPRJ_EQPMT_ETC_COST_FC => null
1171 ,X_SUBPRJ_EQPMT_ETC_COST_PC => null
1172 ,X_SUBPRJ_EARNED_VALUE => null
1173 ,X_CURRENT_FLAG => l_rollup_current_flag -- Bug 3879461 l_current_flag
1174 ,X_PROJFUNC_COST_RATE_TYPE => null
1175 ,X_PROJFUNC_COST_EXCHANGE_RATE => null
1176 ,X_PROJFUNC_COST_RATE_DATE => null
1177 ,X_PROJ_COST_RATE_TYPE => null
1178 ,X_PROJ_COST_EXCHANGE_RATE => null
1179 ,X_PROJ_COST_RATE_DATE => null
1180 ,X_TXN_CURRENCY_CODE => null
1181 ,X_PROG_PA_PERIOD_NAME => l_prog_pa_period_name
1182 ,X_PROG_GL_PERIOD_NAME => l_prog_gl_period_name
1183 --Added by rtarway BUG 3608801
1184 ,X_OTH_QUANTITY_TO_DATE => null
1185 ,X_OTH_ETC_QUANTITY => null
1186 --End Added by rtarway BUG 3608801
1187 ,X_OTH_ACT_RAWCOST_TO_DATE_TC => null
1188 ,X_OTH_ACT_RAWCOST_TO_DATE_FC => null
1189 ,X_OTH_ACT_RAWCOST_TO_DATE_PC => null
1190 ,X_OTH_ETC_RAWCOST_TC => null
1191 ,X_OTH_ETC_RAWCOST_FC => null
1192 ,X_OTH_ETC_RAWCOST_PC => null
1193 ,X_PPL_ACT_RAWCOST_TO_DATE_TC => null
1194 ,X_PPL_ACT_RAWCOST_TO_DATE_FC => null
1195 ,X_PPL_ACT_RAWCOST_TO_DATE_PC => null
1196 ,X_PPL_ETC_RAWCOST_TC => null
1197 ,X_PPL_ETC_RAWCOST_FC => null
1198 ,X_PPL_ETC_RAWCOST_PC => null
1199 ,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => null
1200 ,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => null
1201 ,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => null
1202 ,X_EQPMT_ETC_RAWCOST_TC => null
1203 ,X_EQPMT_ETC_RAWCOST_FC => null
1204 ,X_EQPMT_ETC_RAWCOST_PC => null
1205 ,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
1206 ,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
1207 ,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
1208 ,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
1209 ,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
1210 ,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
1211 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
1212 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
1213 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
1214 ,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
1215 ,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
1216 ,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
1217 ,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
1218 ,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
1219 ,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
1220 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
1221 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
1222 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
1223 );
1224
1225
1226 IF Fnd_Msg_Pub.count_msg > 0 THEN
1227 RAISE FND_API.G_EXC_ERROR;
1228 END IF;
1229
1230
1231 ELSE
1232 ---update progress rollup
1233 -- This is not possible at lowest level that is delievrable and assignments. But still let code be there.
1234
1235 IF g1_debug_mode = 'Y' THEN
1236 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Updating in progress rollup table', x_Log_Level=> 3);
1237 END IF;
1238
1239
1240 PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
1241 X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
1242 ,X_PROJECT_ID => p_project_id
1243 ,X_OBJECT_ID => p_object_id
1244 ,X_OBJECT_TYPE => p_object_type
1245 ,X_AS_OF_DATE => p_as_of_date
1246 ,X_OBJECT_VERSION_ID => p_object_version_id
1247 ,X_LAST_UPDATE_DATE => SYSDATE
1248 ,X_LAST_UPDATED_BY => l_user_id
1249 ,X_PROGRESS_STATUS_CODE => l_progress_status_code
1250 ,X_LAST_UPDATE_LOGIN => l_login_id
1251 ,X_INCREMENTAL_WORK_QTY => null
1252 ,X_CUMULATIVE_WORK_QTY => null
1253 ,X_BASE_PERCENT_COMPLETE => l_BASE_PERCENT_COMPLETE
1254 ,X_EFF_ROLLUP_PERCENT_COMP => null
1255 ,X_COMPLETED_PERCENTAGE => l_percent_complete
1256 ,X_ESTIMATED_START_DATE => null
1257 ,X_ESTIMATED_FINISH_DATE => null
1258 ,X_ACTUAL_START_DATE => null
1259 ,X_ACTUAL_FINISH_DATE => l_ACTUAL_FINISH_DATE
1260 ,X_EST_REMAINING_EFFORT => null
1261 ,X_RECORD_VERSION_NUMBER => l_rollup_rec_ver_number
1262 ,X_BASE_PERCENT_COMP_DERIV_CODE => null
1263 ,X_BASE_PROGRESS_STATUS_CODE => l_BASE_PROGRESS_STATUS_CODE
1264 ,X_EFF_ROLLUP_PROG_STAT_CODE => null
1265 ,X_PERCENT_COMPLETE_ID => l_percent_complete_id
1266 ,X_STRUCTURE_TYPE => p_structure_type
1267 --,X_PROJ_ELEMENT_ID => null --bug# 3799060 For deliverables proj_element_id should be populated. It is a must.
1268 ,X_PROJ_ELEMENT_ID => l_task_id
1269 ,X_STRUCTURE_VERSION_ID => null
1270 ,X_PPL_ACT_EFFORT_TO_DATE => null
1271 ,X_EQPMT_ACT_EFFORT_TO_DATE => null
1272 ,X_EQPMT_ETC_EFFORT => null
1273 ,X_OTH_ACT_COST_TO_DATE_TC => null
1274 ,X_OTH_ACT_COST_TO_DATE_FC => null
1275 ,X_OTH_ACT_COST_TO_DATE_PC => null
1276 ,X_OTH_ETC_COST_TC => null
1277 ,X_OTH_ETC_COST_FC => null
1278 ,X_OTH_ETC_COST_PC => null
1279 ,X_PPL_ACT_COST_TO_DATE_TC => null
1280 ,X_PPL_ACT_COST_TO_DATE_FC => null
1281 ,X_PPL_ACT_COST_TO_DATE_PC => null
1282 ,X_PPL_ETC_COST_TC => null
1283 ,X_PPL_ETC_COST_FC => null
1284 ,X_PPL_ETC_COST_PC => null
1285 ,X_EQPMT_ACT_COST_TO_DATE_TC => null
1286 ,X_EQPMT_ACT_COST_TO_DATE_FC => null
1287 ,X_EQPMT_ACT_COST_TO_DATE_PC => null
1288 ,X_EQPMT_ETC_COST_TC => null
1289 ,X_EQPMT_ETC_COST_FC => null
1290 ,X_EQPMT_ETC_COST_PC => null
1291 ,X_EARNED_VALUE => null
1292 ,X_TASK_WT_BASIS_CODE => null
1293 ,X_SUBPRJ_PPL_ACT_EFFORT => null
1294 ,X_SUBPRJ_EQPMT_ACT_EFFORT => null
1295 ,X_SUBPRJ_PPL_ETC_EFFORT => null
1296 ,X_SUBPRJ_EQPMT_ETC_EFFORT => null
1297 ,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
1298 ,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
1299 ,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
1300 ,X_SUBPRJ_PPL_ACT_COST_TC => null
1301 ,X_SUBPRJ_PPL_ACT_COST_FC => null
1302 ,X_SUBPRJ_PPL_ACT_COST_PC => null
1303 ,X_SUBPRJ_EQPMT_ACT_COST_TC => null
1304 ,X_SUBPRJ_EQPMT_ACT_COST_FC => null
1305 ,X_SUBPRJ_EQPMT_ACT_COST_PC => null
1306 ,X_SUBPRJ_OTH_ETC_COST_TC => null
1307 ,X_SUBPRJ_OTH_ETC_COST_FC => null
1308 ,X_SUBPRJ_OTH_ETC_COST_PC => null
1309 ,X_SUBPRJ_PPL_ETC_COST_TC => null
1310 ,X_SUBPRJ_PPL_ETC_COST_FC => null
1311 ,X_SUBPRJ_PPL_ETC_COST_PC => null
1312 ,X_SUBPRJ_EQPMT_ETC_COST_TC => null
1313 ,X_SUBPRJ_EQPMT_ETC_COST_FC => null
1314 ,X_SUBPRJ_EQPMT_ETC_COST_PC => null
1315 ,X_SUBPRJ_EARNED_VALUE => null
1316 ,X_CURRENT_FLAG => l_rollup_current_flag -- Bug 3879461 l_current_flag
1317 ,X_PROJFUNC_COST_RATE_TYPE => null
1318 ,X_PROJFUNC_COST_EXCHANGE_RATE => null
1319 ,X_PROJFUNC_COST_RATE_DATE => null
1320 ,X_PROJ_COST_RATE_TYPE => null
1321 ,X_PROJ_COST_EXCHANGE_RATE => null
1322 ,X_PROJ_COST_RATE_DATE => null
1323 ,X_TXN_CURRENCY_CODE => null
1324 ,X_PROG_PA_PERIOD_NAME => l_prog_pa_period_name
1325 ,X_PROG_GL_PERIOD_NAME => l_prog_gl_period_name
1326 --Added by rtarway BUG 3608801
1327 ,X_OTH_QUANTITY_TO_DATE => null
1328 ,X_OTH_ETC_QUANTITY => null
1329 --End Added by rtarway BUG 3608801
1330 ,X_OTH_ACT_RAWCOST_TO_DATE_TC => null
1331 ,X_OTH_ACT_RAWCOST_TO_DATE_FC => null
1332 ,X_OTH_ACT_RAWCOST_TO_DATE_PC => null
1333 ,X_OTH_ETC_RAWCOST_TC => null
1334 ,X_OTH_ETC_RAWCOST_FC => null
1335 ,X_OTH_ETC_RAWCOST_PC => null
1336 ,X_PPL_ACT_RAWCOST_TO_DATE_TC => null
1337 ,X_PPL_ACT_RAWCOST_TO_DATE_FC => null
1338 ,X_PPL_ACT_RAWCOST_TO_DATE_PC => null
1339 ,X_PPL_ETC_RAWCOST_TC => null
1340 ,X_PPL_ETC_RAWCOST_FC => null
1341 ,X_PPL_ETC_RAWCOST_PC => null
1342 ,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => null
1343 ,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => null
1344 ,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => null
1345 ,X_EQPMT_ETC_RAWCOST_TC => null
1346 ,X_EQPMT_ETC_RAWCOST_FC => null
1347 ,X_EQPMT_ETC_RAWCOST_PC => null
1348 ,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
1349 ,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
1350 ,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
1351 ,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
1352 ,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
1353 ,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
1354 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
1355 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
1356 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
1357 ,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
1358 ,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
1359 ,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
1360 ,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
1361 ,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
1362 ,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
1363 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
1364 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
1365 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
1366 );
1367
1368 IF Fnd_Msg_Pub.count_msg > 0 THEN
1369 RAISE FND_API.G_EXC_ERROR;
1370 END IF;
1371
1372 END IF;
1373
1374
1375 ELSIF l_db_action = 'UPDATE'
1376 THEN
1377
1378 IF g1_debug_mode = 'Y' THEN
1379 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'DB Action is Update', x_Log_Level=> 3);
1380 END IF;
1381
1382 IF l_working_aod IS NOT NULL
1383 THEN
1384 l_aod := l_working_aod;
1385 ELSE
1386 l_aod := p_as_of_date;
1387 END IF;
1388
1389 IF p_percent_complete_id IS NULL OR p_percent_complete_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR l_working_aod IS NULL -- Bug 10225425
1390 THEN
1391 l_percent_complete_id := PA_PROGRESS_UTILS.get_ppc_id(
1392 p_project_id => p_project_id
1393 ,p_object_id => p_object_id
1394 ,p_object_type => p_object_type
1395 ,p_object_version_id => p_object_version_id
1396 ,p_as_of_date => l_aod
1397 );
1398 -- FPM Dev CR 3 Getting Record Version Number too
1399 BEGIN
1400 SELECT record_version_number into l_record_version_number
1401 FROM pa_percent_completes
1402 where percent_complete_id = l_percent_complete_id;
1403 END;
1404 ELSE
1405 l_percent_complete_id := p_percent_complete_id;
1406 l_record_version_number := p_record_version_number;
1407 END IF;
1408
1409 IF g1_debug_mode = 'Y' THEN
1410 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Updating Percent Complete l_percent_complete_id='||l_percent_complete_id, x_Log_Level=> 3);
1411 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Updating Percent Completel_record_version_number='||l_record_version_number, x_Log_Level=> 3);
1412 END IF;
1413
1414 PA_PERCENT_COMPLETES_PKG.UPDATE_ROW(
1415 p_TASK_ID => l_task_id
1416 ,p_DATE_COMPUTED => p_as_of_date
1417 ,p_LAST_UPDATE_DATE => SYSDATE
1418 ,p_LAST_UPDATED_BY => l_user_id
1419 ,p_LAST_UPDATE_LOGIN => l_login_id
1420 ,p_COMPLETED_PERCENTAGE => l_percent_complete
1421 ,p_DESCRIPTION => l_brief_overview
1422 ,p_PROJECT_ID => p_project_id
1423 ,p_PM_PRODUCT_CODE => l_pm_product_code
1424 ,p_CURRENT_FLAG => l_current_flag
1425 ,p_OBJECT_TYPE => p_object_type
1426 ,p_OBJECT_ID => p_object_id
1427 ,p_OBJECT_VERSION_ID => p_object_version_id
1428 ,p_PROGRESS_STATUS_CODE => l_progress_status_code
1429 ,p_ACTUAL_START_DATE => null
1430 ,p_ACTUAL_FINISH_DATE => l_actual_finish_date
1431 ,p_ESTIMATED_START_DATE => null
1432 ,p_ESTIMATED_FINISH_DATE => null
1433 ,p_PUBLISHED_FLAG => l_published_flag
1434 ,p_PUBLISHED_BY_PARTY_ID => l_published_by_party_id
1435 ,p_PROGRESS_COMMENT => l_PROGRESS_COMMENT
1436 ,p_HISTORY_FLAG => 'N'
1437 ,p_status_code => l_del_status
1438 ,p_RECORD_VERSION_NUMBER => l_record_version_number
1439 ,p_PERCENT_COMPLETE_ID => l_percent_complete_id
1440 ,p_ATTRIBUTE_CATEGORY => null
1441 ,p_ATTRIBUTE1 => null
1442 ,p_ATTRIBUTE2 => null
1443 ,p_ATTRIBUTE3 => null
1444 ,p_ATTRIBUTE4 => null
1445 ,p_ATTRIBUTE5 => null
1446 ,p_ATTRIBUTE6 => null
1447 ,p_ATTRIBUTE7 => null
1448 ,p_ATTRIBUTE8 => null
1449 ,p_ATTRIBUTE9 => null
1450 ,p_ATTRIBUTE10 => null
1451 ,p_ATTRIBUTE11 => null
1452 ,p_ATTRIBUTE12 => null
1453 ,p_ATTRIBUTE13 => null
1454 ,p_ATTRIBUTE14 => null
1455 ,p_ATTRIBUTE15 => null
1456 ,p_structure_type => p_structure_type
1457
1458 );
1459
1460 IF Fnd_Msg_Pub.count_msg > 0 THEN
1461 RAISE FND_API.G_EXC_ERROR;
1462 END IF;
1463
1464 l_PROGRESS_ROLLUP_ID := PA_PROGRESS_UTILS.get_prog_rollup_id(
1465 p_project_id => p_project_id
1466 ,p_object_id => p_object_id
1467 ,p_object_type => p_object_type
1468 ,p_object_version_id => p_object_version_id
1469 ,p_as_of_date => l_aod -- FPM Dev CR 3 : Using l_aod instead of p_as_of_date
1470 ,p_action => p_action -- Bug 3879461
1471 ,x_record_version_number => l_rollup_rec_ver_number
1472 );
1473
1474
1475 IF g1_debug_mode = 'Y' THEN
1476 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'l_PROGRESS_ROLLUP_ID='||l_PROGRESS_ROLLUP_ID, x_Log_Level=> 3);
1477 END IF;
1478
1479 IF l_PROGRESS_ROLLUP_ID IS NOT NULL
1480 THEN
1481
1482 IF g1_debug_mode = 'Y' THEN
1483 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Updating progress rollup table', x_Log_Level=> 3);
1484 END IF;
1485
1486 PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
1487 X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
1488 ,X_PROJECT_ID => p_project_id
1489 ,X_OBJECT_ID => p_object_id
1490 ,X_OBJECT_TYPE => p_object_type
1491 ,X_AS_OF_DATE => p_as_of_date
1492 ,X_OBJECT_VERSION_ID => p_object_version_id
1493 ,X_LAST_UPDATE_DATE => SYSDATE
1494 ,X_LAST_UPDATED_BY => l_user_id
1495 ,X_PROGRESS_STATUS_CODE => l_progress_status_code
1496 ,X_LAST_UPDATE_LOGIN => l_login_id
1497 ,X_INCREMENTAL_WORK_QTY => null
1498 ,X_CUMULATIVE_WORK_QTY => null
1499 ,X_BASE_PERCENT_COMPLETE => l_BASE_PERCENT_COMPLETE
1500 ,X_EFF_ROLLUP_PERCENT_COMP => null
1501 ,X_COMPLETED_PERCENTAGE => l_percent_complete
1502 ,X_ESTIMATED_START_DATE => null
1503 ,X_ESTIMATED_FINISH_DATE => null
1504 ,X_ACTUAL_START_DATE => null
1505 ,X_ACTUAL_FINISH_DATE => l_ACTUAL_FINISH_DATE
1506 ,X_EST_REMAINING_EFFORT => null
1507 ,X_RECORD_VERSION_NUMBER => l_rollup_rec_ver_number
1508 ,X_BASE_PERCENT_COMP_DERIV_CODE => null
1509 ,X_BASE_PROGRESS_STATUS_CODE => l_BASE_PROGRESS_STATUS_CODE
1510 ,X_EFF_ROLLUP_PROG_STAT_CODE => null
1511 ,X_PERCENT_COMPLETE_ID => l_percent_complete_id
1512 ,X_STRUCTURE_TYPE => p_structure_type
1513 --,X_PROJ_ELEMENT_ID => null --bug# 3799060 For deliverables proj_element_id should be populated. It is a must.
1514 ,X_PROJ_ELEMENT_ID => l_task_id
1515 ,X_STRUCTURE_VERSION_ID => null
1516 ,X_PPL_ACT_EFFORT_TO_DATE => null
1517 ,X_EQPMT_ACT_EFFORT_TO_DATE => null
1518 ,X_EQPMT_ETC_EFFORT => null
1519 ,X_OTH_ACT_COST_TO_DATE_TC => null
1520 ,X_OTH_ACT_COST_TO_DATE_FC => null
1521 ,X_OTH_ACT_COST_TO_DATE_PC => null
1522 ,X_OTH_ETC_COST_TC => null
1523 ,X_OTH_ETC_COST_FC => null
1524 ,X_OTH_ETC_COST_PC => null
1525 ,X_PPL_ACT_COST_TO_DATE_TC => null
1526 ,X_PPL_ACT_COST_TO_DATE_FC => null
1527 ,X_PPL_ACT_COST_TO_DATE_PC => null
1528 ,X_PPL_ETC_COST_TC => null
1529 ,X_PPL_ETC_COST_FC => null
1530 ,X_PPL_ETC_COST_PC => null
1531 ,X_EQPMT_ACT_COST_TO_DATE_TC => null
1532 ,X_EQPMT_ACT_COST_TO_DATE_FC => null
1533 ,X_EQPMT_ACT_COST_TO_DATE_PC => null
1534 ,X_EQPMT_ETC_COST_TC => null
1535 ,X_EQPMT_ETC_COST_FC => null
1536 ,X_EQPMT_ETC_COST_PC => null
1537 ,X_EARNED_VALUE => null
1538 ,X_TASK_WT_BASIS_CODE => null
1539 ,X_SUBPRJ_PPL_ACT_EFFORT => null
1540 ,X_SUBPRJ_EQPMT_ACT_EFFORT => null
1541 ,X_SUBPRJ_PPL_ETC_EFFORT => null
1542 ,X_SUBPRJ_EQPMT_ETC_EFFORT => null
1543 ,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
1544 ,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
1545 ,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
1546 ,X_SUBPRJ_PPL_ACT_COST_TC => null
1547 ,X_SUBPRJ_PPL_ACT_COST_FC => null
1548 ,X_SUBPRJ_PPL_ACT_COST_PC => null
1549 ,X_SUBPRJ_EQPMT_ACT_COST_TC => null
1550 ,X_SUBPRJ_EQPMT_ACT_COST_FC => null
1551 ,X_SUBPRJ_EQPMT_ACT_COST_PC => null
1552 ,X_SUBPRJ_OTH_ETC_COST_TC => null
1553 ,X_SUBPRJ_OTH_ETC_COST_FC => null
1554 ,X_SUBPRJ_OTH_ETC_COST_PC => null
1555 ,X_SUBPRJ_PPL_ETC_COST_TC => null
1556 ,X_SUBPRJ_PPL_ETC_COST_FC => null
1557 ,X_SUBPRJ_PPL_ETC_COST_PC => null
1558 ,X_SUBPRJ_EQPMT_ETC_COST_TC => null
1559 ,X_SUBPRJ_EQPMT_ETC_COST_FC => null
1560 ,X_SUBPRJ_EQPMT_ETC_COST_PC => null
1561 ,X_SUBPRJ_EARNED_VALUE => null
1562 ,X_CURRENT_FLAG => l_rollup_current_flag -- Bug 3879461 l_current_flag
1563 ,X_PROJFUNC_COST_RATE_TYPE => null
1564 ,X_PROJFUNC_COST_EXCHANGE_RATE => null
1565 ,X_PROJFUNC_COST_RATE_DATE => null
1566 ,X_PROJ_COST_RATE_TYPE => null
1567 ,X_PROJ_COST_EXCHANGE_RATE => null
1568 ,X_PROJ_COST_RATE_DATE => null
1569 ,X_TXN_CURRENCY_CODE => null
1570 ,X_PROG_PA_PERIOD_NAME => l_prog_pa_period_name
1571 ,X_PROG_GL_PERIOD_NAME => l_prog_gl_period_name
1572 --Added by rtarway BUG 3608801
1573 ,X_OTH_QUANTITY_TO_DATE => null
1574 ,X_OTH_ETC_QUANTITY => null
1575 --End Added by rtarway BUG 3608801
1576 ,X_OTH_ACT_RAWCOST_TO_DATE_TC => null
1577 ,X_OTH_ACT_RAWCOST_TO_DATE_FC => null
1578 ,X_OTH_ACT_RAWCOST_TO_DATE_PC => null
1579 ,X_OTH_ETC_RAWCOST_TC => null
1580 ,X_OTH_ETC_RAWCOST_FC => null
1581 ,X_OTH_ETC_RAWCOST_PC => null
1582 ,X_PPL_ACT_RAWCOST_TO_DATE_TC => null
1583 ,X_PPL_ACT_RAWCOST_TO_DATE_FC => null
1584 ,X_PPL_ACT_RAWCOST_TO_DATE_PC => null
1585 ,X_PPL_ETC_RAWCOST_TC => null
1586 ,X_PPL_ETC_RAWCOST_FC => null
1587 ,X_PPL_ETC_RAWCOST_PC => null
1588 ,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => null
1589 ,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => null
1590 ,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => null
1591 ,X_EQPMT_ETC_RAWCOST_TC => null
1592 ,X_EQPMT_ETC_RAWCOST_FC => null
1593 ,X_EQPMT_ETC_RAWCOST_PC => null
1594 ,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
1595 ,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
1596 ,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
1597 ,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
1598 ,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
1599 ,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
1600 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
1601 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
1602 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
1603 ,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
1604 ,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
1605 ,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
1606 ,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
1607 ,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
1608 ,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
1609 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
1610 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
1611 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
1612 );
1613 IF Fnd_Msg_Pub.count_msg > 0 THEN
1614 RAISE FND_API.G_EXC_ERROR;
1615 END IF;
1616
1617 ELSE
1618
1619 l_EFF_ROLLUP_PERCENT_COMP := null;
1620 l_EFF_ROLLUP_PROG_STAT_CODE := null;
1621
1622 IF g1_debug_mode = 'Y' THEN
1623 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Inserting progress rollup table', x_Log_Level=> 3);
1624 END IF;
1625
1626 PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
1627 X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
1628 ,X_PROJECT_ID => p_project_id
1629 ,X_OBJECT_ID => p_object_id
1630 ,X_OBJECT_TYPE => p_object_type
1631 ,X_AS_OF_DATE => p_as_of_date
1632 ,X_OBJECT_VERSION_ID => p_object_version_id
1633 ,X_LAST_UPDATE_DATE => SYSDATE
1634 ,X_LAST_UPDATED_BY => l_user_id
1635 ,X_CREATION_DATE => SYSDATE
1636 ,X_CREATED_BY => l_user_id
1637 ,X_PROGRESS_STATUS_CODE => l_progress_status_code
1638 ,X_LAST_UPDATE_LOGIN => l_login_id
1639 ,X_INCREMENTAL_WORK_QTY => null
1640 ,X_CUMULATIVE_WORK_QTY => null
1641 ,X_BASE_PERCENT_COMPLETE => l_BASE_PERCENT_COMPLETE
1642 ,X_EFF_ROLLUP_PERCENT_COMP => null
1643 ,X_COMPLETED_PERCENTAGE => l_percent_complete
1644 ,X_ESTIMATED_START_DATE => null
1645 ,X_ESTIMATED_FINISH_DATE => null
1646 ,X_ACTUAL_START_DATE => null
1647 ,X_ACTUAL_FINISH_DATE => l_ACTUAL_FINISH_DATE
1648 ,X_EST_REMAINING_EFFORT => null
1649 ,X_BASE_PERCENT_COMP_DERIV_CODE => null
1650 ,X_BASE_PROGRESS_STATUS_CODE => l_BASE_PROGRESS_STATUS_CODE
1651 ,X_EFF_ROLLUP_PROG_STAT_CODE => null
1652 ,x_percent_complete_id => l_percent_complete_id ---bug.3927389
1653 ,X_STRUCTURE_TYPE => p_structure_type
1654 --,X_PROJ_ELEMENT_ID => null --bug# 3799060 For deliverables proj_element_id should be populated. It is a must.
1655 ,X_PROJ_ELEMENT_ID => l_task_id
1656 ,X_STRUCTURE_VERSION_ID => null
1657 ,X_PPL_ACT_EFFORT_TO_DATE => null
1658 ,X_EQPMT_ACT_EFFORT_TO_DATE => null
1659 ,X_EQPMT_ETC_EFFORT => null
1660 ,X_OTH_ACT_COST_TO_DATE_TC => null
1661 ,X_OTH_ACT_COST_TO_DATE_FC => null
1662 ,X_OTH_ACT_COST_TO_DATE_PC => null
1663 ,X_OTH_ETC_COST_TC => null
1664 ,X_OTH_ETC_COST_FC => null
1665 ,X_OTH_ETC_COST_PC => null
1666 ,X_PPL_ACT_COST_TO_DATE_TC => null
1667 ,X_PPL_ACT_COST_TO_DATE_FC => null
1668 ,X_PPL_ACT_COST_TO_DATE_PC => null
1669 ,X_PPL_ETC_COST_TC => null
1670 ,X_PPL_ETC_COST_FC => null
1671 ,X_PPL_ETC_COST_PC => null
1672 ,X_EQPMT_ACT_COST_TO_DATE_TC => null
1673 ,X_EQPMT_ACT_COST_TO_DATE_FC => null
1674 ,X_EQPMT_ACT_COST_TO_DATE_PC => null
1675 ,X_EQPMT_ETC_COST_TC => null
1676 ,X_EQPMT_ETC_COST_FC => null
1677 ,X_EQPMT_ETC_COST_PC => null
1678 ,X_EARNED_VALUE => null
1679 ,X_TASK_WT_BASIS_CODE => null
1680 ,X_SUBPRJ_PPL_ACT_EFFORT => null
1681 ,X_SUBPRJ_EQPMT_ACT_EFFORT => null
1682 ,X_SUBPRJ_PPL_ETC_EFFORT => null
1683 ,X_SUBPRJ_EQPMT_ETC_EFFORT => null
1684 ,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
1685 ,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
1686 ,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
1687 ,X_SUBPRJ_PPL_ACT_COST_TC => null
1688 ,X_SUBPRJ_PPL_ACT_COST_FC => null
1689 ,X_SUBPRJ_PPL_ACT_COST_PC => null
1690 ,X_SUBPRJ_EQPMT_ACT_COST_TC => null
1691 ,X_SUBPRJ_EQPMT_ACT_COST_FC => null
1692 ,X_SUBPRJ_EQPMT_ACT_COST_PC => null
1693 ,X_SUBPRJ_OTH_ETC_COST_TC => null
1694 ,X_SUBPRJ_OTH_ETC_COST_FC => null
1695 ,X_SUBPRJ_OTH_ETC_COST_PC => null
1696 ,X_SUBPRJ_PPL_ETC_COST_TC => null
1697 ,X_SUBPRJ_PPL_ETC_COST_FC => null
1698 ,X_SUBPRJ_PPL_ETC_COST_PC => null
1699 ,X_SUBPRJ_EQPMT_ETC_COST_TC => null
1700 ,X_SUBPRJ_EQPMT_ETC_COST_FC => null
1701 ,X_SUBPRJ_EQPMT_ETC_COST_PC => null
1702 ,X_SUBPRJ_EARNED_VALUE => null
1703 ,X_CURRENT_FLAG => l_rollup_current_flag -- Bug 3879461 l_current_flag
1704 ,X_PROJFUNC_COST_RATE_TYPE => null
1705 ,X_PROJFUNC_COST_EXCHANGE_RATE => null
1706 ,X_PROJFUNC_COST_RATE_DATE => null
1707 ,X_PROJ_COST_RATE_TYPE => null
1708 ,X_PROJ_COST_EXCHANGE_RATE => null
1709 ,X_PROJ_COST_RATE_DATE => null
1710 ,X_TXN_CURRENCY_CODE => null
1711 ,X_PROG_PA_PERIOD_NAME => l_prog_pa_period_name
1712 ,X_PROG_GL_PERIOD_NAME => l_prog_gl_period_name
1713 --Added by rtarway BUG 3608801
1714 ,X_OTH_QUANTITY_TO_DATE => null
1715 ,X_OTH_ETC_QUANTITY => null
1716 --End Added by rtarway BUG 3608801
1717 ,X_OTH_ACT_RAWCOST_TO_DATE_TC => null
1718 ,X_OTH_ACT_RAWCOST_TO_DATE_FC => null
1719 ,X_OTH_ACT_RAWCOST_TO_DATE_PC => null
1720 ,X_OTH_ETC_RAWCOST_TC => null
1721 ,X_OTH_ETC_RAWCOST_FC => null
1722 ,X_OTH_ETC_RAWCOST_PC => null
1723 ,X_PPL_ACT_RAWCOST_TO_DATE_TC => null
1724 ,X_PPL_ACT_RAWCOST_TO_DATE_FC => null
1725 ,X_PPL_ACT_RAWCOST_TO_DATE_PC => null
1726 ,X_PPL_ETC_RAWCOST_TC => null
1727 ,X_PPL_ETC_RAWCOST_FC => null
1728 ,X_PPL_ETC_RAWCOST_PC => null
1729 ,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => null
1730 ,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => null
1731 ,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => null
1732 ,X_EQPMT_ETC_RAWCOST_TC => null
1733 ,X_EQPMT_ETC_RAWCOST_FC => null
1734 ,X_EQPMT_ETC_RAWCOST_PC => null
1735 ,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
1736 ,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
1737 ,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
1738 ,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
1739 ,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
1740 ,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
1741 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
1742 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
1743 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
1744 ,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
1745 ,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
1746 ,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
1747 ,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
1748 ,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
1749 ,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
1750 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
1751 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
1752 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
1753 );
1754 IF Fnd_Msg_Pub.count_msg > 0 THEN
1755 RAISE FND_API.G_EXC_ERROR;
1756 END IF;
1757
1758 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Inserting progress rollup table', x_Log_Level=> 3);
1759 END IF;
1760 END IF; --<l_db_action>
1761
1762 IF p_action = 'PUBLISH'
1763 THEN
1764
1765 IF g1_debug_mode = 'Y' THEN
1766 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Updating Deliverable status', x_Log_Level=> 3);
1767 END IF;
1768
1769 --Update pa_proj_elements with the status
1770 UPDATE pa_proj_elements
1771 SET status_code = l_del_status
1772 WHERE proj_element_id = p_object_id
1773 AND project_id = p_project_id
1774 AND object_type = p_object_type;
1775
1776 -- FPM Dev CR 1 : Added the following update
1777 UPDATE pa_proj_elem_ver_schedule
1778 SET actual_finish_date = l_actual_finish_date
1779 WHERE project_id = p_project_id
1780 AND proj_element_id = p_object_id
1781 AND element_version_id = p_object_version_id ;
1782
1783
1784 /* -- FPM Dev CR 1 : Commented the code
1785 OPEN cur_sch_id( p_object_version_id );
1786 FETCH cur_sch_id INTO l_pev_schedule_id, l_sch_rec_ver_number, l_actual_start_date, l_estimated_start_date, l_estimated_finish_date ;
1787 CLOSE cur_sch_id;
1788
1789 IF g1_debug_mode = 'Y' THEN
1790 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'Updating Schedule version', x_Log_Level=> 3);
1791 END IF;
1792
1793 PA_TASK_PUB1.Update_Schedule_Version(
1794 p_pev_schedule_id => l_pev_schedule_id
1795 ,p_calling_module => p_calling_module
1796 ,p_actual_start_date => l_actual_start_date
1797 ,p_actual_finish_date => l_actual_finish_date
1798 ,p_estimate_start_date => l_estimated_start_date
1799 ,p_estimate_finish_date => l_estimated_finish_date
1800 ,p_record_version_number => l_sch_rec_ver_number
1801 ,x_return_status => l_return_status
1802 ,x_msg_count => l_msg_count
1803 ,x_msg_data => l_msg_data );
1804
1805 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1806 THEN
1807 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1808 p_msg_name => l_msg_data
1809 );
1810 x_msg_data := l_msg_data;
1811 x_return_status := FND_API.G_RET_STS_ERROR;
1812 RAISE FND_API.G_EXC_ERROR;
1813 END IF;
1814 */
1815
1816 END IF;
1817
1818 IF g1_debug_mode = 'Y' THEN
1819 pa_debug.write(x_Module=>'PA_DELIVERABLE_PROGRESS_PUB.UPDATE_DELIVERABLE_PROGRESS', x_Msg => 'End', x_Log_Level=> 3);
1820 END IF;
1821
1822
1823 EXCEPTION
1824 when FND_API.G_EXC_ERROR then
1825 x_return_status := FND_API.G_RET_STS_ERROR;
1826 l_msg_count := Fnd_Msg_Pub.count_msg;
1827
1828 if p_commit = FND_API.G_TRUE then
1829 rollback to UPDATE_DELIVERABLE_PROGRESS;
1830 end if;
1831
1832 IF l_msg_count = 1 AND x_msg_data IS NULL
1833 THEN
1834 Pa_Interface_Utils_Pub.get_messages
1835 ( p_encoded => Fnd_Api.G_TRUE
1836 , p_msg_index => 1
1837 , p_msg_count => l_msg_count
1838 , p_msg_data => l_msg_data
1839 , p_data => l_data
1840 , p_msg_index_out => l_msg_index_out);
1841 x_msg_data := l_data;
1842 x_msg_count := l_msg_count;
1843 ELSE
1844 x_msg_count := l_msg_count;
1845 END IF;
1846
1847
1848 when FND_API.G_EXC_UNEXPECTED_ERROR then
1849 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1850 x_msg_count := 1;
1851 x_msg_data := SQLERRM;
1852
1853 if p_commit = FND_API.G_TRUE then
1854 rollback to UPDATE_DELIVERABLE_PROGRESS;
1855 end if;
1856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1857 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_DELIVERABLE_PROGRESS_PUB',
1858 p_procedure_name => 'UPDATE_DELIVERABLE_PROGRESS',
1859 p_error_text => SUBSTRB(SQLERRM,1,240));
1860 when OTHERS then
1861
1862 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1863 x_msg_count := 1;
1864 x_msg_data := SUBSTRB(SQLERRM,1,240);
1865 if p_commit = FND_API.G_TRUE then
1866 rollback to UPDATE_DELIVERABLE_PROGRESS;
1867 end if;
1868
1869 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_DELIVERABLE_PROGRESS_PUB',
1870 p_procedure_name => 'UPDATE_DELIVERABLE_PROGRESS',
1871 p_error_text => SUBSTRB(SQLERRM,1,240));
1872 raise;
1873
1874
1875 END UPDATE_DELIVERABLE_PROGRESS;
1876
1877
1878 end PA_DELIVERABLE_PROGRESS_PUB;