[Home] [Help]
PACKAGE BODY: APPS.PA_STATUS_PUB
Source
1 PACKAGE BODY pa_status_pub AS
2 /* $Header: PAPMSTPB.pls 120.11.12010000.2 2008/09/18 04:58:34 paljain ship $*/
3 -- =========================================================================
4 --
5 -- Name: Update_Progress
6 -- Type: PL/SQL Procedure
7 -- Decscription: This procedure updates the PA_CUR_WBS_PERCENT_COMPLETE table.
8 --
9 -- Called Subprograms: Convert_Pm_Projref_To_Id
10 -- , Convert_Pm_Taskref_To_Id
11 -- History: 08-AUG-96 Created jwhite
12 -- 29-AUG-96 Update jwhite Applied latest messaging standards.
13 -- 27-SEP-96 Update jwhite As per Ashwani's direction, if Update_Progress
14 -- can't find a row to update, it inserts it.
15 -- 01-MAY-97 Updated jwhite Gutted/Rewrote api as per jlowel's
16 -- specification changes.
17 --
18
19 g_task_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(); -- Bug 4218507
20
21 PROCEDURE UPDATE_PROGRESS
22 ( p_api_version_number IN NUMBER
23 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
24 , p_commit IN VARCHAR2 := FND_API.G_FALSE
25 , p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
26 , p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
27 , p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
28 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
29 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
30 , p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
31 , p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
32 , p_as_of_date IN DATE
33 , p_percent_complete IN NUMBER
34 , p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
35 , p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
36 , p_object_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
37 , p_object_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
38 , p_object_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
39 , p_progress_status_code IN VARCHAR2 := 'PROGRESS_STAT_ON_TRACK'
40 , p_progress_comment IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
41 , p_actual_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
42 , p_actual_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
43 , p_estimated_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
44 , p_estimated_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
45 , p_scheduled_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
46 , p_scheduled_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
47 , p_task_status IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
48 , p_structure_type IN VARCHAR2 := 'FINANCIAL'
49 , p_est_remaining_effort IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
50 , p_actual_work_quantity IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
51 , p_etc_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM /* FP M Task Progress 3420093*/
52 , p_pm_deliverable_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR -- Bug 3606627
53 , p_pm_task_assgn_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR -- Bug 3606627
54 , p_actual_cost_to_date IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM -- Bug 3606627
55 , p_actual_effort_to_date IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM -- Bug 3606627
56 , p_populate_pji_tables IN VARCHAR2 := 'Y' -- Bug 3606627
57 , p_rollup_entire_wbs IN VARCHAR2 := 'N' -- Bug 3606627
58 , p_txn_currency_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
59 -- Fix for Bug # 3988457.
60 )
61 IS
62
63 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
64 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PROGRESS';
65 l_value_conversion_error BOOLEAN := FALSE;
66 l_return_status VARCHAR2(1);
67 l_msg_count INTEGER;
68
69 l_err_code NUMBER := -1;
70 l_err_stage VARCHAR2(2000) := NULL;
71 l_err_stack VARCHAR2(2000) := NULL;
72
73 l_project_id_out NUMBER := 0;
74 l_task_id_out NUMBER := 0;
75 l_msg_data VARCHAR2(2000);
76 l_function_allowed VARCHAR2(1);
77 l_resp_id NUMBER := 0;
78 l_pm_product_code pa_percent_completes.pm_product_code%TYPE := NULL;
79 l_description pa_percent_completes.description%TYPE := NULL;
80 l_as_of_date DATE;
81 l_current_flag VARCHAR2(1);
82 l_dummy VARCHAR2(1);
83 l_module_name VARCHAR2(80);
84 l_user_id NUMBER := 0;
85 l_date_computed DATE;
86
87 l_object_type VARCHAR2(30);
88 l_object_id NUMBER;
89 l_object_version_id NUMBER;
90 l_progress_status_code pa_percent_completes.progress_status_code%TYPE;
91 l_progress_comment pa_percent_completes.progress_comment%TYPE;
92 l_actual_start_date pa_percent_completes.actual_start_date%TYPE;
93 l_actual_finish_date pa_percent_completes.actual_finish_date%TYPE;
94 l_estimated_start_date pa_percent_completes.estimated_start_date%TYPE;
95 l_estimated_finish_date pa_percent_completes.estimated_finish_date%TYPE;
96 l_scheduled_start_date pa_tasks.scheduled_start_date%TYPE;
97 l_scheduled_finish_date pa_tasks.scheduled_finish_date%TYPE;
98 l_est_remaining_effort NUMBER;
99 l_ETC_cost NUMBER;
100 l_actual_work_quantity NUMBER;
101
102
103 -- ROW LOCKING
104 --Bug 3606627 : Unnecessary Cursor : Commenting
105 /*CURSOR l_percent_complete_csr (l_task_id_out NUMBER, l_as_of_date DATE)
106 IS
107 SELECT trunc(date_computed)
108 FROM pa_percent_completes pc
109 WHERE pc.project_id = l_project_id_out
110 AND pc.task_id = l_task_id_out
111 AND pc.current_flag = 'Y';*/
112
113
114
115 l_progress_mode VARCHAR2(10); --Bug 2736387
116 l_latest_as_of_date DATE; -- Bug 2758319
117 l_task_status VARCHAR2(150); --Bug 2751159
118
119 CURSOR c_get_structure_information(c_project_id NUMBER, c_structure_type VARCHAR2) IS
120 select ppevs.proj_element_id, ppevs.element_version_id
121 from pa_proj_structure_types ppst,
122 pa_structure_types pst,
123 pa_proj_elem_ver_structure ppevs
124 where ppevs.project_id = c_project_id
125 and ppevs.proj_element_id = ppst.proj_element_id
126 and ppevs.status_code = 'STRUCTURE_PUBLISHED'
127 and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'
128 and ppst.structure_type_id = pst.structure_type_id
129 and pst.structure_type_class_code = c_structure_type;
130
131 CURSOR c_get_element_information(c_project_id NUMBER, c_proj_element_id NUMBER, c_object_type VARCHAR2, c_structure_type VARCHAR2) IS
132 select elev.element_version_id, elev.parent_structure_version_id
133 from pa_proj_element_versions elev,
134 pa_proj_structure_types ppst,
135 pa_structure_types pst,
136 pa_proj_elem_ver_structure ppevs
137 where elev.project_id = c_project_id
138 and elev.object_type = c_object_type
139 and elev.proj_element_id = c_proj_element_id
140 and elev.parent_structure_version_id = ppevs.element_version_id
141 and ppevs.project_id = c_project_id
142 and ppevs.proj_element_id = ppst.proj_element_id
143 and ppevs.status_code = 'STRUCTURE_PUBLISHED'
144 and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'
145 and ppst.structure_type_id = pst.structure_type_id
146 and pst.structure_type_class_code = c_structure_type;
147
148 CURSOR c_get_del_associated_task(c_project_id NUMBER, c_del_proj_element_id NUMBER, c_object_type VARCHAR2) IS
149 SELECT ppe.proj_element_id --, por.object_id_to1 (commented by rtarway for BUG 3746647)
150 FROM pa_proj_elements ppe,
151 pa_object_relationships por
152 WHERE
153 ppe.object_type = 'PA_TASKS'
154 and ppe.proj_element_id = por.object_id_from2
155 and por.object_id_to2 = c_del_proj_element_id
156 and por.object_type_to = c_object_type
157 and por.relationship_type = 'A'
158 and por.relationship_subtype = 'TASK_TO_DELIVERABLE'
159 and ppe.base_percent_comp_deriv_code='DELIVERABLE';
160
161 --Begin Add by rtarway for BUG 3746647
162 CURSOR c_get_del_ver_id(c_project_id NUMBER, c_del_proj_element_id NUMBER) IS
163 SELECT ppev.element_version_id
164 FROM pa_proj_element_versions ppev
165 WHERE ppev.proj_element_id = c_del_proj_element_id
166 AND ppev.project_id = c_project_id
167 AND ppev.object_type = 'PA_DELIVERABLES' ;
168 --End Add by rtarway for BUG 3746647
169
170 -- Bug 3799841 Begin
171 CURSOR c_get_rlm_id(c_project_id NUMBER, c_object_id NUMBER, c_structure_version_id NUMBER, c_task_version_id NUMBER) IS
172 SELECT resource_list_member_id
173 FROM pa_task_assignments_v
174 WHERE project_id = c_project_id
175 AND resource_assignment_id = c_object_id
176 AND structure_version_id = c_structure_version_id
177 AND task_version_id = c_task_version_id;
178
179 CURSOR c_verify_rlm_id(c_project_id NUMBER, c_object_id NUMBER, c_structure_version_id NUMBER, c_task_version_id NUMBER) IS
180 SELECT 'Y', ta_display_flag
181 FROM pa_task_assignments_v
182 WHERE project_id = c_project_id
183 AND resource_list_member_id = c_object_id
184 AND structure_version_id = c_structure_version_id
185 AND task_version_id = c_task_version_id;
186
187 l_rlm_id NUMBER;
188 l_valid_rlm_id VARCHAR2(1):= 'N';
189 l_ta_display_flag VARCHAR2(1):= 'X';
190 -- Bug 3799841 End
191
192 l_task_id NUMBER;
193 l_structure_version_id NUMBER;
194 l_task_version_id NUMBER;
195 l_actual_cost_to_date NUMBER;
196 l_actual_effort_to_date NUMBER;
197 l_txn_currency_code VARCHAR2(15);
198 l_system_task_status pa_project_statuses.project_system_status_code%TYPE;
199 g1_debug_mode VARCHAR2(1);
200
201 l_populate_pji_tables VARCHAR2(1);
202 l_rollup_entire_wbs VARCHAR2(1);
203 l_str_id NUMBER;
204 l_str_version_id NUMBER;
205
206 l_baselined_str_ver_id NUMBER; --maansari6/28 bug 3673618
207 --Added by rtarway for BUG 3872176
208 l_published_version_flag VARCHAR2(1);
209 l_version_enabled VARCHAR2(1);
210 l_unique_record VARCHAR2(1);
211
212 BEGIN
213
214 g1_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
215
216 IF g1_debug_mode = 'Y' THEN
217 pa_debug.init_err_stack ('PA_PROGRESS_PUB.UPDATE_PROGRESS');
218 END IF;
219
220 SAVEPOINT Update_Progress_Pub;
221
222 IF g1_debug_mode = 'Y' THEN
223 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'PA_STATUS_PUB.UPDATE_PROGRESS Start : Passed Parameters :', x_Log_Level=> 3);
224 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_api_version_number='||p_api_version_number, x_Log_Level=> 3);
225 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_init_msg_list='||p_init_msg_list, x_Log_Level=> 3);
226 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_commit='||p_commit, x_Log_Level=> 3);
227 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_project_id='||p_project_id, x_Log_Level=> 3);
228 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_project_reference='||p_pm_project_reference, x_Log_Level=> 3);
229 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_task_id='||p_task_id, x_Log_Level=> 3);
230 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_task_reference='||p_pm_task_reference, x_Log_Level=> 3);
231 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_as_of_date='||p_as_of_date, x_Log_Level=> 3);
232 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_percent_complete='||p_percent_complete, x_Log_Level=> 3);
233 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_product_code='||p_pm_product_code, x_Log_Level=> 3);
234 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_description='||p_description, x_Log_Level=> 3);
235 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_object_id='||p_object_id, x_Log_Level=> 3);
236 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_object_version_id='||p_object_version_id, x_Log_Level=> 3);
237 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_object_type='||p_object_type, x_Log_Level=> 3);
238 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_progress_status_code='||p_progress_status_code, x_Log_Level=> 3);
239 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_progress_comment='||p_progress_comment, x_Log_Level=> 3);
240 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_start_date='||p_actual_start_date, x_Log_Level=> 3);
241 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_finish_date='||p_actual_finish_date, x_Log_Level=> 3);
242 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_estimated_start_date='||p_estimated_start_date, x_Log_Level=> 3);
243 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_estimated_finish_date='||p_estimated_finish_date, x_Log_Level=> 3);
244 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_scheduled_start_date='||p_scheduled_start_date, x_Log_Level=> 3);
245 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_scheduled_finish_date='||p_scheduled_finish_date, x_Log_Level=> 3);
246 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_task_status='||p_task_status, x_Log_Level=> 3);
247 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_structure_type='||p_structure_type, x_Log_Level=> 3);
248 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_est_remaining_effort='||p_est_remaining_effort, x_Log_Level=> 3);
249 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_work_quantity='||p_actual_work_quantity, x_Log_Level=> 3);
250 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_etc_cost='||p_etc_cost, x_Log_Level=> 3);
251 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_deliverable_reference='||p_pm_deliverable_reference, x_Log_Level=> 3);
252 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_pm_task_assgn_reference='||p_pm_task_assgn_reference, x_Log_Level=> 3);
253 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_cost_to_date='||p_actual_cost_to_date, x_Log_Level=> 3);
254 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'p_actual_effort_to_date='||p_actual_effort_to_date, x_Log_Level=> 3);
255 END IF;
256
257
258 p_return_status := FND_API.G_RET_STS_SUCCESS;
259 -- We have p_populate_pji_tables as paramter because we can not populate PJI tables in Execute update_task_progress
260 -- as we do not have structure version id there.
261 -- If user passes intentionally as N and calls singule update_progress then we will set it as Y
262 IF(G_bulk_load_flag = 'Y') THEN
263 l_project_id_out := p_project_id;
264 IF FND_API.to_boolean(p_init_msg_list) THEN
265 FND_MSG_PUB.initialize;
266 END IF;
267 l_populate_pji_tables := p_populate_pji_tables;
268 l_rollup_entire_wbs := p_rollup_entire_wbs;
269 ELSE
270 l_populate_pji_tables := 'Y';
271 l_rollup_entire_wbs := 'N';
272
273 IF g1_debug_mode = 'Y' THEN
274 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'G_bulk_load_flag is N, Calling Project_Level_Validations', x_Log_Level=> 3);
275 END IF;
276
277 PA_STATUS_PUB.Project_Level_Validations
278 ( p_api_version_number => p_api_version_number
279 , p_commit => FND_API.G_FALSE --Bug 3754134
280 , p_init_msg_list => p_init_msg_list
281 , p_msg_count => p_msg_count
282 , p_msg_data => p_msg_data
283 , p_return_status => l_return_status
284 , p_project_id => p_project_id
285 , p_pm_project_reference => p_pm_project_reference
286 , p_project_id_out => l_project_id_out
287 );
288 IF g1_debug_mode = 'Y' THEN
289 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'After Project_Level_Validations l_return_status='||l_return_status, x_Log_Level=> 3);
290 END IF;
291
292 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
293 RAISE FND_API.G_EXC_ERROR;
294 END IF;
295 -- Bug 3627315 : Added code to validate date against the project structure
296 -- it won't be validated for each object
297 OPEN c_get_structure_information(l_project_id_out, p_structure_type);
298 FETCH c_get_structure_information INTO l_str_id, l_str_version_id;
299 CLOSE c_get_structure_information;
300
301 /*IF PA_PROGRESS_UTILS.CHECK_VALID_AS_OF_DATE(TRUNC(p_as_of_date), l_project_id_out, l_str_id, 'PA_STRUCTURES', l_str_id ) = 'N'
302 THEN
303 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
304 ,p_msg_name => 'PA_TP_INV_AOD2');
305 p_return_status := FND_API.G_RET_STS_ERROR;
306 RAISE FND_API.G_EXC_ERROR;
307 END IF;*/
308 END IF;
309
310 -- User can pass task_id or task reference
311 -- User can pass object_type and object_id or object_reference
312 -- User can pass task _id as 0 and object_type and object_id as miss char
313
314 IF (p_task_id = 0 AND p_object_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND p_object_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
315 IF g1_debug_mode = 'Y' THEN
316 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'Case 1 : task_id is passed as 0 and object_type and object_id is not passed', x_Log_Level=> 3);
317 END IF;
318 -- Must be Project-Level Progress Data
319 l_task_id_out := 0;
320 l_object_type := 'PA_STRUCTURES';
321 OPEN c_get_structure_information(l_project_id_out, p_structure_type);
322 FETCH c_get_structure_information INTO l_object_id, l_object_version_id;
323 CLOSE c_get_structure_information;
324 l_task_id := null;
325 l_structure_version_id := l_object_version_id;
326 ELSIF (((p_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_task_id <> 0) OR (p_pm_task_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND p_pm_task_reference IS NOT NULL))
327 AND p_object_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND p_object_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
328
329 IF g1_debug_mode = 'Y' THEN
330 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'Case 2 : task_id is passed or task_ref is passed and object_type and object_id is not passed', x_Log_Level=> 3);
331 END IF;
332
333 PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
334 ( p_pa_project_id => l_project_id_out
335 , p_structure_type => p_structure_type
336 , p_pa_task_id => p_task_id
337 , p_pm_task_reference => p_pm_task_reference
338 , p_out_task_id => l_task_id_out
339 , p_return_status => l_return_status
340 );
341 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346 l_object_type := 'PA_TASKS';
347 l_object_id := l_task_id_out;
348 OPEN c_get_element_information(p_project_id, l_object_id, l_object_type, p_structure_type);
349 FETCH c_get_element_information INTO l_object_version_id, l_structure_version_id;
350 CLOSE c_get_element_information;
351 l_task_id := l_task_id_out;
352 ELSIF (p_object_type <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND p_object_type IS NOT NULL) THEN
353 IF g1_debug_mode = 'Y' THEN
354 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'Case 3 : object_type is passed', x_Log_Level=> 3);
355 END IF;
356
357 IF p_object_type <> 'PA_ASSIGNMENTS' AND p_object_type <> 'PA_DELIVERABLES' AND p_object_type <> 'PA_TASKS' AND p_object_type <> 'PA_STRUCTURES' THEN
358 FND_MESSAGE.SET_NAME('PA','PA_PROG_WRONG_OBJ_TYPE');
359 FND_MSG_PUB.add;
360 p_return_status := FND_API.G_RET_STS_ERROR;
361 RAISE FND_API.G_EXC_ERROR;
362 ELSE
363 l_object_type := p_object_type;
364 END IF;
365
366 IF ((l_object_type = 'PA_ASSIGNMENTS' OR l_object_type = 'PA_DELIVERABLES') AND p_structure_type = 'FINANCIAL') THEN
367 FND_MESSAGE.SET_NAME('PA','PA_PROG_ASGN_DEL_NOT_ALLOW_STR');
368 FND_MSG_PUB.add;
369 p_return_status := FND_API.G_RET_STS_ERROR;
370 RAISE FND_API.G_EXC_ERROR;
371 END IF;
372
373 IF p_object_type = 'PA_TASKS' THEN
374 IF ((p_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_task_id IS NOT NULL)
375 OR (p_pm_task_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND p_pm_task_reference IS NOT NULL)) THEN
376
377 PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
378 ( p_pa_project_id => l_project_id_out
379 , p_structure_type => p_structure_type
380 , p_pa_task_id => p_task_id
381 , p_pm_task_reference => p_pm_task_reference
382 , p_out_task_id => l_task_id_out
383 , p_return_status => l_return_status
384 );
385 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
387 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
388 RAISE FND_API.G_EXC_ERROR;
389 END IF;
390 END IF;
391 IF ((p_object_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_object_id IS NOT NULL)
392 OR (p_pm_task_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND p_pm_task_reference IS NOT NULL)) THEN
393
394 PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
395 ( p_pa_project_id => l_project_id_out
396 , p_structure_type => p_structure_type
397 , p_pa_task_id => p_object_id
398 , p_pm_task_reference => p_pm_task_reference
399 , p_out_task_id => l_task_id_out
400 , p_return_status => l_return_status
401 );
402 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
404 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
405 RAISE FND_API.G_EXC_ERROR;
406 END IF;
407 END IF;
408
409 l_object_type := p_object_type;
410 l_object_id := l_task_id_out;
411 l_task_id := l_task_id_out;
412 OPEN c_get_element_information(p_project_id, l_object_id, l_object_type, p_structure_type);
413 FETCH c_get_element_information INTO l_object_version_id, l_structure_version_id;
414 CLOSE c_get_element_information;
415 ELSIF p_object_type = 'PA_DELIVERABLES' THEN
416 PA_DELIVERABLE_UTILS.Convert_pm_dlvrref_to_id
417 (p_deliverable_reference => p_PM_DELIVERABLE_REFERENCE
418 ,p_deliverable_id => p_object_id
419 ,p_project_id => l_project_id_out
420 ,p_out_deliverable_id => l_object_id
421 ,p_return_status => l_return_status);
422 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
425 RAISE FND_API.G_EXC_ERROR;
426 END IF;
427 --Begin add for BUG 3746647, rtarway
428 OPEN c_get_del_ver_id(p_project_id, l_object_id);
429 FETCH c_get_del_ver_id into l_object_version_id;
430 CLOSE c_get_del_ver_id;
431 --End Add for BUG 3746647, rtarway
432 l_object_type := p_object_type;
433 OPEN c_get_del_associated_task(p_project_id, l_object_id, l_object_type);
434 --BUG 3746647, rtarway, get only task id.
435 --FETCH c_get_del_associated_task INTO l_task_id, l_object_version_id;
436 FETCH c_get_del_associated_task INTO l_task_id;
437 CLOSE c_get_del_associated_task;
438 -- 20 May : Structure version_id shd also be got from here
439 IF l_task_id is not NULL THEN
440 OPEN c_get_element_information(p_project_id, l_task_id, 'PA_TASKS', 'WORKPLAN');
441 FETCH c_get_element_information INTO l_task_version_id, l_structure_version_id;
442 CLOSE c_get_element_information;
443 END IF;
444 ELSIF p_object_type = 'PA_ASSIGNMENTS' THEN
445
446 --Added by rtarway for BUG 3872176
447 IF ((p_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_task_id IS NOT NULL)
448 OR (p_pm_task_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND p_pm_task_reference IS NOT NULL)) THEN
449
450 PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
451 ( p_pa_project_id => l_project_id_out
452 , p_structure_type => p_structure_type
453 , p_pa_task_id => p_task_id
454 , p_pm_task_reference => p_pm_task_reference
455 , p_out_task_id => l_task_id_out
456 , p_return_status => l_return_status
457 );
458 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
460 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
461 RAISE FND_API.G_EXC_ERROR;
462 END IF;
463 END IF;
464 --end Added by rtarway for BUG 3872176
465
466 OPEN c_get_element_information(p_project_id, l_task_id_out, 'PA_TASKS', p_structure_type);
467 FETCH c_get_element_information INTO l_task_version_id, l_structure_version_id;
468 CLOSE c_get_element_information;
469
470 -- Bug 3799841, If Object_id(RLM ID is passed, then no need to call Convert_PM_TARef_To_ID
471 -- Addionally when Convert_PM_TARef_To_ID return resource_assignment_id, we need to convert this
472 -- to RLM id.
473
474 -- Added by rtarway for BUG 3872176
475 --1. Check if project is version enabled.
476 l_version_enabled := PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED( p_project_id );
477 if (l_version_enabled = 'N')
478 then
479 l_published_version_flag := 'N';
480 else
481 -- the derived structure version id will always be from published structure version
482 -- please see cursor c_get_element_information
483 l_published_version_flag := 'Y';
484 end if;
485 --end add by rtarway BUG 3872176
486
487
488 IF p_object_id IS NOT NULL AND p_object_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
489 l_valid_rlm_id := 'N';
490 l_ta_display_flag := 'X';
491 OPEN c_verify_rlm_id(l_project_id_out, p_object_id, l_structure_version_id, l_task_version_id);
492 FETCH c_verify_rlm_id INTO l_valid_rlm_id, l_ta_display_flag;
493 CLOSE c_verify_rlm_id;
494
495 IF l_valid_rlm_id = 'N' THEN
496 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
497 p_msg_name => 'PA_TP_INV_ASSGN_AMG',
498 P_TOKEN1 => 'OBJECT_ID',
499 P_VALUE1 => p_object_id);
500 p_return_status := FND_API.G_RET_STS_ERROR;
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503 -- Here if hidden assignment, then we are just returning
504 -- We could have even thrown an erroe message of Invalid Assignment
505 -- But we are not doing this right now as MSP is sending all assignments
506 -- including hidden ones
507 IF l_ta_display_flag = 'N' THEN
508 return;
509 END IF;
510 l_object_id := p_object_id;
511 ELSE
512 PA_TASK_ASSIGNMENTS_PUB.Convert_PM_TARef_To_ID
513 ( p_pm_product_code => p_pm_product_code
514 ,p_pa_project_id => l_project_id_out
515 ,p_pa_structure_version_id => l_structure_version_id
516 --,p_pa_task_id => p_task_id
517 --BUG 3872176, rtarway
518 ,p_pa_task_id => l_task_id_out
519 ,p_pa_task_elem_ver_id => l_task_version_id
520 ,p_pa_task_assignment_id => p_object_id
521 ,p_pm_task_asgmt_reference => p_PM_TASK_ASSGN_REFERENCE
522 ,x_pa_task_assignment_id => l_object_id
523 --Added by rtarway for BUG 3872176
524 ,p_published_version_flag => l_published_version_flag
525 ,x_return_status => l_return_status );
526 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
528 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
529 RAISE FND_API.G_EXC_ERROR;
530 END IF;
531 -- Bug 3799841 Returned l_object_id would be resource_assignment_id, we nned to change this to RLM id
532 OPEN c_get_rlm_id(l_project_id_out, l_object_id, l_structure_version_id, l_task_version_id);
533 FETCH c_get_rlm_id INTO l_rlm_id;
534 CLOSE c_get_rlm_id;
535 l_object_id := l_rlm_id;
536 END IF;
537 l_object_type := p_object_type;
538 l_task_id := l_task_id_out; --rtarway, BUG 3872716
539 l_object_version_id := l_task_version_id;
540 ELSIF p_object_type = 'PA_STRUCTURES' THEN
541 l_object_type := 'PA_STRUCTURES';
542 OPEN c_get_structure_information(l_project_id_out, p_structure_type);
543 FETCH c_get_structure_information INTO l_object_id, l_object_version_id;
544 CLOSE c_get_structure_information;
545 l_task_id := null;
546 l_structure_version_id := l_object_version_id;
547 END IF; -- p_object_type = 'PA_TASKS'
548 END IF; --(p_task_id = 0 AND p_object_type = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND p_object_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
549
550 IF g1_debug_mode = 'Y' THEN
551 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_object_type='||l_object_type, x_Log_Level=> 3);
552 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_object_id='||l_object_id, x_Log_Level=> 3);
553 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_object_version_id='||l_object_version_id, x_Log_Level=> 3);
554 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_structure_version_id='||l_structure_version_id, x_Log_Level=> 3);
555 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'l_task_id='||l_task_id, x_Log_Level=> 3);
556 END IF;
557
558 IF (l_object_type is null or l_object_id is null
559 --BUG3632883
560 --or ( l_object_version_id is null AND l_object_type <> 'PA_DELIVERABLES')--BUG 3746647
561 or ( l_object_version_id is null)
562 or ( l_structure_version_id is null AND l_object_type <> 'PA_DELIVERABLES' )
563 ) THEN
564 FND_MESSAGE.SET_NAME('PA','PA_TP_INV_PROJ_STRUC_INFO');
565 FND_MSG_PUB.add;
566 p_return_status := FND_API.G_RET_STS_ERROR;
567 RAISE FND_API.G_EXC_ERROR;
568 END IF;
569 -- Bug 4218507 Added g_task_version_id_tbl
570 IF l_object_type = 'PA_DELIVERABLES' AND l_task_version_id IS NOT NULL THEN
571 l_unique_record := 'Y';
572 FOR i in 1..g_task_version_id_tbl.count LOOP
573 IF g_task_version_id_tbl(i) = l_task_version_id THEN
574 l_unique_record := 'N';
575 exit;
576 END IF;
577 END LOOP;
578 IF l_unique_record = 'Y' THEN
579 g_task_version_id_tbl.extend(1);
580 g_task_version_id_tbl(g_task_version_id_tbl.count):= l_task_version_id;
581 END IF;
582 ELSIF l_object_version_id IS NOT NULL THEN
583 l_unique_record := 'Y';
584 FOR i in 1..g_task_version_id_tbl.count LOOP
585 IF g_task_version_id_tbl(i) = l_object_version_id THEN
586 l_unique_record := 'N';
587 exit;
588 END IF;
589 END LOOP;
590 IF l_unique_record = 'Y' THEN
591 g_task_version_id_tbl.extend(1);
592 g_task_version_id_tbl(g_task_version_id_tbl.count):= l_object_version_id;
593 END IF;
594 END IF;
595
596 IF l_populate_pji_tables = 'Y' AND l_structure_version_id IS NOT NULL THEN
597 l_baselined_str_ver_id := PA_PROJECT_STRUCTURE_UTILS.Get_Baseline_Struct_Ver(l_project_id_out); --maansari6/28 bug 3673618
598 --maansari7/6 bug 3742356
599 if l_baselined_str_ver_id = -1
600 then
601 l_baselined_str_ver_id := l_structure_version_id;
602 end if;
603
604 PA_PROGRESS_PUB.populate_pji_tab_for_plan(
605 p_api_version => p_api_version_number
606 ,p_init_msg_list => p_init_msg_list
607 ,p_commit => FND_API.G_FALSE --Bug 3754134
608 ,p_calling_module => 'AMG'
609 ,p_project_id => l_project_id_out
610 ,p_structure_version_id => l_structure_version_id
611 ,p_baselined_str_ver_id => l_baselined_str_ver_id --maansari6/28 bug 3673618
612 ,x_return_status => l_return_status
613 ,x_msg_count => l_msg_count
614 ,x_msg_data => l_msg_data
615 );
616
617 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
618 RAISE FND_API.G_EXC_ERROR;
619 END IF;
620 END IF;
621
622 IF (p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ) THEN
623 l_pm_product_code := NULL;
624 ELSE
625 l_pm_product_code := p_pm_product_code;
626 END IF;
627
628 IF (p_description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ) THEN
629 l_description := NULL;
630 ELSE
631 l_description := p_description;
632 END IF;
633
634 l_as_of_date := TRUNC(p_as_of_date);
635
636 IF (p_progress_status_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
637 l_progress_status_code := null;
638 ELSE
639 l_progress_status_code := p_progress_status_code;
640 END IF;
641
642 IF (p_progress_comment = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
643 l_progress_comment := null;
644 ELSE
645 l_progress_comment := p_progress_comment;
646 END IF;
647
648 IF (p_actual_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) THEN
649 l_actual_start_date := null;
650 ELSE
651 l_actual_start_date := p_actual_start_date;
652 END IF;
653
654 IF (p_actual_finish_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) THEN
655 l_actual_finish_date := null;
656 ELSE
657 l_actual_finish_date := p_actual_finish_date;
658 END IF;
659
660 IF (p_estimated_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) THEN
661 l_estimated_start_date := null;
662 ELSE
663 l_estimated_start_date := p_estimated_start_date;
664 END IF;
665
666 IF (p_estimated_finish_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) THEN
667 l_estimated_finish_date := null;
668 ELSE
669 l_estimated_finish_date := p_estimated_finish_date;
670 END IF;
671
672 IF (p_scheduled_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) THEN
673 l_scheduled_start_date := null;
674 ELSE
675 l_scheduled_start_date := p_scheduled_start_date;
676 END IF;
677
678 IF (p_scheduled_finish_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) THEN
679 l_scheduled_finish_date := null;
680 ELSE
681 l_scheduled_finish_date := p_scheduled_finish_date;
682 END IF;
683
684 IF (p_est_remaining_effort = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
685 l_est_remaining_effort := null;
686 ELSE
687 l_est_remaining_effort := p_est_remaining_effort;
688 END IF;
689
690 IF (p_etc_cost = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
691 l_etc_cost := null;
692 ELSE
693 l_etc_cost := p_etc_cost;
694 END IF;
695
696 IF (p_ACTUAL_COST_TO_DATE = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
697 l_ACTUAL_COST_TO_DATE := null;
698 ELSE
699 l_ACTUAL_COST_TO_DATE := p_ACTUAL_COST_TO_DATE;
700 END IF;
701
702 IF (p_ACTUAL_EFFORT_TO_DATE = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
703 l_ACTUAL_EFFORT_TO_DATE := null;
704 ELSE
705 l_ACTUAL_EFFORT_TO_DATE := p_ACTUAL_EFFORT_TO_DATE;
706 END IF;
707
708
709 -- Bug 2736387 added check for 0 also in beflow if condition
710 IF (p_actual_work_quantity = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR p_actual_work_quantity = 0) THEN
711 l_actual_work_quantity := null;
712 ELSE
713 l_actual_work_quantity := p_actual_work_quantity;
714 END IF;
715
716 l_latest_as_of_date := PA_PROGRESS_UTILS.GET_LATEST_AS_OF_DATE(p_task_id => null
717 , p_project_id => l_project_id_out
718 , p_object_id => l_object_id
719 , p_object_type => l_object_type
720 , p_structure_type => p_STRUCTURE_TYPE
721 );
722
723 IF l_latest_as_of_date is NOT NULL THEN
724 IF l_as_of_date >= l_latest_as_of_date THEN
725 l_progress_mode := 'FUTURE';
726 ELSE
727 IF (l_object_type = 'PA_ASSIGNMENTS' OR l_object_type = 'PA_DELIVERABLES')
728 THEN
729 PA_UTILS.ADD_MESSAGE('PA', 'PA_PROG_BACKDATE_NOT_ALLOW',
730 'OBJECT_ID', l_object_id);
731 p_return_status := FND_API.G_RET_STS_ERROR;
732 RAISE FND_API.G_EXC_ERROR;
733 END IF;
734 l_progress_mode := 'BACKDATED';
735 END IF;
736 ELSE
737 l_progress_mode := 'FUTURE';
738 END IF;
739
740 /* Bug 2758319 -- Added the null handling after getting the l_latest_as_of_date */
741
742 /* Bug2736387 -- Added the following IF condition to pass the appropriate p_progress_mode
743 to PA_PROGRESS_PUB.UPDATE_PROGRESS call */
744
745 -- Bug 3606627 : Using new signature of GET_LATEST_AS_OF_DATE
746 --BUG 4133128, rtarway
747 -- bug 4868792 added progress mode check, latest_as_of_date check
748 ---- the reason we have to check valid as_of_date for each record is because this can only be done after finding out it is future or backdated record
749 ----if (G_bulk_load_flag <> 'Y' and l_progress_mode = 'FUTURE') then
750 if p_structure_type <> 'FINANCIAL' then -- added for bug 5398704
751 if (l_progress_mode = 'FUTURE') then
752 if (l_object_type = 'PA_DELIVERABLES' OR l_object_type = 'PA_TASKS' OR l_object_type = 'PA_STRUCTURES')then
753 IF PA_PROGRESS_UTILS.CHECK_VALID_AS_OF_DATE(TRUNC(l_as_of_date), l_project_id_out, l_object_id, l_object_type ) = 'N'
754 AND trunc(nvl(l_latest_as_of_date,l_as_of_date + 1 )) <> TRUNC(l_as_of_date)
755 THEN
756 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
757 ,p_msg_name => 'PA_TP_INV_AOD2');
758 p_return_status := FND_API.G_RET_STS_ERROR;
759 RAISE FND_API.G_EXC_ERROR;
760 END IF;
761 END IF;
762 if (l_object_type = 'PA_ASSIGNMENTS')then
763 IF PA_PROGRESS_UTILS.CHECK_VALID_AS_OF_DATE(TRUNC(l_as_of_date), l_project_id_out, l_object_id, l_object_type, l_task_id ) = 'N'
764 AND trunc(nvl(l_latest_as_of_date,l_as_of_date + 1 )) <> TRUNC(l_as_of_date)
765 THEN
766 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
767 ,p_msg_name => 'PA_TP_INV_AOD2');
768 p_return_status := FND_API.G_RET_STS_ERROR;
769 RAISE FND_API.G_EXC_ERROR;
770 END IF;
771 END IF;
772 end if;
773 end if;
774 -- Bug 3606627 : Now p_task_status column is being used for deliverable status too
775 -- So we can not default values here. Defaulting is happening in update_task_progress and
776 -- update_deliverable_progress
777 /* Bug2751159 -- Added following IF condition to get the appropriate task_status */
778 IF p_task_status = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR p_task_status IS NULL --bug 2791395
779 THEN
780 /* IF p_percent_complete = 100 THEN
781 l_task_status := '127'; ---COMPLETED -- 127 bug 2826235
782 ELSIF p_percent_complete > 0 THEN
783 l_task_status := '125'; --IN_PROGRESS -- 125 bug 2826235
784 ELSIF p_percent_complete is null or p_percent_complete = 0 THEN
785 l_task_status := '124'; --NOT_STARTED -- 124 bug 2826235
786 END IF; */
787 l_task_status := null;
788 ELSE
789 l_task_status := p_task_status;
790 END IF;
791 -- Bug 3606627 : Cancelled Status check is modified
792 /* --Bug 2792857
793 IF PA_PROGRESS_UTILS.get_task_status( l_project_id_out, l_object_id ) = '128' AND --CANCELLED --128 bug 2826235
794 PA_PROGRESS_UTILS.get_system_task_status( l_task_status ) = 'CANCELLED' AND
795 l_progress_mode = 'FUTURE'
796 THEN
797 RETURN;
798 END IF;
799 --Bug 2792857*/
800
801 l_system_task_status := PA_PROGRESS_UTILS.get_system_task_status(l_task_status, l_object_type) ;
802
803 IF ((l_system_task_status = 'CANCELLED' OR l_system_task_status = 'DLVR_CANCELLED')
804 AND l_progress_mode = 'FUTURE') THEN
805 return;
806 END IF;
807
808 IF g1_debug_mode = 'Y' THEN
809 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'Calling PA_PROGRESS_PUB.UPDATE_PROGRESS', x_Log_Level=> 3);
810 END IF;
811
812 if (l_progress_mode = 'FUTURE') then
813 PA_PROGRESS_PUB.UPDATE_PROGRESS(
814 p_api_version => p_api_version_number,
815 p_init_msg_list => p_init_msg_list,
816 p_commit => FND_API.G_FALSE, --Bug 3754134 instead passing p_commit pass false
817 p_action => 'PUBLISH',
818 P_rollup_entire_wbs_flag => l_rollup_entire_wbs,
819 p_progress_mode => l_progress_mode,
820 p_calling_module => 'AMG',
821 p_project_id => l_project_id_out,
822 p_object_id => l_object_id,
823 p_object_version_id => l_object_version_id,
824 p_object_type => l_object_type,
825 p_as_of_date => trunc(l_as_of_date), -- 5294838
826 p_percent_complete => p_percent_complete,
827 p_progress_status_code => l_progress_status_code,
828 p_progress_comment => l_progress_comment,
829 p_brief_overview => l_description,
830 p_actual_start_date => l_actual_start_date,
831 p_actual_finish_date => l_actual_finish_date,
832 p_estimated_start_date => l_estimated_start_date,
833 p_estimated_finish_date => l_estimated_finish_date,
834 p_scheduled_start_date => l_scheduled_start_date,
835 p_scheduled_finish_date => l_scheduled_finish_date,
836 p_pm_product_code => l_pm_product_code,
837 p_record_version_number => 1,
838 p_task_status => l_task_status,
839 p_est_remaining_effort => l_est_remaining_effort,
840 p_actual_work_quantity => l_actual_work_quantity,
841 p_ETC_cost => l_etc_cost,
842 p_structure_type => p_structure_type,
843 p_actual_effort => l_ACTUAL_EFFORT_TO_DATE, -- Bug 3799841 : there was swap b/w cost and effort
844 p_actual_cost => l_ACTUAL_COST_TO_DATE,
845 p_task_id => l_task_id,
846 p_structure_version_id => l_structure_version_id,
847 p_prog_fom_wp_flag => 'N',
848 p_txn_currency_code => p_txn_currency_code, -- Fix for Bug # 3988457.
849 x_return_status => p_return_status,
850 x_msg_count => p_msg_count,
851 x_msg_data => p_msg_data );
852 elsif (l_progress_mode = 'BACKDATED') then
853 PA_PROGRESS_PUB.UPDATE_PROGRESS(
854 p_api_version => p_api_version_number,
855 p_init_msg_list => p_init_msg_list,
856 p_commit => FND_API.G_FALSE,
857 p_action => 'PUBLISH',
858 P_rollup_entire_wbs_flag => l_rollup_entire_wbs,
859 p_progress_mode => l_progress_mode,
860 p_calling_module => 'AMG',
861 p_project_id => l_project_id_out,
862 p_object_id => l_object_id,
863 p_object_version_id => l_object_version_id,
864 p_object_type => l_object_type,
865 p_as_of_date => trunc(l_as_of_date), -- 5294838
866 p_percent_complete => p_percent_complete,
867 p_progress_status_code => l_progress_status_code,
868 p_progress_comment => l_progress_comment,
869 p_brief_overview => l_description,
870 p_scheduled_start_date => l_scheduled_start_date,
871 p_scheduled_finish_date => l_scheduled_finish_date,
872 p_pm_product_code => l_pm_product_code,
873 p_record_version_number => 1,
874 p_task_status => l_task_status,
875 p_structure_type => p_structure_type,
876 p_task_id => l_task_id,
877 p_structure_version_id => l_structure_version_id,
878 p_prog_fom_wp_flag => 'N',
879 p_txn_currency_code => p_txn_currency_code,
880 x_return_status => p_return_status,
881 x_msg_count => p_msg_count,
882 x_msg_data => p_msg_data );
883
884 end if;
885
886 IF g1_debug_mode = 'Y' THEN
887 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'After call of PA_PROGRESS_PUB.UPDATE_PROGRESS p_return_status='||p_return_status, x_Log_Level=> 3);
888 END IF;
889
890
891 IF (p_return_status = FND_API.G_RET_STS_SUCCESS )
892 then
893 IF FND_API.to_boolean(p_commit)
894 THEN
895 COMMIT;
896 END IF;
897 ELSE
898 ROLLBACK TO Update_Progress_Pub;
899 FND_MSG_PUB.Count_And_Get
900 ( p_count => p_msg_count
901 ,p_data => p_msg_data
902 );
903 END IF;
904
905 EXCEPTION
906 WHEN FND_API.G_EXC_ERROR THEN
907
908 p_return_status := FND_API.G_RET_STS_ERROR;
909 ROLLBACK TO Update_Progress_Pub;
910
911 FND_MSG_PUB.Count_And_Get
912 ( p_count => p_msg_count
913 , p_data => p_msg_data
914 );
915
916 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
917
918 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
919 ROLLBACK TO Update_Progress_Pub;
920
921 FND_MSG_PUB.Count_And_Get
922 ( p_count => p_msg_count
923 , p_data => p_msg_data
924 );
925
926
927 WHEN ROW_ALREADY_LOCKED THEN
928
929 p_return_status := FND_API.G_RET_STS_ERROR ;
930 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
931 THEN
932 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
933 FND_MESSAGE.SET_TOKEN('ENTITY', 'PERCENT_COMPLETE');
934 FND_MSG_PUB.Add;
935 END IF;
936 ROLLBACK TO Update_Progress_Pub;
937 FND_MSG_PUB.Count_And_Get
938 (p_count => p_msg_count
939 , p_data => p_msg_data
940 );
941
942 WHEN OTHERS THEN
943
944 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
945 ROLLBACK TO Update_Progress_Pub;
946
947 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
948 FND_MSG_PUB.Add_Exc_Msg
949 ( G_PKG_NAME
950 , l_api_name
951 );
952 END IF;
953
954 FND_MSG_PUB.Count_And_Get
955 ( p_count => p_msg_count
956 , p_data => p_msg_data
957 );
958
959
960 END Update_Progress;
961 -- =========================================================================
962
963 --
964 -- Name: Update_Earned_Value
965 -- Type: PL/SQL Procedure
966 -- Decscription: This procedure updates the PA_EARNED_VALUES table.
967 --
968 -- Called Subprograms: Convert_Pm_Projref_To_Id
969 -- , Convert_Pm_Taskref_To_Id
970 -- , Convert_List_Name_To_Id
971 -- , Convert_Alias_To_Id
972 --
973 -- History: 08-AUG-1996 Created jwhite
974 -- 29-AUG-1996 Update jwhite Applied latest messaging standards.
975 -- 29-OCT-1996 Update jwhite Replaced resource list member
976 -- conversion code with API call
977 -- to PA_RESOURCE_PUB.
978 -- 13-DEC-1996 Update jwhite Applied lastest standards.
979 --
980
981 PROCEDURE Update_Earned_Value
982 (p_api_version_number IN NUMBER
983 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
984 , p_commit IN VARCHAR2 := FND_API.G_FALSE
985 , p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
986 , p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
987 , p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
988 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
989 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
990 , p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
991 , p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
992 , p_resource_list_member_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
993 , p_resource_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
994 , p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
995 , p_as_of_date IN DATE
996 , p_bcws_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
997 , p_acwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
998 , p_bcwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
999 , p_bac_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1000 , p_bcws_itd IN NUMBER
1001 , p_acwp_itd IN NUMBER
1002 , p_bcwp_itd IN NUMBER
1003 , p_bac_itd IN NUMBER
1004 , p_bqws_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1005 , p_aqwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1006 , p_bqwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1007 , p_baq_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1008 , p_bqws_itd IN NUMBER
1009 , p_aqwp_itd IN NUMBER
1010 , p_bqwp_itd IN NUMBER
1011 , p_baq_itd IN NUMBER
1012 )
1013
1014 IS
1015
1016 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1017 l_api_name CONSTANT VARCHAR2(30) := 'Update_Earned_Value';
1018 l_value_conversion_error BOOLEAN := FALSE;
1019 l_return_status VARCHAR2(1);
1020 l_msg_count INTEGER;
1021
1022 l_err_code NUMBER := -1;
1023 l_err_stage VARCHAR2(2000) := NULL;
1024 l_err_stack VARCHAR2(2000) := NULL;
1025
1026 l_project_id_out NUMBER := 0;
1027 l_task_id_out NUMBER := 0;
1028 l_resource_list_member_id_out NUMBER := 0;
1029 l_resource_list_id_out NUMBER := 0;
1030
1031 l_bcws_current NUMBER := 0;
1032 l_acwp_current NUMBER := 0;
1033 l_bcwp_current NUMBER := 0;
1034 l_bac_current NUMBER := 0;
1035
1036 l_bqws_current NUMBER := 0;
1037 l_aqwp_current NUMBER := 0;
1038 l_bqwp_current NUMBER := 0;
1039 l_baq_current NUMBER := 0;
1040 l_msg_data VARCHAR2(2000);
1041 l_function_allowed VARCHAR2(1);
1042 l_resp_id NUMBER := 0;
1043 l_current_flag VARCHAR2(1);
1044 l_dummy VARCHAR2(1);
1045 l_module_name VARCHAR2(80);
1046 l_user_id NUMBER := 0;
1047 l_date_computed DATE;
1048
1049
1050 -- ROW LOCKING
1051
1052 CURSOR l_earned_values_csr (l_project_id_out NUMBER
1053 , l_task_id_out NUMBER
1054 , l_resource_list_member_id_out NUMBER)
1055 IS
1056 SELECT trunc(as_of_date)
1057 FROM pa_earned_values ev
1058 WHERE ev.project_id = l_project_id_out
1059 AND ev.task_id = l_task_id_out
1060 AND ev.resource_list_member_id = l_resource_list_member_id_out
1061 AND ev.current_flag = 'Y'
1062 FOR UPDATE NOWAIT;
1063
1064
1065
1066 BEGIN
1067
1068 SAVEPOINT Update_Earned_Value_Pub;
1069
1070 p_return_status := FND_API.G_RET_STS_SUCCESS;
1071
1072 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
1073 p_api_version_number ,
1074 l_api_name,
1075 G_PKG_NAME)
1076 THEN
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078 END IF;
1079
1080 --Uncommented the following code as the fix of Bug 5632178
1081 /* Moved from below for Advanced Project Security Changes. Bug 2471668*/
1082
1083 PA_PROJECT_PVT.Convert_pm_projref_to_id
1084 ( p_pm_project_reference => p_pm_project_reference
1085 , p_pa_project_id => p_project_id
1086 , p_out_project_id => l_project_id_out
1087 , p_return_status => l_return_status
1088 );
1089
1090
1091 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1093 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1094 RAISE FND_API.G_EXC_ERROR;
1095 END IF;
1096 --End of changes for bug 5632178
1097
1098 l_user_id := FND_GLOBAL.User_id;
1099 l_resp_id := FND_GLOBAL.Resp_id;
1100 --l_module_name := p_pm_product_code||'.'||'PA_PM_UPDATE_EARNED_VALUE';
1101
1102 pa_security.initialize (X_user_id => l_user_id,
1103 X_calling_module => l_module_name);
1104
1105 -- Actions performed using the APIs would be subject to
1106 -- function security. If the responsibility does not allow
1107 -- such functions to be executed, the API should not proceed further
1108 -- since the user does not have access to such functions
1109
1110 --Bug 2471668
1111 PA_INTERFACE_UTILS_PUB.g_project_id := l_project_id_out;
1112
1113 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
1114 (p_api_version_number => p_api_version_number,
1115 p_responsibility_id => l_resp_id,
1116 p_function_name => 'PA_PM_UPDATE_EARNED_VALUE',
1117 p_msg_count => l_msg_count,
1118 p_msg_data => l_msg_data,
1119 p_return_status => l_return_status,
1120 p_function_allowed => l_function_allowed );
1121
1122 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1123 THEN
1124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1125
1126 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1127 THEN
1128 RAISE FND_API.G_EXC_ERROR;
1129 END IF;
1130 IF l_function_allowed = 'N' THEN
1131 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
1132 FND_MSG_PUB.add;
1133 p_return_status := FND_API.G_RET_STS_ERROR;
1134 RAISE FND_API.G_EXC_ERROR;
1135 END IF;
1136
1137 IF FND_API.to_boolean(p_init_msg_list)
1138 THEN
1139 FND_MSG_PUB.initialize;
1140 END IF;
1141
1142
1143 -- VALUE LAYER -----------------------------------------------------------------------
1144
1145 /* Moved up for Advanced project security changes . Bug 2471668
1146 PA_PROJECT_PVT.Convert_pm_projref_to_id
1147 ( p_pm_project_reference => p_pm_project_reference
1148 , p_pa_project_id => p_project_id
1149 , p_out_project_id => l_project_id_out
1150 , p_return_status => l_return_status
1151 );
1152
1153
1154 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1156 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1157 RAISE FND_API.G_EXC_ERROR;
1158 END IF;
1159 */
1160
1161 IF pa_security.allow_query (x_project_id => l_project_id_out ) = 'N' THEN
1162
1163 -- The user does not have query privileges on this project
1164 -- Hence, cannot update the project.Raise error
1165
1166 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
1167 FND_MSG_PUB.add;
1168 p_return_status := FND_API.G_RET_STS_ERROR;
1169 RAISE FND_API.G_EXC_ERROR;
1170 ELSE
1171 -- If the user has query privileges, then check whether
1172 -- update privileges are also available
1173 IF pa_security.allow_update (x_project_id => l_project_id_out ) = 'N'
1174 THEN
1175
1176 -- The user does not have update privileges on this project
1177 -- Hence , raise error
1178
1179 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
1180 FND_MSG_PUB.add;
1181 p_return_status := FND_API.G_RET_STS_ERROR;
1182 RAISE FND_API.G_EXC_ERROR;
1183 END IF;
1184 END IF;
1185
1186 IF (p_task_id = 0) THEN
1187 -- Must Be Project-Level Earned Value Data
1188
1189 l_task_id_out := 0;
1190
1191 ELSE
1192 -- 5262740 Converted PA_PROJECT_PVT.Convert_pm_taskref_to_id to PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
1193 PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
1194 ( p_pa_project_id => l_project_id_out
1195 , p_pa_task_id => p_task_id
1196 , p_pm_task_reference => p_pm_task_reference
1197 , p_out_task_id => l_task_id_out
1198 , p_return_status => l_return_status
1199 );
1200
1201
1202 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1204 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1205 RAISE FND_API.G_EXC_ERROR;
1206 END IF;
1207 END IF;
1208
1209 -- ---------------------------------------------------------------
1210 -- Convert Resource List Member Values to Id
1211 -- ---------------------------------------------------------------
1212
1213 IF (p_resource_list_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1214
1215 IF (p_resource_list_member_id = 0) THEN
1216 -- Must be Task-Level Earned Value Data
1217
1218 l_resource_list_member_id_out := 0;
1219
1220 ELSE
1221
1222 PA_RESOURCE_PUB.Convert_alias_to_id
1223 ( p_resource_list_id => l_resource_list_id_out
1224 , p_resource_list_member_id => p_resource_list_member_id
1225 , p_out_resource_list_member_id => l_resource_list_member_id_out
1226 , p_return_status => l_return_status
1227 );
1228
1229
1230 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1232 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1233 RAISE FND_API.G_EXC_ERROR;
1234 END IF;
1235
1236 END IF;
1237
1238 ELSE
1239 PA_RESOURCE_PUB.Convert_List_name_to_id
1240 ( p_resource_list_name => p_resource_list_name,
1241 p_out_resource_list_id => l_resource_list_id_out,
1242 p_return_status => l_return_status
1243 );
1244
1245 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1247 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1248 RAISE FND_API.G_EXC_ERROR;
1249 END IF;
1250
1251 PA_RESOURCE_PUB.Convert_alias_to_id
1252 ( p_resource_list_id => l_resource_list_id_out
1253 , p_alias => p_resource_alias
1254 , p_out_resource_list_member_id => l_resource_list_member_id_out
1255 , p_return_status => l_return_status
1256 );
1257
1258 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1260 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1261 RAISE FND_API.G_EXC_ERROR;
1262 END IF;
1263
1264 END IF;
1265
1266 -- ------------------------------------------------------------------------------
1267 -- Set Defaults
1268 -- ------------------------------------------------------------------------------
1269
1270 IF (p_bcws_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1271 l_bcws_current := 0;
1272 ELSE
1273 l_bcws_current := p_bcws_current;
1274 END IF;
1275 IF (p_acwp_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1276 l_acwp_current := 0;
1277 ELSE
1278 l_acwp_current := p_acwp_current ;
1279 END IF;
1280 IF (p_bcwp_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1281 l_bcwp_current := 0;
1282 ELSE
1283 l_bcwp_current := p_bcwp_current;
1284 END IF;
1285 IF (p_bac_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1286 l_bac_current := 0;
1287 ELSE
1288 l_bac_current := p_bac_current;
1289 END IF;
1290
1291 IF (p_bqws_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1292 l_bqws_current := 0;
1293 ELSE
1294 l_bqws_current := p_bqws_current;
1295 END IF;
1296 IF (p_aqwp_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1297 l_aqwp_current := 0;
1298 ELSE
1299 l_aqwp_current := p_aqwp_current;
1300 END IF;
1301 IF (p_bqwp_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1302 l_bqwp_current := 0;
1303 ELSE
1304 l_bqwp_current := p_bqwp_current;
1305 END IF;
1306 IF (p_baq_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1307 l_baq_current := 0;
1308 ELSE
1309 l_baq_current := p_baq_current;
1310 END IF;
1311
1312 -- ------------------------------------------------------------------------------
1313 -- UPDATE Current Flag in PA_EARNED_VALUES
1314 -- ------------------------------------------------------------------------------
1315
1316 -- Locking
1317 l_date_computed := NULL;
1318
1319 OPEN l_earned_values_csr (l_project_id_out, l_task_id_out
1320 , l_resource_list_member_id_out );
1321 FETCH l_earned_values_csr INTO l_date_computed;
1322 IF l_earned_values_csr%NOTFOUND THEN
1323 l_current_flag := 'Y'; -- means there was no existing record
1324 -- with current flag = 'Y'.This could be
1325 -- the first time the record is coming in
1326 END IF;
1327 IF l_date_computed IS NOT NULL THEN
1328 IF TRUNC(p_as_of_date) >= l_date_computed THEN -- If the
1329 -- incoming date is >= to the
1330 -- one existing then set the old
1331 -- current to 'N' and insert new
1332 -- record with current_flag = 'Y'
1333 l_current_flag := 'Y'; -- means the current flag is to be
1334 ELSE
1335 l_current_flag := 'N';
1336 END IF;
1337 END IF;
1338 CLOSE l_earned_values_csr;
1339
1340 IF l_current_flag = 'Y' AND l_date_computed IS NOT NULL THEN
1341 UPDATE pa_earned_values ev
1342 SET ev.current_flag = 'N'
1343 WHERE ev.project_id = l_project_id_out
1344 AND ev.task_id = l_task_id_out
1345 AND ev.resource_list_member_id = l_resource_list_member_id_out
1346 AND ev.current_flag = 'Y';
1347 END IF;
1348
1349
1350 -- ------------------------------------------------------------------------------
1351 -- INSERT VALUES INTO PA_EARNED_VALUES
1352 -- ------------------------------------------------------------------------------
1353
1354 INSERT INTO pa_earned_values (
1355 PROJECT_ID
1356 , TASK_ID
1357 , RESOURCE_LIST_MEMBER_ID
1358 , AS_OF_DATE
1359 , CURRENT_FLAG
1360 , BCWS
1361 , ACWP
1362 , BCWP
1363 , BAC
1364 , BCWS_ITD
1365 , ACWP_ITD
1366 , BCWP_ITD
1367 , BAC_ITD
1368 , BQWS
1369 , AQWP
1370 , BQWP
1371 , BAQ
1372 , BQWS_ITD
1373 , AQWP_ITD
1374 , BQWP_ITD
1375 , BAQ_ITD
1376 , LAST_UPDATE_DATE
1377 , LAST_UPDATED_BY
1378 , CREATION_DATE
1379 , CREATED_BY
1380 , LAST_UPDATE_LOGIN)
1381 VALUES (
1382 l_project_id_out
1383 , l_task_id_out
1384 , l_resource_list_member_id_out
1385 , p_as_of_date
1386 , l_current_flag
1387 , pa_currency.round_currency_amt(l_bcws_current)
1388 , pa_currency.round_currency_amt(l_acwp_current)
1389 , pa_currency.round_currency_amt(l_bcwp_current)
1390 , pa_currency.round_currency_amt(l_bac_current)
1391 , pa_currency.round_currency_amt(p_bcws_itd)
1392 , pa_currency.round_currency_amt(p_acwp_itd)
1393 , pa_currency.round_currency_amt(p_bcwp_itd)
1394 , pa_currency.round_currency_amt(p_bac_itd)
1395 , l_bqws_current
1396 , l_aqwp_current
1397 , l_bqwp_current
1398 , l_baq_current
1399 , p_bqws_itd
1400 , p_aqwp_itd
1401 , p_bqwp_itd
1402 , p_baq_itd
1403 , g_last_update_date
1404 , g_last_updated_by
1405 , g_creation_date
1406 , g_created_by
1407 , g_last_update_login);
1408
1409
1410 IF (SQL%ROWCOUNT = 0)
1411 THEN
1412 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1413 THEN
1414 FND_MESSAGE.SET_NAME('PA','PA_EV_INSERT_ERROR');
1415 FND_MSG_PUB.Add;
1416 END IF;
1417 RAISE FND_API.G_EXC_ERROR;
1418 END IF;
1419
1420 IF FND_API.to_boolean(p_commit)
1421 THEN
1422 COMMIT;
1423 END IF;
1424
1425
1426 EXCEPTION
1427 WHEN FND_API.G_EXC_ERROR THEN
1428 p_return_status := FND_API.G_RET_STS_ERROR;
1429 ROLLBACK TO Update_Earned_Value_Pub;
1430
1431 FND_MSG_PUB.Count_And_Get
1432 ( p_count => p_msg_count
1433 ,p_data => p_msg_data
1434 );
1435
1436 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1437 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438 ROLLBACK TO Update_Earned_Value_Pub;
1439
1440 FND_MSG_PUB.Count_And_Get
1441 ( p_count => p_msg_count
1442 ,p_data => p_msg_data
1443 );
1444
1445 WHEN ROW_ALREADY_LOCKED THEN
1446
1447 p_return_status := FND_API.G_RET_STS_ERROR ;
1448
1449 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1450 THEN
1451 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
1452 FND_MESSAGE.SET_TOKEN('ENTITY', 'EARNED_VALUE');
1453 FND_MSG_PUB.Add;
1454 END IF;
1455
1456 ROLLBACK TO Update_Progress_Pub;
1457
1458
1459 WHEN OTHERS THEN
1460
1461 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1462 ROLLBACK TO Update_Earned_Value_Pub;
1463
1464 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1465 FND_MSG_PUB.Add_Exc_Msg
1466 ( G_PKG_NAME
1467 , l_api_name
1468 );
1469 END IF;
1470
1471 FND_MSG_PUB.Count_And_Get
1472 ( p_count => p_msg_count
1473 ,p_data => p_msg_data
1474 );
1475
1476
1477 END Update_Earned_Value;
1478
1479 PROCEDURE Init_Update_Task_Progress
1480 (p_api_version_number IN NUMBER
1481 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1482 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1483 , p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1484 , p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1485 , p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1486 )
1487 IS
1488 l_api_name CONSTANT VARCHAR2(30) := 'Init_Update_Task_Progress';
1489
1490 BEGIN
1491 IF FND_API.to_boolean(p_init_msg_list)
1492 THEN
1493 FND_MSG_PUB.initialize;
1494 END IF;
1495
1496 p_return_status := FND_API.G_RET_STS_SUCCESS;
1497
1498 G_TASK_PROGRESS_in_tbl.delete;
1499 G_TASK_PROGRESS_tbl_count := 0;
1500
1501 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1502 FND_MSG_PUB.Count_And_Get
1503 ( p_count => p_msg_count
1504 ,p_data => p_msg_data
1505 );
1506 END IF;
1507 EXCEPTION
1508 WHEN FND_API.G_EXC_ERROR THEN
1509 p_return_status := FND_API.G_RET_STS_ERROR;
1510 FND_MSG_PUB.Count_And_Get
1511 ( p_count => p_msg_count
1512 ,p_data => p_msg_data
1513 );
1514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1516 FND_MSG_PUB.Count_And_Get
1517 ( p_count => p_msg_count
1518 ,p_data => p_msg_data
1519 );
1520 WHEN OTHERS THEN
1521 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1522 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1523 FND_MSG_PUB.Add_Exc_Msg
1524 ( G_PKG_NAME
1525 , l_api_name
1526 );
1527 END IF;
1528 FND_MSG_PUB.Count_And_Get
1529 ( p_count => p_msg_count
1530 ,p_data => p_msg_data
1531 );
1532
1533 END Init_Update_Task_Progress;
1534
1535 PROCEDURE Load_Task_Progress
1536 (p_api_version_number IN NUMBER
1537 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1538 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1539 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1540 ,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1541 ,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1542 ,p_structure_type IN VARCHAR2 := 'FINANCIAL'
1543 ,p_as_of_date IN DATE
1544 ,p_task_id IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1545 ,p_task_name IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1546 ,p_task_number IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1547 ,p_pm_task_reference IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1548 ,p_percent_complete IN PA_NUM_1000_NUM
1549 ,p_description IN PA_VC_1000_2000 := PA_VC_1000_2000(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1550 ,p_object_id IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1551 ,p_object_version_id IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1552 ,p_object_type IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1553 ,p_progress_status_code IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1554 ,p_progress_comment IN PA_VC_1000_4000 := PA_VC_1000_4000(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1555 ,p_actual_start_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1556 ,p_actual_finish_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1557 ,p_estimated_start_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1558 ,p_estimated_finish_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1559 ,p_scheduled_start_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1560 ,p_scheduled_finish_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1561 ,p_task_status IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1562 ,p_est_remaining_effort IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1563 ,p_actual_work_quantity IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1564 ,p_etc_cost IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) /* FP M Task Progress 3420093*/
1565 ,p_pm_deliverable_reference IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) -- Bug 3606627
1566 ,p_pm_task_assgn_reference IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) -- Bug 3606627
1567 ,p_actual_cost_to_date IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) -- Bug 3606627
1568 ,p_actual_effort_to_date IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) -- Bug 3606627
1569 ,p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1570 ,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1571 ,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1572 )
1573 IS
1574 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1575 l_api_name CONSTANT VARCHAR2(30) := 'Load_Task_Progress';
1576 l_progress_mode VARCHAR2(10);
1577 l_latest_as_of_date Date;
1578 G1_DEBUG_MODE VARCHAR2(1);
1579
1580 l_TASK_ID NUMBER;
1581 l_TASK_NAME VARCHAR2(150);
1582 l_TASK_NUMBER VARCHAR2(150);
1583 l_PM_TASK_REFERENCE VARCHAR2(150);
1584 l_PERCENT_COMPLETE NUMBER;
1585 l_DESCRIPTION VARCHAR2(2000);
1586 l_OBJECT_ID NUMBER;
1587 l_OBJECT_VERSION_ID NUMBER;
1588 l_OBJECT_TYPE VARCHAR2(150);
1589 l_PROGRESS_STATUS_CODE VARCHAR2(150);
1590 l_PROGRESS_COMMENT VARCHAR2(4000);
1591 l_ACTUAL_START_DATE DATE;
1592 l_ACTUAL_FINISH_DATE DATE;
1593 l_ESTIMATED_START_DATE DATE;
1594 l_ESTIMATED_FINISH_DATE DATE;
1595 l_SCHEDULED_START_DATE DATE;
1596 l_SCHEDULED_FINISH_DATE DATE;
1597 l_TASK_STATUS VARCHAR2(150);
1598 l_EST_REMAINING_EFFORT NUMBER;
1599 l_ACTUAL_WORK_QUANTITY NUMBER;
1600 l_ETC_COST NUMBER;
1601 l_PM_DELIVERABLE_REFERENCE VARCHAR2(150);
1602 l_PM_TASK_ASSGN_REFERENCE VARCHAR2(150);
1603 l_ACTUAL_COST_TO_DATE NUMBER;
1604 l_ACTUAL_EFFORT_TO_DATE NUMBER;
1605
1606 BEGIN
1607
1608 g1_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1609
1610 IF g1_debug_mode = 'Y' THEN
1611 pa_debug.init_err_stack ('PA_PROGRESS_PUB.LOAD_TASK_PROGRESS');
1612 END IF;
1613
1614 IF FND_API.to_boolean(p_init_msg_list)
1615 THEN
1616 FND_MSG_PUB.initialize;
1617 END IF;
1618
1619 p_return_status := FND_API.G_RET_STS_SUCCESS;
1620
1621 IF(G_TASK_PROGRESS_tbl_count = 0) THEN
1622 G_PROJECT_ID := P_PROJECT_ID;
1623 G_pm_project_reference := P_pm_project_reference;
1624 G_PM_PRODUCT_CODE := P_PM_PRODUCT_CODE;
1625 G_STRUCTURE_TYPE := P_STRUCTURE_TYPE;
1626 G_AS_OF_DATE := TRUNC(P_AS_OF_DATE);
1627 END IF;
1628
1629 IF g1_debug_mode = 'Y' THEN
1630 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'P_TASK_ID.count='||P_TASK_ID.count, x_Log_Level=> 3);
1631 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'P_TASK_ID.count='||P_TASK_ID.count, x_Log_Level=> 3);
1632 END IF;
1633
1634 FOR i in 1..1000 LOOP
1635 -- Bug 3606627 : Commented the code which return if task_id is null. Now task_id can be null for Deleiverable.
1636 -- if (((P_TASK_ID(i) is null) or (P_TASK_ID(i) =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)) and ((P_PM_TASK_REFERENCE(i) is null) or (P_PM_TASK_REFERENCE(i) =PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))) then
1637 -- return;
1638 --else
1639
1640 -- Bug 3673618 : Introduced local variables to store the value first.
1641 -- This is done to ensure that if any parameter is not passed it does not give error subscript out of bound
1642
1643 IF P_TASK_ID.count = 1 AND P_TASK_ID(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1644 l_TASK_ID := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1645 ELSE
1646 l_TASK_ID := P_TASK_ID(i);
1647 END IF;
1648
1649
1650
1651 IF P_TASK_NAME.count = 1 AND P_TASK_NAME(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1652 l_TASK_NAME := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1653 ELSE
1654 l_TASK_NAME := P_TASK_NAME(i);
1655 END IF;
1656
1657
1658 IF P_TASK_NUMBER.count = 1 AND P_TASK_NUMBER(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1659 l_TASK_NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1660 ELSE
1661 l_TASK_NUMBER := P_TASK_NUMBER(i);
1662 END IF;
1663
1664 --end if;
1665
1666 IF p_PM_TASK_REFERENCE.count = 1 AND p_PM_TASK_REFERENCE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1667 l_PM_TASK_REFERENCE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1668 ELSE
1669 l_PM_TASK_REFERENCE := p_PM_TASK_REFERENCE(i);
1670 END IF;
1671
1672
1673 IF p_PERCENT_COMPLETE.count = 1 AND p_PERCENT_COMPLETE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1674 l_PERCENT_COMPLETE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1675 ELSE
1676 l_PERCENT_COMPLETE := p_PERCENT_COMPLETE(i);
1677 END IF;
1678
1679 IF p_DESCRIPTION.count = 1 AND p_DESCRIPTION(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1680 l_DESCRIPTION := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1681 ELSE
1682 l_DESCRIPTION := p_DESCRIPTION(i);
1683 END IF;
1684
1685 IF p_OBJECT_ID.count = 1 AND p_OBJECT_ID(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1686 l_OBJECT_ID := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1687 ELSE
1688 l_OBJECT_ID := p_OBJECT_ID(i);
1689 END IF;
1690
1691 IF p_OBJECT_VERSION_ID.count = 1 AND p_OBJECT_VERSION_ID(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1692 l_OBJECT_VERSION_ID := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1693 ELSE
1694 l_OBJECT_VERSION_ID := p_OBJECT_VERSION_ID(i);
1695 END IF;
1696
1697 IF p_OBJECT_TYPE.count = 1 AND p_OBJECT_TYPE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1698 l_OBJECT_TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1699 ELSE
1700 l_OBJECT_TYPE := p_OBJECT_TYPE(i);
1701 END IF;
1702
1703 IF p_PROGRESS_STATUS_CODE.count = 1 AND p_PROGRESS_STATUS_CODE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1704 l_PROGRESS_STATUS_CODE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1705 ELSE
1706 l_PROGRESS_STATUS_CODE := p_PROGRESS_STATUS_CODE(i);
1707 END IF;
1708
1709 IF p_PROGRESS_COMMENT.count = 1 AND p_PROGRESS_COMMENT(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1710 l_PROGRESS_COMMENT := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1711 ELSE
1712 l_PROGRESS_COMMENT := p_PROGRESS_COMMENT(i);
1713 END IF;
1714
1715 IF p_ACTUAL_START_DATE.count = 1 AND p_ACTUAL_START_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1716 l_ACTUAL_START_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1717 ELSE
1718 l_ACTUAL_START_DATE := p_ACTUAL_START_DATE(i);
1719 END IF;
1720
1721 IF p_ACTUAL_FINISH_DATE.count = 1 AND p_ACTUAL_FINISH_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1722 l_ACTUAL_FINISH_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1723 ELSE
1724 l_ACTUAL_FINISH_DATE := p_ACTUAL_FINISH_DATE(i);
1725 END IF;
1726
1727 IF p_ESTIMATED_START_DATE.count = 1 AND p_ESTIMATED_START_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1728 l_ESTIMATED_START_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1729 ELSE
1730 l_ESTIMATED_START_DATE := p_ESTIMATED_START_DATE(i);
1731 END IF;
1732
1733 IF p_ESTIMATED_FINISH_DATE.count = 1 AND p_ESTIMATED_FINISH_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1734 l_ESTIMATED_FINISH_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1735 ELSE
1736 l_ESTIMATED_FINISH_DATE := p_ESTIMATED_FINISH_DATE(i);
1737 END IF;
1738
1739 IF p_SCHEDULED_START_DATE.count = 1 AND p_SCHEDULED_START_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1740 l_SCHEDULED_START_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1741 ELSE
1742 l_SCHEDULED_START_DATE := p_SCHEDULED_START_DATE(i);
1743 END IF;
1744
1745 IF p_SCHEDULED_FINISH_DATE.count = 1 AND p_SCHEDULED_FINISH_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1746 l_SCHEDULED_FINISH_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1747 ELSE
1748 l_SCHEDULED_FINISH_DATE := p_SCHEDULED_FINISH_DATE(i);
1749 END IF;
1750
1751 IF p_TASK_STATUS.count = 1 AND p_TASK_STATUS(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1752 l_TASK_STATUS := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1753 ELSE
1754 l_TASK_STATUS := p_TASK_STATUS(i);
1755 END IF;
1756
1757 IF p_EST_REMAINING_EFFORT.count = 1 AND p_EST_REMAINING_EFFORT(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1758 l_EST_REMAINING_EFFORT := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1759 ELSE
1760 l_EST_REMAINING_EFFORT := p_EST_REMAINING_EFFORT(i);
1761 END IF;
1762
1763 IF p_ACTUAL_WORK_QUANTITY.count = 1 AND p_ACTUAL_WORK_QUANTITY(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1764 l_ACTUAL_WORK_QUANTITY := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1765 ELSE
1766 l_ACTUAL_WORK_QUANTITY := p_ACTUAL_WORK_QUANTITY(i);
1767 END IF;
1768
1769 IF p_ETC_COST.count = 1 AND p_ETC_COST(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1770 l_ETC_COST := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1771 ELSE
1772 l_ETC_COST := p_ETC_COST(i);
1773 END IF;
1774
1775 --Bug 3606627 : Assign new variables Start
1776 IF p_PM_DELIVERABLE_REFERENCE.count = 1 AND p_PM_DELIVERABLE_REFERENCE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1777 l_PM_DELIVERABLE_REFERENCE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1778 ELSE
1779 l_PM_DELIVERABLE_REFERENCE := p_PM_DELIVERABLE_REFERENCE(i);
1780 END IF;
1781
1782 IF p_PM_TASK_ASSGN_REFERENCE.count = 1 AND p_PM_TASK_ASSGN_REFERENCE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1783 l_PM_TASK_ASSGN_REFERENCE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1784 ELSE
1785 l_PM_TASK_ASSGN_REFERENCE := p_PM_TASK_ASSGN_REFERENCE(i);
1786 END IF;
1787
1788 IF p_ACTUAL_COST_TO_DATE.count = 1 AND p_ACTUAL_COST_TO_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1789 l_ACTUAL_COST_TO_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1790 ELSE
1791 l_ACTUAL_COST_TO_DATE := p_ACTUAL_COST_TO_DATE(i);
1792 END IF;
1793
1794 IF p_ACTUAL_EFFORT_TO_DATE.count = 1 AND p_ACTUAL_EFFORT_TO_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1795 l_ACTUAL_EFFORT_TO_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1796 ELSE
1797 l_ACTUAL_EFFORT_TO_DATE := p_ACTUAL_EFFORT_TO_DATE(i);
1798 END IF;
1799
1800 IF g1_debug_mode = 'Y' THEN
1801 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'Values passed at count i='||i, x_Log_Level=> 3);
1802 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_TASK_ID :'||l_TASK_ID, x_Log_Level=> 3);
1803 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_TASK_NAME :'||l_TASK_NAME, x_Log_Level=> 3);
1804 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_TASK_NUMBER :'||l_TASK_NUMBER, x_Log_Level=> 3);
1805 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_PM_TASK_REFERENCE :'||l_PM_TASK_REFERENCE, x_Log_Level=> 3);
1806 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_PERCENT_COMPLETE :'||l_PERCENT_COMPLETE, x_Log_Level=> 3);
1807 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_DESCRIPTION :'||l_DESCRIPTION, x_Log_Level=> 3);
1808 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_OBJECT_ID :'||l_OBJECT_ID, x_Log_Level=> 3);
1809 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_OBJECT_VERSION_ID :'||l_OBJECT_VERSION_ID, x_Log_Level=> 3);
1810 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_OBJECT_TYPE :'||l_OBJECT_TYPE, x_Log_Level=> 3);
1811 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_PROGRESS_STATUS_CODE :'||l_PROGRESS_STATUS_CODE, x_Log_Level=> 3);
1812 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_PROGRESS_COMMENT :'||l_PROGRESS_COMMENT, x_Log_Level=> 3);
1813 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_ACTUAL_START_DATE :'||l_ACTUAL_START_DATE, x_Log_Level=> 3);
1814 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_ACTUAL_FINISH_DATE :'||l_ACTUAL_FINISH_DATE, x_Log_Level=> 3);
1815 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_ESTIMATED_START_DATE :'||l_ESTIMATED_START_DATE, x_Log_Level=> 3);
1816 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_ESTIMATED_FINISH_DATE :'||l_ESTIMATED_FINISH_DATE, x_Log_Level=> 3);
1817 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_SCHEDULED_START_DATE :'||l_SCHEDULED_START_DATE, x_Log_Level=> 3);
1818 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_SCHEDULED_FINISH_DATE :'||l_SCHEDULED_FINISH_DATE, x_Log_Level=> 3);
1819 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_TASK_STATUS :'||l_TASK_STATUS, x_Log_Level=> 3);
1820 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_ACTUAL_WORK_QUANTITY :'||l_ACTUAL_WORK_QUANTITY, x_Log_Level=> 3);
1821 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_ETC_COST :'||l_ETC_COST, x_Log_Level=> 3);
1822 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_PM_DELIVERABLE_REFERENCE :'||l_PM_DELIVERABLE_REFERENCE, x_Log_Level=> 3);
1823 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_PM_TASK_ASSGN_REFERENCE :'||l_PM_TASK_ASSGN_REFERENCE, x_Log_Level=> 3);
1824 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_ACTUAL_COST_TO_DATE :'||l_ACTUAL_COST_TO_DATE, x_Log_Level=> 3);
1825 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_ACTUAL_EFFORT_TO_DATE :'||l_ACTUAL_EFFORT_TO_DATE, x_Log_Level=> 3);
1826 END IF;
1827
1828 -- Bug 3673618 : Added this condition to do not process blank records.
1829 IF(((l_TASK_ID is null) or (l_TASK_ID =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
1830 and ((l_PM_TASK_REFERENCE is null) or (l_PM_TASK_REFERENCE =PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1831 and ((l_OBJECT_ID is null) or (l_OBJECT_ID =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
1832 and ((l_PM_DELIVERABLE_REFERENCE is null) or (l_PM_DELIVERABLE_REFERENCE =PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1833 and ((l_PM_TASK_ASSGN_REFERENCE is null) or (l_PM_TASK_ASSGN_REFERENCE =PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1834 )
1835 THEN
1836 return;
1837 END IF;
1838 -- Bug 3673618 : Moved assignment to global table later at the code.
1839
1840 G_TASK_PROGRESS_tbl_count := G_TASK_PROGRESS_tbl_count + 1;
1841 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_ID := l_TASK_ID;
1842 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_ID := P_TASK_ID(i);
1843
1844 --IF(P_TASK_NAME(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1845 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NAME := substr(P_TASK_NAME(i), 1, 20);
1846 --ELSE
1847 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NAME := P_TASK_NAME(i);
1848 --END IF;
1849
1850 IF(l_TASK_NAME <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1851 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NAME := substrb(l_TASK_NAME, 1, 20); --5458363
1852 ELSE
1853 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NAME := l_TASK_NAME;
1854 END IF;
1855
1856 --IF(P_TASK_NUMBER(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1857 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NUMBER := substr(P_TASK_NUMBER(i), 1, 25);
1858 --ELSE
1859 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NUMBER := P_TASK_NUMBER(i);
1860 --END IF;
1861
1862 IF(l_TASK_NUMBER <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1863 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NUMBER := substrb(l_TASK_NUMBER, 1, 25); --5458363
1864 ELSE
1865 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NUMBER := l_TASK_NUMBER;
1866 END IF;
1867
1868 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_TASK_REFERENCE := l_PM_TASK_REFERENCE;
1869 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_TASK_REFERENCE := p_PM_TASK_REFERENCE(i);
1870
1871 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PERCENT_COMPLETE := l_PERCENT_COMPLETE;
1872 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PERCENT_COMPLETE := p_PERCENT_COMPLETE(i);
1873
1874 --IF(p_DESCRIPTION(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1875 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).DESCRIPTION := substr(p_DESCRIPTION(i), 1, 250);
1876 --ELSE
1877 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).DESCRIPTION := p_DESCRIPTION(i);
1878 --END IF;
1879
1880 IF(l_DESCRIPTION <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1881 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).DESCRIPTION := substrb(l_DESCRIPTION, 1, 250); --5458363
1882 ELSE
1883 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).DESCRIPTION := l_DESCRIPTION;
1884 END IF;
1885
1886 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_ID := l_OBJECT_ID;
1887 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_ID := p_OBJECT_ID(i);
1888
1889 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_VERSION_ID := p_OBJECT_VERSION_ID(i);
1890 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_VERSION_ID := l_OBJECT_VERSION_ID;
1891
1892 --IF(p_OBJECT_TYPE(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1893 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE := substr(p_OBJECT_TYPE(i), 1, 30);
1894 --ELSE
1895 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE := p_OBJECT_TYPE(i);
1896 --END IF;
1897
1898 IF(l_OBJECT_TYPE <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1899 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE := substrb(l_OBJECT_TYPE, 1, 30); --5458363
1900 ELSE
1901 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE := l_OBJECT_TYPE;
1902 END IF;
1903
1904 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_STATUS_CODE := p_PROGRESS_STATUS_CODE(i);
1905 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_COMMENT := p_PROGRESS_COMMENT(i);
1906 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_START_DATE := p_ACTUAL_START_DATE(i);
1907 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_FINISH_DATE := p_ACTUAL_FINISH_DATE(i);
1908 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ESTIMATED_START_DATE := p_ESTIMATED_START_DATE(i);
1909 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ESTIMATED_FINISH_DATE := p_ESTIMATED_FINISH_DATE(i);
1910 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).SCHEDULED_START_DATE := p_SCHEDULED_START_DATE(i);
1911 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).SCHEDULED_FINISH_DATE := p_SCHEDULED_FINISH_DATE(i);
1912 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_STATUS := p_TASK_STATUS(i);
1913 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).EST_REMAINING_EFFORT := p_EST_REMAINING_EFFORT(i);
1914 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_WORK_QUANTITY := p_ACTUAL_WORK_QUANTITY(i);
1915
1916 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_STATUS_CODE := l_PROGRESS_STATUS_CODE;
1917 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_COMMENT := l_PROGRESS_COMMENT;
1918 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_START_DATE := l_ACTUAL_START_DATE;
1919 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_FINISH_DATE := l_ACTUAL_FINISH_DATE;
1920 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ESTIMATED_START_DATE := l_ESTIMATED_START_DATE;
1921 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ESTIMATED_FINISH_DATE := l_ESTIMATED_FINISH_DATE;
1922 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).SCHEDULED_START_DATE := l_SCHEDULED_START_DATE;
1923 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).SCHEDULED_FINISH_DATE := l_SCHEDULED_FINISH_DATE;
1924 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_STATUS := l_TASK_STATUS;
1925 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).EST_REMAINING_EFFORT := l_EST_REMAINING_EFFORT;
1926 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_WORK_QUANTITY := l_ACTUAL_WORK_QUANTITY;
1927 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ETC_COST := l_ETC_COST;
1928
1929 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_DELIVERABLE_REFERENCE := p_PM_DELIVERABLE_REFERENCE(i);
1930 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_TASK_ASSGN_REFERENCE := p_PM_TASK_ASSGN_REFERENCE(i);
1931 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_COST_TO_DATE := p_ACTUAL_COST_TO_DATE(i);
1932 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_EFFORT_TO_DATE := p_ACTUAL_EFFORT_TO_DATE(i);
1933
1934 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_DELIVERABLE_REFERENCE := l_PM_DELIVERABLE_REFERENCE;
1935 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_TASK_ASSGN_REFERENCE := l_PM_TASK_ASSGN_REFERENCE;
1936 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_COST_TO_DATE := l_ACTUAL_COST_TO_DATE;
1937 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_EFFORT_TO_DATE := l_ACTUAL_EFFORT_TO_DATE;
1938
1939 IF G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE = 'PA_TASKS'
1940 THEN
1941 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).LOWEST_LEVEL_TASK := PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(p_task_version_id => l_OBJECT_VERSION_ID);
1942 ELSE
1943 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).LOWEST_LEVEL_TASK := 'N';
1944 END IF;
1945
1946 --Bug 3606627 : Assign new variables End
1947
1948 --Bug 3606627
1949 -- This code to determine l_latest_as_of_date should be there in pa_status_pub.update_progress
1950 -- as here we may not be having object_id. We may need to derive it from ref fields.
1951 /* l_latest_as_of_date := PA_PROGRESS_UTILS.GET_LATEST_AS_OF_DATE(p_task_id => null
1952 , p_project_id => G_PROJECT_ID
1953 , p_object_id => G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_ID
1954 , p_object_type => G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE
1955 , p_structure_type => G_STRUCTURE_TYPE
1956 );
1957
1958 IF l_latest_as_of_date is NOT NULL THEN
1959 IF P_AS_OF_DATE >= l_latest_as_of_date THEN
1960 l_progress_mode := 'FUTURE';
1961 ELSE
1962 -- IF (G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE = 'PA_ASSIGNMENTS' OR
1963 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE = 'PA_DELIVERABLES')
1964 -- THEN
1965 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_PROG_WRONG_OBJ_TYPE',
1966 -- 'OBJECT_ID', G_TASK_PROGRESS_in_tbl(i).OBJECT_ID);
1967 --
1968 -- END IF;
1969 l_progress_mode := 'BACKDATED';
1970 END IF;
1971 ELSE
1972 l_progress_mode := 'FUTURE';
1973 END IF;*/
1974
1975 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).LATEST_AS_OF_DATE := l_latest_as_of_date;
1976 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_MODE := l_progress_mode;
1977
1978 END LOOP;
1979
1980 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1981 FND_MSG_PUB.Count_And_Get
1982 ( p_count => p_msg_count
1983 ,p_data => p_msg_data
1984 );
1985 END IF;
1986 EXCEPTION
1987 WHEN FND_API.G_EXC_ERROR THEN
1988 p_return_status := FND_API.G_RET_STS_ERROR;
1989 FND_MSG_PUB.Count_And_Get
1990 ( p_count => p_msg_count
1991 ,p_data => p_msg_data
1992 );
1993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1994 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1995 FND_MSG_PUB.Count_And_Get
1996 ( p_count => p_msg_count
1997 ,p_data => p_msg_data
1998 );
1999 WHEN OTHERS THEN
2000 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2001 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2002 FND_MSG_PUB.Add_Exc_Msg
2003 ( G_PKG_NAME
2004 , l_api_name
2005 );
2006 END IF;
2007 FND_MSG_PUB.Count_And_Get
2008 ( p_count => p_msg_count
2009 ,p_data => p_msg_data
2010 );
2011 END Load_Task_Progress;
2012
2013 PROCEDURE Execute_Update_Task_Progress
2014 ( p_api_version_number IN NUMBER
2015 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2016 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2017 , p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2018 , p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2019 , p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2020 )
2021 IS
2022
2023 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
2024 l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE_UPDATE_TASK_PROGRESS';
2025 l_return_status VARCHAR2(1):= 'S';
2026 l_msg_count INTEGER;
2027 l_msg_data VARCHAR2(2000);
2028 l_data VARCHAR2(2000);
2029 l_msg_index_out INTEGER;
2030 i INTEGER;
2031 l_application_short_name VARCHAR2(50) :='PA';
2032
2033 l_TASK_ID NUMBER;
2034 l_PM_TASK_REFERENCE VARCHAR2(150);
2035 l_PERCENT_COMPLETE NUMBER;
2036 l_DESCRIPTION VARCHAR2(250);
2037 l_OBJECT_ID NUMBER;
2038 l_OBJECT_VERSION_ID NUMBER;
2039 l_OBJECT_TYPE VARCHAR2(30);
2040 l_PROGRESS_STATUS_CODE VARCHAR2(150);
2041 l_PROGRESS_COMMENT VARCHAR2(4000);
2042 l_ACTUAL_START_DATE Date;
2043 l_ACTUAL_FINISH_DATE Date;
2044 l_ESTIMATED_START_DATE Date;
2045 l_ESTIMATED_FINISH_DATE Date;
2046 l_SCHEDULED_START_DATE Date;
2047 l_SCHEDULED_FINISH_DATE Date;
2048 l_TASK_STATUS VARCHAR2(150);
2049 l_EST_REMAINING_EFFORT NUMBER;
2050 l_ACTUAL_WORK_QUANTITY NUMBER;
2051
2052 l_project_id_out NUMBER;
2053 l_structure_id NUMBER;
2054 l_structure_version_id NUMBER;
2055 l_project_validation_flag VARCHAR2(1);
2056 l_rollup_flag VARCHAR2(1);
2057 l_bulk_load_flag VARCHAR2(1);
2058 l_progress_mode varchar2(10);
2059 l_latest_as_of_date Date;
2060 l_lowest_level_task VARCHAR2(1);
2061 -- Bug 3606627 : Added following 6 parameters
2062 l_etc_cost NUMBER;
2063 l_PM_DELIVERABLE_REFERENCE VARCHAR2(150);
2064 l_PM_TASK_ASSGN_REFERENCE VARCHAR2(150);
2065 l_ACTUAL_COST_TO_DATE NUMBER;
2066 l_ACTUAL_EFFORT_TO_DATE NUMBER;
2067 l_populate_pji_tables VARCHAR2(1) :='Y';
2068 l_rollup_table PA_SCHEDULE_OBJECTS_PVT.PA_SCHEDULE_OBJECTS_TBL_TYPE;
2069 l_max_as_of_date Date ; -- Bug 6917961
2070
2071 l_rollup varchar2(1); --bug 6717386
2072
2073 -- Bug 3606627 : Added curosrs c_get_structure_information, c_get_task_weight_method
2074 CURSOR c_get_structure_information(c_project_id NUMBER, c_structure_type VARCHAR2) IS
2075 select ppevs.proj_element_id, ppevs.element_version_id
2076 from pa_proj_structure_types ppst,
2077 pa_structure_types pst,
2078 pa_proj_elem_ver_structure ppevs
2079 where ppevs.project_id = c_project_id
2080 and ppevs.proj_element_id = ppst.proj_element_id
2081 and ppevs.status_code = 'STRUCTURE_PUBLISHED'
2082 and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'
2083 and ppst.structure_type_id = pst.structure_type_id
2084 and pst.structure_type_class_code = c_structure_type;
2085
2086 CURSOR c_get_task_weight_method(c_project_id NUMBER, c_structure_type VARCHAR2)
2087 IS
2088 SELECT task_weight_basis_code
2089 FROM pa_proj_progress_attr
2090 WHERE project_id = c_project_id
2091 AND structure_type = c_structure_type;
2092
2093 l_rollup_method pa_proj_progress_attr.task_weight_basis_code%TYPE; -- Bug 3606627
2094 l_wp_rollup_method pa_proj_progress_attr.task_weight_basis_code%TYPE; -- Bug 3606627
2095 l_fin_rollup_method pa_proj_progress_attr.task_weight_basis_code%TYPE; -- Bug 3606627
2096
2097 --maansari6/28 bug 3673618
2098 l_progress_updated_flag VARCHAR2(1) := 'N';
2099 l_raise_exception VARCHAR2(1) := 'N';
2100 l_baselined_str_ver_id NUMBER;
2101
2102 -- rtarway, BUG 3964278
2103 l_is_progress_status_null varchar2(1) := 'Y';
2104 G1_DEBUG_MODE VARCHAR2(1);
2105
2106 -- Bug 4186007 Begin
2107 l_msg_code VARCHAR2(32);
2108 l_base_struct_ver_id NUMBER;
2109 -- Bug 4186007 End
2110
2111 -- Bug 3994165 : Added variables below
2112 l_structure_sharing_code pa_projects_all.structure_sharing_code%TYPE;
2113 l_sharing_enabled varchar2(1) ;
2114 l_split_workplan varchar2(1) ;
2115 l_project_ids SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type() ;
2116 l_struture_version_ids SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type() ;
2117 l_proj_thru_dates_tbl SYSTEM.pa_date_tbl_type:= SYSTEM.pa_date_tbl_type() ;
2118
2119 BEGIN
2120
2121 IF FND_API.to_boolean(p_init_msg_list)
2122 THEN
2123 FND_MSG_PUB.initialize;
2124 END IF;
2125 g1_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2126 savepoint Execute_Update_Task_Progress;
2127
2128
2129 p_return_status := FND_API.G_RET_STS_SUCCESS;
2130 -- Bug 3606627 : Added call of PA_STATUS_PUB.Project_Level_Validations
2131 PA_STATUS_PUB.Project_Level_Validations
2132 (p_api_version_number => p_api_version_number
2133 ,p_msg_count => p_msg_count
2134 ,p_msg_data => p_msg_data
2135 ,p_return_status => l_return_status
2136 , p_project_id => G_PROJECT_ID
2137 , p_pm_project_reference => G_pm_project_reference
2138 , p_project_id_out => l_project_id_out
2139 );
2140 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2141 p_return_status := l_return_status;
2142 RAISE FND_API.G_EXC_ERROR;
2143 END IF;
2144
2145 G_PROJECT_ID := l_project_id_out;
2146
2147
2148 IF(G_TASK_PROGRESS_tbl_count = 0) THEN
2149 FND_MESSAGE.SET_NAME('PA','PA_NO_TASK_PROGRESS_UPDATE');
2150 FND_MSG_PUB.add;
2151 p_return_status := FND_API.G_RET_STS_ERROR;
2152 RAISE FND_API.G_EXC_ERROR;
2153 -- Bug 3606627 : Changed the following if condition to >=1 from =1
2154 ELSIF(G_TASK_PROGRESS_tbl_count >= 1) THEN
2155 --G_bulk_load_flag := 'N'; Bug 3606627
2156 G_bulk_load_flag := 'Y'; -- Bug 3606627
2157 --l_project_id_out := G_PROJECT_ID; Bug 3606627
2158 -- Bug 3606627
2159 OPEN c_get_structure_information(l_project_id_out, g_structure_type);
2160 FETCH c_get_structure_information INTO l_structure_id, l_structure_version_id;
2161 CLOSE c_get_structure_information;
2162
2163 -- Bug 3627315 : Added code to validate date against the project structure
2164 -- it won't be validated for each object
2165
2166 -- it has to be validated for each task for backdate and correct progress flows
2167 /* IF PA_PROGRESS_UTILS.CHECK_VALID_AS_OF_DATE( TRUNC(g_as_of_date), l_project_id_out, l_structure_id, 'PA_STRUCTURES', l_structure_id --Bug 3764224 ) = 'N'
2168 THEN
2169 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2170 ,p_msg_name => 'PA_TP_INV_AOD2');
2171 p_return_status := FND_API.G_RET_STS_ERROR;
2172 RAISE FND_API.G_EXC_ERROR;
2173 END IF; */
2174
2175 l_baselined_str_ver_id := PA_PROJECT_STRUCTURE_UTILS.Get_Baseline_Struct_Ver(l_project_id_out); --maansari6/28 bug 3673618
2176
2177 --maansari7/6 bug 3742356
2178 if l_baselined_str_ver_id = -1
2179 then
2180 l_baselined_str_ver_id := l_structure_version_id;
2181 end if;
2182
2183 IF l_structure_version_id IS NOT NULL
2184 AND l_baselined_str_ver_id IS NOT NULL THEN --maansari6/28 bug 3673618
2185 PA_PROGRESS_PUB.populate_pji_tab_for_plan(
2186 p_api_version => p_api_version_number
2187 ,p_init_msg_list => p_init_msg_list
2188 ,p_commit => FND_API.G_FALSE --Bug 3754134
2189 ,p_calling_module => 'AMG'
2190 ,p_project_id => l_project_id_out
2191 ,p_structure_version_id => l_structure_version_id
2192 ,p_baselined_str_ver_id => l_baselined_str_ver_id --maansari6/28 bug 3673618
2193 ,x_return_status => l_return_status
2194 ,x_msg_count => l_msg_count
2195 ,x_msg_data => l_msg_data
2196 );
2197
2198 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2199 RAISE FND_API.G_EXC_ERROR;
2200 END IF;
2201 END IF;
2202 g_task_version_id_tbl.delete; -- Bug 4218507
2203 i := G_TASK_PROGRESS_in_tbl.first;
2204 WHILE i IS NOT NULL LOOP
2205 l_TASK_ID := G_TASK_PROGRESS_in_tbl(i).TASK_ID;
2206 l_PM_TASK_REFERENCE := G_TASK_PROGRESS_in_tbl(i).PM_TASK_REFERENCE;
2207 l_PERCENT_COMPLETE := G_TASK_PROGRESS_in_tbl(i).PERCENT_COMPLETE;
2208 l_DESCRIPTION := G_TASK_PROGRESS_in_tbl(i).DESCRIPTION;
2209 l_OBJECT_ID := G_TASK_PROGRESS_in_tbl(i).OBJECT_ID;
2210 l_OBJECT_VERSION_ID := G_TASK_PROGRESS_in_tbl(i).OBJECT_VERSION_ID;
2211 l_OBJECT_TYPE := G_TASK_PROGRESS_in_tbl(i).OBJECT_TYPE;
2212 l_PROGRESS_STATUS_CODE := G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE;
2213 l_PROGRESS_COMMENT := G_TASK_PROGRESS_in_tbl(i).PROGRESS_COMMENT;
2214 l_ACTUAL_START_DATE := G_TASK_PROGRESS_in_tbl(i).ACTUAL_START_DATE;
2215 l_ACTUAL_FINISH_DATE := G_TASK_PROGRESS_in_tbl(i).ACTUAL_FINISH_DATE;
2216 l_ESTIMATED_START_DATE := G_TASK_PROGRESS_in_tbl(i).ESTIMATED_START_DATE;
2217 l_ESTIMATED_FINISH_DATE := G_TASK_PROGRESS_in_tbl(i).ESTIMATED_FINISH_DATE;
2218 l_SCHEDULED_START_DATE := G_TASK_PROGRESS_in_tbl(i).SCHEDULED_START_DATE;
2219 l_SCHEDULED_FINISH_DATE := G_TASK_PROGRESS_in_tbl(i).SCHEDULED_FINISH_DATE;
2220 l_TASK_STATUS := G_TASK_PROGRESS_in_tbl(i).TASK_STATUS;
2221 l_EST_REMAINING_EFFORT := G_TASK_PROGRESS_in_tbl(i).EST_REMAINING_EFFORT;
2222 l_ACTUAL_WORK_QUANTITY := G_TASK_PROGRESS_in_tbl(i).ACTUAL_WORK_QUANTITY;
2223 -- Bug 3606627 : Added following 6 new parms assignment
2224 l_etc_cost := G_TASK_PROGRESS_in_tbl(i).etc_cost;
2225 l_PM_DELIVERABLE_REFERENCE := G_TASK_PROGRESS_in_tbl(i).PM_DELIVERABLE_REFERENCE;
2226 l_PM_TASK_ASSGN_REFERENCE := G_TASK_PROGRESS_in_tbl(i).PM_TASK_ASSGN_REFERENCE;
2227 l_ACTUAL_COST_TO_DATE := G_TASK_PROGRESS_in_tbl(i).ACTUAL_COST_TO_DATE;
2228 l_ACTUAL_EFFORT_TO_DATE := G_TASK_PROGRESS_in_tbl(i).ACTUAL_EFFORT_TO_DATE;
2229
2230
2231
2232 -- IF i = G_TASK_PROGRESS_in_tbl.first THEN
2233 -- l_populate_pji_tables := 'Y';
2234 -- ELSE
2235 -- l_populate_pji_tables := 'N';
2236 -- END IF;
2237
2238 -- Start of changes for bug 6717386
2239
2240 IF G_TASK_PROGRESS_in_tbl(i).OBJECT_TYPE = 'PA_ASSIGNMENTS'
2241 THEN
2242 begin
2243 select 'Y' into l_rollup from dual
2244 where exists (select 1 from pa_percent_completes
2245 where task_id = l_TASK_ID
2246 AND PROJECT_ID = l_project_id_out
2247 and object_type = 'PA_TASKS'
2248 and PROGRESS_STATUS_CODE is not null);
2249 G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE := 'PROGRESS_STAT_ON_TRACK';
2250 l_PROGRESS_STATUS_CODE := G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE; -- Bug 3754134
2251 exception WHEN NO_DATA_FOUND THEN
2252 null;
2253
2254 end;
2255
2256 END IF;
2257
2258 -- End of changes for bug 6717386
2259 /* Commented for bug 6717386
2260 IF G_TASK_PROGRESS_in_tbl(i).OBJECT_TYPE = 'PA_ASSIGNMENTS'
2261 THEN
2262 G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE := 'PROGRESS_STAT_ON_TRACK';
2263 l_PROGRESS_STATUS_CODE := G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE; -- Bug 3754134
2264 END IF; */
2265
2266 --Commented by rtarway for BUG 3901982
2267 --maansari6/28 bug 3673618
2268 /*IF G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE IS NOT NULL AND
2269 G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2270 THEN*/
2271 --Added by rtarway for BUG 3901982
2272 IF (
2273 ( G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE IS NOT NULL AND
2274 G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2275 AND G_STRUCTURE_TYPE = 'WORKPLAN'
2276 ) OR
2277 ( l_PERCENT_COMPLETE IS NOT NULL AND
2278 l_PERCENT_COMPLETE <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND G_STRUCTURE_TYPE = 'FINANCIAL'
2279 )
2280 )
2281 THEN
2282 --rtarway, 3964278
2283 if G_STRUCTURE_TYPE = 'WORKPLAN' then
2284 l_is_progress_status_null := 'N';
2285 end if;
2286
2287 l_progress_updated_flag := 'Y'; --maansari6/28 bug 3673618
2288
2289
2290 PA_STATUS_PUB.UPDATE_PROGRESS
2291 (p_api_version_number => p_api_version_number
2292 ,p_msg_count => l_msg_count
2293 ,p_msg_data => l_msg_data
2294 ,p_commit => FND_API.G_FALSE -- Bug 3754134 Added this
2295 ,p_return_status => l_return_status
2296 , p_project_id => l_project_id_out
2297 , p_pm_project_reference => g_pm_project_reference
2298 , p_task_id => l_task_id
2299 , p_pm_task_reference => l_pm_task_reference
2300 , p_as_of_date => g_as_of_date
2301 , p_percent_complete => l_percent_complete
2302 , p_pm_product_code => g_pm_product_code
2303 , p_description => l_description
2304 , p_object_id => l_object_id
2305 , p_object_version_id => l_object_version_id
2306 , p_object_type => l_object_type
2307 , p_progress_status_code => l_progress_status_code
2308 , p_progress_comment => l_progress_comment
2309 , p_actual_start_date => l_actual_start_date
2310 , p_actual_finish_date => l_actual_finish_date
2311 , p_estimated_start_date => l_estimated_start_date
2312 , p_estimated_finish_date => l_estimated_finish_date
2313 , p_scheduled_start_date => l_scheduled_start_date
2314 , p_scheduled_finish_date => l_scheduled_finish_date
2315 , p_task_status => l_task_status
2316 , p_structure_type => g_structure_type
2317 , p_est_remaining_effort => l_est_remaining_effort
2318 , p_actual_work_quantity => l_actual_work_quantity
2319 , p_etc_cost => l_etc_cost -- bug 3606627
2320 , p_pm_deliverable_reference => l_pm_deliverable_reference -- bug 3606627
2321 , p_pm_task_assgn_reference => l_pm_task_assgn_reference -- bug 3606627
2322 , p_actual_cost_to_date => l_actual_cost_to_date -- bug 3606627
2323 , p_actual_effort_to_date => l_actual_effort_to_date -- bug 3606627
2324 , p_populate_pji_tables => 'N' -- bug 3606627
2325 , p_rollup_entire_wbs => 'Y' -- bug 3606627
2326 );
2327
2328 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2329 p_return_status := l_return_status;
2330
2331 FND_MSG_PUB.get (
2332 p_msg_index => l_msg_count,
2333 p_encoded => FND_API.G_FALSE,
2334 p_data => l_data,
2335 p_msg_index_out => l_msg_index_out );
2336
2337 IF l_data IS NOT NULL THEN
2338 FND_MSG_PUB.DELETE_MSG(p_msg_index => l_msg_count);
2339
2340 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_TASK_NAME_NUM_ERR',
2341 'ROWNUM', 'ROW# '||i,--Added by rtarway for bug 4293075
2342 'TASK_NAME', G_TASK_PROGRESS_in_tbl(i).TASK_NAME,
2343 'TASK_NUMBER', G_TASK_PROGRESS_in_tbl(i).TASK_NUMBER,
2344 'MESSAGE', l_data);
2345 END IF;
2346 l_raise_exception := l_return_status; --maansari6/28 bug 3673618
2347
2348 END IF;
2349 END IF;
2350 i := G_TASK_PROGRESS_in_tbl.next(i);
2351 END LOOP;
2352
2353 -- rtarway, BUG 3964278
2354 IF G_STRUCTURE_TYPE = 'WORKPLAN' and l_is_progress_status_null = 'Y'
2355 THEN
2356 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2357 ,p_msg_name => 'PA_TP_STATUS_NOT_DEFINED');
2358 p_return_status := FND_API.G_RET_STS_ERROR;
2359 RAISE FND_API.G_EXC_ERROR;
2360 END IF;
2361
2362 --maansari6/28 bug 3673618
2363 IF l_progress_updated_flag = 'N'
2364 THEN
2365 return;
2366 END IF;
2367
2368 IF (l_raise_exception = 'E' )
2369 THEN
2370 RAISE FND_API.G_EXC_ERROR;
2371 ELSIF l_raise_exception = 'U'
2372 THEN
2373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2374 END IF;
2375 --maansari6/28 bug 3673618
2376
2377
2378 -- Bug 3606627 Begin
2379 OPEN c_get_task_weight_method(l_project_id_out, g_structure_type);
2380 FETCH c_get_task_weight_method INTO l_rollup_method;
2381 CLOSE c_get_task_weight_method;
2382
2383 IF g_structure_type = 'WORKPLAN' THEN
2384 l_wp_rollup_method := l_rollup_method;
2385 ELSE
2386 l_fin_rollup_method := l_rollup_method;
2387 END IF;
2388 -- Bug 3994165 : Moved call of MAINTAIN_ACTUAL_AMT_WRP here
2389
2390 l_structure_sharing_code := PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(l_project_id_out);
2391 l_sharing_Enabled := PA_PROJECT_STRUCTURE_UTILS.check_sharing_enabled(l_project_id_out);
2392 IF (l_sharing_Enabled = 'N' OR (l_sharing_Enabled = 'Y' AND l_structure_sharing_code <> 'SHARE_FULL')) AND g_structure_type = 'WORKPLAN' THEN
2393 l_split_workplan := 'Y';
2394 ELSE
2395 l_split_workplan := 'N';
2396 END IF;
2397
2398 IF l_split_workplan = 'Y' AND l_structure_version_id IS NOT NULL THEN
2399 BEGIN
2400 Pa_Task_Pub1.G_CALL_PJI_ROLLUP := 'N';
2401 -- This flag is set so that plan_update from MAINTAIN_ACTUAL_AMT_WRP
2402 -- is not called. Actually it gets called but PJI code does not do anything.
2403
2404 l_project_ids.extend(1);
2405 l_project_ids(1) := l_project_id_out;
2406 l_struture_version_ids.extend(1);
2407 l_struture_version_ids(1) := l_structure_version_id;
2408 l_proj_thru_dates_tbl.extend(1);
2409 l_proj_thru_dates_tbl(1) := trunc(g_as_of_date); -- 5294838
2410 PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_WRP
2411 (P_PROJECT_ID_TAB => l_project_ids,
2412 P_WP_STR_VERSION_ID_TAB => l_struture_version_ids,
2413 P_ACTUALS_THRU_DATE => l_proj_thru_dates_tbl,
2414 P_CALLING_CONTEXT => 'WP_PROGRESS',
2415 P_EXTRACTION_TYPE => 'INCREMENTAL',
2416 X_RETURN_STATUS => l_return_status,
2417 X_MSG_COUNT => l_msg_count,
2418 X_MSG_DATA => l_msg_data
2419 );
2420
2421
2422 Pa_Task_Pub1.G_CALL_PJI_ROLLUP := null ;
2423
2424 delete from PA_PROG_ACT_BY_PERIOD_TEMP where project_id = l_project_id_out
2425 AND structure_version_id = l_structure_version_id;
2426 EXCEPTION
2427 WHEN OTHERS THEN
2428 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STATUS_PUB',
2429 p_procedure_name => 'execute_update_progress',
2430 p_error_text => SUBSTRB('PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_WRP:'||SQLERRM,1,120));
2431 RAISE FND_API.G_EXC_ERROR;
2432 END;
2433 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2434 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2435 p_msg_name => l_msg_data);
2436 p_return_status := 'E';
2437 RAISE FND_API.G_EXC_ERROR;
2438 END IF;
2439 END IF;
2440 -- Bug 4186007 Begin
2441 BEGIN
2442 PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (x_msg_code => l_msg_code,
2443 x_return_status => l_return_status);
2444 EXCEPTION
2445 WHEN OTHERS THEN
2446 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STATUS_PUB',
2447 p_procedure_name => 'EXECUTE_UPDATE_TASK_PROGRESS',
2448 p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE Failed:'||SQLERRM,1,120));
2449 RAISE FND_API.G_EXC_ERROR;
2450 END;
2451
2452 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2453 p_return_status := l_return_status;
2454 RAISE FND_API.G_EXC_ERROR;
2455 END IF;
2456
2457
2458 l_base_struct_ver_id := pa_project_structure_utils.get_baseline_struct_ver(l_project_id_out);
2459
2460 IF (l_base_struct_ver_id = -1) THEN
2461 l_base_struct_ver_id := l_structure_version_id;
2462 END IF;
2463
2464 -- 4392189 : Changed call of populate_workplan_data to populate_pji_tab_for_plan
2465 PA_PROGRESS_PUB.POPULATE_PJI_TAB_FOR_PLAN(
2466 p_calling_module => 'AMG'
2467 ,p_project_id => l_project_id_out
2468 ,p_structure_version_id => l_structure_version_id
2469 ,p_baselined_str_ver_id => l_base_struct_ver_id
2470 ,p_program_rollup_flag => 'Y'
2471 ,p_calling_context => 'SUMMARIZE'
2472 ,p_as_of_date => trunc(g_as_of_date) -- 5294838
2473 ,x_return_status => l_return_status
2474 ,x_msg_count => l_msg_count
2475 ,x_msg_data => l_msg_data
2476 );
2477 /*
2478 BEGIN
2479 PJI_FM_XBS_ACCUM_UTILS.populate_workplan_data(
2480 p_project_id => l_project_id_out,
2481 p_struct_ver_id => l_structure_version_id,
2482 p_base_struct_ver_id => l_base_struct_ver_id,
2483 x_return_status => l_return_status,
2484 x_msg_code => l_msg_code
2485 );
2486 EXCEPTION
2487 WHEN OTHERS THEN
2488 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STATUS_PUB',
2489 p_procedure_name => 'EXECUTE_UPDATE_TASK_PROGRESS',
2490 p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_UTILS.populate_workplan_data: failed'||SQLERRM,1,120));
2491 RAISE FND_API.G_EXC_ERROR;
2492 END;
2493 */
2494
2495 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2496 p_return_status := l_return_status;
2497 RAISE FND_API.G_EXC_ERROR;
2498 END IF;
2499
2500 -- Bug 4186007 End
2501
2502
2503 PA_PROGRESS_PVT.ROLLUP_PROGRESS_PVT(
2504 p_init_msg_list => p_init_msg_list
2505 ,p_commit => FND_API.G_FALSE -- Bug 3754134 instead of passing p_commit passing fasle
2506 ,p_calling_module => 'AMG' --bug 3673618
2507 ,p_project_id => l_project_id_out
2508 ,p_structure_version_id => l_structure_version_id
2509 ,p_as_of_date => trunc(g_as_of_date) -- 5294838
2510 ,p_wp_rollup_method => l_wp_rollup_method
2511 ,p_fin_rollup_method => l_fin_rollup_method
2512 ,p_rollup_entire_wbs => 'Y'
2513 ,p_structure_type => g_structure_type
2514 ,p_task_version_id_tbl => g_task_version_id_tbl -- Bug 4218507
2515 ,x_return_status => l_return_status
2516 ,x_msg_count => l_msg_count
2517 ,x_msg_data => l_msg_data);
2518
2519 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2520 p_return_status := l_return_status;
2521 RAISE FND_API.G_EXC_ERROR;
2522 END IF;
2523
2524 PA_PROGRESS_PUB.ROLLUP_FUTURE_PROGRESS_PVT(
2525 p_project_id => l_project_id_out
2526 --,P_OBJECT_TYPE => l_object_type
2527 --,P_OBJECT_ID => l_task_id -- p_task_id 3603636
2528 --,p_object_version_id => l_task_version_id
2529 ,p_as_of_date => trunc(g_as_of_date) -- 5294838
2530 --,p_lowest_level_task => NVL( l_lowest_level_task, 'N' )
2531 ,p_calling_module => 'AMG'
2532 ,p_structure_type => g_structure_type
2533 ,p_structure_version_id => l_structure_version_id
2534 ,p_fin_rollup_method => l_fin_rollup_method
2535 ,p_wp_rollup_method => l_wp_rollup_method
2536 ,p_rollup_entire_wbs => 'Y'
2537 ,x_return_status => l_return_status
2538 ,x_msg_count => l_msg_count
2539 ,x_msg_data => l_msg_data
2540 );
2541
2542 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2543 p_return_status := l_return_status;
2544 RAISE FND_API.G_EXC_ERROR;
2545 END IF;
2546 -- Bug 3606627 End
2547 END IF; --(G_TASK_PROGRESS_tbl_count >= 1) THEN Bug 3606627 : Added Ebd IF here itself
2548 -- BUG 4080922 Adding Program Rollup Code : rtarway
2549 IF g_structure_type = 'WORKPLAN' AND l_structure_version_id IS NOT NULL
2550 THEN
2551 IF g1_debug_mode = 'Y' THEN
2552 pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_PROGRESS', x_Msg => 'Calling program_rollup_pvt', x_Log_Level=> 3);
2553 END IF;
2554
2555 pa_progress_pvt.program_rollup_pvt(
2556 p_init_msg_list => 'F'
2557 ,p_commit => 'F'
2558 ,p_calling_module => 'AMG'
2559 ,p_validate_only => 'F'
2560 ,p_project_id => l_project_id_out
2561 ,p_as_of_date => trunc(g_as_of_date) -- 5294838
2562 ,p_structure_type => g_structure_type
2563 ,p_structure_ver_id => l_structure_version_id
2564 ,x_return_status => l_return_status
2565 ,x_msg_count => l_msg_count
2566 ,x_msg_data => l_msg_data);
2567
2568 IF g1_debug_mode = 'Y' THEN
2569 pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_PROGRESS', x_Msg => 'After Calling program_rollup_pvt l_return_status='||l_return_status, x_Log_Level=> 3);
2570 END IF;
2571
2572 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2573 p_return_status := l_return_status;
2574 RAISE FND_API.G_EXC_ERROR;
2575 END IF;
2576
2577 END IF;
2578 -- End Add 4080922
2579
2580 -- Bug 3606627 : Commenthing the below complete code
2581 /* Bug 3606627
2582 ELSE
2583 G_bulk_load_flag := 'N';
2584 PA_STATUS_PUB.Project_Level_Validations
2585 (p_api_version_number => p_api_version_number
2586 ,p_msg_count => p_msg_count
2587 ,p_msg_data => p_msg_data
2588 ,p_return_status => l_return_status
2589 , p_project_id => G_PROJECT_ID
2590 , p_pm_project_reference => G_pm_project_reference
2591 , p_project_id_out => l_project_id_out
2592 );
2593 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2594 p_return_status := l_return_status;
2595 RAISE FND_API.G_EXC_ERROR;
2596 END IF;
2597 G_PROJECT_ID := l_project_id_out;
2598
2599 l_structure_version_id := PA_PROJECT_STRUCTURE_UTILS.get_latest_wp_version(l_project_id_out);
2600 */
2601 /*
2602 select STRUCTURE_TYPE into G_STRUCTURE_TYPE
2603 from PA_STRUCT_VERSIONS_LOV_AMG_V where STRUCTURE_VERSION_ID = l_structure_version_id;
2604 */
2605 /*Bug 3606627
2606 IF(G_STRUCTURE_TYPE = 'FINANCIAL') then
2607 IF(PA_PROJECT_STRUCTURE_UTILS.CHECK_SHARING_ENABLED(l_project_id_out) = 'Y') THEN
2608 l_rollup_flag := 'Y';
2609 G_STRUCTURE_TYPE := 'WORKPLAN';
2610 ELSE
2611 l_rollup_flag := 'N';
2612 END IF;
2613 else
2614 l_rollup_flag := 'Y';
2615 end if;
2616
2617
2618 PA_PROGRESS_PUB.INSERT_TASK_PROGRESSES(
2619 p_api_version => p_api_version_number,
2620 p_calling_module => 'AMG',
2621 p_project_id => l_project_id_out,
2622 p_pm_product_code => G_PM_PRODUCT_CODE,
2623 p_structure_version_id => l_structure_version_id,
2624 p_structure_type => G_STRUCTURE_TYPE,
2625 p_as_of_date => G_AS_OF_DATE,
2626 p_task_progress_list_table => G_TASK_PROGRESS_in_tbl,
2627 p_return_status => l_return_status,
2628 p_msg_count => p_msg_count,
2629 p_msg_data => p_msg_data
2630 );
2631 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2632 p_return_status := l_return_status;
2633 RAISE FND_API.G_EXC_ERROR;
2634 END IF;
2635
2636 IF(l_rollup_flag = 'Y') then
2637 PA_PROGRESS_PUB.ROLLUP_TASK_PROGRESSES(
2638 p_api_version => p_api_version_number,
2639 p_calling_module => 'AMG',
2640 p_progress_mode => l_progress_mode,
2641 p_project_id => l_project_id_out,
2642 p_structure_version_id => l_structure_version_id,
2643 p_as_of_date => G_AS_OF_DATE,
2644 p_rollup_table => l_rollup_table,
2645 p_return_status => l_return_status,
2646 p_msg_count => p_msg_count,
2647 p_msg_data => p_msg_data
2648 );
2649 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2650 p_return_status := l_return_status;
2651 RAISE FND_API.G_EXC_ERROR;
2652 END IF;
2653 */
2654
2655
2656 /* Commented out for Progress Management Changes. Bug # 3420093.
2657
2658 PA_PROGRESS_PUB.UPDATE_ROLLUP_PROGRESSES(
2659 p_api_version => p_api_version_number,
2660 p_calling_module => 'AMG',
2661 p_progress_mode => l_progress_mode,
2662 p_project_id => l_project_id_out,
2663 p_structure_version_id => l_structure_version_id,
2664 p_structure_type => G_STRUCTURE_TYPE,
2665 p_as_of_date => G_AS_OF_DATE,
2666 p_rollup_table => l_rollup_table,
2667 p_task_progress_list_table => G_TASK_PROGRESS_in_tbl,
2668 p_return_status => l_return_status,
2669 p_msg_count => p_msg_count,
2670 p_msg_data => p_msg_data
2671 );
2672 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2673 p_return_status := l_return_status;
2674 RAISE FND_API.G_EXC_ERROR;
2675 END IF;
2676
2677 Commented out for Progress Management Changes. Bug # 3420093. */
2678
2679 /* Progress Management Changes. Bug # 3420093. */
2680
2681 /*Bug 3606627
2682 pa_status_pub.update_task_progress_amg(
2683 p_api_version => p_api_version_number,
2684 p_calling_module => 'AMG',
2685 p_progress_mode => l_progress_mode,
2686 p_project_id => l_project_id_out,
2687 p_structure_version_id => l_structure_version_id,
2688 p_structure_type => G_STRUCTURE_TYPE,
2689 p_as_of_date => G_AS_OF_DATE,
2690 p_task_progress_list_table => G_TASK_PROGRESS_in_tbl,
2691 x_return_status => l_return_status,
2692 x_msg_count => p_msg_count,
2693 x_msg_data => p_msg_data
2694 );
2695 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2696 p_return_status := l_return_status;
2697 RAISE FND_API.G_EXC_ERROR;
2698 END IF;
2699 */
2700
2701 /* Progress Management Changes. Bug # 3420093. */
2702
2703 /*Bug 3606627
2704 i := G_TASK_PROGRESS_in_tbl.first;
2705
2706 WHILE i IS NOT NULL LOOP
2707 l_TASK_ID := G_TASK_PROGRESS_in_tbl(i).TASK_ID;
2708 l_PM_TASK_REFERENCE := G_TASK_PROGRESS_in_tbl(i).PM_TASK_REFERENCE;
2709 l_PERCENT_COMPLETE := G_TASK_PROGRESS_in_tbl(i).PERCENT_COMPLETE;
2710 l_DESCRIPTION := G_TASK_PROGRESS_in_tbl(i).DESCRIPTION;
2711 l_OBJECT_ID := G_TASK_PROGRESS_in_tbl(i).OBJECT_ID;
2712 l_OBJECT_VERSION_ID := G_TASK_PROGRESS_in_tbl(i).OBJECT_VERSION_ID;
2713 l_OBJECT_TYPE := G_TASK_PROGRESS_in_tbl(i).OBJECT_TYPE;
2714 l_progress_mode := G_TASK_PROGRESS_in_tbl(i).PROGRESS_MODE;
2715 */
2716 /*
2717 l_latest_as_of_date := PA_PROGRESS_UTILS.GET_LATEST_AS_OF_DATE(l_object_id);
2718 IF l_latest_as_of_date is NOT NULL THEN
2719 IF G_AS_OF_DATE >= l_latest_as_of_date THEN
2720 l_progress_mode := 'FUTURE';
2721 ELSE
2722 l_progress_mode := 'BACKDATED';
2723 END IF;
2724 ELSE
2725 l_progress_mode := 'FUTURE';
2726 END IF;
2727 */
2728
2729 /* Bug 3606627
2730 if l_progress_mode <> 'BACKDATED' then
2731 IF l_object_type = 'PA_TASKS'
2732 THEN
2733 l_lowest_level_task := PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(p_task_version_id => l_OBJECT_VERSION_ID);
2734 ELSE
2735 l_lowest_level_task := 'N';
2736 END IF;
2737
2738 PA_PROGRESS_PUB.ROLLUP_FUTURE_PROGRESS_PVT(
2739 p_project_id => l_project_id_out
2740 ,P_OBJECT_TYPE => L_OBJECT_TYPE
2741 ,P_OBJECT_ID => L_OBJECT_ID
2742 ,p_object_version_id => L_object_version_id
2743 ,p_as_of_date => G_AS_OF_DATE
2744 ,p_lowest_level_task => NVL( l_lowest_level_task, 'N' )
2745 ,x_return_status => l_return_status
2746 ,x_msg_count => l_msg_count
2747 ,x_msg_data => l_msg_data
2748 );
2749 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2750 p_return_status := l_return_status;
2751 RAISE FND_API.G_EXC_ERROR;
2752 END IF;
2753
2754 IF ( NVL( l_lowest_level_task, 'N' ) = 'Y' )
2755 THEN
2756 PA_TASK_PVT1.Update_Dates_To_All_Versions(
2757 p_project_id => l_project_id_out
2758 ,p_element_version_id => l_object_version_id
2759 ,x_return_status => l_return_status
2760 ,x_msg_count => l_msg_count
2761 ,x_msg_data => l_msg_data );
2762 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2763 THEN
2764 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2765 p_msg_name => l_msg_data
2766 );
2767 p_msg_data := l_msg_data;
2768 p_return_status := 'E';
2769 RAISE FND_API.G_EXC_ERROR;
2770 END IF;
2771 begin
2772 Select ppev1.proj_element_id
2773 into l_structure_id
2774 from pa_proj_element_versions ppev1, pa_proj_element_versions ppev2
2775 where ppev2.element_version_id = l_object_version_id
2776 and ppev2.project_id = ppev1.project_id
2777 and ppev2.parent_structure_version_id = ppev1.element_version_id;
2778 exception when others then
2779 l_structure_id := -999;
2780 end;
2781 IF ((PA_WORKPLAN_ATTR_UTILS.CHECK_AUTO_DATE_SYNC_ENABLED(l_structure_id) = 'Y')
2782 AND
2783 (PA_PROJECT_STRUCTURE_UTILS.CHECK_SHARING_ENABLED(l_project_id_out) = 'Y')) THEN
2784 --copy to transaction dates
2785 PA_PROJECT_DATES_PUB.COPY_PROJECT_DATES(
2786 p_validate_only => FND_API.G_FALSE
2787 ,p_project_id => l_project_id_out
2788 ,x_return_status => l_return_status
2789 ,x_msg_count => l_msg_count
2790 ,x_msg_data => l_msg_data);
2791 END IF;
2792 END IF;
2793 end if; ------ p_progress_mode <> backdated
2794 i := G_TASK_PROGRESS_in_tbl.next(i);
2795 END LOOP;
2796 end if; -------l_rollup_flag = 'Y'
2797 end if;
2798 */
2799
2800 /* bug 6917961 */
2801
2802 select nvl(max(as_of_date),sysdate) into l_max_as_of_date
2803 from pa_progress_rollup where project_id= l_project_id_out;
2804
2805
2806
2807 update pa_progress_rollup
2808 set COMPLETED_PERCENTAGE = null
2809 where project_id = l_project_id_out
2810 AND OBJECT_TYPE ='PA_TASKS'
2811 AND CURRENT_FLAG='Y'
2812 AND as_of_date=l_max_as_of_date
2813 and nvl(COMPLETED_PERCENTAGE,0) = nvl(eff_rollup_percent_comp, -1);
2814
2815 /* bug 6917961 */
2816
2817 IF (p_return_status = FND_API.G_RET_STS_SUCCESS )
2818 then
2819 IF FND_API.to_boolean(p_commit)
2820 THEN
2821 COMMIT;
2822 END IF;
2823 ELSE
2824 ROLLBACK TO Execute_Update_Task_Progress;
2825 FND_MSG_PUB.Count_And_Get
2826 ( p_count => p_msg_count
2827 ,p_data => p_msg_data
2828 );
2829 END IF;
2830 G_bulk_load_flag :='N';
2831 G_TASK_PROGRESS_in_tbl.delete;
2832 G_TASK_PROGRESS_tbl_count := 0;
2833 EXCEPTION
2834 WHEN FND_API.G_EXC_ERROR THEN
2835 p_return_status := FND_API.G_RET_STS_ERROR;
2836 ROLLBACK TO Execute_Update_Task_Progress;
2837 FND_MSG_PUB.Count_And_Get
2838 ( p_count => p_msg_count
2839 ,p_data => p_msg_data
2840 );
2841 G_bulk_load_flag :='N';
2842 G_TASK_PROGRESS_in_tbl.delete;
2843 G_TASK_PROGRESS_tbl_count := 0;
2844 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2845 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2846 ROLLBACK TO Execute_Update_Task_Progress;
2847 FND_MSG_PUB.Count_And_Get
2848 ( p_count => p_msg_count
2849 ,p_data => p_msg_data
2850 );
2851 G_bulk_load_flag :='N';
2852 G_TASK_PROGRESS_in_tbl.delete;
2853 G_TASK_PROGRESS_tbl_count := 0;
2854 WHEN OTHERS THEN
2855 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2856 ROLLBACK TO Execute_Update_Task_Progress;
2857 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2858 FND_MSG_PUB.Add_Exc_Msg
2859 ( G_PKG_NAME
2860 , l_api_name
2861 );
2862 END IF;
2863 FND_MSG_PUB.Count_And_Get
2864 ( p_count => p_msg_count
2865 ,p_data => p_msg_data
2866 );
2867 G_bulk_load_flag :='N';
2868 G_TASK_PROGRESS_in_tbl.delete;
2869 G_TASK_PROGRESS_tbl_count := 0;
2870 END Execute_Update_Task_Progress;
2871
2872
2873 PROCEDURE Project_Level_Validations
2874 (p_api_version_number IN NUMBER
2875 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2876 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2877 , p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2878 , p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2879 , p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2880 , p_project_id IN NUMBER
2881 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2882 , p_project_id_out OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2883 )
2884 IS
2885 l_api_name CONSTANT VARCHAR2(30) := 'Project_Level_Validations';
2886 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
2887 l_msg_count INTEGER;
2888 l_msg_data VARCHAR2(2000);
2889 l_return_status VARCHAR2(1):= 'S';
2890 l_project_id_out NUMBER := 0;
2891 l_function_allowed VARCHAR2(1);
2892 l_resp_id NUMBER := 0;
2893 l_pm_product_code pa_percent_completes.pm_product_code%TYPE := NULL;
2894 l_description pa_percent_completes.description%TYPE := NULL;
2895 l_as_of_date DATE;
2896 l_current_flag VARCHAR2(1);
2897 l_dummy VARCHAR2(1);
2898 l_module_name VARCHAR2(80);
2899 l_user_id NUMBER := 0;
2900 l_date_computed DATE;
2901
2902
2903 BEGIN
2904 IF FND_API.to_boolean(p_init_msg_list)
2905 THEN
2906 FND_MSG_PUB.initialize;
2907 END IF;
2908
2909 p_return_status := FND_API.G_RET_STS_SUCCESS;
2910
2911
2912 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
2913 p_api_version_number ,
2914 l_api_name,
2915 G_PKG_NAME)
2916
2917 THEN
2918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2919 END IF;
2920
2921 --Moved from below for project security changes. --Bug 2471668
2922
2923 PA_PROJECT_PVT.Convert_pm_projref_to_id
2924 ( p_pm_project_reference => p_pm_project_reference
2925 , p_pa_project_id => p_project_id
2926 , p_out_project_id => l_project_id_out
2927 , p_return_status => l_return_status
2928 );
2929
2930
2931 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2933 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2934 RAISE FND_API.G_EXC_ERROR;
2935 END IF;
2936 p_project_id_out := l_project_id_out;
2937
2938 --Bug 2471668
2939
2940 l_user_id := FND_GLOBAL.User_id;
2941 l_resp_id := FND_GLOBAL.Resp_id;
2942 --l_module_name := p_pm_product_code||'.'||'PA_PM_UPDATE_PROJ_PROGRESS';
2943
2944 pa_security.initialize (X_user_id => l_user_id,
2945 X_calling_module => l_module_name);
2946
2947 -- Actions performed using the APIs would be subject to
2948 -- function security. If the responsibility does not allow
2949 -- such functions to be executed, the API should not proceed further
2950 -- since the user does not have access to such functions
2951
2952 --Bug 2471668
2953 PA_INTERFACE_UTILS_PUB.g_project_id := l_project_id_out;
2954
2955 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
2956 (p_api_version_number => p_api_version_number,
2957 p_responsibility_id => l_resp_id,
2958 p_function_name => 'PA_PM_UPDATE_PROJ_PROGRESS',
2959 p_msg_count => l_msg_count,
2960 p_msg_data => l_msg_data,
2961 p_return_status => l_return_status,
2962 p_function_allowed => l_function_allowed );
2963
2964
2965 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2966 THEN
2967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2968
2969 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2970 THEN
2971 RAISE FND_API.G_EXC_ERROR;
2972 END IF;
2973 IF l_function_allowed = 'N' THEN
2974 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
2975 FND_MSG_PUB.add;
2976 p_return_status := FND_API.G_RET_STS_ERROR;
2977 RAISE FND_API.G_EXC_ERROR;
2978 END IF;
2979 IF FND_API.to_boolean(p_init_msg_list)
2980 THEN
2981 FND_MSG_PUB.initialize;
2982 END IF;
2983
2984 -- VALUE LAYER -----------------------------------------------------------------------
2985
2986 IF pa_security.allow_query (x_project_id => l_project_id_out ) = 'N' THEN
2987
2988 -- The user does not have query privileges on this project
2989 -- Hence, cannot update the project.Raise error
2990
2991 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
2992 FND_MSG_PUB.add;
2993 p_return_status := FND_API.G_RET_STS_ERROR;
2994 RAISE FND_API.G_EXC_ERROR;
2995 ELSE
2996 -- If the user has query privileges, then check whether
2997 -- update privileges are also available
2998 IF pa_security.allow_update (x_project_id => l_project_id_out ) = 'N'
2999 THEN
3000
3001 -- The user does not have update privileges on this project
3002 -- Hence , raise error
3003
3004 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
3005 FND_MSG_PUB.add;
3006 p_return_status := FND_API.G_RET_STS_ERROR;
3007 RAISE FND_API.G_EXC_ERROR;
3008 END IF;
3009 END IF;
3010
3011 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS ) then
3012 FND_MSG_PUB.Count_And_Get
3013 ( p_count => p_msg_count
3014 ,p_data => p_msg_data
3015 );
3016 END IF;
3017 EXCEPTION
3018 WHEN FND_API.G_EXC_ERROR THEN
3019 p_return_status := FND_API.G_RET_STS_ERROR;
3020 FND_MSG_PUB.Count_And_Get
3021 ( p_count => p_msg_count
3022 ,p_data => p_msg_data
3023 );
3024 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3025 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3026 FND_MSG_PUB.Count_And_Get
3027 ( p_count => p_msg_count
3028 ,p_data => p_msg_data
3029 );
3030 WHEN OTHERS THEN
3031 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3032 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3033 FND_MSG_PUB.Add_Exc_Msg
3034 ( G_PKG_NAME
3035 , l_api_name
3036 );
3037 END IF;
3038 FND_MSG_PUB.Count_And_Get
3039 ( p_count => p_msg_count
3040 ,p_data => p_msg_data
3041 );
3042
3043 END Project_Level_Validations;
3044
3045 /* Progress Management Changes. Bug # 3420093. */
3046
3047 PROCEDURE update_task_progress_amg
3048 ( p_api_version IN NUMBER :=1.0
3049 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
3050 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
3051 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
3052 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
3053 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
3054 ,p_debug_mode IN VARCHAR2 :='N'
3055 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3056 ,p_progress_mode IN VARCHAR2 := 'FUTURE'
3057 ,p_project_id IN NUMBER
3058 ,p_structure_version_id IN NUMBER
3059 ,p_structure_type IN VARCHAR2
3060 ,p_as_of_date IN DATE
3061 ,p_task_progress_list_table IN PA_PROGRESS_PUB.PA_TASK_PROGRESS_LIST_TBL_TYPE
3062 ,x_return_status OUT NOCOPY VARCHAR2
3063 ,x_msg_count OUT NOCOPY NUMBER
3064 ,x_msg_data OUT NOCOPY VARCHAR2
3065 ) is
3066
3067 l_task_progress_rec PA_PROGRESS_PUB.PA_TASK_PROGRESS_LIST_REC_TYPE;
3068 l_return_status VARCHAR2(1);
3069 l_api_name VARCHAR2(30) := 'update_task_progress_amg';
3070 begin
3071 IF FND_API.to_boolean(p_init_msg_list)
3072 THEN
3073 FND_MSG_PUB.initialize;
3074 END IF;
3075
3076 x_return_status := FND_API.G_RET_STS_SUCCESS;
3077
3078 -- Setting context for the temporary fin_plan table.
3079
3080 PA_PROGRESS_PUB.populate_pji_tab_for_plan(
3081 p_api_version => p_api_version
3082 ,p_init_msg_list => p_init_msg_list
3083 ,p_commit => p_commit
3084 ,p_calling_module => 'AMG'
3085 ,p_debug_mode => p_debug_mode
3086 ,p_max_msg_count => p_max_msg_count
3087 ,p_project_id => p_project_id
3088 ,p_structure_version_id => p_structure_version_id
3089 --,p_baselined_str_ver_id => p_baselined_str_ver_id
3090 ,x_return_status => x_return_status
3091 ,x_msg_count => x_msg_count
3092 ,x_msg_data => x_msg_data
3093 );
3094
3095 -- 4537865 : Included check for x_return_status
3096 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3097 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3098 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3099 RAISE FND_API.G_EXC_ERROR;
3100 END IF;
3101
3102 -- Call Update progress for each task with P_rollup_entire_wbs_flag = 'N' and p_calling_module = 'AMG'.
3103
3104 for l_loop_count in 1 .. p_task_progress_list_table.count
3105 loop
3106 l_task_progress_rec := p_task_progress_list_table(l_loop_count);
3107
3108 PA_PROGRESS_PUB.UPDATE_PROGRESS(
3109 p_api_version => p_api_version
3110 ,p_init_msg_list => p_init_msg_list
3111 ,p_commit => p_commit
3112 ,p_validate_only => p_validate_only
3113 ,p_validation_level => p_validation_level
3114 ,p_calling_module => 'AMG'
3115 ,p_debug_mode => p_debug_mode
3116 ,p_max_msg_count => p_max_msg_count
3117 ,P_rollup_entire_wbs_flag => 'N'
3118 ,p_progress_mode => p_progress_mode
3119 --,p_percent_complete_id => l_task_progress_rec.percent_complete_id
3120 ,p_project_id => p_project_id
3121 ,p_object_id => l_task_progress_rec.object_id
3122 ,p_object_version_id => l_task_progress_rec.object_version_id
3123 ,p_object_type => l_task_progress_rec.object_type
3124 ,p_as_of_date => p_as_of_date
3125 ,p_percent_complete => l_task_progress_rec.percent_complete
3126 ,p_progress_status_code => l_task_progress_rec.progress_status_code
3127 ,p_progress_comment => l_task_progress_rec.progress_comment
3128 --,p_brief_overview => l_task_progress_rec.brief_overview
3129 ,p_actual_start_date => l_task_progress_rec.actual_start_date
3130 ,p_actual_finish_date => l_task_progress_rec.actual_finish_date
3131 ,p_estimated_start_date => l_task_progress_rec.estimated_start_date
3132 ,p_estimated_finish_date => l_task_progress_rec.estimated_finish_date
3133 ,p_scheduled_start_date => l_task_progress_rec.scheduled_start_date
3134 ,p_scheduled_finish_date => l_task_progress_rec.scheduled_finish_date
3135 --,p_record_version_number => l_task_progress_rec.record_version_number
3136 ,p_task_status => l_task_progress_rec.task_status
3137 ,p_est_remaining_effort => l_task_progress_rec.est_remaining_effort
3138 ,p_ETC_cost => l_task_progress_rec.ETC_cost
3139 ,p_actual_work_quantity => l_task_progress_rec.actual_work_quantity
3140 --,p_pm_product_code => l_task_progress_rec.pm_product_code
3141 ,p_structure_type => p_structure_type
3142 --,p_actual_effort => l_task_progress_rec.actual_effort
3143 --,p_actual_cost => l_task_progress_rec.actual_cost
3144 --,p_actual_effort_this_period => l_task_progress_rec.actual_effort_this_period
3145 --,p_actual_cost_this_period => l_task_progress_rec.actual_cost_this_period
3146 --,p_object_sub_type => l_task_progress_rec.object_sub_type
3147 ,p_task_id => l_task_progress_rec.task_id
3148 ,p_structure_version_id => p_structure_version_id
3149 --,p_prog_fom_wp_flag => l_task_progress_rec.prog_fom_wp_flag
3150 --,p_rollup_reporting_lines_flag=> l_task_progress_rec.rollup_reporting_lines_flag
3151 --,p_planned_cost => l_task_progress_rec.planned_cost
3152 --,p_planned_effort => l_task_progress_rec.planned_effort
3153 --,p_rate_based_flag => l_task_progress_rec.rate_based_flag
3154 --,p_resource_class_code => l_task_progress_rec.resource_class_code
3155 --,p_transfer_wp_pc_flag => l_task_progress_rec.transfer_wp_pc_flag
3156 --,p_rbs_element_id => l_task_progress_rec.rbs_element_id
3157 --,p_resource_list_member_id => l_task_progress_rec.resource_list_member_id
3158 ,x_return_status => x_return_status
3159 ,x_msg_count => x_msg_count
3160 ,x_msg_data => x_msg_data
3161 );
3162 -- 4537865 : Included check for x_return_status
3163 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3165 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3166 RAISE FND_API.G_EXC_ERROR;
3167 END IF;
3168 end loop;
3169
3170 -- Call rollup_progress_pvt api for the entire structure.
3171
3172 PA_PROGRESS_PUB.ROLLUP_PROGRESS_PVT(
3173 p_init_msg_list => p_init_msg_list
3174 ,p_commit => p_commit
3175 ,p_validate_only => p_validate_only
3176 ,p_project_id => p_project_id
3177 ,p_structure_version_id => p_structure_version_id
3178 ,p_as_of_date => trunc(p_as_of_date) -- 5294838
3179 ,x_return_status => x_return_status
3180 ,x_msg_count => x_msg_count
3181 ,x_msg_data => x_msg_data);
3182
3183 if (x_return_status <> fnd_api.g_ret_sts_success ) then
3184 fnd_msg_pub.count_and_get
3185 (p_count => x_msg_count
3186 ,p_data => x_msg_data);
3187 end if;
3188
3189 exception
3190
3191 when fnd_api.g_exc_error then
3192 x_return_status := fnd_api.g_ret_sts_error;
3193 fnd_msg_pub.count_and_get
3194 (p_count => x_msg_count
3195 ,p_data => x_msg_data);
3196 when fnd_api.g_exc_unexpected_error then
3197 x_return_status := fnd_api.g_ret_sts_unexp_error;
3198 fnd_msg_pub.count_and_get
3199 (p_count => x_msg_count
3200 ,p_data => x_msg_data);
3201 when others then
3202 x_return_status := fnd_api.g_ret_sts_unexp_error;
3203 if fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) then
3204 fnd_msg_pub.add_exc_msg(g_pkg_name,l_api_name);
3205 end if;
3206 fnd_msg_pub.count_and_get
3207 (p_count => x_msg_count
3208 ,p_data => x_msg_data);
3209
3210 end update_task_progress_amg;
3211
3212 /* Progress Management Changes. Bug # 3420093. */
3213
3214 -- =================================================================
3215
3216 END pa_Status_Pub;