[Home] [Help]
PACKAGE BODY: APPS.PA_STATUS_PUB
Source
1 PACKAGE BODY pa_status_pub AS
2 /* $Header: PAPMSTPB.pls 120.19.12020000.5 2012/10/25 14:39:36 djambhek 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_token1 => 'AS_OF_DATE'
759 ,p_value1 => l_as_of_date);
760 p_return_status := FND_API.G_RET_STS_ERROR;
761 RAISE FND_API.G_EXC_ERROR;
762 END IF;
763 END IF;
764 if (l_object_type = 'PA_ASSIGNMENTS')then
765 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'
766 AND trunc(nvl(l_latest_as_of_date,l_as_of_date + 1 )) <> TRUNC(l_as_of_date)
767 THEN
768 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
769 ,p_msg_name => 'PA_TP_INV_AOD2'
770 ,p_token1 => 'AS_OF_DATE'
771 ,p_value1 => l_as_of_date);
772 p_return_status := FND_API.G_RET_STS_ERROR;
773 RAISE FND_API.G_EXC_ERROR;
774 END IF;
775 END IF;
776 end if;
777 end if;
778 -- Bug 3606627 : Now p_task_status column is being used for deliverable status too
779 -- So we can not default values here. Defaulting is happening in update_task_progress and
780 -- update_deliverable_progress
781 /* Bug2751159 -- Added following IF condition to get the appropriate task_status */
782 IF p_task_status = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR p_task_status IS NULL --bug 2791395
783 THEN
784 /* IF p_percent_complete = 100 THEN
785 l_task_status := '127'; ---COMPLETED -- 127 bug 2826235
786 ELSIF p_percent_complete > 0 THEN
787 l_task_status := '125'; --IN_PROGRESS -- 125 bug 2826235
788 ELSIF p_percent_complete is null or p_percent_complete = 0 THEN
789 l_task_status := '124'; --NOT_STARTED -- 124 bug 2826235
790 END IF; */
791 l_task_status := null;
792 ELSE
793 l_task_status := p_task_status;
794 END IF;
795 -- Bug 3606627 : Cancelled Status check is modified
796 /* --Bug 2792857
797 IF PA_PROGRESS_UTILS.get_task_status( l_project_id_out, l_object_id ) = '128' AND --CANCELLED --128 bug 2826235
798 PA_PROGRESS_UTILS.get_system_task_status( l_task_status ) = 'CANCELLED' AND
799 l_progress_mode = 'FUTURE'
800 THEN
801 RETURN;
802 END IF;
803 --Bug 2792857*/
804
805 l_system_task_status := PA_PROGRESS_UTILS.get_system_task_status(l_task_status, l_object_type) ;
806
807 IF ((l_system_task_status = 'CANCELLED' OR l_system_task_status = 'DLVR_CANCELLED')
808 AND l_progress_mode = 'FUTURE') THEN
809 return;
810 END IF;
811
812 IF g1_debug_mode = 'Y' THEN
813 pa_debug.write(x_Module=>'PA_STATUS_PUB.UPDATE_PROGRESS', x_Msg => 'Calling PA_PROGRESS_PUB.UPDATE_PROGRESS', x_Log_Level=> 3);
814 END IF;
815
816 if (l_progress_mode = 'FUTURE') then
817 PA_PROGRESS_PUB.UPDATE_PROGRESS(
818 p_api_version => p_api_version_number,
819 p_init_msg_list => p_init_msg_list,
820 p_commit => FND_API.G_FALSE, --Bug 3754134 instead passing p_commit pass false
821 p_action => 'PUBLISH',
822 P_rollup_entire_wbs_flag => l_rollup_entire_wbs,
823 p_progress_mode => l_progress_mode,
824 p_calling_module => 'AMG',
825 p_project_id => l_project_id_out,
826 p_object_id => l_object_id,
827 p_object_version_id => l_object_version_id,
828 p_object_type => l_object_type,
829 p_as_of_date => trunc(l_as_of_date), -- 5294838
830 p_percent_complete => p_percent_complete,
831 p_progress_status_code => l_progress_status_code,
832 p_progress_comment => l_progress_comment,
833 p_brief_overview => l_description,
834 p_actual_start_date => l_actual_start_date,
835 p_actual_finish_date => l_actual_finish_date,
836 p_estimated_start_date => l_estimated_start_date,
837 p_estimated_finish_date => l_estimated_finish_date,
838 p_scheduled_start_date => l_scheduled_start_date,
839 p_scheduled_finish_date => l_scheduled_finish_date,
840 p_pm_product_code => l_pm_product_code,
841 p_record_version_number => 1,
842 p_task_status => l_task_status,
843 p_est_remaining_effort => l_est_remaining_effort,
844 p_actual_work_quantity => l_actual_work_quantity,
845 p_ETC_cost => l_etc_cost,
846 p_structure_type => p_structure_type,
847 p_actual_effort => l_ACTUAL_EFFORT_TO_DATE, -- Bug 3799841 : there was swap b/w cost and effort
848 p_actual_cost => l_ACTUAL_COST_TO_DATE,
849 p_task_id => l_task_id,
850 p_structure_version_id => l_structure_version_id,
851 p_prog_fom_wp_flag => 'N',
852 p_txn_currency_code => p_txn_currency_code, -- Fix for Bug # 3988457.
853 x_return_status => p_return_status,
854 x_msg_count => p_msg_count,
855 x_msg_data => p_msg_data );
856 elsif (l_progress_mode = 'BACKDATED') then
857 PA_PROGRESS_PUB.UPDATE_PROGRESS(
858 p_api_version => p_api_version_number,
859 p_init_msg_list => p_init_msg_list,
860 p_commit => FND_API.G_FALSE,
861 p_action => 'PUBLISH',
862 P_rollup_entire_wbs_flag => l_rollup_entire_wbs,
863 p_progress_mode => l_progress_mode,
864 p_calling_module => 'AMG',
865 p_project_id => l_project_id_out,
866 p_object_id => l_object_id,
867 p_object_version_id => l_object_version_id,
868 p_object_type => l_object_type,
869 p_as_of_date => trunc(l_as_of_date), -- 5294838
870 p_percent_complete => p_percent_complete,
871 p_progress_status_code => l_progress_status_code,
872 p_progress_comment => l_progress_comment,
873 p_brief_overview => l_description,
874 p_scheduled_start_date => l_scheduled_start_date,
875 p_scheduled_finish_date => l_scheduled_finish_date,
876 p_pm_product_code => l_pm_product_code,
877 p_record_version_number => 1,
878 p_task_status => l_task_status,
879 p_structure_type => p_structure_type,
880 p_task_id => l_task_id,
881 p_structure_version_id => l_structure_version_id,
882 p_prog_fom_wp_flag => 'N',
883 p_txn_currency_code => p_txn_currency_code,
884 x_return_status => p_return_status,
885 x_msg_count => p_msg_count,
886 x_msg_data => p_msg_data );
887
888 end if;
889
890 IF g1_debug_mode = 'Y' THEN
891 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);
892 END IF;
893
894
895 IF (p_return_status = FND_API.G_RET_STS_SUCCESS )
896 then
897 IF FND_API.to_boolean(p_commit)
898 THEN
899 COMMIT;
900 END IF;
901 ELSE
902 ROLLBACK TO Update_Progress_Pub;
903 FND_MSG_PUB.Count_And_Get
904 ( p_count => p_msg_count
905 ,p_data => p_msg_data
906 );
907 END IF;
908
909 EXCEPTION
910 WHEN FND_API.G_EXC_ERROR THEN
911
912 p_return_status := FND_API.G_RET_STS_ERROR;
913 ROLLBACK TO Update_Progress_Pub;
914
915 FND_MSG_PUB.Count_And_Get
916 ( p_count => p_msg_count
917 , p_data => p_msg_data
918 );
919
920 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
921
922 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
923 ROLLBACK TO Update_Progress_Pub;
924
925 FND_MSG_PUB.Count_And_Get
926 ( p_count => p_msg_count
927 , p_data => p_msg_data
928 );
929
930
931 WHEN ROW_ALREADY_LOCKED THEN
932
933 p_return_status := FND_API.G_RET_STS_ERROR ;
934 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
935 THEN
936 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
937 FND_MESSAGE.SET_TOKEN('ENTITY', 'PERCENT_COMPLETE');
938 FND_MSG_PUB.Add;
939 END IF;
940 ROLLBACK TO Update_Progress_Pub;
941 FND_MSG_PUB.Count_And_Get
942 (p_count => p_msg_count
943 , p_data => p_msg_data
944 );
945
946 WHEN OTHERS THEN
947
948 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
949 ROLLBACK TO Update_Progress_Pub;
950
951 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
952 FND_MSG_PUB.Add_Exc_Msg
953 ( G_PKG_NAME
954 , l_api_name
955 );
956 END IF;
957
958 FND_MSG_PUB.Count_And_Get
959 ( p_count => p_msg_count
960 , p_data => p_msg_data
961 );
962
963
964 END Update_Progress;
965 -- =========================================================================
966
967 --
968 -- Name: Update_Earned_Value
969 -- Type: PL/SQL Procedure
970 -- Decscription: This procedure updates the PA_EARNED_VALUES table.
971 --
972 -- Called Subprograms: Convert_Pm_Projref_To_Id
973 -- , Convert_Pm_Taskref_To_Id
974 -- , Convert_List_Name_To_Id
975 -- , Convert_Alias_To_Id
976 --
977 -- History: 08-AUG-1996 Created jwhite
978 -- 29-AUG-1996 Update jwhite Applied latest messaging standards.
979 -- 29-OCT-1996 Update jwhite Replaced resource list member
980 -- conversion code with API call
981 -- to PA_RESOURCE_PUB.
982 -- 13-DEC-1996 Update jwhite Applied lastest standards.
983 --
984
985 PROCEDURE Update_Earned_Value
986 (p_api_version_number IN NUMBER
987 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
988 , p_commit IN VARCHAR2 := FND_API.G_FALSE
989 , p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
990 , p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
991 , p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
992 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
993 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
994 , p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
995 , p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
996 , p_resource_list_member_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
997 , p_resource_alias IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
998 , p_resource_list_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
999 , p_as_of_date IN DATE
1000 , p_bcws_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1001 , p_acwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1002 , p_bcwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1003 , p_bac_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1004 , p_bcws_itd IN NUMBER
1005 , p_acwp_itd IN NUMBER
1006 , p_bcwp_itd IN NUMBER
1007 , p_bac_itd IN NUMBER
1008 , p_bqws_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1009 , p_aqwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1010 , p_bqwp_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1011 , p_baq_current IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1012 , p_bqws_itd IN NUMBER
1013 , p_aqwp_itd IN NUMBER
1014 , p_bqwp_itd IN NUMBER
1015 , p_baq_itd IN NUMBER
1016 )
1017
1018 IS
1019
1020 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1021 l_api_name CONSTANT VARCHAR2(30) := 'Update_Earned_Value';
1022 l_value_conversion_error BOOLEAN := FALSE;
1023 l_return_status VARCHAR2(1);
1024 l_msg_count INTEGER;
1025
1026 l_err_code NUMBER := -1;
1027 l_err_stage VARCHAR2(2000) := NULL;
1028 l_err_stack VARCHAR2(2000) := NULL;
1029
1030 l_project_id_out NUMBER := 0;
1031 l_task_id_out NUMBER := 0;
1032 l_resource_list_member_id_out NUMBER := 0;
1033 l_resource_list_id_out NUMBER := 0;
1034
1035 l_bcws_current NUMBER := 0;
1036 l_acwp_current NUMBER := 0;
1037 l_bcwp_current NUMBER := 0;
1038 l_bac_current NUMBER := 0;
1039
1040 l_bqws_current NUMBER := 0;
1041 l_aqwp_current NUMBER := 0;
1042 l_bqwp_current NUMBER := 0;
1043 l_baq_current NUMBER := 0;
1044 l_msg_data VARCHAR2(2000);
1045 l_function_allowed VARCHAR2(1);
1046 l_resp_id NUMBER := 0;
1047 l_current_flag VARCHAR2(1);
1048 l_dummy VARCHAR2(1);
1049 l_module_name VARCHAR2(80);
1050 l_user_id NUMBER := 0;
1051 l_date_computed DATE;
1052
1053
1054 -- ROW LOCKING
1055
1056 CURSOR l_earned_values_csr (l_project_id_out NUMBER
1057 , l_task_id_out NUMBER
1058 , l_resource_list_member_id_out NUMBER)
1059 IS
1060 SELECT trunc(as_of_date)
1061 FROM pa_earned_values ev
1062 WHERE ev.project_id = l_project_id_out
1063 AND ev.task_id = l_task_id_out
1064 AND ev.resource_list_member_id = l_resource_list_member_id_out
1065 AND ev.current_flag = 'Y'
1066 FOR UPDATE NOWAIT;
1067
1068
1069
1070 BEGIN
1071
1072 SAVEPOINT Update_Earned_Value_Pub;
1073
1074 p_return_status := FND_API.G_RET_STS_SUCCESS;
1075
1076 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
1077 p_api_version_number ,
1078 l_api_name,
1079 G_PKG_NAME)
1080 THEN
1081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1082 END IF;
1083
1084 --Uncommented the following code as the fix of Bug 5632178
1085 /* Moved from below for Advanced Project Security Changes. Bug 2471668*/
1086
1087 PA_PROJECT_PVT.Convert_pm_projref_to_id
1088 ( p_pm_project_reference => p_pm_project_reference
1089 , p_pa_project_id => p_project_id
1090 , p_out_project_id => l_project_id_out
1091 , p_return_status => l_return_status
1092 );
1093
1094
1095 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1098 RAISE FND_API.G_EXC_ERROR;
1099 END IF;
1100 --End of changes for bug 5632178
1101
1102 l_user_id := FND_GLOBAL.User_id;
1103 l_resp_id := FND_GLOBAL.Resp_id;
1104 --l_module_name := p_pm_product_code||'.'||'PA_PM_UPDATE_EARNED_VALUE';
1105
1106 pa_security.initialize (X_user_id => l_user_id,
1107 X_calling_module => l_module_name);
1108
1109 -- Actions performed using the APIs would be subject to
1110 -- function security. If the responsibility does not allow
1111 -- such functions to be executed, the API should not proceed further
1112 -- since the user does not have access to such functions
1113
1114 --Bug 2471668
1115 PA_INTERFACE_UTILS_PUB.g_project_id := l_project_id_out;
1116
1117 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
1118 (p_api_version_number => p_api_version_number,
1119 p_responsibility_id => l_resp_id,
1120 p_function_name => 'PA_PM_UPDATE_EARNED_VALUE',
1121 p_msg_count => l_msg_count,
1122 p_msg_data => l_msg_data,
1123 p_return_status => l_return_status,
1124 p_function_allowed => l_function_allowed );
1125
1126 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1127 THEN
1128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1129
1130 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1131 THEN
1132 RAISE FND_API.G_EXC_ERROR;
1133 END IF;
1134 IF l_function_allowed = 'N' THEN
1135 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
1136 FND_MSG_PUB.add;
1137 p_return_status := FND_API.G_RET_STS_ERROR;
1138 RAISE FND_API.G_EXC_ERROR;
1139 END IF;
1140
1141 IF FND_API.to_boolean(p_init_msg_list)
1142 THEN
1143 FND_MSG_PUB.initialize;
1144 END IF;
1145
1146
1147 -- VALUE LAYER -----------------------------------------------------------------------
1148
1149 /* Moved up for Advanced project security changes . Bug 2471668
1150 PA_PROJECT_PVT.Convert_pm_projref_to_id
1151 ( p_pm_project_reference => p_pm_project_reference
1152 , p_pa_project_id => p_project_id
1153 , p_out_project_id => l_project_id_out
1154 , p_return_status => l_return_status
1155 );
1156
1157
1158 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1160 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1161 RAISE FND_API.G_EXC_ERROR;
1162 END IF;
1163 */
1164
1165 IF pa_security.allow_query (x_project_id => l_project_id_out ) = 'N' THEN
1166
1167 -- The user does not have query privileges on this project
1168 -- Hence, cannot update the project.Raise error
1169
1170 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
1171 FND_MSG_PUB.add;
1172 p_return_status := FND_API.G_RET_STS_ERROR;
1173 RAISE FND_API.G_EXC_ERROR;
1174 ELSE
1175 -- If the user has query privileges, then check whether
1176 -- update privileges are also available
1177 IF pa_security.allow_update (x_project_id => l_project_id_out ) = 'N'
1178 THEN
1179
1180 -- The user does not have update privileges on this project
1181 -- Hence , raise error
1182
1183 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
1184 FND_MSG_PUB.add;
1185 p_return_status := FND_API.G_RET_STS_ERROR;
1186 RAISE FND_API.G_EXC_ERROR;
1187 END IF;
1188 END IF;
1189
1190 IF (p_task_id = 0) THEN
1191 -- Must Be Project-Level Earned Value Data
1192
1193 l_task_id_out := 0;
1194
1195 ELSE
1196 -- 5262740 Converted PA_PROJECT_PVT.Convert_pm_taskref_to_id to PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
1197 PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
1198 ( p_pa_project_id => l_project_id_out
1199 , p_pa_task_id => p_task_id
1200 , p_pm_task_reference => p_pm_task_reference
1201 , p_out_task_id => l_task_id_out
1202 , p_return_status => l_return_status
1203 );
1204
1205
1206 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1208 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1209 RAISE FND_API.G_EXC_ERROR;
1210 END IF;
1211 END IF;
1212
1213 -- ---------------------------------------------------------------
1214 -- Convert Resource List Member Values to Id
1215 -- ---------------------------------------------------------------
1216
1217 IF (p_resource_list_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1218
1219 IF (p_resource_list_member_id = 0) THEN
1220 -- Must be Task-Level Earned Value Data
1221
1222 l_resource_list_member_id_out := 0;
1223
1224 ELSE
1225
1226 PA_RESOURCE_PUB.Convert_alias_to_id
1227 ( p_resource_list_id => l_resource_list_id_out
1228 , p_resource_list_member_id => p_resource_list_member_id
1229 , p_out_resource_list_member_id => l_resource_list_member_id_out
1230 , p_return_status => l_return_status
1231 );
1232
1233
1234 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1236 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1237 RAISE FND_API.G_EXC_ERROR;
1238 END IF;
1239
1240 END IF;
1241
1242 ELSE
1243 PA_RESOURCE_PUB.Convert_List_name_to_id
1244 ( p_resource_list_name => p_resource_list_name,
1245 p_out_resource_list_id => l_resource_list_id_out,
1246 p_return_status => l_return_status
1247 );
1248
1249 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1251 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1252 RAISE FND_API.G_EXC_ERROR;
1253 END IF;
1254
1255 PA_RESOURCE_PUB.Convert_alias_to_id
1256 ( p_resource_list_id => l_resource_list_id_out
1257 , p_alias => p_resource_alias
1258 , p_out_resource_list_member_id => l_resource_list_member_id_out
1259 , p_return_status => l_return_status
1260 );
1261
1262 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1264 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1265 RAISE FND_API.G_EXC_ERROR;
1266 END IF;
1267
1268 END IF;
1269
1270 -- ------------------------------------------------------------------------------
1271 -- Set Defaults
1272 -- ------------------------------------------------------------------------------
1273
1274 IF (p_bcws_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1275 l_bcws_current := 0;
1276 ELSE
1277 l_bcws_current := p_bcws_current;
1278 END IF;
1279 IF (p_acwp_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1280 l_acwp_current := 0;
1281 ELSE
1282 l_acwp_current := p_acwp_current ;
1283 END IF;
1284 IF (p_bcwp_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1285 l_bcwp_current := 0;
1286 ELSE
1287 l_bcwp_current := p_bcwp_current;
1288 END IF;
1289 IF (p_bac_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1290 l_bac_current := 0;
1291 ELSE
1292 l_bac_current := p_bac_current;
1293 END IF;
1294
1295 IF (p_bqws_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1296 l_bqws_current := 0;
1297 ELSE
1298 l_bqws_current := p_bqws_current;
1299 END IF;
1300 IF (p_aqwp_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1301 l_aqwp_current := 0;
1302 ELSE
1303 l_aqwp_current := p_aqwp_current;
1304 END IF;
1305 IF (p_bqwp_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1306 l_bqwp_current := 0;
1307 ELSE
1308 l_bqwp_current := p_bqwp_current;
1309 END IF;
1310 IF (p_baq_current = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
1311 l_baq_current := 0;
1312 ELSE
1313 l_baq_current := p_baq_current;
1314 END IF;
1315
1316 -- ------------------------------------------------------------------------------
1317 -- UPDATE Current Flag in PA_EARNED_VALUES
1318 -- ------------------------------------------------------------------------------
1319
1320 -- Locking
1321 l_date_computed := NULL;
1322
1323 OPEN l_earned_values_csr (l_project_id_out, l_task_id_out
1324 , l_resource_list_member_id_out );
1325 FETCH l_earned_values_csr INTO l_date_computed;
1326 IF l_earned_values_csr%NOTFOUND THEN
1327 l_current_flag := 'Y'; -- means there was no existing record
1328 -- with current flag = 'Y'.This could be
1329 -- the first time the record is coming in
1330 END IF;
1331 IF l_date_computed IS NOT NULL THEN
1332 IF TRUNC(p_as_of_date) >= l_date_computed THEN -- If the
1333 -- incoming date is >= to the
1334 -- one existing then set the old
1335 -- current to 'N' and insert new
1336 -- record with current_flag = 'Y'
1337 l_current_flag := 'Y'; -- means the current flag is to be
1338 ELSE
1339 l_current_flag := 'N';
1340 END IF;
1341 END IF;
1342 CLOSE l_earned_values_csr;
1343
1344 IF l_current_flag = 'Y' AND l_date_computed IS NOT NULL THEN
1345 UPDATE pa_earned_values ev
1346 SET ev.current_flag = 'N'
1347 WHERE ev.project_id = l_project_id_out
1348 AND ev.task_id = l_task_id_out
1349 AND ev.resource_list_member_id = l_resource_list_member_id_out
1350 AND ev.current_flag = 'Y';
1351 END IF;
1352
1353
1354 -- ------------------------------------------------------------------------------
1355 -- INSERT VALUES INTO PA_EARNED_VALUES
1356 -- ------------------------------------------------------------------------------
1357
1358 INSERT INTO pa_earned_values (
1359 PROJECT_ID
1360 , TASK_ID
1361 , RESOURCE_LIST_MEMBER_ID
1362 , AS_OF_DATE
1363 , CURRENT_FLAG
1364 , BCWS
1365 , ACWP
1366 , BCWP
1367 , BAC
1368 , BCWS_ITD
1369 , ACWP_ITD
1370 , BCWP_ITD
1371 , BAC_ITD
1372 , BQWS
1373 , AQWP
1374 , BQWP
1375 , BAQ
1376 , BQWS_ITD
1377 , AQWP_ITD
1378 , BQWP_ITD
1379 , BAQ_ITD
1380 , LAST_UPDATE_DATE
1381 , LAST_UPDATED_BY
1382 , CREATION_DATE
1383 , CREATED_BY
1384 , LAST_UPDATE_LOGIN)
1385 VALUES (
1386 l_project_id_out
1387 , l_task_id_out
1388 , l_resource_list_member_id_out
1389 , p_as_of_date
1390 , l_current_flag
1391 , pa_currency.round_currency_amt(l_bcws_current)
1392 , pa_currency.round_currency_amt(l_acwp_current)
1393 , pa_currency.round_currency_amt(l_bcwp_current)
1394 , pa_currency.round_currency_amt(l_bac_current)
1395 , pa_currency.round_currency_amt(p_bcws_itd)
1396 , pa_currency.round_currency_amt(p_acwp_itd)
1397 , pa_currency.round_currency_amt(p_bcwp_itd)
1398 , pa_currency.round_currency_amt(p_bac_itd)
1399 , l_bqws_current
1400 , l_aqwp_current
1401 , l_bqwp_current
1402 , l_baq_current
1403 , p_bqws_itd
1404 , p_aqwp_itd
1405 , p_bqwp_itd
1406 , p_baq_itd
1407 , g_last_update_date
1408 , g_last_updated_by
1409 , g_creation_date
1410 , g_created_by
1411 , g_last_update_login);
1412
1413
1414 IF (SQL%ROWCOUNT = 0)
1415 THEN
1416 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1417 THEN
1418 FND_MESSAGE.SET_NAME('PA','PA_EV_INSERT_ERROR');
1419 FND_MSG_PUB.Add;
1420 END IF;
1421 RAISE FND_API.G_EXC_ERROR;
1422 END IF;
1423
1424 IF FND_API.to_boolean(p_commit)
1425 THEN
1426 COMMIT;
1427 END IF;
1428
1429
1430 EXCEPTION
1431 WHEN FND_API.G_EXC_ERROR THEN
1432 p_return_status := FND_API.G_RET_STS_ERROR;
1433 ROLLBACK TO Update_Earned_Value_Pub;
1434
1435 FND_MSG_PUB.Count_And_Get
1436 ( p_count => p_msg_count
1437 ,p_data => p_msg_data
1438 );
1439
1440 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1441 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1442 ROLLBACK TO Update_Earned_Value_Pub;
1443
1444 FND_MSG_PUB.Count_And_Get
1445 ( p_count => p_msg_count
1446 ,p_data => p_msg_data
1447 );
1448
1449 WHEN ROW_ALREADY_LOCKED THEN
1450
1451 p_return_status := FND_API.G_RET_STS_ERROR ;
1452
1453 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1454 THEN
1455 FND_MESSAGE.SET_NAME('PA','PA_ROW_ALREADY_LOCKED');
1456 FND_MESSAGE.SET_TOKEN('ENTITY', 'EARNED_VALUE');
1457 FND_MSG_PUB.Add;
1458 END IF;
1459
1460 ROLLBACK TO Update_Progress_Pub;
1461
1462
1463 WHEN OTHERS THEN
1464
1465 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1466 ROLLBACK TO Update_Earned_Value_Pub;
1467
1468 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1469 FND_MSG_PUB.Add_Exc_Msg
1470 ( G_PKG_NAME
1471 , l_api_name
1472 );
1473 END IF;
1474
1475 FND_MSG_PUB.Count_And_Get
1476 ( p_count => p_msg_count
1477 ,p_data => p_msg_data
1478 );
1479
1480
1481 END Update_Earned_Value;
1482
1483 PROCEDURE Init_Update_Task_Progress
1484 (p_api_version_number IN NUMBER
1485 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1486 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1487 , p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1488 , p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1489 , p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1490 )
1491 IS
1492 l_api_name CONSTANT VARCHAR2(30) := 'Init_Update_Task_Progress';
1493
1494 BEGIN
1495 IF FND_API.to_boolean(p_init_msg_list)
1496 THEN
1497 FND_MSG_PUB.initialize;
1498 END IF;
1499
1500 p_return_status := FND_API.G_RET_STS_SUCCESS;
1501
1502 G_TASK_PROGRESS_in_tbl.delete;
1503 G_TASK_PROGRESS_tbl_count := 0;
1504
1505 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1506 FND_MSG_PUB.Count_And_Get
1507 ( p_count => p_msg_count
1508 ,p_data => p_msg_data
1509 );
1510 END IF;
1511 EXCEPTION
1512 WHEN FND_API.G_EXC_ERROR THEN
1513 p_return_status := FND_API.G_RET_STS_ERROR;
1514 FND_MSG_PUB.Count_And_Get
1515 ( p_count => p_msg_count
1516 ,p_data => p_msg_data
1517 );
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520 FND_MSG_PUB.Count_And_Get
1521 ( p_count => p_msg_count
1522 ,p_data => p_msg_data
1523 );
1524 WHEN OTHERS THEN
1525 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1526 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1527 FND_MSG_PUB.Add_Exc_Msg
1528 ( G_PKG_NAME
1529 , l_api_name
1530 );
1531 END IF;
1532 FND_MSG_PUB.Count_And_Get
1533 ( p_count => p_msg_count
1534 ,p_data => p_msg_data
1535 );
1536
1537 END Init_Update_Task_Progress;
1538
1539 PROCEDURE Load_Task_Progress
1540 (p_api_version_number IN NUMBER
1541 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1542 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1543 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1544 ,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1545 ,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1546 ,p_structure_type IN VARCHAR2 := 'FINANCIAL'
1547 ,p_as_of_date IN DATE
1548 ,p_task_id IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1549 ,p_task_name IN PA_VC_1000_240 := PA_VC_1000_240(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1550 ,p_task_number IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1551 ,p_pm_task_reference IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1552 ,p_percent_complete IN PA_NUM_1000_NUM
1553 ,p_description IN PA_VC_1000_2000 := PA_VC_1000_2000(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1554 ,p_object_id IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1555 ,p_object_version_id IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1556 ,p_object_type IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1557 ,p_progress_status_code IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1558 ,p_progress_comment IN PA_VC_1000_4000 := PA_VC_1000_4000(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1559 ,p_actual_start_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1560 ,p_actual_finish_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1561 ,p_estimated_start_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1562 ,p_estimated_finish_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1563 ,p_scheduled_start_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1564 ,p_scheduled_finish_date IN PA_DATE_1000_DATE := PA_DATE_1000_DATE(PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE)
1565 ,p_task_status IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
1566 ,p_est_remaining_effort IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1567 ,p_actual_work_quantity IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
1568 ,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*/
1569 ,p_pm_deliverable_reference IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) -- Bug 3606627
1570 ,p_pm_task_assgn_reference IN PA_VC_1000_150 := PA_VC_1000_150(PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) -- Bug 3606627
1571 ,p_actual_cost_to_date IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) -- Bug 3606627
1572 ,p_actual_effort_to_date IN PA_NUM_1000_NUM := PA_NUM_1000_NUM(PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) -- Bug 3606627
1573 ,p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1574 ,p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1575 ,p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1576 )
1577 IS
1578 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
1579 l_api_name CONSTANT VARCHAR2(30) := 'Load_Task_Progress';
1580 l_progress_mode VARCHAR2(10);
1581 l_latest_as_of_date Date;
1582 G1_DEBUG_MODE VARCHAR2(1);
1583
1584 l_TASK_ID NUMBER;
1585 l_TASK_NAME VARCHAR2(240);
1586 l_TASK_NUMBER VARCHAR2(150);
1587 l_PM_TASK_REFERENCE VARCHAR2(150);
1588 l_PERCENT_COMPLETE NUMBER;
1589 l_DESCRIPTION VARCHAR2(2000);
1590 l_OBJECT_ID NUMBER;
1591 l_OBJECT_VERSION_ID NUMBER;
1592 l_OBJECT_TYPE VARCHAR2(150);
1593 l_PROGRESS_STATUS_CODE VARCHAR2(150);
1594 l_PROGRESS_COMMENT VARCHAR2(4000);
1595 l_ACTUAL_START_DATE DATE;
1596 l_ACTUAL_FINISH_DATE DATE;
1597 l_ESTIMATED_START_DATE DATE;
1598 l_ESTIMATED_FINISH_DATE DATE;
1599 l_SCHEDULED_START_DATE DATE;
1600 l_SCHEDULED_FINISH_DATE DATE;
1601 l_TASK_STATUS VARCHAR2(150);
1602 l_EST_REMAINING_EFFORT NUMBER;
1603 l_ACTUAL_WORK_QUANTITY NUMBER;
1604 l_ETC_COST NUMBER;
1605 l_PM_DELIVERABLE_REFERENCE VARCHAR2(150);
1606 l_PM_TASK_ASSGN_REFERENCE VARCHAR2(150);
1607 l_ACTUAL_COST_TO_DATE NUMBER;
1608 l_ACTUAL_EFFORT_TO_DATE NUMBER;
1609
1610 BEGIN
1611
1612 g1_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1613
1614 IF g1_debug_mode = 'Y' THEN
1615 pa_debug.init_err_stack ('PA_PROGRESS_PUB.LOAD_TASK_PROGRESS');
1616 END IF;
1617
1618 IF FND_API.to_boolean(p_init_msg_list)
1619 THEN
1620 FND_MSG_PUB.initialize;
1621 END IF;
1622
1623 p_return_status := FND_API.G_RET_STS_SUCCESS;
1624
1625 IF(G_TASK_PROGRESS_tbl_count = 0) THEN
1626 G_PROJECT_ID := P_PROJECT_ID;
1627 G_pm_project_reference := P_pm_project_reference;
1628 G_PM_PRODUCT_CODE := P_PM_PRODUCT_CODE;
1629 G_STRUCTURE_TYPE := P_STRUCTURE_TYPE;
1630 G_AS_OF_DATE := TRUNC(P_AS_OF_DATE);
1631 END IF;
1632
1633 IF g1_debug_mode = 'Y' THEN
1634 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);
1635 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);
1636 END IF;
1637
1638 FOR i in 1..1000 LOOP
1639 -- Bug 3606627 : Commented the code which return if task_id is null. Now task_id can be null for Deleiverable.
1640 -- 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
1641 -- return;
1642 --else
1643
1644 -- Bug 3673618 : Introduced local variables to store the value first.
1645 -- This is done to ensure that if any parameter is not passed it does not give error subscript out of bound
1646
1647 IF P_TASK_ID.count = 1 AND P_TASK_ID(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1648 l_TASK_ID := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1649 ELSE
1650 l_TASK_ID := P_TASK_ID(i);
1651 END IF;
1652
1653
1654
1655 IF P_TASK_NAME.count = 1 AND P_TASK_NAME(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1656 l_TASK_NAME := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1657 ELSE
1658 l_TASK_NAME := P_TASK_NAME(i);
1659 END IF;
1660
1661
1662 IF P_TASK_NUMBER.count = 1 AND P_TASK_NUMBER(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1663 l_TASK_NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1664 ELSE
1665 l_TASK_NUMBER := P_TASK_NUMBER(i);
1666 END IF;
1667
1668 --end if;
1669
1670 IF p_PM_TASK_REFERENCE.count = 1 AND p_PM_TASK_REFERENCE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1671 l_PM_TASK_REFERENCE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1672 ELSE
1673 l_PM_TASK_REFERENCE := p_PM_TASK_REFERENCE(i);
1674 END IF;
1675
1676
1677 IF p_PERCENT_COMPLETE.count = 1 AND p_PERCENT_COMPLETE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1678 l_PERCENT_COMPLETE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1679 ELSE
1680 l_PERCENT_COMPLETE := p_PERCENT_COMPLETE(i);
1681 END IF;
1682
1683 IF p_DESCRIPTION.count = 1 AND p_DESCRIPTION(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1684 l_DESCRIPTION := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1685 ELSE
1686 l_DESCRIPTION := p_DESCRIPTION(i);
1687 END IF;
1688
1689 IF p_OBJECT_ID.count = 1 AND p_OBJECT_ID(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1690 l_OBJECT_ID := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1691 ELSE
1692 l_OBJECT_ID := p_OBJECT_ID(i);
1693 END IF;
1694
1695 IF p_OBJECT_VERSION_ID.count = 1 AND p_OBJECT_VERSION_ID(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1696 l_OBJECT_VERSION_ID := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1697 ELSE
1698 l_OBJECT_VERSION_ID := p_OBJECT_VERSION_ID(i);
1699 END IF;
1700
1701 IF p_OBJECT_TYPE.count = 1 AND p_OBJECT_TYPE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1702 l_OBJECT_TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1703 ELSE
1704 l_OBJECT_TYPE := p_OBJECT_TYPE(i);
1705 END IF;
1706
1707 IF p_PROGRESS_STATUS_CODE.count = 1 AND p_PROGRESS_STATUS_CODE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1708 l_PROGRESS_STATUS_CODE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1709 ELSE
1710 l_PROGRESS_STATUS_CODE := p_PROGRESS_STATUS_CODE(i);
1711 END IF;
1712
1713 IF p_PROGRESS_COMMENT.count = 1 AND p_PROGRESS_COMMENT(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1714 l_PROGRESS_COMMENT := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1715 ELSE
1716 l_PROGRESS_COMMENT := p_PROGRESS_COMMENT(i);
1717 END IF;
1718
1719 IF p_ACTUAL_START_DATE.count = 1 AND p_ACTUAL_START_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1720 l_ACTUAL_START_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1721 ELSE
1722 l_ACTUAL_START_DATE := p_ACTUAL_START_DATE(i);
1723 END IF;
1724
1725 IF p_ACTUAL_FINISH_DATE.count = 1 AND p_ACTUAL_FINISH_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1726 l_ACTUAL_FINISH_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1727 ELSE
1728 l_ACTUAL_FINISH_DATE := p_ACTUAL_FINISH_DATE(i);
1729 END IF;
1730
1731 IF p_ESTIMATED_START_DATE.count = 1 AND p_ESTIMATED_START_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1732 l_ESTIMATED_START_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1733 ELSE
1734 l_ESTIMATED_START_DATE := p_ESTIMATED_START_DATE(i);
1735 END IF;
1736
1737 IF p_ESTIMATED_FINISH_DATE.count = 1 AND p_ESTIMATED_FINISH_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1738 l_ESTIMATED_FINISH_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1739 ELSE
1740 l_ESTIMATED_FINISH_DATE := p_ESTIMATED_FINISH_DATE(i);
1741 END IF;
1742
1743 IF p_SCHEDULED_START_DATE.count = 1 AND p_SCHEDULED_START_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1744 l_SCHEDULED_START_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1745 ELSE
1746 l_SCHEDULED_START_DATE := p_SCHEDULED_START_DATE(i);
1747 END IF;
1748
1749 IF p_SCHEDULED_FINISH_DATE.count = 1 AND p_SCHEDULED_FINISH_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
1750 l_SCHEDULED_FINISH_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE;
1751 ELSE
1752 l_SCHEDULED_FINISH_DATE := p_SCHEDULED_FINISH_DATE(i);
1753 END IF;
1754
1755 IF p_TASK_STATUS.count = 1 AND p_TASK_STATUS(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1756 l_TASK_STATUS := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1757 ELSE
1758 l_TASK_STATUS := p_TASK_STATUS(i);
1759 END IF;
1760
1761 IF p_EST_REMAINING_EFFORT.count = 1 AND p_EST_REMAINING_EFFORT(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1762 l_EST_REMAINING_EFFORT := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1763 ELSE
1764 l_EST_REMAINING_EFFORT := p_EST_REMAINING_EFFORT(i);
1765 END IF;
1766
1767 IF p_ACTUAL_WORK_QUANTITY.count = 1 AND p_ACTUAL_WORK_QUANTITY(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1768 l_ACTUAL_WORK_QUANTITY := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1769 ELSE
1770 l_ACTUAL_WORK_QUANTITY := p_ACTUAL_WORK_QUANTITY(i);
1771 END IF;
1772
1773 IF p_ETC_COST.count = 1 AND p_ETC_COST(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1774 l_ETC_COST := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1775 ELSE
1776 l_ETC_COST := p_ETC_COST(i);
1777 END IF;
1778
1779 --Bug 3606627 : Assign new variables Start
1780 IF p_PM_DELIVERABLE_REFERENCE.count = 1 AND p_PM_DELIVERABLE_REFERENCE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1781 l_PM_DELIVERABLE_REFERENCE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1782 ELSE
1783 l_PM_DELIVERABLE_REFERENCE := p_PM_DELIVERABLE_REFERENCE(i);
1784 END IF;
1785
1786 IF p_PM_TASK_ASSGN_REFERENCE.count = 1 AND p_PM_TASK_ASSGN_REFERENCE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
1787 l_PM_TASK_ASSGN_REFERENCE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR;
1788 ELSE
1789 l_PM_TASK_ASSGN_REFERENCE := p_PM_TASK_ASSGN_REFERENCE(i);
1790 END IF;
1791
1792 IF p_ACTUAL_COST_TO_DATE.count = 1 AND p_ACTUAL_COST_TO_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1793 l_ACTUAL_COST_TO_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1794 ELSE
1795 l_ACTUAL_COST_TO_DATE := p_ACTUAL_COST_TO_DATE(i);
1796 END IF;
1797
1798 IF p_ACTUAL_EFFORT_TO_DATE.count = 1 AND p_ACTUAL_EFFORT_TO_DATE(1) = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
1799 l_ACTUAL_EFFORT_TO_DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM;
1800 ELSE
1801 l_ACTUAL_EFFORT_TO_DATE := p_ACTUAL_EFFORT_TO_DATE(i);
1802 END IF;
1803
1804 IF g1_debug_mode = 'Y' THEN
1805 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'Values passed at count i='||i, x_Log_Level=> 3);
1806 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_TASK_ID :'||l_TASK_ID, x_Log_Level=> 3);
1807 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_TASK_NAME :'||l_TASK_NAME, x_Log_Level=> 3);
1808 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_TASK_NUMBER :'||l_TASK_NUMBER, x_Log_Level=> 3);
1809 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);
1810 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_PERCENT_COMPLETE :'||l_PERCENT_COMPLETE, x_Log_Level=> 3);
1811 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_DESCRIPTION :'||l_DESCRIPTION, x_Log_Level=> 3);
1812 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_OBJECT_ID :'||l_OBJECT_ID, x_Log_Level=> 3);
1813 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);
1814 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_OBJECT_TYPE :'||l_OBJECT_TYPE, x_Log_Level=> 3);
1815 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);
1816 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_PROGRESS_COMMENT :'||l_PROGRESS_COMMENT, x_Log_Level=> 3);
1817 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);
1818 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);
1819 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);
1820 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);
1821 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);
1822 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);
1823 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_TASK_STATUS :'||l_TASK_STATUS, x_Log_Level=> 3);
1824 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);
1825 pa_debug.write(x_Module=>'PA_STATUS_PUB.LOAD_TASK_PROGRESS', x_Msg => 'l_ETC_COST :'||l_ETC_COST, x_Log_Level=> 3);
1826 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);
1827 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);
1828 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);
1829 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);
1830 END IF;
1831
1832 -- Bug 3673618 : Added this condition to do not process blank records.
1833 IF(((l_TASK_ID is null) or (l_TASK_ID =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
1834 and ((l_PM_TASK_REFERENCE is null) or (l_PM_TASK_REFERENCE =PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1835 and ((l_OBJECT_ID is null) or (l_OBJECT_ID =PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
1836 and ((l_PM_DELIVERABLE_REFERENCE is null) or (l_PM_DELIVERABLE_REFERENCE =PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1837 and ((l_PM_TASK_ASSGN_REFERENCE is null) or (l_PM_TASK_ASSGN_REFERENCE =PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR))
1838 )
1839 THEN
1840 return;
1841 END IF;
1842 -- Bug 3673618 : Moved assignment to global table later at the code.
1843
1844 G_TASK_PROGRESS_tbl_count := G_TASK_PROGRESS_tbl_count + 1;
1845 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_ID := l_TASK_ID;
1846 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_ID := P_TASK_ID(i);
1847
1848 --IF(P_TASK_NAME(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1849 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NAME := substr(P_TASK_NAME(i), 1, 20);
1850 --ELSE
1851 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NAME := P_TASK_NAME(i);
1852 --END IF;
1853
1854 IF(l_TASK_NAME <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1855 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NAME := substrb(l_TASK_NAME, 1, 20); --5458363
1856 ELSE
1857 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NAME := l_TASK_NAME;
1858 END IF;
1859
1860 --IF(P_TASK_NUMBER(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1861 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NUMBER := substr(P_TASK_NUMBER(i), 1, 25);
1862 --ELSE
1863 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NUMBER := P_TASK_NUMBER(i);
1864 --END IF;
1865
1866 IF(l_TASK_NUMBER <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1867 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NUMBER := substrb(l_TASK_NUMBER, 1, 25); --5458363
1868 ELSE
1869 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_NUMBER := l_TASK_NUMBER;
1870 END IF;
1871
1872 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_TASK_REFERENCE := l_PM_TASK_REFERENCE;
1873 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_TASK_REFERENCE := p_PM_TASK_REFERENCE(i);
1874
1875 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PERCENT_COMPLETE := l_PERCENT_COMPLETE;
1876 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PERCENT_COMPLETE := p_PERCENT_COMPLETE(i);
1877
1878 --IF(p_DESCRIPTION(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1879 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).DESCRIPTION := substr(p_DESCRIPTION(i), 1, 250);
1880 --ELSE
1881 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).DESCRIPTION := p_DESCRIPTION(i);
1882 --END IF;
1883
1884 IF(l_DESCRIPTION <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1885 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).DESCRIPTION := substrb(l_DESCRIPTION, 1, 250); --5458363
1886 ELSE
1887 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).DESCRIPTION := l_DESCRIPTION;
1888 END IF;
1889
1890 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_ID := l_OBJECT_ID;
1891 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_ID := p_OBJECT_ID(i);
1892
1893 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_VERSION_ID := p_OBJECT_VERSION_ID(i);
1894 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_VERSION_ID := l_OBJECT_VERSION_ID;
1895
1896 --IF(p_OBJECT_TYPE(i) <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1897 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE := substr(p_OBJECT_TYPE(i), 1, 30);
1898 --ELSE
1899 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE := p_OBJECT_TYPE(i);
1900 --END IF;
1901
1902 IF(l_OBJECT_TYPE <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) THEN
1903 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE := substrb(l_OBJECT_TYPE, 1, 30); --5458363
1904 ELSE
1905 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE := l_OBJECT_TYPE;
1906 END IF;
1907
1908 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_STATUS_CODE := p_PROGRESS_STATUS_CODE(i);
1909 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_COMMENT := p_PROGRESS_COMMENT(i);
1910 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_START_DATE := p_ACTUAL_START_DATE(i);
1911 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_FINISH_DATE := p_ACTUAL_FINISH_DATE(i);
1912 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ESTIMATED_START_DATE := p_ESTIMATED_START_DATE(i);
1913 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ESTIMATED_FINISH_DATE := p_ESTIMATED_FINISH_DATE(i);
1914 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).SCHEDULED_START_DATE := p_SCHEDULED_START_DATE(i);
1915 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).SCHEDULED_FINISH_DATE := p_SCHEDULED_FINISH_DATE(i);
1916 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_STATUS := p_TASK_STATUS(i);
1917 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).EST_REMAINING_EFFORT := p_EST_REMAINING_EFFORT(i);
1918 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_WORK_QUANTITY := p_ACTUAL_WORK_QUANTITY(i);
1919
1920 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_STATUS_CODE := l_PROGRESS_STATUS_CODE;
1921 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_COMMENT := l_PROGRESS_COMMENT;
1922 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_START_DATE := l_ACTUAL_START_DATE;
1923 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_FINISH_DATE := l_ACTUAL_FINISH_DATE;
1924 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ESTIMATED_START_DATE := l_ESTIMATED_START_DATE;
1925 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ESTIMATED_FINISH_DATE := l_ESTIMATED_FINISH_DATE;
1926 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).SCHEDULED_START_DATE := l_SCHEDULED_START_DATE;
1927 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).SCHEDULED_FINISH_DATE := l_SCHEDULED_FINISH_DATE;
1928 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).TASK_STATUS := l_TASK_STATUS;
1929 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).EST_REMAINING_EFFORT := l_EST_REMAINING_EFFORT;
1930 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_WORK_QUANTITY := l_ACTUAL_WORK_QUANTITY;
1931 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ETC_COST := l_ETC_COST;
1932
1933 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_DELIVERABLE_REFERENCE := p_PM_DELIVERABLE_REFERENCE(i);
1934 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_TASK_ASSGN_REFERENCE := p_PM_TASK_ASSGN_REFERENCE(i);
1935 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_COST_TO_DATE := p_ACTUAL_COST_TO_DATE(i);
1936 --G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_EFFORT_TO_DATE := p_ACTUAL_EFFORT_TO_DATE(i);
1937
1938 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_DELIVERABLE_REFERENCE := l_PM_DELIVERABLE_REFERENCE;
1939 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PM_TASK_ASSGN_REFERENCE := l_PM_TASK_ASSGN_REFERENCE;
1940 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_COST_TO_DATE := l_ACTUAL_COST_TO_DATE;
1941 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).ACTUAL_EFFORT_TO_DATE := l_ACTUAL_EFFORT_TO_DATE;
1942
1943 IF G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE = 'PA_TASKS'
1944 THEN
1945 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);
1946 ELSE
1947 G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).LOWEST_LEVEL_TASK := 'N';
1948 END IF;
1949
1950 --Bug 3606627 : Assign new variables End
1951
1952 --Bug 3606627
1953 -- This code to determine l_latest_as_of_date should be there in pa_status_pub.update_progress
1954 -- as here we may not be having object_id. We may need to derive it from ref fields.
1955 /* l_latest_as_of_date := PA_PROGRESS_UTILS.GET_LATEST_AS_OF_DATE(p_task_id => null
1956 , p_project_id => G_PROJECT_ID
1957 , p_object_id => G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_ID
1958 , p_object_type => G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE
1959 , p_structure_type => G_STRUCTURE_TYPE
1960 );
1961
1962 IF l_latest_as_of_date is NOT NULL THEN
1963 IF P_AS_OF_DATE >= l_latest_as_of_date THEN
1964 l_progress_mode := 'FUTURE';
1965 ELSE
1966 -- IF (G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE = 'PA_ASSIGNMENTS' OR
1967 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).OBJECT_TYPE = 'PA_DELIVERABLES')
1968 -- THEN
1969 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_PROG_WRONG_OBJ_TYPE',
1970 -- 'OBJECT_ID', G_TASK_PROGRESS_in_tbl(i).OBJECT_ID);
1971 --
1972 -- END IF;
1973 l_progress_mode := 'BACKDATED';
1974 END IF;
1975 ELSE
1976 l_progress_mode := 'FUTURE';
1977 END IF;*/
1978
1979 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).LATEST_AS_OF_DATE := l_latest_as_of_date;
1980 -- G_TASK_PROGRESS_in_tbl(G_TASK_PROGRESS_tbl_count).PROGRESS_MODE := l_progress_mode;
1981
1982 END LOOP;
1983
1984 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1985 FND_MSG_PUB.Count_And_Get
1986 ( p_count => p_msg_count
1987 ,p_data => p_msg_data
1988 );
1989 END IF;
1990 EXCEPTION
1991 WHEN FND_API.G_EXC_ERROR THEN
1992 p_return_status := FND_API.G_RET_STS_ERROR;
1993 FND_MSG_PUB.Count_And_Get
1994 ( p_count => p_msg_count
1995 ,p_data => p_msg_data
1996 );
1997 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1998 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1999 FND_MSG_PUB.Count_And_Get
2000 ( p_count => p_msg_count
2001 ,p_data => p_msg_data
2002 );
2003 WHEN OTHERS THEN
2004 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2005 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2006 FND_MSG_PUB.Add_Exc_Msg
2007 ( G_PKG_NAME
2008 , l_api_name
2009 );
2010 END IF;
2011 FND_MSG_PUB.Count_And_Get
2012 ( p_count => p_msg_count
2013 ,p_data => p_msg_data
2014 );
2015 END Load_Task_Progress;
2016
2017 PROCEDURE Execute_Update_Task_Progress
2018 ( p_api_version_number IN NUMBER
2019 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2020 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2021 , p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2022 , p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2023 , p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2024 )
2025 IS
2026
2027 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
2028 l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE_UPDATE_TASK_PROGRESS';
2029 l_return_status VARCHAR2(1):= 'S';
2030 l_msg_count INTEGER;
2031 l_msg_data VARCHAR2(2000);
2032 l_data VARCHAR2(2000);
2033 l_msg_index_out INTEGER;
2034 i INTEGER;
2035 l_application_short_name VARCHAR2(50) :='PA';
2036
2037 l_TASK_ID NUMBER;
2038 l_PM_TASK_REFERENCE VARCHAR2(150);
2039 l_PERCENT_COMPLETE NUMBER;
2040 l_DESCRIPTION VARCHAR2(250);
2041 l_OBJECT_ID NUMBER;
2042 l_OBJECT_VERSION_ID NUMBER;
2043 l_OBJECT_TYPE VARCHAR2(30);
2044 l_PROGRESS_STATUS_CODE VARCHAR2(150);
2045 l_PROGRESS_COMMENT VARCHAR2(4000);
2046 l_ACTUAL_START_DATE Date;
2047 l_ACTUAL_FINISH_DATE Date;
2048 l_ESTIMATED_START_DATE Date;
2049 l_ESTIMATED_FINISH_DATE Date;
2050 l_SCHEDULED_START_DATE Date;
2051 l_SCHEDULED_FINISH_DATE Date;
2052 l_TASK_STATUS VARCHAR2(150);
2053 l_EST_REMAINING_EFFORT NUMBER;
2054 l_ACTUAL_WORK_QUANTITY NUMBER;
2055
2056 l_project_id_out NUMBER;
2057 l_structure_id NUMBER;
2058 l_structure_version_id NUMBER;
2059 l_project_validation_flag VARCHAR2(1);
2060 l_rollup_flag VARCHAR2(1);
2061 l_bulk_load_flag VARCHAR2(1);
2062 l_progress_mode varchar2(10);
2063 l_latest_as_of_date Date;
2064 l_lowest_level_task VARCHAR2(1);
2065 -- Bug 3606627 : Added following 6 parameters
2066 l_etc_cost NUMBER;
2067 l_PM_DELIVERABLE_REFERENCE VARCHAR2(150);
2068 l_PM_TASK_ASSGN_REFERENCE VARCHAR2(150);
2069 l_ACTUAL_COST_TO_DATE NUMBER;
2070 l_ACTUAL_EFFORT_TO_DATE NUMBER;
2071 l_populate_pji_tables VARCHAR2(1) :='Y';
2072 l_rollup_table PA_SCHEDULE_OBJECTS_PVT.PA_SCHEDULE_OBJECTS_TBL_TYPE;
2073 l_max_as_of_date Date ; -- Bug 6917961
2074
2075 l_rollup varchar2(1); --bug 6717386
2076
2077 -- Bug 3606627 : Added curosrs c_get_structure_information, c_get_task_weight_method
2078 CURSOR c_get_structure_information(c_project_id NUMBER, c_structure_type VARCHAR2) IS
2079 select ppevs.proj_element_id, ppevs.element_version_id
2080 from pa_proj_structure_types ppst,
2081 pa_structure_types pst,
2082 pa_proj_elem_ver_structure ppevs
2083 where ppevs.project_id = c_project_id
2084 and ppevs.proj_element_id = ppst.proj_element_id
2085 and ppevs.status_code = 'STRUCTURE_PUBLISHED'
2086 and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'
2087 and ppst.structure_type_id = pst.structure_type_id
2088 and pst.structure_type_class_code = c_structure_type;
2089
2090 CURSOR c_get_task_weight_method(c_project_id NUMBER, c_structure_type VARCHAR2)
2091 IS
2092 SELECT task_weight_basis_code
2093 FROM pa_proj_progress_attr
2094 WHERE project_id = c_project_id
2095 AND structure_type = c_structure_type;
2096
2097 l_rollup_method pa_proj_progress_attr.task_weight_basis_code%TYPE; -- Bug 3606627
2098 l_wp_rollup_method pa_proj_progress_attr.task_weight_basis_code%TYPE; -- Bug 3606627
2099 l_fin_rollup_method pa_proj_progress_attr.task_weight_basis_code%TYPE; -- Bug 3606627
2100
2101 --maansari6/28 bug 3673618
2102 l_progress_updated_flag VARCHAR2(1) := 'N';
2103 l_raise_exception VARCHAR2(1) := 'N';
2104 l_baselined_str_ver_id NUMBER;
2105
2106 -- rtarway, BUG 3964278
2107 l_is_progress_status_null varchar2(1) := 'Y';
2108 G1_DEBUG_MODE VARCHAR2(1);
2109
2110 -- Bug 4186007 Begin
2111 l_msg_code VARCHAR2(32);
2112 l_base_struct_ver_id NUMBER;
2113 -- Bug 4186007 End
2114
2115 -- Bug 3994165 : Added variables below
2116 l_structure_sharing_code pa_projects_all.structure_sharing_code%TYPE;
2117 l_sharing_enabled varchar2(1) ;
2118 l_split_workplan varchar2(1) ;
2119 l_project_ids SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type() ;
2120 l_struture_version_ids SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.pa_num_tbl_type() ;
2121 l_proj_thru_dates_tbl SYSTEM.pa_date_tbl_type:= SYSTEM.pa_date_tbl_type() ;
2122
2123 -- Bug 8472681
2124 TYPE progress_err_rec_type IS RECORD (
2125 row_number VARCHAR2(25),
2126 task_name VARCHAR2(20),
2127 task_number VARCHAR2(25),
2128 data VARCHAR2(2000)
2129 );
2130
2131 TYPE progress_err_tbl_type IS TABLE OF progress_err_rec_type INDEX BY BINARY_INTEGER;
2132
2133 l_progress_err_tbl progress_err_tbl_type;
2134 l_progress_err_index NUMBER := 0;
2135
2136 BEGIN
2137
2138 IF FND_API.to_boolean(p_init_msg_list)
2139 THEN
2140 FND_MSG_PUB.initialize;
2141 END IF;
2142 g1_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2143 savepoint Execute_Update_Task_Progress;
2144
2145
2146 p_return_status := FND_API.G_RET_STS_SUCCESS;
2147 -- Bug 3606627 : Added call of PA_STATUS_PUB.Project_Level_Validations
2148 PA_STATUS_PUB.Project_Level_Validations
2149 (p_api_version_number => p_api_version_number
2150 ,p_msg_count => p_msg_count
2151 ,p_msg_data => p_msg_data
2152 ,p_return_status => l_return_status
2153 , p_project_id => G_PROJECT_ID
2154 , p_pm_project_reference => G_pm_project_reference
2155 , p_project_id_out => l_project_id_out
2156 );
2157 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2158 p_return_status := l_return_status;
2159 RAISE FND_API.G_EXC_ERROR;
2160 END IF;
2161
2162 G_PROJECT_ID := l_project_id_out;
2163
2164
2165 IF(G_TASK_PROGRESS_tbl_count = 0) THEN
2166 FND_MESSAGE.SET_NAME('PA','PA_NO_TASK_PROGRESS_UPDATE');
2167 FND_MSG_PUB.add;
2168 p_return_status := FND_API.G_RET_STS_ERROR;
2169 RAISE FND_API.G_EXC_ERROR;
2170 -- Bug 3606627 : Changed the following if condition to >=1 from =1
2171 ELSIF(G_TASK_PROGRESS_tbl_count >= 1) THEN
2172 --G_bulk_load_flag := 'N'; Bug 3606627
2173 G_bulk_load_flag := 'Y'; -- Bug 3606627
2174 --l_project_id_out := G_PROJECT_ID; Bug 3606627
2175 -- Bug 3606627
2176 OPEN c_get_structure_information(l_project_id_out, g_structure_type);
2177 FETCH c_get_structure_information INTO l_structure_id, l_structure_version_id;
2178 CLOSE c_get_structure_information;
2179
2180 -- Bug 3627315 : Added code to validate date against the project structure
2181 -- it won't be validated for each object
2182
2183 -- it has to be validated for each task for backdate and correct progress flows
2184 /* 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'
2185 THEN
2186 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2187 ,p_msg_name => 'PA_TP_INV_AOD2');
2188 p_return_status := FND_API.G_RET_STS_ERROR;
2189 RAISE FND_API.G_EXC_ERROR;
2190 END IF; */
2191
2192 l_baselined_str_ver_id := PA_PROJECT_STRUCTURE_UTILS.Get_Baseline_Struct_Ver(l_project_id_out); --maansari6/28 bug 3673618
2193
2194 --maansari7/6 bug 3742356
2195 if l_baselined_str_ver_id = -1
2196 then
2197 l_baselined_str_ver_id := l_structure_version_id;
2198 end if;
2199
2200 IF l_structure_version_id IS NOT NULL
2201 AND l_baselined_str_ver_id IS NOT NULL THEN --maansari6/28 bug 3673618
2202 PA_PROGRESS_PUB.populate_pji_tab_for_plan(
2203 p_api_version => p_api_version_number
2204 ,p_init_msg_list => p_init_msg_list
2205 ,p_commit => FND_API.G_FALSE --Bug 3754134
2206 ,p_calling_module => 'AMG'
2207 ,p_project_id => l_project_id_out
2208 ,p_structure_version_id => l_structure_version_id
2209 ,p_baselined_str_ver_id => l_baselined_str_ver_id --maansari6/28 bug 3673618
2210 ,x_return_status => l_return_status
2211 ,x_msg_count => l_msg_count
2212 ,x_msg_data => l_msg_data
2213 );
2214
2215 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2216 RAISE FND_API.G_EXC_ERROR;
2217 END IF;
2218 END IF;
2219 g_task_version_id_tbl.delete; -- Bug 4218507
2220 i := G_TASK_PROGRESS_in_tbl.first;
2221 WHILE i IS NOT NULL LOOP
2222 l_TASK_ID := G_TASK_PROGRESS_in_tbl(i).TASK_ID;
2223 l_PM_TASK_REFERENCE := G_TASK_PROGRESS_in_tbl(i).PM_TASK_REFERENCE;
2224 l_PERCENT_COMPLETE := G_TASK_PROGRESS_in_tbl(i).PERCENT_COMPLETE;
2225 l_DESCRIPTION := G_TASK_PROGRESS_in_tbl(i).DESCRIPTION;
2226 l_OBJECT_ID := G_TASK_PROGRESS_in_tbl(i).OBJECT_ID;
2227 l_OBJECT_VERSION_ID := G_TASK_PROGRESS_in_tbl(i).OBJECT_VERSION_ID;
2228 l_OBJECT_TYPE := G_TASK_PROGRESS_in_tbl(i).OBJECT_TYPE;
2229 l_PROGRESS_STATUS_CODE := G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE;
2230 l_PROGRESS_COMMENT := G_TASK_PROGRESS_in_tbl(i).PROGRESS_COMMENT;
2231 l_ACTUAL_START_DATE := G_TASK_PROGRESS_in_tbl(i).ACTUAL_START_DATE;
2232 l_ACTUAL_FINISH_DATE := G_TASK_PROGRESS_in_tbl(i).ACTUAL_FINISH_DATE;
2233 l_ESTIMATED_START_DATE := G_TASK_PROGRESS_in_tbl(i).ESTIMATED_START_DATE;
2234 l_ESTIMATED_FINISH_DATE := G_TASK_PROGRESS_in_tbl(i).ESTIMATED_FINISH_DATE;
2235 l_SCHEDULED_START_DATE := G_TASK_PROGRESS_in_tbl(i).SCHEDULED_START_DATE;
2236 l_SCHEDULED_FINISH_DATE := G_TASK_PROGRESS_in_tbl(i).SCHEDULED_FINISH_DATE;
2237 l_TASK_STATUS := G_TASK_PROGRESS_in_tbl(i).TASK_STATUS;
2238 l_EST_REMAINING_EFFORT := G_TASK_PROGRESS_in_tbl(i).EST_REMAINING_EFFORT;
2239 l_ACTUAL_WORK_QUANTITY := G_TASK_PROGRESS_in_tbl(i).ACTUAL_WORK_QUANTITY;
2240 -- Bug 3606627 : Added following 6 new parms assignment
2241 l_etc_cost := G_TASK_PROGRESS_in_tbl(i).etc_cost;
2242 l_PM_DELIVERABLE_REFERENCE := G_TASK_PROGRESS_in_tbl(i).PM_DELIVERABLE_REFERENCE;
2243 l_PM_TASK_ASSGN_REFERENCE := G_TASK_PROGRESS_in_tbl(i).PM_TASK_ASSGN_REFERENCE;
2244 l_ACTUAL_COST_TO_DATE := G_TASK_PROGRESS_in_tbl(i).ACTUAL_COST_TO_DATE;
2245 l_ACTUAL_EFFORT_TO_DATE := G_TASK_PROGRESS_in_tbl(i).ACTUAL_EFFORT_TO_DATE;
2246
2247
2248
2249 -- IF i = G_TASK_PROGRESS_in_tbl.first THEN
2250 -- l_populate_pji_tables := 'Y';
2251 -- ELSE
2252 -- l_populate_pji_tables := 'N';
2253 -- END IF;
2254
2255 -- Start of changes for bug 6717386
2256
2257 IF G_TASK_PROGRESS_in_tbl(i).OBJECT_TYPE = 'PA_ASSIGNMENTS'
2258 THEN
2259 begin
2260 select 'Y' into l_rollup from dual
2261 where exists (select 1 from pa_percent_completes
2262 where task_id = l_TASK_ID
2263 AND PROJECT_ID = l_project_id_out
2264 and object_type = 'PA_TASKS'
2265 and PROGRESS_STATUS_CODE is not null);
2266 G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE := 'PROGRESS_STAT_ON_TRACK';
2267 l_PROGRESS_STATUS_CODE := G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE; -- Bug 3754134
2268 exception WHEN NO_DATA_FOUND THEN
2269 null;
2270
2271 end;
2272
2273 END IF;
2274
2275 -- End of changes for bug 6717386
2276 /* Commented for bug 6717386
2277 IF G_TASK_PROGRESS_in_tbl(i).OBJECT_TYPE = 'PA_ASSIGNMENTS'
2278 THEN
2279 G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE := 'PROGRESS_STAT_ON_TRACK';
2280 l_PROGRESS_STATUS_CODE := G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE; -- Bug 3754134
2281 END IF; */
2282
2283 --Commented by rtarway for BUG 3901982
2284 --maansari6/28 bug 3673618
2285 /*IF G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE IS NOT NULL AND
2286 G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2287 THEN*/
2288
2289 -- Bug 10253952
2290 -- l_is_progress_status_null := 'Y'; -- Bug 6497559
2291
2292 --Added by rtarway for BUG 3901982
2293 IF (
2294 ( G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE IS NOT NULL AND
2295 G_TASK_PROGRESS_in_tbl(i).PROGRESS_STATUS_CODE <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2296 AND G_STRUCTURE_TYPE = 'WORKPLAN'
2297 ) OR
2298 ( l_PERCENT_COMPLETE IS NOT NULL AND
2299 l_PERCENT_COMPLETE <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND G_STRUCTURE_TYPE = 'FINANCIAL'
2300 )
2301 )
2302 THEN
2303 --rtarway, 3964278
2304 if G_STRUCTURE_TYPE = 'WORKPLAN' then
2305 l_is_progress_status_null := 'N';
2306 end if;
2307
2308 l_progress_updated_flag := 'Y'; --maansari6/28 bug 3673618
2309
2310
2311 PA_STATUS_PUB.UPDATE_PROGRESS
2312 (p_api_version_number => p_api_version_number
2313 ,p_msg_count => l_msg_count
2314 ,p_msg_data => l_msg_data
2315 ,p_commit => FND_API.G_FALSE -- Bug 3754134 Added this
2316 ,p_return_status => l_return_status
2317 , p_project_id => l_project_id_out
2318 , p_pm_project_reference => g_pm_project_reference
2319 , p_task_id => l_task_id
2320 , p_pm_task_reference => l_pm_task_reference
2321 , p_as_of_date => g_as_of_date
2322 , p_percent_complete => l_percent_complete
2323 , p_pm_product_code => g_pm_product_code
2324 , p_description => l_description
2325 , p_object_id => l_object_id
2326 , p_object_version_id => l_object_version_id
2327 , p_object_type => l_object_type
2328 , p_progress_status_code => l_progress_status_code
2329 , p_progress_comment => l_progress_comment
2330 , p_actual_start_date => l_actual_start_date
2331 , p_actual_finish_date => l_actual_finish_date
2332 , p_estimated_start_date => l_estimated_start_date
2333 , p_estimated_finish_date => l_estimated_finish_date
2334 , p_scheduled_start_date => l_scheduled_start_date
2335 , p_scheduled_finish_date => l_scheduled_finish_date
2336 , p_task_status => l_task_status
2337 , p_structure_type => g_structure_type
2338 , p_est_remaining_effort => l_est_remaining_effort
2339 , p_actual_work_quantity => l_actual_work_quantity
2340 , p_etc_cost => l_etc_cost -- bug 3606627
2341 , p_pm_deliverable_reference => l_pm_deliverable_reference -- bug 3606627
2342 , p_pm_task_assgn_reference => l_pm_task_assgn_reference -- bug 3606627
2343 , p_actual_cost_to_date => l_actual_cost_to_date -- bug 3606627
2344 , p_actual_effort_to_date => l_actual_effort_to_date -- bug 3606627
2345 , p_populate_pji_tables => 'N' -- bug 3606627
2346 , p_rollup_entire_wbs => 'Y' -- bug 3606627
2347 );
2348
2349 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2350 p_return_status := l_return_status;
2351
2352 FND_MSG_PUB.get (
2353 p_msg_index => l_msg_count,
2354 p_encoded => FND_API.G_FALSE,
2355 p_data => l_data,
2356 p_msg_index_out => l_msg_index_out );
2357
2358 IF l_data IS NOT NULL THEN
2359 FND_MSG_PUB.DELETE_MSG(p_msg_index => l_msg_count);
2360
2361 /* Commenting for bug 8472681
2362 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_TASK_NAME_NUM_ERR',
2363 'ROWNUM', 'ROW# '||i,--Added by rtarway for bug 4293075
2364 'TASK_NAME', G_TASK_PROGRESS_in_tbl(i).TASK_NAME,
2365 'TASK_NUMBER', G_TASK_PROGRESS_in_tbl(i).TASK_NUMBER,
2366 'MESSAGE', l_data);
2367 */
2368
2369 -- Bug 8472681
2370 l_progress_err_index := l_progress_err_index + 1;
2371 l_progress_err_tbl(l_progress_err_index).row_number := 'ROW# '||i;
2372 l_progress_err_tbl(l_progress_err_index).task_name := G_TASK_PROGRESS_in_tbl(i).TASK_NAME;
2373 l_progress_err_tbl(l_progress_err_index).task_number := G_TASK_PROGRESS_in_tbl(i).TASK_NUMBER;
2374 l_progress_err_tbl(l_progress_err_index).data := l_data;
2375
2376 END IF;
2377 l_raise_exception := l_return_status; --maansari6/28 bug 3673618
2378
2379 END IF;
2380 END IF;
2381
2382
2383 --added for bug 9839893
2384
2385 i:= G_TASK_PROGRESS_in_tbl.next(i); -- Bug 6497559
2386 END LOOP;
2387
2388
2389 FOR z IN 1..l_progress_err_tbl.COUNT LOOP
2390 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_TASK_NAME_NUM_ERR',
2391 'ROWNUM', 'ROW# '||l_progress_err_tbl(z).row_number,
2392 'TASK_NAME', l_progress_err_tbl(z).task_name,
2393 'TASK_NUMBER', l_progress_err_tbl(z).task_number,
2394 'MESSAGE', l_progress_err_tbl(z).data);
2395 END LOOP;
2396 --rbruno
2397
2398
2399 -- rtarway, BUG 3964278
2400 IF G_STRUCTURE_TYPE = 'WORKPLAN' and l_is_progress_status_null = 'Y'
2401 THEN
2402
2403 /*Commenting the following for bug 10146713 *
2404
2405 IF g_pm_product_code = 'MSPROJECT'
2406 THEN
2407 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2408 ,p_msg_name => 'PA_TP_STATUS_NOT_DEFINED_MSP'
2409 ,p_token1 => 'TASK_NAME'
2410 ,p_value1 => G_TASK_PROGRESS_in_tbl(i).TASK_NAME
2411 ,p_token2 => 'TASK_NUMBER'
2412 ,p_value2 => G_TASK_PROGRESS_in_tbl(i).TASK_NUMBER);
2413 p_return_status := FND_API.G_RET_STS_ERROR;
2414 RAISE FND_API.G_EXC_ERROR;
2415 ELSE
2416 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2417 ,p_msg_name => 'PA_TP_STATUS_NOT_DEFINED_AMG'
2418 ,p_token1 => 'TASK_ID'
2419 ,p_value1 => G_TASK_PROGRESS_in_tbl(i).TASK_ID
2420 );
2421 p_return_status := FND_API.G_RET_STS_ERROR;
2422 RAISE FND_API.G_EXC_ERROR;
2423 END IF;
2424
2425 * Commenting ends for bug 10146713 */
2426
2427 /* Added new code for Bug 10146713 Below *
2428 * Porting fix of 7133440. We need not show tokenzied message here. *
2429 * This was causing a Exception in case we have no task progress *
2430 * Entered from MSP. */
2431 -- Bug 10077520
2432 IF G_PM_PRODUCT_CODE = 'MSPROJECT' AND
2433 G_TASK_PROGRESS_IN_TBL(G_TASK_PROGRESS_IN_TBL.FIRST).OBJECT_TYPE = 'PA_ASSIGNMENTS' THEN
2434 NULL; -- do nothing
2435 ELSE
2436 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2437 ,p_msg_name => 'PA_TP_STATUS_NOT_DEFINED' --Bug 6530100 MSP Messages
2438 );
2439 p_return_status := FND_API.G_RET_STS_ERROR;
2440 RAISE FND_API.G_EXC_ERROR;
2441 END IF;
2442 /* New code for Bug 10146713 ends */
2443
2444 END IF;
2445
2446 /* commenting for bug 9839893
2447 i := G_TASK_PROGRESS_in_tbl.next(i); -- Bug 6497559
2448 END LOOP;
2449
2450 -- Bug 8472681 - Add messages back to the message stack
2451 FOR z IN 1..l_progress_err_tbl.COUNT LOOP
2452 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_TASK_NAME_NUM_ERR',
2453 'ROWNUM', 'ROW# '||l_progress_err_tbl(z).row_number,
2454 'TASK_NAME', l_progress_err_tbl(z).task_name,
2455 'TASK_NUMBER', l_progress_err_tbl(z).task_number,
2456 'MESSAGE', l_progress_err_tbl(z).data);
2457
2458 END LOOP;
2459 */
2460
2461 --maansari6/28 bug 3673618
2462 IF l_progress_updated_flag = 'N'
2463 THEN
2464 return;
2465 END IF;
2466
2467 IF (l_raise_exception = 'E' )
2468 THEN
2469 RAISE FND_API.G_EXC_ERROR;
2470 ELSIF l_raise_exception = 'U'
2471 THEN
2472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2473 END IF;
2474 --maansari6/28 bug 3673618
2475
2476
2477 -- Bug 3606627 Begin
2478 OPEN c_get_task_weight_method(l_project_id_out, g_structure_type);
2479 FETCH c_get_task_weight_method INTO l_rollup_method;
2480 CLOSE c_get_task_weight_method;
2481
2482 IF g_structure_type = 'WORKPLAN' THEN
2483 l_wp_rollup_method := l_rollup_method;
2484 ELSE
2485 l_fin_rollup_method := l_rollup_method;
2486 END IF;
2487 -- Bug 3994165 : Moved call of MAINTAIN_ACTUAL_AMT_WRP here
2488
2489 l_structure_sharing_code := PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(l_project_id_out);
2490 l_sharing_Enabled := PA_PROJECT_STRUCTURE_UTILS.check_sharing_enabled(l_project_id_out);
2491 IF (l_sharing_Enabled = 'N' OR (l_sharing_Enabled = 'Y' AND l_structure_sharing_code <> 'SHARE_FULL')) AND g_structure_type = 'WORKPLAN' THEN
2492 l_split_workplan := 'Y';
2493 ELSE
2494 l_split_workplan := 'N';
2495 END IF;
2496
2497 IF l_split_workplan = 'Y' AND l_structure_version_id IS NOT NULL THEN
2498 BEGIN
2499 Pa_Task_Pub1.G_CALL_PJI_ROLLUP := 'N';
2500 -- This flag is set so that plan_update from MAINTAIN_ACTUAL_AMT_WRP
2501 -- is not called. Actually it gets called but PJI code does not do anything.
2502
2503 l_project_ids.extend(1);
2504 l_project_ids(1) := l_project_id_out;
2505 l_struture_version_ids.extend(1);
2506 l_struture_version_ids(1) := l_structure_version_id;
2507 l_proj_thru_dates_tbl.extend(1);
2508 l_proj_thru_dates_tbl(1) := trunc(g_as_of_date); -- 5294838
2509 PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_WRP
2510 (P_PROJECT_ID_TAB => l_project_ids,
2511 P_WP_STR_VERSION_ID_TAB => l_struture_version_ids,
2512 P_ACTUALS_THRU_DATE => l_proj_thru_dates_tbl,
2513 P_CALLING_CONTEXT => 'WP_PROGRESS',
2514 P_EXTRACTION_TYPE => 'INCREMENTAL',
2515 X_RETURN_STATUS => l_return_status,
2516 X_MSG_COUNT => l_msg_count,
2517 X_MSG_DATA => l_msg_data
2518 );
2519
2520
2521 Pa_Task_Pub1.G_CALL_PJI_ROLLUP := null ;
2522
2523 delete from PA_PROG_ACT_BY_PERIOD_TEMP where project_id = l_project_id_out
2524 AND structure_version_id = l_structure_version_id;
2525 EXCEPTION
2526 WHEN OTHERS THEN
2527 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STATUS_PUB',
2528 p_procedure_name => 'execute_update_progress',
2529 p_error_text => SUBSTRB('PA_FP_MAINTAIN_ACTUAL_PUB.MAINTAIN_ACTUAL_AMT_WRP:'||SQLERRM,1,120));
2530 RAISE FND_API.G_EXC_ERROR;
2531 END;
2532 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2533 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2534 p_msg_name => l_msg_data);
2535 p_return_status := 'E';
2536 RAISE FND_API.G_EXC_ERROR;
2537 END IF;
2538 END IF;
2539 -- Bug 4186007 Begin
2540 BEGIN
2541 PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE (x_msg_code => l_msg_code,
2542 x_return_status => l_return_status);
2543 EXCEPTION
2544 WHEN OTHERS THEN
2545 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STATUS_PUB',
2546 p_procedure_name => 'EXECUTE_UPDATE_TASK_PROGRESS',
2547 p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_MAINT.PLAN_UPDATE Failed:'||SQLERRM,1,120));
2548 RAISE FND_API.G_EXC_ERROR;
2549 END;
2550
2551 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2552 p_return_status := l_return_status;
2553 RAISE FND_API.G_EXC_ERROR;
2554 END IF;
2555
2556
2557 l_base_struct_ver_id := pa_project_structure_utils.get_baseline_struct_ver(l_project_id_out);
2558
2559 IF (l_base_struct_ver_id = -1) THEN
2560 l_base_struct_ver_id := l_structure_version_id;
2561 END IF;
2562
2563 -- 4392189 : Changed call of populate_workplan_data to populate_pji_tab_for_plan
2564 PA_PROGRESS_PUB.POPULATE_PJI_TAB_FOR_PLAN(
2565 p_calling_module => 'AMG'
2566 ,p_project_id => l_project_id_out
2567 ,p_structure_version_id => l_structure_version_id
2568 ,p_baselined_str_ver_id => l_base_struct_ver_id
2569 ,p_program_rollup_flag => 'Y'
2570 ,p_calling_context => 'SUMMARIZE'
2571 ,p_as_of_date => trunc(g_as_of_date) -- 5294838
2572 ,x_return_status => l_return_status
2573 ,x_msg_count => l_msg_count
2574 ,x_msg_data => l_msg_data
2575 );
2576 /*
2577 BEGIN
2578 PJI_FM_XBS_ACCUM_UTILS.populate_workplan_data(
2579 p_project_id => l_project_id_out,
2580 p_struct_ver_id => l_structure_version_id,
2581 p_base_struct_ver_id => l_base_struct_ver_id,
2582 x_return_status => l_return_status,
2583 x_msg_code => l_msg_code
2584 );
2585 EXCEPTION
2586 WHEN OTHERS THEN
2587 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_STATUS_PUB',
2588 p_procedure_name => 'EXECUTE_UPDATE_TASK_PROGRESS',
2589 p_error_text => SUBSTRB('Call of PJI_FM_XBS_ACCUM_UTILS.populate_workplan_data: failed'||SQLERRM,1,120));
2590 RAISE FND_API.G_EXC_ERROR;
2591 END;
2592 */
2593
2594 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2595 p_return_status := l_return_status;
2596 RAISE FND_API.G_EXC_ERROR;
2597 END IF;
2598
2599 -- Bug 4186007 End
2600
2601
2602 PA_PROGRESS_PVT.ROLLUP_PROGRESS_PVT(
2603 p_init_msg_list => p_init_msg_list
2604 ,p_commit => FND_API.G_FALSE -- Bug 3754134 instead of passing p_commit passing fasle
2605 ,p_calling_module => 'AMG' --bug 3673618
2606 ,p_project_id => l_project_id_out
2607 ,p_structure_version_id => l_structure_version_id
2608 ,p_as_of_date => trunc(g_as_of_date) -- 5294838
2609 ,p_wp_rollup_method => l_wp_rollup_method
2610 ,p_fin_rollup_method => l_fin_rollup_method
2611 ,p_rollup_entire_wbs => 'Y'
2612 ,p_structure_type => g_structure_type
2613 ,p_task_version_id_tbl => g_task_version_id_tbl -- Bug 4218507
2614 ,x_return_status => l_return_status
2615 ,x_msg_count => l_msg_count
2616 ,x_msg_data => l_msg_data);
2617
2618 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2619 p_return_status := l_return_status;
2620 RAISE FND_API.G_EXC_ERROR;
2621 END IF;
2622
2623 PA_PROGRESS_PUB.ROLLUP_FUTURE_PROGRESS_PVT(
2624 p_project_id => l_project_id_out
2625 --,P_OBJECT_TYPE => l_object_type
2626 --,P_OBJECT_ID => l_task_id -- p_task_id 3603636
2627 --,p_object_version_id => l_task_version_id
2628 ,p_as_of_date => trunc(g_as_of_date) -- 5294838
2629 --,p_lowest_level_task => NVL( l_lowest_level_task, 'N' )
2630 ,p_calling_module => 'AMG'
2631 ,p_structure_type => g_structure_type
2632 ,p_structure_version_id => l_structure_version_id
2633 ,p_fin_rollup_method => l_fin_rollup_method
2634 ,p_wp_rollup_method => l_wp_rollup_method
2635 ,p_rollup_entire_wbs => 'Y'
2636 ,x_return_status => l_return_status
2637 ,x_msg_count => l_msg_count
2638 ,x_msg_data => l_msg_data
2639 );
2640
2641 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2642 p_return_status := l_return_status;
2643 RAISE FND_API.G_EXC_ERROR;
2644 END IF;
2645 -- Bug 3606627 End
2646 END IF; --(G_TASK_PROGRESS_tbl_count >= 1) THEN Bug 3606627 : Added Ebd IF here itself
2647 -- BUG 4080922 Adding Program Rollup Code : rtarway
2648 IF g_structure_type = 'WORKPLAN' AND l_structure_version_id IS NOT NULL
2649 THEN
2650 IF g1_debug_mode = 'Y' THEN
2651 pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_PROGRESS', x_Msg => 'Calling program_rollup_pvt', x_Log_Level=> 3);
2652 END IF;
2653
2654 pa_progress_pvt.program_rollup_pvt(
2655 p_init_msg_list => 'F'
2656 ,p_commit => 'F'
2657 ,p_calling_module => 'AMG'
2658 ,p_validate_only => 'F'
2659 ,p_project_id => l_project_id_out
2660 ,p_as_of_date => trunc(g_as_of_date) -- 5294838
2661 ,p_structure_type => g_structure_type
2662 ,p_structure_ver_id => l_structure_version_id
2663 ,x_return_status => l_return_status
2664 ,x_msg_count => l_msg_count
2665 ,x_msg_data => l_msg_data);
2666
2667 IF g1_debug_mode = 'Y' THEN
2668 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);
2669 END IF;
2670
2671 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2672 p_return_status := l_return_status;
2673 RAISE FND_API.G_EXC_ERROR;
2674 END IF;
2675
2676 END IF;
2677 -- End Add 4080922
2678
2679 -- Bug 3606627 : Commenthing the below complete code
2680 /* Bug 3606627
2681 ELSE
2682 G_bulk_load_flag := 'N';
2683 PA_STATUS_PUB.Project_Level_Validations
2684 (p_api_version_number => p_api_version_number
2685 ,p_msg_count => p_msg_count
2686 ,p_msg_data => p_msg_data
2687 ,p_return_status => l_return_status
2688 , p_project_id => G_PROJECT_ID
2689 , p_pm_project_reference => G_pm_project_reference
2690 , p_project_id_out => l_project_id_out
2691 );
2692 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2693 p_return_status := l_return_status;
2694 RAISE FND_API.G_EXC_ERROR;
2695 END IF;
2696 G_PROJECT_ID := l_project_id_out;
2697
2698 l_structure_version_id := PA_PROJECT_STRUCTURE_UTILS.get_latest_wp_version(l_project_id_out);
2699 */
2700 /*
2701 select STRUCTURE_TYPE into G_STRUCTURE_TYPE
2702 from PA_STRUCT_VERSIONS_LOV_AMG_V where STRUCTURE_VERSION_ID = l_structure_version_id;
2703 */
2704 /*Bug 3606627
2705 IF(G_STRUCTURE_TYPE = 'FINANCIAL') then
2706 IF(PA_PROJECT_STRUCTURE_UTILS.CHECK_SHARING_ENABLED(l_project_id_out) = 'Y') THEN
2707 l_rollup_flag := 'Y';
2708 G_STRUCTURE_TYPE := 'WORKPLAN';
2709 ELSE
2710 l_rollup_flag := 'N';
2711 END IF;
2712 else
2713 l_rollup_flag := 'Y';
2714 end if;
2715
2716
2717 PA_PROGRESS_PUB.INSERT_TASK_PROGRESSES(
2718 p_api_version => p_api_version_number,
2719 p_calling_module => 'AMG',
2720 p_project_id => l_project_id_out,
2721 p_pm_product_code => G_PM_PRODUCT_CODE,
2722 p_structure_version_id => l_structure_version_id,
2723 p_structure_type => G_STRUCTURE_TYPE,
2724 p_as_of_date => G_AS_OF_DATE,
2725 p_task_progress_list_table => G_TASK_PROGRESS_in_tbl,
2726 p_return_status => l_return_status,
2727 p_msg_count => p_msg_count,
2728 p_msg_data => p_msg_data
2729 );
2730 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2731 p_return_status := l_return_status;
2732 RAISE FND_API.G_EXC_ERROR;
2733 END IF;
2734
2735 IF(l_rollup_flag = 'Y') then
2736 PA_PROGRESS_PUB.ROLLUP_TASK_PROGRESSES(
2737 p_api_version => p_api_version_number,
2738 p_calling_module => 'AMG',
2739 p_progress_mode => l_progress_mode,
2740 p_project_id => l_project_id_out,
2741 p_structure_version_id => l_structure_version_id,
2742 p_as_of_date => G_AS_OF_DATE,
2743 p_rollup_table => l_rollup_table,
2744 p_return_status => l_return_status,
2745 p_msg_count => p_msg_count,
2746 p_msg_data => p_msg_data
2747 );
2748 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2749 p_return_status := l_return_status;
2750 RAISE FND_API.G_EXC_ERROR;
2751 END IF;
2752 */
2753
2754
2755 /* Commented out for Progress Management Changes. Bug # 3420093.
2756
2757 PA_PROGRESS_PUB.UPDATE_ROLLUP_PROGRESSES(
2758 p_api_version => p_api_version_number,
2759 p_calling_module => 'AMG',
2760 p_progress_mode => l_progress_mode,
2761 p_project_id => l_project_id_out,
2762 p_structure_version_id => l_structure_version_id,
2763 p_structure_type => G_STRUCTURE_TYPE,
2764 p_as_of_date => G_AS_OF_DATE,
2765 p_rollup_table => l_rollup_table,
2766 p_task_progress_list_table => G_TASK_PROGRESS_in_tbl,
2767 p_return_status => l_return_status,
2768 p_msg_count => p_msg_count,
2769 p_msg_data => p_msg_data
2770 );
2771 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2772 p_return_status := l_return_status;
2773 RAISE FND_API.G_EXC_ERROR;
2774 END IF;
2775
2776 Commented out for Progress Management Changes. Bug # 3420093. */
2777
2778 /* Progress Management Changes. Bug # 3420093. */
2779
2780 /*Bug 3606627
2781 pa_status_pub.update_task_progress_amg(
2782 p_api_version => p_api_version_number,
2783 p_calling_module => 'AMG',
2784 p_progress_mode => l_progress_mode,
2785 p_project_id => l_project_id_out,
2786 p_structure_version_id => l_structure_version_id,
2787 p_structure_type => G_STRUCTURE_TYPE,
2788 p_as_of_date => G_AS_OF_DATE,
2789 p_task_progress_list_table => G_TASK_PROGRESS_in_tbl,
2790 x_return_status => l_return_status,
2791 x_msg_count => p_msg_count,
2792 x_msg_data => p_msg_data
2793 );
2794 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2795 p_return_status := l_return_status;
2796 RAISE FND_API.G_EXC_ERROR;
2797 END IF;
2798 */
2799
2800 /* Progress Management Changes. Bug # 3420093. */
2801
2802 /*Bug 3606627
2803 i := G_TASK_PROGRESS_in_tbl.first;
2804
2805 WHILE i IS NOT NULL LOOP
2806 l_TASK_ID := G_TASK_PROGRESS_in_tbl(i).TASK_ID;
2807 l_PM_TASK_REFERENCE := G_TASK_PROGRESS_in_tbl(i).PM_TASK_REFERENCE;
2808 l_PERCENT_COMPLETE := G_TASK_PROGRESS_in_tbl(i).PERCENT_COMPLETE;
2809 l_DESCRIPTION := G_TASK_PROGRESS_in_tbl(i).DESCRIPTION;
2810 l_OBJECT_ID := G_TASK_PROGRESS_in_tbl(i).OBJECT_ID;
2811 l_OBJECT_VERSION_ID := G_TASK_PROGRESS_in_tbl(i).OBJECT_VERSION_ID;
2812 l_OBJECT_TYPE := G_TASK_PROGRESS_in_tbl(i).OBJECT_TYPE;
2813 l_progress_mode := G_TASK_PROGRESS_in_tbl(i).PROGRESS_MODE;
2814 */
2815 /*
2816 l_latest_as_of_date := PA_PROGRESS_UTILS.GET_LATEST_AS_OF_DATE(l_object_id);
2817 IF l_latest_as_of_date is NOT NULL THEN
2818 IF G_AS_OF_DATE >= l_latest_as_of_date THEN
2819 l_progress_mode := 'FUTURE';
2820 ELSE
2821 l_progress_mode := 'BACKDATED';
2822 END IF;
2823 ELSE
2824 l_progress_mode := 'FUTURE';
2825 END IF;
2826 */
2827
2828 /* Bug 3606627
2829 if l_progress_mode <> 'BACKDATED' then
2830 IF l_object_type = 'PA_TASKS'
2831 THEN
2832 l_lowest_level_task := PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(p_task_version_id => l_OBJECT_VERSION_ID);
2833 ELSE
2834 l_lowest_level_task := 'N';
2835 END IF;
2836
2837 PA_PROGRESS_PUB.ROLLUP_FUTURE_PROGRESS_PVT(
2838 p_project_id => l_project_id_out
2839 ,P_OBJECT_TYPE => L_OBJECT_TYPE
2840 ,P_OBJECT_ID => L_OBJECT_ID
2841 ,p_object_version_id => L_object_version_id
2842 ,p_as_of_date => G_AS_OF_DATE
2843 ,p_lowest_level_task => NVL( l_lowest_level_task, 'N' )
2844 ,x_return_status => l_return_status
2845 ,x_msg_count => l_msg_count
2846 ,x_msg_data => l_msg_data
2847 );
2848 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2849 p_return_status := l_return_status;
2850 RAISE FND_API.G_EXC_ERROR;
2851 END IF;
2852
2853 IF ( NVL( l_lowest_level_task, 'N' ) = 'Y' )
2854 THEN
2855 PA_TASK_PVT1.Update_Dates_To_All_Versions(
2856 p_project_id => l_project_id_out
2857 ,p_element_version_id => l_object_version_id
2858 ,x_return_status => l_return_status
2859 ,x_msg_count => l_msg_count
2860 ,x_msg_data => l_msg_data );
2861 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2862 THEN
2863 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2864 p_msg_name => l_msg_data
2865 );
2866 p_msg_data := l_msg_data;
2867 p_return_status := 'E';
2868 RAISE FND_API.G_EXC_ERROR;
2869 END IF;
2870 begin
2871 Select ppev1.proj_element_id
2872 into l_structure_id
2873 from pa_proj_element_versions ppev1, pa_proj_element_versions ppev2
2874 where ppev2.element_version_id = l_object_version_id
2875 and ppev2.project_id = ppev1.project_id
2876 and ppev2.parent_structure_version_id = ppev1.element_version_id;
2877 exception when others then
2878 l_structure_id := -999;
2879 end;
2880 IF ((PA_WORKPLAN_ATTR_UTILS.CHECK_AUTO_DATE_SYNC_ENABLED(l_structure_id) = 'Y')
2881 AND
2882 (PA_PROJECT_STRUCTURE_UTILS.CHECK_SHARING_ENABLED(l_project_id_out) = 'Y')) THEN
2883 --copy to transaction dates
2884 PA_PROJECT_DATES_PUB.COPY_PROJECT_DATES(
2885 p_validate_only => FND_API.G_FALSE
2886 ,p_project_id => l_project_id_out
2887 ,x_return_status => l_return_status
2888 ,x_msg_count => l_msg_count
2889 ,x_msg_data => l_msg_data);
2890 END IF;
2891 END IF;
2892 end if; ------ p_progress_mode <> backdated
2893 i := G_TASK_PROGRESS_in_tbl.next(i);
2894 END LOOP;
2895 end if; -------l_rollup_flag = 'Y'
2896 end if;
2897 */
2898
2899 /* bug 6917961 */
2900
2901 select nvl(max(as_of_date),sysdate) into l_max_as_of_date
2902 from pa_progress_rollup where project_id= l_project_id_out;
2903
2904
2905
2906 update pa_progress_rollup
2907 set COMPLETED_PERCENTAGE = null
2908 where project_id = l_project_id_out
2909 AND OBJECT_TYPE ='PA_TASKS'
2910 AND CURRENT_FLAG='Y'
2911 AND as_of_date=l_max_as_of_date
2912 and nvl(COMPLETED_PERCENTAGE,0) = nvl(eff_rollup_percent_comp, -1);
2913
2914 /* bug 6917961 */
2915
2916 IF (p_return_status = FND_API.G_RET_STS_SUCCESS )
2917 then
2918 IF FND_API.to_boolean(p_commit)
2919 THEN
2920 COMMIT;
2921 END IF;
2922 ELSE
2923 ROLLBACK TO Execute_Update_Task_Progress;
2924 FND_MSG_PUB.Count_And_Get
2925 ( p_count => p_msg_count
2926 ,p_data => p_msg_data
2927 );
2928 END IF;
2929 G_bulk_load_flag :='N';
2930 G_TASK_PROGRESS_in_tbl.delete;
2931 G_TASK_PROGRESS_tbl_count := 0;
2932 EXCEPTION
2933 WHEN FND_API.G_EXC_ERROR THEN
2934 p_return_status := FND_API.G_RET_STS_ERROR;
2935 ROLLBACK TO Execute_Update_Task_Progress;
2936 FND_MSG_PUB.Count_And_Get
2937 ( p_count => p_msg_count
2938 ,p_data => p_msg_data
2939 );
2940 G_bulk_load_flag :='N';
2941 G_TASK_PROGRESS_in_tbl.delete;
2942 G_TASK_PROGRESS_tbl_count := 0;
2943 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2944 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2945 ROLLBACK TO Execute_Update_Task_Progress;
2946 FND_MSG_PUB.Count_And_Get
2947 ( p_count => p_msg_count
2948 ,p_data => p_msg_data
2949 );
2950 G_bulk_load_flag :='N';
2951 G_TASK_PROGRESS_in_tbl.delete;
2952 G_TASK_PROGRESS_tbl_count := 0;
2953 WHEN OTHERS THEN
2954 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2955 ROLLBACK TO Execute_Update_Task_Progress;
2956 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2957 FND_MSG_PUB.Add_Exc_Msg
2958 ( G_PKG_NAME
2959 , l_api_name
2960 );
2961 END IF;
2962 FND_MSG_PUB.Count_And_Get
2963 ( p_count => p_msg_count
2964 ,p_data => p_msg_data
2965 );
2966 G_bulk_load_flag :='N';
2967 G_TASK_PROGRESS_in_tbl.delete;
2968 G_TASK_PROGRESS_tbl_count := 0;
2969 END Execute_Update_Task_Progress;
2970
2971
2972 PROCEDURE Project_Level_Validations
2973 (p_api_version_number IN NUMBER
2974 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2975 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2976 , p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2977 , p_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2978 , p_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2979 , p_project_id IN NUMBER
2980 , p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2981 , p_project_id_out OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2982 )
2983 IS
2984 l_api_name CONSTANT VARCHAR2(30) := 'Project_Level_Validations';
2985 l_api_version_number CONSTANT NUMBER := G_API_VERSION_NUMBER;
2986 l_msg_count INTEGER;
2987 l_msg_data VARCHAR2(2000);
2988 l_return_status VARCHAR2(1):= 'S';
2989 l_project_id_out NUMBER := 0;
2990 l_function_allowed VARCHAR2(1);
2991 l_resp_id NUMBER := 0;
2992 l_pm_product_code pa_percent_completes.pm_product_code%TYPE := NULL;
2993 l_description pa_percent_completes.description%TYPE := NULL;
2994 l_as_of_date DATE;
2995 l_current_flag VARCHAR2(1);
2996 l_dummy VARCHAR2(1);
2997 l_module_name VARCHAR2(80);
2998 l_user_id NUMBER := 0;
2999 l_date_computed DATE;
3000
3001
3002 BEGIN
3003 IF FND_API.to_boolean(p_init_msg_list)
3004 THEN
3005 FND_MSG_PUB.initialize;
3006 END IF;
3007
3008 p_return_status := FND_API.G_RET_STS_SUCCESS;
3009
3010
3011 IF NOT FND_API.Compatible_API_Call ( l_api_version_number ,
3012 p_api_version_number ,
3013 l_api_name,
3014 G_PKG_NAME)
3015
3016 THEN
3017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3018 END IF;
3019
3020 --Moved from below for project security changes. --Bug 2471668
3021
3022 PA_PROJECT_PVT.Convert_pm_projref_to_id
3023 ( p_pm_project_reference => p_pm_project_reference
3024 , p_pa_project_id => p_project_id
3025 , p_out_project_id => l_project_id_out
3026 , p_return_status => l_return_status
3027 );
3028
3029
3030 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3032 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3033 RAISE FND_API.G_EXC_ERROR;
3034 END IF;
3035 p_project_id_out := l_project_id_out;
3036
3037 --Bug 2471668
3038
3039 l_user_id := FND_GLOBAL.User_id;
3040 l_resp_id := FND_GLOBAL.Resp_id;
3041 --l_module_name := p_pm_product_code||'.'||'PA_PM_UPDATE_PROJ_PROGRESS';
3042
3043 pa_security.initialize (X_user_id => l_user_id,
3044 X_calling_module => l_module_name);
3045
3046 -- Actions performed using the APIs would be subject to
3047 -- function security. If the responsibility does not allow
3048 -- such functions to be executed, the API should not proceed further
3049 -- since the user does not have access to such functions
3050
3051 --Bug 2471668
3052 PA_INTERFACE_UTILS_PUB.g_project_id := l_project_id_out;
3053
3054 PA_PM_FUNCTION_SECURITY_PUB.check_function_security
3055 (p_api_version_number => p_api_version_number,
3056 p_responsibility_id => l_resp_id,
3057 p_function_name => 'PA_PM_UPDATE_PROJ_PROGRESS',
3058 p_msg_count => l_msg_count,
3059 p_msg_data => l_msg_data,
3060 p_return_status => l_return_status,
3061 p_function_allowed => l_function_allowed );
3062
3063
3064 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3065 THEN
3066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3067
3068 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3069 THEN
3070 RAISE FND_API.G_EXC_ERROR;
3071 END IF;
3072 IF l_function_allowed = 'N' THEN
3073 FND_MESSAGE.SET_NAME('PA','PA_FUNCTION_SECURITY_ENFORCED');
3074 FND_MSG_PUB.add;
3075 p_return_status := FND_API.G_RET_STS_ERROR;
3076 RAISE FND_API.G_EXC_ERROR;
3077 END IF;
3078 IF FND_API.to_boolean(p_init_msg_list)
3079 THEN
3080 FND_MSG_PUB.initialize;
3081 END IF;
3082
3083 -- VALUE LAYER -----------------------------------------------------------------------
3084
3085 IF pa_security.allow_query (x_project_id => l_project_id_out ) = 'N' THEN
3086
3087 -- The user does not have query privileges on this project
3088 -- Hence, cannot update the project.Raise error
3089
3090 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
3091 FND_MSG_PUB.add;
3092 p_return_status := FND_API.G_RET_STS_ERROR;
3093 RAISE FND_API.G_EXC_ERROR;
3094 ELSE
3095 -- If the user has query privileges, then check whether
3096 -- update privileges are also available
3097 IF pa_security.allow_update (x_project_id => l_project_id_out ) = 'N'
3098 THEN
3099
3100 -- The user does not have update privileges on this project
3101 -- Hence , raise error
3102
3103 FND_MESSAGE.SET_NAME('PA','PA_PROJECT_SECURITY_ENFORCED');
3104 FND_MSG_PUB.add;
3105 p_return_status := FND_API.G_RET_STS_ERROR;
3106 RAISE FND_API.G_EXC_ERROR;
3107 END IF;
3108 END IF;
3109
3110 IF (p_return_status <> FND_API.G_RET_STS_SUCCESS ) then
3111 FND_MSG_PUB.Count_And_Get
3112 ( p_count => p_msg_count
3113 ,p_data => p_msg_data
3114 );
3115 END IF;
3116 EXCEPTION
3117 WHEN FND_API.G_EXC_ERROR THEN
3118 p_return_status := FND_API.G_RET_STS_ERROR;
3119 FND_MSG_PUB.Count_And_Get
3120 ( p_count => p_msg_count
3121 ,p_data => p_msg_data
3122 );
3123 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3124 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3125 FND_MSG_PUB.Count_And_Get
3126 ( p_count => p_msg_count
3127 ,p_data => p_msg_data
3128 );
3129 WHEN OTHERS THEN
3130 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3131 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3132 FND_MSG_PUB.Add_Exc_Msg
3133 ( G_PKG_NAME
3134 , l_api_name
3135 );
3136 END IF;
3137 FND_MSG_PUB.Count_And_Get
3138 ( p_count => p_msg_count
3139 ,p_data => p_msg_data
3140 );
3141
3142 END Project_Level_Validations;
3143
3144 /* Progress Management Changes. Bug # 3420093. */
3145
3146 PROCEDURE update_task_progress_amg
3147 ( p_api_version IN NUMBER :=1.0
3148 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
3149 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
3150 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
3151 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
3152 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
3153 ,p_debug_mode IN VARCHAR2 :='N'
3154 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3155 ,p_progress_mode IN VARCHAR2 := 'FUTURE'
3156 ,p_project_id IN NUMBER
3157 ,p_structure_version_id IN NUMBER
3158 ,p_structure_type IN VARCHAR2
3159 ,p_as_of_date IN DATE
3160 ,p_task_progress_list_table IN PA_PROGRESS_PUB.PA_TASK_PROGRESS_LIST_TBL_TYPE
3161 ,x_return_status OUT NOCOPY VARCHAR2
3162 ,x_msg_count OUT NOCOPY NUMBER
3163 ,x_msg_data OUT NOCOPY VARCHAR2
3164 ) is
3165
3166 l_task_progress_rec PA_PROGRESS_PUB.PA_TASK_PROGRESS_LIST_REC_TYPE;
3167 l_return_status VARCHAR2(1);
3168 l_api_name VARCHAR2(30) := 'update_task_progress_amg';
3169 begin
3170 IF FND_API.to_boolean(p_init_msg_list)
3171 THEN
3172 FND_MSG_PUB.initialize;
3173 END IF;
3174
3175 x_return_status := FND_API.G_RET_STS_SUCCESS;
3176
3177 -- Setting context for the temporary fin_plan table.
3178
3179 PA_PROGRESS_PUB.populate_pji_tab_for_plan(
3180 p_api_version => p_api_version
3181 ,p_init_msg_list => p_init_msg_list
3182 ,p_commit => p_commit
3183 ,p_calling_module => 'AMG'
3184 ,p_debug_mode => p_debug_mode
3185 ,p_max_msg_count => p_max_msg_count
3186 ,p_project_id => p_project_id
3187 ,p_structure_version_id => p_structure_version_id
3188 --,p_baselined_str_ver_id => p_baselined_str_ver_id
3189 ,x_return_status => x_return_status
3190 ,x_msg_count => x_msg_count
3191 ,x_msg_data => x_msg_data
3192 );
3193
3194 -- 4537865 : Included check for x_return_status
3195 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3197 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3198 RAISE FND_API.G_EXC_ERROR;
3199 END IF;
3200
3201 -- Call Update progress for each task with P_rollup_entire_wbs_flag = 'N' and p_calling_module = 'AMG'.
3202
3203 for l_loop_count in 1 .. p_task_progress_list_table.count
3204 loop
3205 l_task_progress_rec := p_task_progress_list_table(l_loop_count);
3206
3207 PA_PROGRESS_PUB.UPDATE_PROGRESS(
3208 p_api_version => p_api_version
3209 ,p_init_msg_list => p_init_msg_list
3210 ,p_commit => p_commit
3211 ,p_validate_only => p_validate_only
3212 ,p_validation_level => p_validation_level
3213 ,p_calling_module => 'AMG'
3214 ,p_debug_mode => p_debug_mode
3215 ,p_max_msg_count => p_max_msg_count
3216 ,P_rollup_entire_wbs_flag => 'N'
3217 ,p_progress_mode => p_progress_mode
3218 --,p_percent_complete_id => l_task_progress_rec.percent_complete_id
3219 ,p_project_id => p_project_id
3220 ,p_object_id => l_task_progress_rec.object_id
3221 ,p_object_version_id => l_task_progress_rec.object_version_id
3222 ,p_object_type => l_task_progress_rec.object_type
3223 ,p_as_of_date => p_as_of_date
3224 ,p_percent_complete => l_task_progress_rec.percent_complete
3225 ,p_progress_status_code => l_task_progress_rec.progress_status_code
3226 ,p_progress_comment => l_task_progress_rec.progress_comment
3227 --,p_brief_overview => l_task_progress_rec.brief_overview
3228 ,p_actual_start_date => l_task_progress_rec.actual_start_date
3229 ,p_actual_finish_date => l_task_progress_rec.actual_finish_date
3230 ,p_estimated_start_date => l_task_progress_rec.estimated_start_date
3231 ,p_estimated_finish_date => l_task_progress_rec.estimated_finish_date
3232 ,p_scheduled_start_date => l_task_progress_rec.scheduled_start_date
3233 ,p_scheduled_finish_date => l_task_progress_rec.scheduled_finish_date
3234 --,p_record_version_number => l_task_progress_rec.record_version_number
3235 ,p_task_status => l_task_progress_rec.task_status
3236 ,p_est_remaining_effort => l_task_progress_rec.est_remaining_effort
3237 ,p_ETC_cost => l_task_progress_rec.ETC_cost
3238 ,p_actual_work_quantity => l_task_progress_rec.actual_work_quantity
3239 --,p_pm_product_code => l_task_progress_rec.pm_product_code
3240 ,p_structure_type => p_structure_type
3241 --,p_actual_effort => l_task_progress_rec.actual_effort
3242 --,p_actual_cost => l_task_progress_rec.actual_cost
3243 --,p_actual_effort_this_period => l_task_progress_rec.actual_effort_this_period
3244 --,p_actual_cost_this_period => l_task_progress_rec.actual_cost_this_period
3245 --,p_object_sub_type => l_task_progress_rec.object_sub_type
3246 ,p_task_id => l_task_progress_rec.task_id
3247 ,p_structure_version_id => p_structure_version_id
3248 --,p_prog_fom_wp_flag => l_task_progress_rec.prog_fom_wp_flag
3249 --,p_rollup_reporting_lines_flag=> l_task_progress_rec.rollup_reporting_lines_flag
3250 --,p_planned_cost => l_task_progress_rec.planned_cost
3251 --,p_planned_effort => l_task_progress_rec.planned_effort
3252 --,p_rate_based_flag => l_task_progress_rec.rate_based_flag
3253 --,p_resource_class_code => l_task_progress_rec.resource_class_code
3254 --,p_transfer_wp_pc_flag => l_task_progress_rec.transfer_wp_pc_flag
3255 --,p_rbs_element_id => l_task_progress_rec.rbs_element_id
3256 --,p_resource_list_member_id => l_task_progress_rec.resource_list_member_id
3257 ,x_return_status => x_return_status
3258 ,x_msg_count => x_msg_count
3259 ,x_msg_data => x_msg_data
3260 );
3261 -- 4537865 : Included check for x_return_status
3262 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3264 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3265 RAISE FND_API.G_EXC_ERROR;
3266 END IF;
3267 end loop;
3268
3269 -- Call rollup_progress_pvt api for the entire structure.
3270
3271 PA_PROGRESS_PUB.ROLLUP_PROGRESS_PVT(
3272 p_init_msg_list => p_init_msg_list
3273 ,p_commit => p_commit
3274 ,p_validate_only => p_validate_only
3275 ,p_project_id => p_project_id
3276 ,p_structure_version_id => p_structure_version_id
3277 ,p_as_of_date => trunc(p_as_of_date) -- 5294838
3278 ,x_return_status => x_return_status
3279 ,x_msg_count => x_msg_count
3280 ,x_msg_data => x_msg_data);
3281
3282 if (x_return_status <> fnd_api.g_ret_sts_success ) then
3283 fnd_msg_pub.count_and_get
3284 (p_count => x_msg_count
3285 ,p_data => x_msg_data);
3286 end if;
3287
3288 exception
3289
3290 when fnd_api.g_exc_error then
3291 x_return_status := fnd_api.g_ret_sts_error;
3292 fnd_msg_pub.count_and_get
3293 (p_count => x_msg_count
3294 ,p_data => x_msg_data);
3295 when fnd_api.g_exc_unexpected_error then
3296 x_return_status := fnd_api.g_ret_sts_unexp_error;
3297 fnd_msg_pub.count_and_get
3298 (p_count => x_msg_count
3299 ,p_data => x_msg_data);
3300 when others then
3301 x_return_status := fnd_api.g_ret_sts_unexp_error;
3302 if fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) then
3303 fnd_msg_pub.add_exc_msg(g_pkg_name,l_api_name);
3304 end if;
3305 fnd_msg_pub.count_and_get
3306 (p_count => x_msg_count
3307 ,p_data => x_msg_data);
3308
3309 end update_task_progress_amg;
3310
3311 /* Progress Management Changes. Bug # 3420093. */
3312
3313 -- =================================================================
3314
3315 PROCEDURE UPDATE_PROGRESS_BULK(
3316 p_api_version IN NUMBER :=1.0
3317 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
3318 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
3319 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
3320 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
3321 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
3322 ,p_calling_mode IN VARCHAR2 := null
3323 ,p_debug_mode IN VARCHAR2 :='N'
3324 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3325 ,p_action IN VARCHAR2 :='SAVE'
3326 ,p_rollup_entire_wbs_flag IN VARCHAR2 :='N'
3327 ,p_progress_mode IN VARCHAR2 :='FUTURE'
3328 ,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
3329 ,p_structure_type IN VARCHAR2 := 'WORKPLAN'
3330 ,p_project_id IN NUMBER
3331 ,p_object_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3332 ,p_object_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3333 ,p_object_type_tbl IN SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type()
3334 ,p_task_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3335 ,p_structure_version_id IN NUMBER
3336 ,p_as_of_date_tbl IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
3337 ,p_rbs_element_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3338 ,p_resource_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3339 ,p_rate_based_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type := SYSTEM.pa_varchar2_1_tbl_type()
3340 ,p_resource_class_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type()
3341 ,p_txn_currency_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type()
3342 ,p_percent_complete_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3343 ,p_record_version_number_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3344 ,p_percent_complete_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3345 ,p_eff_rup_percent_complete_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3346 ,p_task_status_tbl IN SYSTEM.pa_varchar2_150_tbl_type := SYSTEM.pa_varchar2_150_tbl_type()
3347 ,p_progress_status_code_tbl IN SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type()
3348 ,p_progress_comment_tbl IN SYSTEM.pa_varchar2_4000_tbl_type2 := SYSTEM.pa_varchar2_4000_tbl_type2() /* 9754781 : Changes type */
3349 ,p_brief_overview_tbl IN SYSTEM.pa_varchar2_250_tbl_type := SYSTEM.pa_varchar2_250_tbl_type() /* 7709400 : Changed type */
3350 ,p_actual_start_date_tbl IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
3351 ,p_actual_finish_date_tbl IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
3352 ,p_estimated_start_date_tbl IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
3353 ,p_estimated_finish_date_tbl IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
3354 ,p_scheduled_start_date_tbl IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
3355 ,p_scheduled_finish_date_tbl IN SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type()
3356 ,p_est_remaining_effort_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3357 ,p_etc_cost_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3358 ,p_actual_work_quantity_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3359 ,p_actual_effort_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3360 ,p_actual_cost_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3361 ,p_act_eff_this_period_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3362 ,p_actual_cost_this_period_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3363 ,p_planned_cost_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3364 ,p_planned_effort_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3365 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3366 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3367 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3368 )
3369 IS
3370
3371 l_project_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3372 l_structure_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3373 message varchar2(2000);
3374 t_str varchar2(1);
3375
3376 BEGIN
3377
3378 /* Added below block to ensure that the new API's are controlled by Dev*/
3379 begin
3380 select 'x' into t_str
3381 from pji_system_parameters
3382 where name = 'BULK_LITE'
3383 and value = 'Y';
3384 exception
3385 when no_data_found then
3386 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3387 p_msg_name => 'PA_LITE_API_CANT_USE');
3388 RAISE FND_API.G_EXC_ERROR;
3389 when others then
3390 raise;
3391 end;
3392
3393
3394 -- Set the G_ROLLUP_FLAG based on profile options
3395 PA_PROJECT_PUB.G_ROLLUP_DEFER:=NVL(fnd_profile.value('G_ROLLUP_DEFER'),'N');
3396 IF (PA_PROJECT_PUB.G_ROLLUP_DEFER='Y') THEN
3397 PA_PROJECT_PVT.CONC_LOCK_PROJECT(p_project_id,'PROGRESS',x_msg_data,x_return_status);
3398 IF x_return_status <> 'S'
3399 THEN
3400 RAISE FND_API.G_EXC_ERROR;
3401 ELSE
3402 PA_PROJECT_PVT.LOG_ROLLUP_EVENT(p_project_id,p_structure_version_id,'PROGRESS');
3403 --PA_PROJECT_PVT.LOG_TASK_LEVEL_EVENT(p_pa_project_id,p_pa_task_id,p_structure_version_id,'PROGRESS');
3404 END IF;
3405 END IF;
3406
3407 FOR i in p_task_id_tbl.FIRST..p_task_id_tbl.LAST LOOP
3408 l_project_id_tbl.extend(1);
3409 l_structure_version_id_tbl.extend(1);
3410 l_project_id_tbl(i) := p_project_id;
3411 l_structure_version_id_tbl(i) :=p_structure_version_id;
3412 IF (PA_PROJECT_PUB.G_ROLLUP_DEFER='Y') THEN
3413 PA_PROJECT_PVT.LOG_TASK_LEVEL_EVENT(p_project_id,p_task_id_tbl(i),p_structure_version_id,'PROGRESS');
3414 END IF;
3415 END LOOP;
3416
3417
3418 PA_PROGRESS_PUB.UPDATE_PROGRESS_BULK(
3419 p_api_version
3420 ,p_init_msg_list
3421 ,p_commit
3422 ,p_validate_only
3423 ,p_validation_level
3424 ,p_calling_module
3425 ,p_calling_mode
3426 ,p_debug_mode
3427 ,p_max_msg_count
3428 ,p_action
3429 ,p_rollup_entire_wbs_flag
3430 ,p_progress_mode
3431 ,p_pm_product_code
3432 ,p_structure_type
3433 ,l_project_id_tbl
3434 ,p_object_id_tbl
3435 ,p_object_version_id_tbl
3436 ,p_object_type_tbl
3437 ,p_task_id_tbl
3438 ,l_structure_version_id_tbl
3439 ,p_as_of_date_tbl
3440 ,p_rbs_element_id_tbl
3441 ,p_resource_assignment_id_tbl
3442 ,p_rate_based_flag_tbl
3443 ,p_resource_class_code_tbl
3444 ,p_txn_currency_code_tbl
3445 ,p_percent_complete_id_tbl
3446 ,p_record_version_number_tbl
3447 ,p_percent_complete_tbl
3448 ,p_eff_rup_percent_complete_tbl
3449 ,p_task_status_tbl
3450 ,p_progress_status_code_tbl
3451 ,p_progress_comment_tbl
3452 ,p_brief_overview_tbl
3453 ,p_actual_start_date_tbl
3454 ,p_actual_finish_date_tbl
3455 ,p_estimated_start_date_tbl
3456 ,p_estimated_finish_date_tbl
3457 ,p_scheduled_start_date_tbl
3458 ,p_scheduled_finish_date_tbl
3459 ,p_est_remaining_effort_tbl
3460 ,p_etc_cost_tbl
3461 ,p_actual_work_quantity_tbl
3462 ,p_actual_effort_tbl
3463 ,p_actual_cost_tbl
3464 ,p_act_eff_this_period_tbl
3465 ,p_actual_cost_this_period_tbl
3466 ,p_planned_cost_tbl
3467 ,p_planned_effort_tbl
3468 ,x_return_status
3469 ,x_msg_count
3470 ,x_msg_data
3471 );
3472
3473 IF (PA_PROJECT_PUB.G_ROLLUP_DEFER='Y') THEN
3474 PA_PROJECT_PVT.CONC_RELEASE_LOCK_PROJECT(p_project_id);
3475 END IF;
3476 EXCEPTION
3477 WHEN FND_API.G_EXC_ERROR THEN
3478 x_return_status := FND_API.G_RET_STS_ERROR ;
3479 IF (PA_PROJECT_PUB.G_ROLLUP_DEFER='Y') THEN
3480 PA_PROJECT_PVT.CONC_RELEASE_LOCK_PROJECT(p_project_id);
3481 END IF;
3482 FND_MSG_PUB.Count_And_Get
3483 ( p_count => x_msg_count ,
3484 p_data => x_msg_data );
3485 when OTHERS then
3486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3487 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3488 FND_MSG_PUB.add_exc_msg
3489 ( p_pkg_name => G_PKG_NAME
3490 , p_procedure_name => 'UPDATE_PROGRESS_BULK' );
3491 END IF;
3492 IF (PA_PROJECT_PUB.G_ROLLUP_DEFER='Y') THEN
3493 PA_PROJECT_PVT.CONC_RELEASE_LOCK_PROJECT(p_project_id);
3494 END IF;
3495 FND_MSG_PUB.Count_And_Get
3496 ( p_count => x_msg_count ,
3497 p_data => x_msg_data );
3498 END update_progress_bulk;
3499
3500 END pa_Status_Pub;