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