[Home] [Help]
PACKAGE BODY: APPS.PA_ASSIGNMENT_PROGRESS_PUB
Source
1 PACKAGE BODY PA_ASSIGNMENT_PROGRESS_PUB AS
2 /* $Header: PAPRASPB.pls 120.9 2007/02/06 09:40:36 dthakker ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_ASSIGNMENT_PROGRESS_PUB';
5
6 --g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
7
8 PROCEDURE UPDATE_ASSIGNMENT_PROGRESS(
9 p_api_version IN NUMBER := 1.0 ,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
12 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
13 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
14 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
15 p_action IN VARCHAR2 := 'SAVE' ,
16 p_bulk_load_flag IN VARCHAR2 := 'N' ,
17 p_progress_mode IN VARCHAR2 := 'FUTURE' ,
18 p_percent_complete_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
19 p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
20 p_object_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
21 p_object_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
22 p_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
23 p_as_of_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ,
24 p_progress_comment IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ,
25 p_brief_overview IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ,
26 p_actual_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ,
27 p_actual_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ,
28 p_estimated_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ,
29 p_estimated_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ,
30 p_record_version_number IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
31 p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ,
32 p_rate_based_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ,
33 p_resource_class_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ,
34 p_txn_currency_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR ,
35 p_rbs_element_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
36 --p_resource_list_member_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , --bug# 3764224
37 p_resource_assignment_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
38 p_actual_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , -- Bug3621404 This parameter represents raw cost
39 p_actual_effort IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
40 p_planned_cost IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , -- Bug3621404 This parameter represents raw cost
41 p_planned_effort IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
42 p_structure_type IN VARCHAR2 := 'WORKPLAN' ,
43 p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
44 p_actual_cost_this_period IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , -- Bug3621404 This parameter represents raw cost
45 p_actual_effort_this_period IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ,
46 p_etc_cost_this_period IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , -- Though the name of this column is this period but it is cumulative -- Bug3621404 This parameter represents raw cost
47 p_etc_effort_this_period IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , -- Though the name of this column is this period but it is cumulative
48 p_scheduled_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ,
49 p_scheduled_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE ,
50 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
51 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
52 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
53 IS
54
55 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_ASSIGNMENT_PROGRESS' ;
56 l_api_version CONSTANT NUMBER := 1.0 ;
57 l_return_status VARCHAR2(1) ;
58 l_msg_count NUMBER ;
59 l_msg_data VARCHAR2(250) ;
60 l_data VARCHAR2(250) ;
61 l_msg_index_out NUMBER ;
62 l_error_msg_code VARCHAR2(250) ;
63 l_user_id NUMBER := FND_GLOBAL.USER_ID ;
64 l_login_id NUMBER := FND_GLOBAL.LOGIN_ID ;
65 l_task_id NUMBER ;
66 l_assignment_id NUMBER ;
67 l_project_id NUMBER ;
68 l_percent_complete_id NUMBER ;
69 l_PROGRESS_ROLLUP_ID NUMBER ;
70 l_last_progress_date DATE ;
71 l_progress_exists_on_aod VARCHAR2(15) ;
72 l_db_action VARCHAR2(10) ;
73 l_published_flag VARCHAR2(1) ;
74 l_current_flag VARCHAR2(1) ;
75 l_actual_start_date DATE ;
76 l_actual_finish_date DATE ;
77 --bug no. 3586648 start
78 l_scheduled_start_date DATE ;
79 l_scheduled_finish_date DATE ;
80 --bug no. 3586648 end
81 l_estimated_start_date DATE ;
82 l_estimated_finish_date DATE ;
83 l_est_remaining_eff_flag VARCHAR2(1) ;
84 l_rollup_rec_ver_number NUMBER ;
85 l_published_by_party_id NUMBER := PA_UTILS.get_party_id( l_user_id ) ;
86 l_working_aod DATE ;
87 l_aod DATE ;
88 l_progress_entry_enable_flag VARCHAR2(1) ;
89 l_object_type VARCHAR2(15) := 'PA_ASSIGNMENTS' ;
90 l_structure_version_id NUMBER ;
91 l_published_structure VARCHAR2(1) ;
92 l_object_version_id NUMBER ;
93 g1_debug_mode VARCHAR2(1) ;
94 --l_structure_shared VARCHAR2(1) ;
95 l_structure_sharing_code VARCHAR2(30) ; --bug no. 3586648
96 l_actual_effort_this_period NUMBER ;
97 l_etc_effort_this_period NUMBER ;
98 l_brief_overview VARCHAR2(250) ;
99 l_progress_comment VARCHAR2(4000) ;
100 l_pm_product_code VARCHAR2(30) ;
101
102
103 -- Raw Cost Changes : Changed the variables to include raw or burden
104 l_ppl_act_raw_cost_to_date_tc NUMBER ;
105 l_ppl_act_raw_cost_to_date_fc NUMBER ;
106 l_ppl_act_raw_cost_to_date_pc NUMBER ;
107 l_ppl_act_bur_cost_to_date_tc NUMBER ;
108 l_ppl_act_bur_cost_to_date_fc NUMBER ;
109 l_ppl_act_bur_cost_to_date_pc NUMBER ;
110 l_eqp_act_raw_cost_to_date_tc NUMBER ;
111 l_eqp_act_raw_cost_to_date_fc NUMBER ;
112 l_eqp_act_raw_cost_to_date_pc NUMBER ;
113 l_eqp_act_bur_cost_to_date_tc NUMBER ;
114 l_eqp_act_bur_cost_to_date_fc NUMBER ;
115 l_eqp_act_bur_cost_to_date_pc NUMBER ;
116 l_oth_act_raw_cost_to_date_tc NUMBER ;
117 l_oth_act_raw_cost_to_date_fc NUMBER ;
118 l_oth_act_raw_cost_to_date_pc NUMBER ;
119 l_oth_act_bur_cost_to_date_tc NUMBER ;
120 l_oth_act_bur_cost_to_date_fc NUMBER ;
121 l_oth_act_bur_cost_to_date_pc NUMBER ;
122 l_ppl_act_effort_to_date NUMBER ;
123 l_eqpmt_act_effort_to_date NUMBER ;
124 l_act_txn_raw_cost NUMBER ;
125 l_act_txn_bur_cost NUMBER ;
126
127 l_ppl_etc_raw_cost_tc NUMBER ;
128 l_ppl_etc_raw_cost_fc NUMBER ;
129 l_ppl_etc_raw_cost_pc NUMBER ;
130 l_ppl_etc_bur_cost_tc NUMBER ;
131 l_ppl_etc_bur_cost_fc NUMBER ;
132 l_ppl_etc_bur_cost_pc NUMBER ;
133 l_eqpmt_etc_raw_cost_tc NUMBER ;
134 l_eqpmt_etc_raw_cost_fc NUMBER ;
135 l_eqpmt_etc_raw_cost_pc NUMBER ;
136 l_eqpmt_etc_bur_cost_tc NUMBER ;
137 l_eqpmt_etc_bur_cost_fc NUMBER ;
138 l_eqpmt_etc_bur_cost_pc NUMBER ;
139 l_oth_etc_raw_cost_tc NUMBER ;
140 l_oth_etc_raw_cost_fc NUMBER ;
141 l_oth_etc_raw_cost_pc NUMBER ;
142 l_oth_etc_bur_cost_tc NUMBER ;
143 l_oth_etc_bur_cost_fc NUMBER ;
144 l_oth_etc_bur_cost_pc NUMBER ;
145 l_ppl_etc_effort NUMBER ;
146 l_eqpmt_etc_effort NUMBER ;
147 l_etc_txn_raw_cost NUMBER ;
148 l_etc_txn_bur_cost NUMBER ;
149 l_txn_currency_code VARCHAR2(30) ;
150 l_project_curr_code VARCHAR2(30) ;
151 l_project_rate_type VARCHAR2(30) ;
152 l_project_rate_date DATE ;
153 l_project_exch_rate NUMBER ;
154 l_act_project_raw_cost NUMBER ;
155 l_act_project_bur_cost NUMBER ;
156 l_projfunc_curr_code VARCHAR2(30) ;
157 l_projfunc_cost_rate_type VARCHAR2(30) ;
158 l_projfunc_cost_rate_date DATE ;
159 l_projfunc_cost_exch_rate NUMBER ;
160 l_act_projfunc_raw_cost NUMBER ;
161 l_act_projfunc_bur_cost NUMBER ;
162
163 l_etc_project_raw_cost NUMBER ;
164 l_etc_projfunc_raw_cost NUMBER ;
165 l_etc_project_bur_cost NUMBER ;
166 l_etc_projfunc_bur_cost NUMBER ;
167
168 l_prog_pa_period_name VARCHAR2(30) ;
169 l_prog_gl_period_name VARCHAR2(30) ;
170
171 --bug no.3595585 Satish start
172 l_etc_txn_raw_cost_this_period NUMBER ;
173 l_etc_prj_raw_cost_this_period NUMBER ;
174 l_etc_pfc_raw_cost_this_period NUMBER ;
175 l_etc_txn_bur_cost_this_period NUMBER ;
176 l_etc_prj_bur_cost_this_period NUMBER ;
177 l_etc_pfc_bur_cost_this_period NUMBER ;
178
179 l_etc_effort_incr NUMBER ;
180 l_etc_effort_last NUMBER ;
181 --bug no.3595585 Satish end
182
183 -- required for compilation
184 l_percent_complete NUMBER ;
185 l_progress_status_code VARCHAR2(30) :='PROGRESS_STAT_ON_TRACK' ;
186 l_task_status VARCHAR2(150) ;
187 l_rollup_progress_status VARCHAR2(150):='PROGRESS_STAT_ON_TRACK' ;
188 l_INCREMENTAL_WORK_QTY NUMBER ;
189 l_CUMULATIVE_WORK_QTY NUMBER ;
190 l_BASE_PERCENT_COMPLETE NUMBER ;
191 l_EFF_ROLLUP_PERCENT_COMP NUMBER ;
192 l_rollup_completed_percentage NUMBER ;
193 l_BASE_PERCENT_COMP_DERIV_CODE VARCHAR2(30) ;
194 l_BASE_PROGRESS_STATUS_CODE VARCHAR2(30) :='PROGRESS_STAT_ON_TRACK' ;
195 l_EFF_ROLLUP_PROG_STAT_CODE VARCHAR2(150) ;
196 l_ACTUAL_WQ_ENTRY_CODE VARCHAR2(30) ;
197 l_wq_enabled_flag VARCHAR2(1) ;
198 l_percent_complete_flag VARCHAR2(1) ;
199 l_allow_collab_prog_entry VARCHAR2(1) ;
200 l_allw_phy_prcnt_cmp_overrides VARCHAR2(1) ;
201 l_task_weight_basis_code VARCHAR2(30) ;
202
203 -- l_res_list_memb_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
204 -- l_res_effort_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
208
205 -- l_res_txn_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
206 -- l_etc_res_effort_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
207 -- l_etc_res_txn_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
209 CURSOR cur_check_published_version(c_structure_version_id number, c_project_id number)
210 IS
211 SELECT decode(status.project_system_status_code, 'STRUCTURE_PUBLISHED', 'Y', 'N')
212 FROM pa_proj_elem_ver_structure str, pa_project_statuses status
213 where str.element_version_id = c_structure_version_id
214 AND str.project_id = c_project_id
215 AND str.status_code = status.project_status_code;
216
217
218 -- FPM Dev CR 3 Begin
219 l_task_elem_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
220 l_planned_people_effort_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
221 l_planned_equip_effort_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
222 l_resource_assignment_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
223 l_resource_list_member_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
224 l_resource_class_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE() ;
225
226 -- Raw Cost Changes : Changed the cost columns to Raw Cost
227 CURSOR cur_task_cost(c_project_id number, c_object_id number, c_structure_type VARCHAR2, c_resource_class_code VARCHAR2, c_as_of_date DATE)
228 IS
229 SELECT decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_rawcost_to_date_tc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_rawcost_to_date_tc,0), nvl(ppr.oth_act_rawcost_to_date_tc,0)) tc_raw_cost,
230 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_rawcost_to_date_pc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_rawcost_to_date_pc,0), nvl(ppr.oth_act_rawcost_to_date_pc,0)) pc_raw_cost,
231 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_rawcost_to_date_fc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_rawcost_to_date_fc,0), nvl(ppr.oth_act_rawcost_to_date_fc,0)) fc_raw_cost,
232 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_cost_to_date_tc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_cost_to_date_tc,0), nvl(ppr.oth_act_cost_to_date_tc,0)) tc_bur_cost,
233 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_cost_to_date_pc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_cost_to_date_pc,0), nvl(ppr.oth_act_cost_to_date_pc,0)) pc_bur_cost,
234 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_cost_to_date_fc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_cost_to_date_fc,0), nvl(ppr.oth_act_cost_to_date_fc,0)) fc_bur_cost,
235 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.PPL_ACT_EFFORT_TO_DATE,0), 'EQUIPMENT', nvl(ppr.EQPMT_ACT_EFFORT_TO_DATE,0), nvl(ppr.OTH_QUANTITY_TO_DATE,0)) act_effort -- 3696572 OTH_QUANTITY_TO_DATE shd also be considered here
236 FROM pa_progress_rollup ppr
237 WHERE ppr.project_id = c_project_id
238 AND ppr.object_id = c_object_id
239 AND ppr.structure_type = c_structure_type
240 AND ppr.structure_version_id is null --bug# 3821106 Satish
241 AND ppr.object_type = 'PA_ASSIGNMENTS'
242 AND ppr.proj_element_id = p_task_id --bug 3861360
243 AND ppr.current_flag <> 'W' -- Bug 3879461
244 --bug# 3821106 Satish
245 /*AND ppr.as_of_date = ( SELECT max(as_of_date)
246 from pa_progress_rollup ppr2
247 WHERE ppr2.as_of_date < c_as_of_date
248 AND ppr2.project_id = c_project_id
249 AND ppr2.object_id = c_object_id
250 AND ppr2.object_type = 'PA_ASSIGNMENTS'
251 AND ppr2.structure_type = c_structure_type
252 );*/
253 AND ppr.as_of_date = ( SELECT max(as_of_date)
254 from pa_progress_rollup ppr2
255 WHERE ppr2.as_of_date < c_as_of_date ---4290592
256 AND ppr2.project_id = c_project_id
257 AND ppr2.object_id = c_object_id
258 AND ppr2.object_type = 'PA_ASSIGNMENTS'
259 AND ppr2.structure_type = c_structure_type
260 AND ppr2.structure_version_id is null
261 AND ppr2.proj_element_id = p_task_id --bug 3861360
262 AND ppr2.current_flag <> 'W' -- Bug 3879461
263 -- Bug 3879461 : Not exists is not required now
264 -- AND NOT EXISTS (
265 -- SELECT 'X' FROM pa_percent_completes ppc
266 -- WHERE ppc.date_computed = ppr2.as_of_date
267 -- AND ppc.project_id = c_project_id
268 -- AND ppc.object_id = c_object_id
269 -- AND ppc.object_type = 'PA_ASSIGNMENTS'
270 -- AND ppc.structure_type = c_structure_type
271 -- AND ppc.published_flag = 'N'
272 -- AND ppc.task_id = p_task_id --3861360
273 -- )
274 );
275
276 CURSOR cur_task_cost_shared(c_project_id number, c_object_id number, c_structure_type VARCHAR2, c_resource_class_code VARCHAR2, c_as_of_date DATE)
277 IS
278 SELECT decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_rawcost_to_date_tc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_rawcost_to_date_tc,0), nvl(ppr.oth_act_rawcost_to_date_tc,0)) tc_raw_cost,
279 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_rawcost_to_date_pc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_rawcost_to_date_pc,0), nvl(ppr.oth_act_rawcost_to_date_pc,0)) pc_raw_cost,
280 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_rawcost_to_date_fc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_rawcost_to_date_fc,0), nvl(ppr.oth_act_rawcost_to_date_fc,0)) fc_raw_cost,
284 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.PPL_ACT_EFFORT_TO_DATE,0), 'EQUIPMENT', nvl(ppr.EQPMT_ACT_EFFORT_TO_DATE,0), nvl(ppr.OTH_QUANTITY_TO_DATE,0)) act_effort -- 3696572 OTH_QUANTITY_TO_DATE shd also be considered here
281 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_cost_to_date_tc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_cost_to_date_tc,0), nvl(ppr.oth_act_cost_to_date_tc,0)) tc_bur_cost,
282 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_cost_to_date_pc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_cost_to_date_pc,0), nvl(ppr.oth_act_cost_to_date_pc,0)) pc_bur_cost,
283 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_cost_to_date_fc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_cost_to_date_fc,0), nvl(ppr.oth_act_cost_to_date_fc,0)) fc_bur_cost,
285 FROM pa_progress_rollup ppr
286 WHERE ppr.project_id = c_project_id
287 AND ppr.object_id = c_object_id
288 AND ppr.structure_type = c_structure_type
289 AND ppr.structure_version_id is null --bug# 3821106 Satish
290 AND ppr.object_type = 'PA_ASSIGNMENTS'
291 AND ppr.proj_element_id = p_task_id --bug 3861360
292 AND ppr.current_flag <> 'W' -- Bug 3879461
293 --bug# 3821106 Satish
294 AND ppr.as_of_date = ( SELECT max(as_of_date)
295 from pa_progress_rollup ppr2
296 WHERE ppr2.as_of_date <= c_as_of_date ---4290592
297 AND ppr2.project_id = c_project_id
298 AND ppr2.object_id = c_object_id
299 AND ppr2.object_type = 'PA_ASSIGNMENTS'
300 AND ppr2.structure_type = c_structure_type
301 AND ppr2.structure_version_id is null
302 AND ppr2.proj_element_id = p_task_id --bug 3861360
303 AND ppr2.current_flag <> 'W' -- Bug 3879461
304 );
305
306 CURSOR cur_task_cost_latest(c_project_id number, c_object_id number, c_structure_type VARCHAR2, c_resource_class_code VARCHAR2, c_as_of_date DATE)
307 IS
308 SELECT decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_rawcost_to_date_tc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_rawcost_to_date_tc,0), nvl(ppr.oth_act_rawcost_to_date_tc,0)) tc_raw_cost,
309 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_rawcost_to_date_pc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_rawcost_to_date_pc,0), nvl(ppr.oth_act_rawcost_to_date_pc,0)) pc_raw_cost,
310 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_rawcost_to_date_fc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_rawcost_to_date_fc,0), nvl(ppr.oth_act_rawcost_to_date_fc,0)) fc_raw_cost,
311 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_cost_to_date_tc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_cost_to_date_tc,0), nvl(ppr.oth_act_cost_to_date_tc,0)) tc_bur_cost,
312 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_cost_to_date_pc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_cost_to_date_pc,0), nvl(ppr.oth_act_cost_to_date_pc,0)) pc_bur_cost,
313 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.ppl_act_cost_to_date_fc,0), 'EQUIPMENT', nvl(ppr.eqpmt_act_cost_to_date_fc,0), nvl(ppr.oth_act_cost_to_date_fc,0)) fc_bur_cost,
314 decode(c_resource_class_code, 'PEOPLE', nvl(ppr.PPL_ACT_EFFORT_TO_DATE,0), 'EQUIPMENT', nvl(ppr.EQPMT_ACT_EFFORT_TO_DATE,0), nvl(ppr.OTH_QUANTITY_TO_DATE,0)) act_effort
315 FROM pa_progress_rollup ppr
316 WHERE ppr.project_id = c_project_id
317 AND ppr.object_id = c_object_id
318 AND ppr.structure_type = c_structure_type
319 AND ppr.structure_version_id is null --bug# 3821106 Satish
320 AND ppr.object_type = 'PA_ASSIGNMENTS'
321 AND ppr.proj_element_id = p_task_id --bug 3861360
322 AND ppr.current_flag <> 'W' -- Bug 3879461
323 AND ppr.as_of_date = c_as_of_date;
324
325 subm_prog_exists_aod VARCHAR2(1):='N';
326 l_act_raw_cost_last_subm_tc NUMBER ;
327 l_act_raw_cost_last_subm_pc NUMBER ;
328 l_act_raw_cost_last_subm_fc NUMBER ;
329 l_act_bur_cost_last_subm_tc NUMBER ;
330 l_act_bur_cost_last_subm_pc NUMBER ;
331 l_act_bur_cost_last_subm_fc NUMBER ;
332 l_act_effort_last_subm NUMBER ;
333 l_act_raw_cost_latest_subm_tc NUMBER ;
334 l_act_raw_cost_latest_subm_pc NUMBER ;
335 l_act_raw_cost_latest_subm_fc NUMBER ;
336 l_act_bur_cost_latest_subm_tc NUMBER ;
337 l_act_bur_cost_latest_subm_pc NUMBER ;
338 l_act_bur_cost_latest_subm_fc NUMBER ;
339 l_act_effort_latest_subm NUMBER ;
340 l_total_effort NUMBER ;
341 l_record_version_number NUMBER ;
342 -- FPM Dev CR 3 End
343
344 --bug 3608801
345 l_oth_quantity_to_date NUMBER;
346 l_oth_etc_quantity NUMBER;
347 --bug 3608801
348 -- Bug 3606627 Start
349 l_resource_list_member_id NUMBER;
350 l_rate_based_flag VARCHAR2(1);
351 l_resource_class_code pa_task_assignments_v.resource_class_code%TYPE;
352 l_rbs_element_id NUMBER;
353 l_actual_raw_cost NUMBER;
354 l_actual_raw_cost_this_period NUMBER;
355 l_actual_bur_cost NUMBER;
356 l_actual_bur_cost_this_period NUMBER;
357
358 l_actual_effort NUMBER;
359 l_etc_raw_cost_this_period NUMBER;
360 l_etc_bur_cost_this_period NUMBER;
361 L_DUMMY_RAW_COST NUMBER;
362 L_RES_RAW_RATE NUMBER;
366 l_etc_txn_raw_cost_last NUMBER;
363 L_RES_BURDEN_RATE NUMBER;
364 L_BURDEN_MULTIPLIER NUMBER;
365 L_RES_CUR_CODE VARCHAR2(15);
367 l_etc_prj_raw_cost_last NUMBER;
368 l_etc_pfc_raw_cost_last NUMBER;
369 l_etc_txn_bur_cost_last NUMBER;
370 l_etc_prj_bur_cost_last NUMBER;
371 l_etc_pfc_bur_cost_last NUMBER;
372 L_DUMMY_BURDEN_COST NUMBER;
373
374 -- Bug 3627315 : Added new parameters to hold plan rates for ETC
375 l_plan_res_cur_code VARCHAR2(15);
376 l_plan_res_raw_rate NUMBER;
377 l_plan_res_burden_rate NUMBER;
378 l_plan_burden_multiplier NUMBER;
379
380 -- Bug 3696429 Satish start
381 l_proj_res_raw_rate NUMBER;
382 l_projfunc_res_raw_rate NUMBER;
383 l_proj_res_burden_rate NUMBER;
384 l_projfunc_res_burden_rate NUMBER;
385 -- Bug 3696429 Satish end
386
387
388 --Begin add by rtarway
389 --BUG 3630743( This cursor is to get planned values)
390 CURSOR c_get_planned_values (l_resource_list_member_id NUMBER, l_resource_assignment_id NUMBER, l_project_id NUMBER) IS
391 SELECT
392 planned_quantity,
393 planned_bur_cost_txn_cur,
394 planned_bur_cost_projfunc,
395 planned_bur_cost_proj_cur,
396 planned_raw_cost_txn_cur,
397 planned_raw_cost_proj_cur,
398 planned_raw_cost_projfunc,
399 budget_version_id ---4372462
400 FROM
401 pa_task_asgmts_v
402 WHERE
403 RESOURCE_LIST_MEMBER_ID = l_resource_list_member_id
404 AND
405 RESOURCE_ASSIGNMENT_ID = l_resource_assignment_id
406 AND
407 PROJECT_ID = l_project_id;
408
409 --This cursor to see if any progress record exists.
410 CURSOR c_if_progress_exists(l_object_id NUMBER, l_project_id NUMBER, l_structure_version_id NUMBER) IS
411 SELECT 'x' FROM dual
412 WHERE EXISTS
413 (
414 SELECT 'y' FROM PA_PROGRESS_ROLLUP
415 WHERE OBJECT_ID = l_object_id
416 AND PROJECT_ID = l_project_id
417 AND OBJECT_TYPE = 'PA_ASSIGNMENTS'
418 AND STRUCTURE_TYPE = 'WORKPLAN'
419 and proj_element_id = l_task_id --3818384
420 AND current_flag <> 'W' -- Bug 3879461
421 AND
422 (
423 ( l_published_structure = 'Y' AND STRUCTURE_VERSION_ID IS NULL)
424 OR
425 ( l_published_structure = 'N' AND STRUCTURE_VERSION_ID = l_structure_version_id)
426 )
427 );
428
429 --bug# 3814545 Satish
430 l_progress_exists VARCHAR2(1):='N';
431 l_budget_version_id NUMBER; -- 4372462
432 l_planned_quantity NUMBER;
433 l_planned_bur_cost_txn_cur NUMBER;
434 l_planned_bur_cost_projfunc NUMBER;
435 l_planned_bur_cost_proj_cur NUMBER;
436 l_planned_raw_cost_txn_cur NUMBER;
437 l_planned_raw_cost_proj_cur NUMBER;
438 l_planned_raw_cost_projfunc NUMBER;
439 l_track_wp_cost_flag VARCHAR2(1) := 'Y'; -- Bug 3801745
440
441 --bug 3824042
442 l_prj_currency_code VARCHAR2(15) := null;
443 l_prjfunc_currency_code VARCHAR2(15) := null;
444 l_rollup_current_flag VARCHAR2(1); -- Bug 3879461
445
446 /*
447 --bug 3958686, check whether the the assignment is hidden assignment, start
448 CURSOR cur_assgmt(l_resource_list_member_id NUMBER, l_resource_assignment_id NUMBER, l_task_id NUMBER, l_project_id NUMBER, l_structure_version_id NUMBER) IS
449 SELECT ta_display_flag
450 FROM pa_task_assignments_v
451 WHERE RESOURCE_LIST_MEMBER_ID = l_resource_list_member_id AND
452 RESOURCE_ASSIGNMENT_ID = l_resource_assignment_id AND
453 TASK_ID = l_task_id AND
454 PROJECT_ID = l_project_id AND
455 STRUCTURE_VERSION_ID = l_structure_version_id;
456
457 l_ta_display_flag VARCHAR2(1);
458 --bug 3958686, check whether the the assignment is hidden assignment, end
459 */
460 -- 4533112 Begin
461 CURSOR c_get_dates_overrides(c_project_id number, c_object_id number, c_object_type varchar2, c_as_of_date Date) IS
462 SELECT decode(ppr.base_progress_status_code,'Y','Y','N') date_override_flag
463 , ppr.estimated_start_date
464 , ppr.estimated_finish_date
465 , ppr.actual_start_date
466 , ppr.actual_finish_date
467 , ppe.status_code
468 FROM pa_progress_rollup ppr
469 , pa_proj_elements ppe
470 WHERE ppr.project_id = c_project_id
471 AND ppr.object_id = c_object_id
472 AND ppr.object_type = c_object_type
473 AND ppr.structure_type = 'WORKPLAN'
474 AND ppr.structure_version_id is null
475 AND trunc(ppr.as_of_date) <= trunc(c_as_of_date)
476 AND ppr.current_flag IN ('Y', 'N')
477 AND ppr.proj_element_id = ppe.proj_element_id
478 ORDER by as_of_date desc;
479
480
481 l_date_override_flag VARCHAR2(1):='N';
482 l_db_date_override_flag VARCHAR2(1):='N';
483 l_db_estimated_start_date DATE;
484 l_db_estimated_finish_date DATE;
485 l_db_actual_start_date DATE;
486 l_db_actual_finish_date DATE;
487 l_clex_estimated_start_date DATE;
488 l_clex_estimated_finish_date DATE;
489 l_clex_actual_start_date DATE;
490 l_clex_actual_finish_date DATE;
491 l_task_status_code VARCHAR2(150);
492 -- 4533112 End
493
494 BEGIN
495
499 -- Update Insert Yes When record save on 1st March(current) and then publish on 8th March(future)
496 -- Percent Complete Progress Rollup Possible Comments
497 -- Insert Update No Applicable for summary level
498 -- Insert Insert Yes Normal Case
500 -- Update Update Yes Normal Case
501
502 g1_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
503 IF g1_debug_mode = 'Y' THEN
504 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'ENTERED', x_Log_Level=> 3);
505 END IF;
506
507 IF g1_debug_mode = 'Y' THEN
508 pa_debug.init_err_stack ('PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS');
509 END IF;
510
511 IF (p_commit = FND_API.G_TRUE) THEN
512 savepoint UPDATE_ASSIGNMENT_PROGRESS;
513 END IF;
514
515 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
517 END IF;
518
519 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
520 FND_MSG_PUB.initialize;
521 END IF;
522
523 IF g1_debug_mode = 'Y' THEN
524 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_task_id: '||p_task_id, x_Log_Level=> 3);
525 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_object_version_id: '||p_object_version_id, x_Log_Level=> 3);
526 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_project_id: '||p_project_id, x_Log_Level=> 3);
527 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_object_id: '||p_object_id, x_Log_Level=> 3);
528 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_as_of_date: '||p_as_of_date, x_Log_Level=> 3);
529 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_percent_complete_id: '||p_percent_complete_id, x_Log_Level=> 3);
530 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_actual_start_date: '||p_actual_start_date, x_Log_Level=> 3);
531 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_actual_finish_date: '||p_actual_finish_date, x_Log_Level=> 3);
532 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_estimated_start_date: '||p_estimated_start_date, x_Log_Level=> 3);
533 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_estimated_finish_date: '||p_estimated_finish_date, x_Log_Level=> 3);
534 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_record_version_number: '||p_record_version_number, x_Log_Level=> 3);
535 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_actual_cost_this_period: '||p_actual_cost_this_period, x_Log_Level=> 3);
536 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_actual_effort_this_period: '||p_actual_effort_this_period, x_Log_Level=> 3);
537 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_etc_cost_this_period: '||p_etc_cost_this_period, x_Log_Level=> 3);
538 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_etc_effort_this_period: '||p_etc_effort_this_period, x_Log_Level=> 3);
539 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_structure_type: '||p_structure_type, x_Log_Level=> 3);
540 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_structure_version_id: '||p_structure_version_id, x_Log_Level=> 3);
541 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_resource_class_code: '||p_resource_class_code, x_Log_Level=> 3);
542 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_rate_based_flag: '||p_rate_based_flag, x_Log_Level=> 3);
543 END IF;
544
545
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547
548 --moved this up for bug 3675107
549 ---4457403, added begin/end block to handle no_data_found exception
550 begin
551 l_prog_pa_period_name := PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(p_as_of_date);
552 l_prog_gl_period_name := PA_PROGRESS_UTILS.Prog_Get_gl_Period_Name(p_as_of_date);
553 exception
554 WHEN OTHERS THEN
555 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
556 p_msg_name => 'PA_FP_INVALID_DATE_RANGE');
557 x_msg_data := 'PA_FP_INVALID_DATE_RANGE';
558 x_return_status := 'E';
559 x_msg_count := fnd_msg_pub.count_msg;
560 RAISE FND_API.G_EXC_ERROR;
561 end;
562
563 l_track_wp_cost_flag := pa_fp_wp_gen_amt_utils.get_wp_track_cost_amt_flag(p_project_id); --Bug 3801745
564 --moved this up for bug 3675107
565
566 --bug 3824042
567 SELECT project_currency_code, projfunc_currency_code INTO l_prj_currency_code, l_prjfunc_currency_code FROM pa_projects_all WHERE project_id = p_project_id;
568
569 IF p_task_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
570 THEN
571 l_task_id := 0;
572 ELSE
573 l_task_id := nvl(p_task_id, 0);
574 END IF;
575
576 --bug# 3764224 Changes for RLM start
577 /*IF p_object_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
578 THEN
582 END IF;*/
579 l_assignment_id := 0;
580 ELSE
581 l_assignment_id := nvl(p_object_id, 0);
583
584 IF p_resource_assignment_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
585 THEN
586 l_assignment_id := 0;
587 ELSE
588 l_assignment_id := nvl(p_resource_assignment_id, 0);
589 END IF;
590 --bug# 3764224 Changes for RLM end
591
592 IF p_object_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
593 THEN
594 l_object_version_id := 0;
595 ELSE
596 l_object_version_id := nvl(p_object_version_id, 0);
597 END IF;
598
599 IF p_brief_overview = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
600 THEN
601 l_brief_overview := null;
602 ELSE
603 l_brief_overview := p_brief_overview;
604 END IF;
605
606 IF p_progress_comment = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
607 THEN
608 l_progress_comment := null;
609 ELSE
610 l_progress_comment := p_progress_comment;
611 END IF;
612
613 IF p_pm_product_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
614 THEN
615 l_pm_product_code := null;
616 ELSE
617 l_pm_product_code := p_pm_product_code;
618 END IF;
619
620 OPEN cur_check_published_version(p_structure_version_id, p_project_id);
621 FETCH cur_check_published_version INTO l_published_structure;
622 CLOSE cur_check_published_version;
623
624 IF l_published_structure = 'Y'
625 OR p_structure_version_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM --maansari4/8
626 THEN
627 l_structure_version_id := null;
628 ELSE
629 l_structure_version_id := p_structure_version_id;
630 END IF;
631
632 --bug no. 3586648 start
633 IF p_scheduled_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
634 THEN
635 l_scheduled_start_date := null;
636 ELSE
637 l_scheduled_start_date := p_scheduled_start_date;
638 END IF;
639
640 IF p_scheduled_finish_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
641 THEN
642 l_scheduled_finish_date := null;
643 ELSE
644 l_scheduled_finish_date := p_scheduled_finish_date;
645 END IF;
646
647 /* Bug 3606627 : Shifting this code below
648 IF p_actual_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
649 THEN
650 IF p_actual_cost_this_period > 0 OR p_actual_effort_this_period > 0
651 THEN
652 l_actual_start_date := l_scheduled_start_date;
653 ELSE
654 l_actual_start_date := null;
655 END IF;
656 ELSE
657 l_actual_start_date := p_actual_start_date;
658 END IF;
659 */
660
661 --bug no. 3586648 end
662
663 IF p_actual_finish_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
664 THEN
665 l_actual_finish_date := null;
666 ELSE
667 l_actual_finish_date := p_actual_finish_date;
668 END IF;
669
670 IF p_estimated_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
671 THEN
672 l_estimated_start_date := null;
673 ELSE
674 l_estimated_start_date := p_estimated_start_date;
675 END IF;
676
677 IF p_estimated_finish_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
678 THEN
679 l_estimated_finish_date := null;
680 ELSE
681 l_estimated_finish_date := p_estimated_finish_date;
682 END IF;
683
684 IF p_actual_effort_this_period <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
685 p_actual_effort_this_period IS NOT NULL
686 THEN
687 l_actual_effort_this_period := p_actual_effort_this_period; -- This is incremental
688 ELSE
689 l_actual_effort_this_period := null;
690 END IF;
691
692 -- 3958686, moved this code from below
693 --bug# 3764224 Changes for RLM start
694 -- Bug 3606627 Start
695 /*IF p_resource_list_member_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
696 p_resource_list_member_id IS NOT NULL
697 THEN
698 l_resource_list_member_id := p_resource_list_member_id;
699 ELSE
700 l_resource_list_member_id := null;
701 END IF;*/
702
703 IF p_object_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
704 p_object_id IS NOT NULL
705 THEN
706 l_resource_list_member_id := p_object_id;
707 ELSE
708 l_resource_list_member_id := null;
709 END IF;
710 --bug# 3764224 Changes for RLM end
711 /*
712 --bug 3958686, start
713 OPEN cur_assgmt(l_resource_list_member_id, l_assignment_id, l_task_id, p_project_id, p_structure_version_id);
714 FETCH cur_assgmt INTO l_ta_display_flag;
715 CLOSE cur_assgmt;
716 --bug 3958686, end
717
718 IF g1_debug_mode = 'Y' THEN
719 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_ta_display_flag '||l_ta_display_flag, x_Log_Level=> 3);
720 END IF;
721 */
722
723 -- 3970229 actuals can be -ive
724 /*--3779387, 3958686 this error should not be raised for hidden assignment as in this case it is being called from update_task_progress
725 IF l_actual_effort_this_period < 0 AND l_ta_display_flag = 'Y'
726 THEN
727 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
731 RAISE FND_API.G_EXC_ERROR;
728 ,p_msg_name => 'PA_TP_NO_NEG_ACT');
729 x_msg_data := 'PA_TP_NO_NEG_ACT';
730 x_return_status := 'E';
732 END IF;
733 */
734 --bug 3958686, end
735
736
737 IF p_etc_effort_this_period <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
738 p_etc_effort_this_period IS NOT NULL
739 --p_actual_finish_date IS NULL AND p_actual_finish_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE maansari4/27
740 THEN
741 l_etc_effort_this_period := p_etc_effort_this_period; -- This is cumulative
742 ELSE
743 l_etc_effort_this_period := null;
744 END IF;
745
746 /* 5726773
747 --bug 3779387
748 IF l_etc_effort_this_period < 0
749 THEN
750 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
751 ,p_msg_name => 'PA_TP_NO_NEG_ETC');
752 x_msg_data := 'PA_TP_NO_NEG_ETC';
753 x_return_status := 'E';
754 RAISE FND_API.G_EXC_ERROR;
755 END IF;
756 */
757
758
759 --maansari 4/27
760 -- Bug 3956299 : As per discussion with Saima, ETC should be given precedence over act finish date
761 --IF p_actual_finish_date IS NOT NULL AND p_actual_finish_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
762 --THEN
763 -- l_etc_effort_this_period := 0;
764 --END IF;
765 IF l_etc_effort_this_period IS NOT NULL AND l_etc_effort_this_period > 0
766 THEN
767 l_actual_finish_date := null;
768 END IF;
769
770 --maansari 4/27
771
772
773 IF p_txn_currency_code <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND
774 p_txn_currency_code IS NOT NULL
775 THEN
776 l_txn_currency_code := p_txn_currency_code;
777 ELSE
778 l_txn_currency_code := null;
779 END IF;
780
781 IF p_rate_based_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND
782 p_rate_based_flag IS NOT NULL
783 THEN
784 l_rate_based_flag := p_rate_based_flag;
785 ELSE
786 l_rate_based_flag := null;
787 END IF;
788
789 IF p_resource_class_code <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND
790 p_resource_class_code IS NOT NULL
791 THEN
792 l_resource_class_code := p_resource_class_code;
793 ELSE
794 l_resource_class_code := null;
795 END IF;
796
797 IF p_rbs_element_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
798 p_rbs_element_id IS NOT NULL
799 THEN
800 l_rbs_element_id := p_rbs_element_id;
801 ELSE
802 l_rbs_element_id := null;
803 END IF;
804 -- Raw Cost Changes : Changed the vaiable names to include raw
805 IF p_actual_cost <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
806 p_actual_cost IS NOT NULL
807 THEN
808 l_actual_raw_cost := p_actual_cost;
809 ELSE
810 l_actual_raw_cost := null;
811 END IF;
812 -- Raw Cost Changes : Changed the vaiable names to include raw
813 IF p_actual_cost_this_period <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
814 p_actual_cost_this_period IS NOT NULL
815 THEN
816 l_actual_raw_cost_this_period := p_actual_cost_this_period;
817 ELSE
818 l_actual_raw_cost_this_period := null;
819 END IF;
820
821 ---- 3970229 actuals can be -ive
822 /* --3779387
823 IF l_actual_raw_cost_this_period < 0
824 THEN
825 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
826 ,p_msg_name => 'PA_TP_NO_NEG_ACT');
827 x_msg_data := 'PA_TP_NO_NEG_ACT';
828 x_return_status := 'E';
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831 */
832
833
834 IF p_actual_effort <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
835 p_actual_effort IS NOT NULL
836 THEN
837 l_actual_effort := p_actual_effort;
838 ELSE
839 l_actual_effort := null;
840 END IF;
841 -- Raw Cost Changes : Changed the vaiable names to include raw
842 IF p_etc_cost_this_period <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND
843 p_etc_cost_this_period IS NOT NULL
844 THEN
845 l_etc_raw_cost_this_period := p_etc_cost_this_period;
846 ELSE
847 l_etc_raw_cost_this_period := null;
848 END IF;
849 -- Bug 3606627 End
850
851 /* 5726773
852 --bug 3779387 ---4378391 Added l_etc_effort_this_period is null
853 IF l_etc_raw_cost_this_period < 0 and l_etc_effort_this_period is null
854 THEN
855 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
856 ,p_msg_name => 'PA_TP_NO_NEG_ETC');
857 x_msg_data := 'PA_TP_NO_NEG_ETC';
858 x_return_status := 'E';
859 RAISE FND_API.G_EXC_ERROR;
860 END IF;
861 */
862
863 /* Bug 3606627 Shifting this code below
864 l_res_list_memb_id_tbl.extend(1);
865 l_res_list_memb_id_tbl(1) := p_resource_list_member_id;
866 l_txn_currency_code := p_txn_currency_code;
867 */
868
869
870 ---- if status is CANCELLED
871 IF PA_PROGRESS_UTILS.get_system_task_status(PA_PROGRESS_UTILS.get_task_status( p_project_id, p_task_id)) = 'CANCELLED'
872 THEN
873 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
877 RAISE FND_API.G_EXC_ERROR;
874 ,p_msg_name => 'PA_TP_ASSG_CANT_NTER_PRG_CNCEL');
875 x_msg_data := 'PA_TP_ASSG_CANT_NTER_PRG_CNCEL';
876 x_return_status := 'E';
878 END IF;
879 ---- if status is CANCELLED
880
881 /* Commented by rtarway for BUG 3762650
882 ---- if status is ON HOLD
883 IF PA_PROGRESS_UTILS.get_system_task_status(PA_PROGRESS_UTILS.get_task_status( p_project_id, p_task_id)) = 'ON HOLD'
884 THEN
885 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
886 ,p_msg_name => 'PA_TP_ASSG_CANT_NTER_PRG_ONHOLD');
887 x_msg_data := 'PA_TP_ASSG_CANT_NTER_PRG_ONHOLD';
888 x_return_status := 'E';
889 RAISE FND_API.G_EXC_ERROR;
890 END IF;
891 ---- if status is ON HOLD
892 */
893 --bug no. 3586648 start
894 SELECT structure_sharing_code
895 INTO l_structure_sharing_code
896 FROM pa_projects_all
897 WHERE project_id = p_project_id;
898
899 --- check if structure is shared and actuals are passed
900 /*l_structure_shared := PA_PROJECT_STRUCTURE_UTILS.Check_Sharing_Enabled(p_project_id);
901 IF l_structure_shared = 'Y'
902 THEN
903 IF (p_actual_cost_this_period <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_actual_cost_this_period >0)
904 OR (p_actual_effort_this_period <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM AND p_actual_effort_this_period >0)
905 THEN
906 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
907 ,p_msg_name => 'PA_TP_CANT_NTER_ACT_SHR_STR');
908 x_msg_data := 'PA_TP_CANT_NTER_ACT_SHR_STR';
909 x_return_status := 'E';
910 RAISE FND_API.G_EXC_ERROR;
911 END IF;
912 END IF;*/
913 --- check if structure is shared and actuals are passed
914 --bug no. 3586648 end
915 --added by rtarway for 3816022
916 IF (p_calling_module = 'AMG' AND l_structure_sharing_code = 'SHARE_FULL')
917 THEN
918 IF (l_actual_effort IS NOT NULL AND l_actual_effort >0)
919 OR (l_actual_raw_cost IS NOT NULL AND l_actual_raw_cost >0)
920 THEN
921
922 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
923 ,p_msg_name => 'PA_TP_CANT_NTER_ACT_SHR_STR');
924 x_msg_data := 'PA_TP_CANT_NTER_ACT_SHR_STR';
925 x_return_status := 'E';
926 RAISE FND_API.G_EXC_ERROR;
927 END IF;
928 END IF;
929 --end added by rtarway for 3816022
930
931 ----- Wrong MODE
932 IF p_progress_mode not in ( 'FUTURE' )
933 THEN
934 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
935 ,p_msg_name => 'PA_TP_WRONG_PRG_MODE4');
936 x_msg_data := 'PA_TP_WRONG_PRG_MODE4';
937 x_return_status := 'E';
938 RAISE FND_API.G_EXC_ERROR;
939 END IF;
940 ----- Wrong MODE
941
942 ----- Invalid DATE
943 IF p_as_of_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE OR p_as_of_date IS NULL
944 THEN
945 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
946 ,p_msg_name => 'PA_TP_INV_AOD');
947 x_msg_data := 'PA_TP_INV_AOD';
948 x_return_status := 'E';
949 RAISE FND_API.G_EXC_ERROR;
950 END IF;
951 ----- Wrong DATE
952
953
954 -- Bug 3979303 : Commentee code here and moved it below with some modification
955 /*
956 IF p_estimated_finish_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE AND p_estimated_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
957 THEN
958 IF (p_estimated_finish_date < p_estimated_start_date)
959 THEN
960 x_return_status := FND_API.G_RET_STS_ERROR;
961 pa_utils.add_message(p_app_short_name => 'PA',
962 p_msg_name => 'PA_EST_DATES_INV');
963 raise fnd_api.g_exc_error;
964 END IF;
965 END IF;
966 */
967
968 --bug 4185364, start
969 -- if action is SAVE then delete al the present and future working records.
970 --there shud be only one working record.
971 if p_action = 'SAVE'
972 THEN
973 delete from pa_percent_completes
974 where project_id= p_project_id
975 and object_id = p_object_id
976 and published_flag = 'N'
977 and task_id = p_task_id
978 and structure_type = p_structure_type
979 ;
980
981 delete from pa_progress_rollup
982 where project_id= p_project_id
983 and object_id = p_object_id
984 and current_flag = 'W'
985 and proj_element_id = p_task_id
986 and structure_type = p_structure_type
987 and structure_version_id is null
988 ;
989 end if;
990 --bug 4185364, end
991
992
993 --bug 3879461
994 --This code is required is PUBLISH mode to delete working progress records on previous dates.
995 if p_action = 'PUBLISH' and p_structure_type = 'WORKPLAN'
996 then
997 delete from pa_percent_completes
998 where project_id= p_project_id
999 and object_id = p_object_id
1000 and published_flag = 'N'
1001 and date_computed <= p_as_of_date --bug 4247839, modified so that two records are not created for same as of date
1002 and task_id = p_task_id
1006 delete from pa_progress_rollup
1003 and structure_type = p_structure_type
1004 ;
1005
1007 where project_id= p_project_id
1008 and object_id = p_object_id
1009 and current_flag = 'W'
1010 and as_of_date < p_as_of_date
1011 and proj_element_id = p_task_id
1012 and structure_type = p_structure_type
1013 and structure_version_id is null
1014 ;
1015 end if;
1016 --bug 3879461
1017
1018
1019 -- Bug 3606627 Start
1020 IF p_calling_module = 'AMG' THEN
1021 BEGIN
1022 /* COMMENTING for performance issues
1023 SELECT rate_based_flag, resource_class_code, txn_currency_code,
1024 rbs_element_id, resource_list_member_id, assignment_start_date, assignment_end_date -- Bug 3956299 : Added assignment_end_date
1025 INTO l_rate_based_flag, l_resource_class_code, l_txn_currency_code,
1026 l_rbs_element_id, l_resource_list_member_id, l_scheduled_start_date, l_scheduled_finish_date -- Bug 3956299 : Added l_scheduled_finish_date
1027 FROM pa_task_assignments_v
1028 WHERE --resource_assignment_id = l_assignment_id Bug 3799841
1029 resource_list_member_id = l_resource_list_member_id -- Bug 3799841
1030 AND structure_version_id = p_structure_version_id
1031 AND task_version_id = l_object_version_id;
1032 */
1033
1034 SELECT rate_based_flag,
1035 resource_class_code,
1036 PA_TASK_ASSIGNMENT_UTILS.get_planned_currency_info(pra.resource_assignment_id, pra.project_id, 'txn_currency_code') as txn_currency_code,
1037 rbs_element_id,
1038 resource_list_member_id,
1039 schedule_start_date,
1040 schedule_end_date,
1041 pra.resource_assignment_id -- Bug 4186007 : Derive res_assignment_id from AMG, it can be null
1042 INTO l_rate_based_flag, l_resource_class_code, l_txn_currency_code,
1043 l_rbs_element_id, l_resource_list_member_id, l_scheduled_start_date,
1044 l_scheduled_finish_date,
1045 l_assignment_id -- Bug 4186007 : Derive res_assignment_id from AMG, it can be null
1046 FROM pa_resource_assignments pra,
1047 PA_PROJ_ELEMENT_VERSIONS PPEV
1048 where resource_list_member_id = l_resource_list_member_id
1049 AND PPEV.PROJECT_ID = p_project_id
1050 AND PPEV.PARENT_STRUCTURE_VERSION_ID = p_structure_version_id
1051 AND pra.TASK_ID = PPEV.PROJ_ELEMENT_ID
1052 AND pra.wbs_element_version_id = ppev.element_version_id
1053 --Added following conditions bug4110593, rtarway
1054 AND pra.task_id = p_task_id
1055 AND pra.wbs_element_version_id = p_object_version_id;
1056 EXCEPTION
1057 WHEN NO_DATA_FOUND THEN
1058 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1059 p_msg_name => 'PA_TP_INV_ASSGN_AMG',
1060 P_TOKEN1 => 'OBJECT_ID',
1061 P_VALUE1 => l_resource_list_member_id);
1062 x_msg_data := 'PA_TP_INV_ASSGN_AMG';
1063 x_return_status := FND_API.G_RET_STS_ERROR;
1064 RAISE FND_API.G_EXC_ERROR;
1065 END;
1066
1067
1068 /* Begin: Fix for Bug # 3988457. */
1069
1070 /* Currently the value of p_txn_currency_code is not passed to this API in the AMG flow.
1071 However, the following code will handle the case if the AMG flow is later modified to
1072 pass the value of p_txn_currency_code to this API. */
1073
1074 IF (p_txn_currency_code <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR AND
1075
1076 p_txn_currency_code IS NOT NULL)
1077
1078 THEN
1079
1080 l_txn_currency_code := p_txn_currency_code;
1081
1082 END IF;
1083
1084 /* End: Fix for Bug # 3988457. */
1085
1086 END IF;
1087
1088 -- Bug 3979303 : Begin
1089 IF l_estimated_finish_date is not null and l_estimated_start_date IS NULL THEN
1090 l_estimated_start_date := l_scheduled_start_date;
1091 END IF;
1092
1093 IF l_estimated_finish_date is not null and l_estimated_finish_date < nvl(l_estimated_start_date,l_estimated_finish_date+1) THEN
1094 x_return_status := FND_API.G_RET_STS_ERROR;
1095 pa_utils.add_message(p_app_short_name => 'PA',
1096 p_msg_name => 'PA_EST_DATES_INV');
1097 raise fnd_api.g_exc_error;
1098 END IF;
1099 -- Bug 3979303 : End
1100
1101
1102 IF p_actual_start_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1103 THEN
1104 IF l_actual_raw_cost_this_period > 0 OR l_actual_effort_this_period > 0
1105 THEN
1106 l_actual_start_date := l_scheduled_start_date;
1107 ELSE
1108 l_actual_start_date := null;
1109 END IF;
1110 ELSE
1111 l_actual_start_date := p_actual_start_date;
1112 END IF;
1113
1114 -- FPM Dev CR 3 : Added Logic to get total cost from last submission and this period.
1115 --bug# 3764224 Changes for RLM
1116 --OPEN cur_task_cost(p_project_id, l_assignment_id, p_structure_type, l_resource_class_code, p_as_of_date);
1117
1118 -- if structure is shared then we need to get last actuals <= to as_of_date
1119 if (l_structure_sharing_code = 'SHARE_FULL' OR p_calling_module = 'AMG' or p_calling_module = 'HIDDEN_ASGMT') then -- Bug 5294838:Added AMG check; 5441402:added HIDDEN_ASGMT
1120 OPEN cur_task_cost_shared(p_project_id, l_resource_list_member_id, p_structure_type, l_resource_class_code, p_as_of_date);
1124 OPEN cur_task_cost(p_project_id, l_resource_list_member_id, p_structure_type, l_resource_class_code, p_as_of_date);
1121 FETCH cur_task_cost_shared INTO l_act_raw_cost_last_subm_tc, l_act_raw_cost_last_subm_pc, l_act_raw_cost_last_subm_fc,l_act_bur_cost_last_subm_tc, l_act_bur_cost_last_subm_pc, l_act_bur_cost_last_subm_fc, l_act_effort_last_subm;
1122 CLOSE cur_task_cost_shared;
1123 else
1125 FETCH cur_task_cost INTO l_act_raw_cost_last_subm_tc, l_act_raw_cost_last_subm_pc, l_act_raw_cost_last_subm_fc,l_act_bur_cost_last_subm_tc, l_act_bur_cost_last_subm_pc, l_act_bur_cost_last_subm_fc, l_act_effort_last_subm;
1126 CLOSE cur_task_cost;
1127 end if;
1128
1129 OPEN cur_task_cost_latest(p_project_id, l_resource_list_member_id, p_structure_type, l_resource_class_code, p_as_of_date);
1130 FETCH cur_task_cost_latest INTO l_act_raw_cost_latest_subm_tc, l_act_raw_cost_latest_subm_pc, l_act_raw_cost_latest_subm_fc,l_act_bur_cost_latest_subm_tc, l_act_bur_cost_latest_subm_pc, l_act_bur_cost_latest_subm_fc, l_act_effort_latest_subm;
1131 if cur_task_cost_latest%notfound then
1132 subm_prog_exists_aod := 'N';
1133 else
1134 subm_prog_exists_aod := 'Y';
1135 end if;
1136 CLOSE cur_task_cost_latest;
1137
1138
1139 IF p_calling_module = 'AMG' THEN
1140 -- If called from AMG then l_Actual_cost and l_actual_effort will behave as to_date cost and not as last_submitted
1141 -- So we need to derive this_period cost or effort
1142 -- Bug 5294838 : Commented the fix of 4541353, Added new
1143 /*
1144 -- Begin Bug # 4541353.
1145 -- Need to set the values of: l_actual_raw_cost and l_actual_effort before calculating: l_actual_raw_cost_this_period
1146 -- and l_actual_effort_this_period.
1147 l_actual_raw_cost := nvl(l_act_raw_cost_last_subm_tc,0);
1148 l_actual_effort := nvl(l_act_effort_last_subm,0);
1149 -- End Bug # 4541353.
1150 */
1151 l_actual_raw_cost_this_period := nvl(l_actual_raw_cost,0) - nvl(l_act_raw_cost_last_subm_tc,0);
1152 l_actual_effort_this_period := nvl(l_actual_effort,0) - nvl(l_act_effort_last_subm,0);
1153
1154 l_actual_raw_cost := nvl(l_act_raw_cost_last_subm_tc,0);
1155 l_actual_effort := nvl(l_act_effort_last_subm,0);
1156 -- Bug 5294838 : Added code below
1157 if (l_structure_sharing_code = 'SHARE_FULL') then
1158 l_actual_raw_cost_this_period := 0;
1159 l_actual_effort_this_period := 0;
1160 end if;
1161
1162 END IF;
1163
1164
1165 -- l_res_list_memb_id_tbl.extend(1);
1166 -- l_res_list_memb_id_tbl(1) := l_resource_list_member_id;
1167
1168 -- Bug 3606627 End
1169
1170 -- 4533112 Begin
1171
1172 IF g1_debug_mode = 'Y' THEN
1173 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Client Extension Logic Starts', x_Log_Level=> 3);
1174 END IF;
1175
1176 OPEN c_get_dates_overrides(p_project_id,l_resource_list_member_id,'PA_ASSIGNMENTS',p_as_of_date);
1177 FETCH c_get_dates_overrides INTO
1178 l_db_date_override_flag
1179 , l_db_estimated_start_date
1180 , l_db_estimated_finish_date
1181 , l_db_actual_start_date
1182 , l_db_actual_finish_date
1183 , l_task_status_code;
1184 CLOSE c_get_dates_overrides;
1185
1186 IF g1_debug_mode = 'Y' THEN
1187 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_db_date_override_flag='||l_db_date_override_flag, x_Log_Level=> 3);
1188 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_db_estimated_start_date='||l_db_estimated_start_date, x_Log_Level=> 3);
1189 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_db_estimated_finish_date='||l_db_estimated_finish_date, x_Log_Level=> 3);
1190 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_db_actual_start_date='||l_db_actual_start_date, x_Log_Level=> 3);
1191 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_db_actual_finish_date='||l_db_actual_finish_date, x_Log_Level=> 3);
1192 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_task_status_code='||l_task_status_code, x_Log_Level=> 3);
1193 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Calling PA_PROGRESS_CLIENT_EXTN.GET_TASK_RES_OVERRIDE_INFO with following params', x_Log_Level=> 3);
1194 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_project_id='||p_project_id, x_Log_Level=> 3);
1195 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_structure_version_id='||p_structure_version_id, x_Log_Level=> 3);
1196 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_object_type='||l_object_type, x_Log_Level=> 3);
1197 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_object_version_id='||l_object_version_id, x_Log_Level=> 3);
1198 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_resource_list_member_id='||l_resource_list_member_id, x_Log_Level=> 3);
1199 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'p_task_id='||p_task_id, x_Log_Level=> 3);
1200 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_estimated_start_date='||l_estimated_start_date, x_Log_Level=> 3);
1201 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_estimated_finish_date='||l_estimated_finish_date, x_Log_Level=> 3);
1202 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_actual_start_date='||l_actual_start_date, x_Log_Level=> 3);
1206
1203 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_actual_finish_date='||l_actual_finish_date, x_Log_Level=> 3);
1204 END IF;
1205
1207 l_date_override_flag := 'N';
1208
1209 PA_PROGRESS_CLIENT_EXTN.GET_TASK_RES_OVERRIDE_INFO(
1210 p_project_id => p_project_id,
1211 p_structure_type => 'WORKPLAN',
1212 p_structure_version_id => p_structure_version_id,
1213 p_object_type => l_object_type,
1214 p_object_id => l_resource_list_member_id,
1215 p_object_version_id => l_object_version_id,
1216 p_proj_element_id => p_task_id,
1217 p_task_status => l_task_status_code,
1218 p_percent_complete => null,
1219 p_estimated_start_date => l_estimated_start_date,
1220 p_estimated_finish_date => l_estimated_finish_date,
1221 p_actual_start_date => l_actual_start_date,
1222 p_actual_finish_date => l_actual_finish_date,
1223 x_estimated_start_date => l_clex_estimated_start_date,
1224 x_estimated_finish_date => l_clex_estimated_finish_date,
1225 x_actual_start_date => l_clex_actual_start_date,
1226 x_actual_finish_date => l_clex_actual_finish_date,
1227 x_return_status => x_return_status,
1228 x_msg_count => x_msg_count,
1229 x_msg_data => x_msg_data
1230 );
1231
1232 IF g1_debug_mode = 'Y' THEN
1233 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'After Call PA_PROGRESS_CLIENT_EXTN.GET_TASK_RES_OVERRIDE_INFO x_return_status='||x_return_status, x_Log_Level=> 3);
1234 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_clex_estimated_start_date='||l_clex_estimated_start_date, x_Log_Level=> 3);
1235 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_clex_estimated_finish_date='||l_clex_estimated_finish_date, x_Log_Level=> 3);
1236 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_clex_actual_start_date='||l_clex_actual_start_date, x_Log_Level=> 3);
1237 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_clex_actual_finish_date='||l_clex_actual_finish_date, x_Log_Level=> 3);
1238 END IF;
1239
1240 IF x_return_status <> 'S' THEN
1241 raise FND_API.G_EXC_ERROR;
1242 END IF;
1243
1244 IF nvl(l_estimated_start_date,FND_API.g_miss_date) <> nvl(l_clex_estimated_start_date,FND_API.g_miss_date)
1245 OR nvl(l_estimated_finish_date,FND_API.g_miss_date) <> nvl(l_clex_estimated_finish_date,FND_API.g_miss_date)
1246 OR nvl(l_actual_start_date,FND_API.g_miss_date) <> nvl(l_clex_actual_start_date,FND_API.g_miss_date)
1247 OR nvl(l_actual_finish_date,FND_API.g_miss_date) <> nvl(l_clex_actual_finish_date,FND_API.g_miss_date)
1248 THEN
1249 l_date_override_flag := 'Y';
1250 l_estimated_start_date := l_clex_estimated_start_date;
1251 l_estimated_finish_date := l_clex_estimated_finish_date;
1252 l_actual_start_date := l_clex_actual_start_date;
1253 l_actual_finish_date := l_clex_actual_finish_date;
1254 END IF;
1255
1256 IF l_date_override_flag = 'N' AND nvl(l_db_date_override_flag, 'N') = 'Y' THEN
1257 l_date_override_flag := 'Y';
1258 END IF;
1259
1260 IF l_date_override_flag = 'N' AND
1261 (nvl(l_estimated_start_date,FND_API.g_miss_date) <> nvl(l_db_estimated_start_date,FND_API.g_miss_date)
1262 OR nvl(l_estimated_finish_date,FND_API.g_miss_date) <> nvl(l_db_estimated_finish_date,FND_API.g_miss_date)
1263 OR nvl(l_actual_start_date,FND_API.g_miss_date) <> nvl(l_db_actual_start_date,FND_API.g_miss_date)
1264 OR nvl(l_actual_finish_date,FND_API.g_miss_date) <> nvl(l_db_actual_finish_date,FND_API.g_miss_date))
1265 THEN
1266 l_date_override_flag := 'Y';
1267 END IF;
1268
1269 IF l_etc_effort_this_period IS NOT NULL AND l_etc_effort_this_period > 0 THEN
1270 l_actual_finish_date := null;
1271 END IF;
1272
1273 IF g1_debug_mode = 'Y' THEN
1274 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_date_override_flag='||l_date_override_flag, x_Log_Level=> 3);
1275 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_estimated_start_date='||l_estimated_start_date, x_Log_Level=> 3);
1276 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_estimated_finish_date='||l_estimated_finish_date, x_Log_Level=> 3);
1277 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_actual_start_date='||l_actual_start_date, x_Log_Level=> 3);
1278 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_actual_finish_date='||l_actual_finish_date, x_Log_Level=> 3);
1279 END IF;
1280
1281 -- 4533112 End
1282
1283
1284
1285 IF l_actual_start_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE AND l_actual_finish_date <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1286 THEN
1287 IF l_actual_finish_date < l_actual_start_date
1288 THEN
1289 IF TRUNC(SYSDATE) < l_actual_start_date
1290 THEN
1291 l_actual_finish_date := l_actual_start_date;
1292 ELSE
1293 l_actual_finish_date := TRUNC(SYSDATE);
1294 END IF;
1295 END IF;
1296 END IF;
1297
1298
1299 l_last_progress_date := PA_PROGRESS_UTILS.GET_LATEST_AS_OF_DATE(
1300 p_task_id => p_task_id
1301 ,p_project_id => p_project_id
1302 --,p_object_id => l_assignment_id --bug# 3764224 Changes for RLM
1303 ,p_object_id => l_resource_list_member_id
1304 ,p_object_type => l_object_type
1308
1305 ,p_structure_type => p_structure_type
1306 );
1307
1309 IF g1_debug_mode = 'Y' THEN
1310 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_last_progress_date: '||l_last_progress_date, x_Log_Level=> 3);
1311 END IF;
1312
1313 l_working_aod := PA_PROGRESS_UTILS.Working_version_exist(
1314 --p_task_id => l_assignment_id --bug# 3764224 Changes for RLM
1315 p_task_id => p_task_id
1316 ,p_project_id => p_project_id
1317 ,p_object_type => l_object_type
1318 ,p_object_id => l_resource_list_member_id --bug# 3764224 Added for RLM
1319 ,p_as_of_date => p_as_of_date); --bug 4185364, get working record upto p_as_of_date
1320 -- as we dont want to update future working records.
1321
1322 IF g1_debug_mode = 'Y' THEN
1323 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_working_aod: '||l_working_aod, x_Log_Level=> 3);
1324 END IF;
1325
1326
1327 l_progress_exists_on_aod := PA_PROGRESS_UTILS.check_prog_exists_on_aod(
1328 p_project_id => p_project_id
1329 ,p_object_type => l_object_type
1330 ,p_object_version_id => l_object_version_id
1331 --,p_task_id => l_assignment_id --bug# 3764224 Changes for RLM
1332 ,p_task_id => p_task_id
1333 ,p_as_of_date => p_as_of_date
1334 ,p_structure_type => p_structure_type
1335 ,p_object_id => l_resource_list_member_id --bug# 3764224 Added for RLM
1336 );
1337
1338 IF g1_debug_mode = 'Y' THEN
1339 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_progress_exists_on_aod: '||l_progress_exists_on_aod, x_Log_Level=> 3);
1340 END IF;
1341
1342
1343 IF p_as_of_date < NVL( l_last_progress_date, p_as_of_date )
1344 --AND l_working_aod IS NULL -- progress exists after as of date -- commented as not needed Satish
1345 THEN
1346 --You cannot create a future progress when there exists a progress
1347 --after AS_OF_DATE for this task.
1348 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1349 p_msg_name => 'PA_TP_ASSG_WRONG_PRG_MODE3',
1350 p_token1 => 'AS_OF_DATE',
1351 p_value1 => p_as_of_date );
1352 x_msg_data := 'PA_TP_ASSG_WRONG_PRG_MODE3';
1353 x_return_status := 'E';
1354 RAISE FND_API.G_EXC_ERROR;
1355 --bug# 3821106 Satish start, bug 4185364
1356 /*ELSIF (p_as_of_date = NVL(l_last_progress_date, p_as_of_date + 1 ) AND p_action = 'SAVE')
1357 THEN
1358 if (l_working_aod = p_as_of_date) then
1359 l_db_action := 'UPDATE';
1360 else
1361 l_db_action := 'CREATE';
1362 end if;
1363 ELSIF (p_as_of_date = NVL(l_last_progress_date, p_as_of_date + 1 ) AND p_action = 'PUBLISH')
1364 THEN
1365 l_db_action := 'CREATE';*/
1366 /* commenting out as now we will allow to create a working rec if published record exists on that as_of_date
1367 --You cannot save progress when there exists a published progress
1368 --for this as of date.
1369 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1370 p_msg_name => 'PA_TP_ASSG_WRONG_ACTION',
1371 p_token1 => 'AS_OF_DATE',
1372 p_value1 => p_as_of_date );
1373 x_msg_data := 'PA_TP_ASSG_WRONG_ACTION';
1374 x_return_status := 'E';
1375 RAISE FND_API.G_EXC_ERROR;
1376 --bug# 3821106 Satish end
1377 */
1378 ELSE
1379 --Validate as of date
1380 -- Bug 3627315 : Check valid as of date should not be called from AMG or Task Progress Details page
1381 -- Beacuse from both the places we submit progress for all objects against one cycle date
1382
1383 --bug 3994165, commmenting as as of date validation is not required
1384 /*IF p_calling_module <> 'AMG' -- Bug 3627315
1385 AND p_calling_module <> 'TASK_PROG_DET_PAGE' -- Bug 3627315
1386 --bug# 3764224 Added for RLM
1387 --AND PA_PROGRESS_UTILS.CHECK_VALID_AS_OF_DATE( p_as_of_date, p_project_id, l_assignment_id, l_object_type ) = 'N'
1388 AND PA_PROGRESS_UTILS.CHECK_VALID_AS_OF_DATE( p_as_of_date, p_project_id, l_resource_list_member_id, l_object_type, p_task_id ) = 'N'
1389 AND nvl(l_last_progress_date,p_as_of_date + 1 ) <> p_as_of_date
1390 THEN
1391 --Add message
1392 --Invalid as of date
1393 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1394 ,p_msg_name => 'PA_TP_INV_AOD2');
1395 x_msg_data := 'PA_TP_INV_AOD2';
1396 x_return_status := 'E';
1397 RAISE FND_API.G_EXC_ERROR;
1398 END IF;*/
1399
1400 IF l_progress_exists_on_aod = 'WORKING'
1401 THEN
1402 --update the existing working progress record
1403 l_db_action := 'UPDATE';
1404 --This code is modified for Correction flow bug no.3595585. Satish
1405 --In ppc for correction flow record should be inserted. bug no. 3595585 Satish
1409 --and if action is save then create a new record
1406 ELSIF l_progress_exists_on_aod = 'PUBLISHED'
1407 THEN
1408 --bug 4185364, if correcting published record it should be update as we dont want to maintain history of corrected records
1410 IF p_action = 'PUBLISH' THEN
1411 l_db_action := 'UPDATE';
1412 ELSE
1413 l_db_action := 'CREATE';
1414 END IF;
1415 ELSIF l_progress_exists_on_aod = 'N'
1416 THEN
1417 --Create a new working progress record.
1418 l_db_action := 'CREATE';
1419 --there is only one working version allowed hence update that record only if it exists
1420 --IF l_working_aod IS NOT NULL , now this case will never come
1421 IF l_working_aod IS NOT NULL
1422 THEN
1423 l_db_action := 'UPDATE';
1424 END IF;
1425 END IF;
1426 END IF;
1427
1428 IF g1_debug_mode = 'Y' THEN
1429 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_db_action: '||l_db_action, x_Log_Level=> 3);
1430 END IF;
1431
1432 IF ( p_action = 'PUBLISH')
1433 THEN
1434 l_published_flag := 'Y';
1435 l_current_flag := 'Y';
1436 l_rollup_current_flag := 'Y'; -- Bug 3879461
1437
1438 UPDATE pa_percent_completes
1439 SET current_flag = 'N'
1440 WHERE project_id = p_project_id
1441 --AND object_id = l_assignment_id --bug# 3764224 Changes for RLM
1442 AND object_id = l_resource_list_member_id
1443 AND task_id = l_task_id --maansari7/21 bug# 3764224 Changes for RLM
1444 AND current_flag = 'Y'
1445 AND object_type = l_object_type;
1446
1447 UPDATE pa_progress_rollup
1448 SET current_flag = 'N'
1449 WHERE project_id = p_project_id
1450 --AND object_id = l_assignment_id --bug# 3764224 Changes for RLM
1451 AND object_id = l_resource_list_member_id
1452 AND proj_element_id = l_task_id --maansari7/21 bug# 3764224 Changes for RLM
1453 AND current_flag = 'Y'
1454 AND object_type = l_object_type
1455 AND structure_version_id is null -- Bug 3846353 : *** AMKSINGH 08/24/04 09:53 am *** Issue F - 2
1456 ;
1457 -- Bug 3879461 Begin
1458 ----- IF l_db_action = 'UPDATE' THEN
1459 /*
1460 -- Delete the published progress record on the same as of date
1461 DELETE FROM pa_progress_rollup
1462 where project_id = p_project_id
1463 and object_id = l_resource_list_member_id
1464 and proj_element_id = l_task_id
1465 and object_type = l_object_type
1466 and structure_version_id is null
1467 and structure_type = 'WORKPLAN'
1468 and current_flag = 'Y'
1469 and trunc(as_of_date) = trunc(p_as_of_date)
1470 and exists(select 1
1471 from pa_progress_rollup
1472 where project_id = p_project_id
1473 and object_id = l_resource_list_member_id
1474 and proj_element_id = l_task_id
1475 and object_type = l_object_type
1476 and structure_version_id is null
1477 and structure_type = 'WORKPLAN'
1478 and current_flag = 'W'
1479 and trunc(as_of_date) = trunc(p_as_of_date)
1480 );
1481 -- Update the working progress record on the same as of date as published progress
1482 -- so that while updating rollup record its values can be considered
1483 -- Basically this is done so get_prog_rollup_id can return this row for update mode
1484
1485 Update pa_progress_rollup
1486 set current_flag = 'Y'
1487 where project_id = p_project_id
1488 and object_id = l_resource_list_member_id
1489 and proj_element_id = l_task_id
1490 and object_type = l_object_type
1491 and structure_version_id is null
1492 and structure_type = 'WORKPLAN'
1493 and current_flag = 'W'
1494 and trunc(as_of_date) = trunc(p_as_of_date);
1495 */
1496 --- while PUBLISH we delete all working rec <= to as_of_date
1497 Delete from pa_progress_rollup
1498 where project_id = p_project_id
1499 and object_id = l_resource_list_member_id
1500 and proj_element_id = l_task_id
1501 and object_type = l_object_type
1502 and structure_version_id is null
1503 and structure_type = 'WORKPLAN'
1504 and current_flag = 'W'
1505 and trunc(as_of_date) <= trunc(p_as_of_date);
1506 ------ END IF;
1507 -- Bug 3879461 End
1508 ELSE
1509 l_published_flag := 'N';
1510 l_current_flag := 'N';
1511 l_rollup_current_flag := 'W'; -- Bug 3879461
1512 END IF;
1513
1514
1515 --bug 3824042, round effort upto 2 deciaml places, start
1516 IF l_rate_based_flag = 'Y'
1517 THEN
1518 l_actual_effort_this_period := round(l_actual_effort_this_period, 5);
1519 l_etc_effort_this_period := round(l_etc_effort_this_period, 5);
1520 ELSE
1521 l_actual_effort_this_period := pa_currency.round_trans_currency_amt(l_actual_effort_this_period, l_txn_currency_code);
1522 l_etc_effort_this_period := pa_currency.round_trans_currency_amt(l_etc_effort_this_period, l_txn_currency_code);
1523 END IF;
1524 --bug 3824042, round effort upto 2 deciaml places, end
1525
1526 --BUG3630743 (Get all planned values)
1527 -- Bug 3696572 : Wrongly l_project_id was passed to below cursor instead of p_project_id
1528 ---4372462 (moved this code here from below)
1529 OPEN c_get_planned_values (l_resource_list_member_id , l_assignment_id, p_project_id ) ;
1530 FETCH c_get_planned_values INTO
1531 l_planned_quantity ,
1535 l_planned_raw_cost_txn_cur,
1532 l_planned_bur_cost_txn_cur,
1533 l_planned_bur_cost_projfunc,
1534 l_planned_bur_cost_proj_cur,
1536 l_planned_raw_cost_proj_cur,
1537 l_planned_raw_cost_projfunc,
1538 l_budget_version_id ;
1539 CLOSE c_get_planned_values;
1540 --BUG3630743
1541
1542
1543 -- Raw Cost Changes Begin
1544 -- IF l_actual_effort_this_period IS NOT NULL bug 3784733 need to call this api even for non-rate based assgns to get the burden multiplier.
1545 -- THEN
1546
1547 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
1548 --bug 4334545
1549 if (l_actual_effort_this_period is not null AND (l_actual_effort_this_period <> 0 or l_actual_raw_cost_this_period <> 0)) then
1550 BEGIN
1551 IF g1_debug_mode = 'Y' THEN
1552 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Calling Get_Res_txn_Cost_Rate', x_Log_Level=> 3);
1553 END IF;
1554 PA_PROGRESS_UTILS.Get_Res_Rate_Burden_Multiplier(
1555 P_res_list_mem_id => l_resource_list_member_id
1556 ,P_project_id => p_project_id
1557 ,P_task_id => p_task_id --bug 3860575
1558 ,p_as_of_date => p_as_of_date --bug 3901289
1559 --maansari6/14 3686920
1560 ,p_structure_version_id => p_structure_version_id
1561 ,p_currency_code => l_txn_currency_code
1562 --maansari6/14 3686920
1563 ,p_calling_mode => 'ACTUAL_RATES'
1564 ,x_resource_curr_code => l_res_cur_code
1565 ,x_resource_raw_rate => l_res_raw_rate
1566 ,x_resource_burden_rate => l_res_burden_rate
1567 ,X_burden_multiplier => l_burden_multiplier
1568 ,x_return_status => l_return_status
1569 ,x_msg_count => l_msg_count
1570 ,x_msg_data => l_msg_data
1571 );
1572
1573 IF g1_debug_mode = 'Y' THEN
1574 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => ' Before conversion l_res_raw_rate'||l_res_raw_rate, x_Log_Level=> 3);
1575 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Before conversion l_res_burden_rate'||l_res_burden_rate, x_Log_Level=> 3);
1576 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Before conversion l_res_cur_code'||l_res_cur_code, x_Log_Level=> 3);
1577 END IF;
1578
1579
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSIGNMENT_PROGRESS_PUB',
1583 p_procedure_name => 'UPDATE_ASSIGNMENT_PROGRESS',
1584 p_error_text => SUBSTRB('PA_PROGRESS_UTILS.Get_Res_Rate_Burden_Multiplier1:'||SQLERRM,1,240));
1585 RAISE FND_API.G_EXC_ERROR;
1586
1587 END;
1588
1589 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1590 x_return_status := 'E';
1591 x_msg_data := l_msg_data;
1592 x_msg_count := l_msg_count;
1593 RAISE FND_API.G_EXC_ERROR;
1594 END IF;
1595
1596 -- Bug 3696429 currency conversion for resource Satish 03-JUL-2004 start
1597 IF l_txn_currency_code <> l_res_cur_code
1598 THEN
1599 PA_PROGRESS_UTILS.CONVERT_CURRENCY_AMOUNTS(
1600 p_project_id => p_project_id
1601 ,p_task_id => p_task_id
1602 --,p_as_of_date => SYSDATE
1603 ,p_as_of_date => p_as_of_date --bug 3901289
1604 ,p_txn_cost => l_res_raw_rate
1605 ,p_txn_curr_code => l_res_cur_code
1606 ,p_project_curr_code => l_txn_currency_code
1607 ,p_project_rate_type => l_project_rate_type
1608 ,p_project_rate_date => l_project_rate_date
1609 ,p_project_exch_rate => l_project_exch_rate
1610 ,p_project_raw_cost => l_proj_res_raw_rate
1611 --,p_projfunc_curr_code => l_txn_currency_code
1612 ,p_projfunc_curr_code => l_projfunc_curr_code --BUG 4354031
1613 ,p_projfunc_cost_rate_type => l_projfunc_cost_rate_type
1614 ,p_projfunc_cost_rate_date => l_projfunc_cost_rate_date
1615 ,p_projfunc_cost_exch_rate => l_projfunc_cost_exch_rate
1616 ,p_projfunc_raw_cost => l_projfunc_res_raw_rate
1617 ,p_structure_version_id => p_structure_version_id -- 3627787
1618 ,x_return_status => l_return_status
1619 ,x_msg_count => l_msg_count
1620 ,x_msg_data => l_msg_data
1621 );
1622
1623 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1624 x_return_status := 'E';
1625 x_msg_data := l_msg_data;
1626 x_msg_count := l_msg_count;
1627 RAISE FND_API.G_EXC_ERROR;
1628 END IF;
1629
1630 IF l_res_raw_rate <> l_res_burden_rate
1631 THEN
1632 PA_PROGRESS_UTILS.CONVERT_CURRENCY_AMOUNTS(
1633 p_project_id => p_project_id
1634 ,p_task_id => p_task_id
1635 -- ,p_as_of_date => SYSDATE
1636 ,p_as_of_date => p_as_of_date --bug 3901289
1637 ,p_txn_cost => l_res_burden_rate
1641 ,p_project_rate_date => l_project_rate_date
1638 ,p_txn_curr_code => l_res_cur_code
1639 ,p_project_curr_code => l_txn_currency_code
1640 ,p_project_rate_type => l_project_rate_type
1642 ,p_project_exch_rate => l_project_exch_rate
1643 ,p_project_raw_cost => l_proj_res_burden_rate
1644 --,p_projfunc_curr_code => l_txn_currency_code
1645 ,p_projfunc_curr_code => l_projfunc_curr_code --BUG 4354031
1646 ,p_projfunc_cost_rate_type => l_projfunc_cost_rate_type
1647 ,p_projfunc_cost_rate_date => l_projfunc_cost_rate_date
1648 ,p_projfunc_cost_exch_rate => l_projfunc_cost_exch_rate
1649 ,p_projfunc_raw_cost => l_projfunc_res_burden_rate
1650 ,p_structure_version_id => p_structure_version_id -- 3627787
1651 ,x_return_status => l_return_status
1652 ,x_msg_count => l_msg_count
1653 ,x_msg_data => l_msg_data
1654 );
1655 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1656 x_return_status := 'E';
1657 x_msg_data := l_msg_data;
1658 x_msg_count := l_msg_count;
1659 RAISE FND_API.G_EXC_ERROR;
1660 END IF;
1661
1662 l_res_burden_rate := l_proj_res_burden_rate;
1663 ELSE
1664 l_res_burden_rate := l_proj_res_raw_rate;
1665 END IF;
1666 l_res_raw_rate := l_proj_res_raw_rate;
1667 END IF; -- IF l_txn_currency_code <> l_res_cur_code
1668
1669 IF g1_debug_mode = 'Y' THEN
1670 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => ' After conversion l_res_raw_rate'||l_res_raw_rate, x_Log_Level=> 3);
1671 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'After conversion l_res_burden_rate'||l_res_burden_rate, x_Log_Level=> 3);
1672 END IF;
1673
1674 end if;
1675
1676 -- IF l_etc_effort_this_period IS NOT NULL --maansari6/14 bug 3784733 need to call this api even for non-rate based assgns to get the burden multiplier.
1677 --bug 4334545
1678 if (l_etc_effort_this_period is not null and (l_etc_effort_this_period <> 0 or l_etc_raw_cost_this_period <> 0)) then
1679 BEGIN
1680 PA_PROGRESS_UTILS.Get_Res_Rate_Burden_Multiplier(
1681 P_res_list_mem_id => l_resource_list_member_id
1682 ,P_project_id => p_project_id
1683 ,P_task_id => p_task_id --bug 3860575
1684 ,p_as_of_date => p_as_of_date --bug 3901289
1685 --maansari6/14 3686920
1686 ,p_structure_version_id => p_structure_version_id
1687 ,p_currency_code => l_txn_currency_code
1688 --maansari6/14 3686920
1689 ,p_calling_mode => 'PLAN_RATES'
1690 ,x_resource_curr_code => l_plan_res_cur_code
1691 ,x_resource_raw_rate => l_plan_res_raw_rate
1692 ,x_resource_burden_rate => l_plan_res_burden_rate
1693 ,X_burden_multiplier => l_plan_burden_multiplier
1694 ,x_return_status => l_return_status
1695 ,x_msg_count => l_msg_count
1696 ,x_msg_data => l_msg_data
1697 );
1698
1699 IF g1_debug_mode = 'Y' THEN
1700 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Before conversion l_plan_res_raw_rate'||l_plan_res_raw_rate, x_Log_Level=> 3);
1701 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Before conversion l_plan_res_burden_rate'||l_plan_res_burden_rate, x_Log_Level=> 3);
1702 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_plan_res_cur_code'||l_plan_res_cur_code, x_Log_Level=> 3);
1703 END IF;
1704
1705 EXCEPTION
1706 WHEN OTHERS THEN
1707 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSIGNMENT_PROGRESS_PUB',
1708 p_procedure_name => 'UPDATE_ASSIGNMENT_PROGRESS',
1709 p_error_text => SUBSTRB('PA_PROGRESS_UTILS.Get_Res_Rate_Burden_Multiplier2:'||SQLERRM,1,240));
1710 RAISE FND_API.G_EXC_ERROR;
1711
1712 END;
1713
1714 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1715 x_return_status := 'E';
1716 x_msg_data := l_msg_data;
1717 x_msg_count := l_msg_count;
1718 RAISE FND_API.G_EXC_ERROR;
1719 END IF;
1720 -- Bug 3784874 currency conversion for ETC Start
1721 --IF l_txn_currency_code <> l_res_cur_code
1722 IF l_txn_currency_code <> l_plan_res_cur_code -- 06-sep-2004,3860575 Satish
1723 THEN
1724 PA_PROGRESS_UTILS.CONVERT_CURRENCY_AMOUNTS(
1725 p_project_id => p_project_id
1726 ,p_task_id => p_task_id
1727 -- ,p_as_of_date => SYSDATE
1728 ,p_as_of_date => p_as_of_date --bug 3901289
1729 ,p_txn_cost => l_plan_res_raw_rate
1730 ,p_txn_curr_code => l_plan_res_cur_code
1731 ,p_project_curr_code => l_txn_currency_code
1732 ,p_project_rate_type => l_project_rate_type
1733 ,p_project_rate_date => l_project_rate_date
1734 ,p_project_exch_rate => l_project_exch_rate
1735 ,p_project_raw_cost => l_proj_res_raw_rate
1736 --,p_projfunc_curr_code => l_txn_currency_code
1740 ,p_projfunc_cost_exch_rate => l_projfunc_cost_exch_rate
1737 ,p_projfunc_curr_code => l_projfunc_curr_code --BUG 4354031
1738 ,p_projfunc_cost_rate_type => l_projfunc_cost_rate_type
1739 ,p_projfunc_cost_rate_date => l_projfunc_cost_rate_date
1741 ,p_projfunc_raw_cost => l_projfunc_res_raw_rate
1742 ,p_structure_version_id => p_structure_version_id
1743 ,x_return_status => l_return_status
1744 ,x_msg_count => l_msg_count
1745 ,x_msg_data => l_msg_data
1746 );
1747
1748 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1749 x_return_status := 'E';
1750 x_msg_data := l_msg_data;
1751 x_msg_count := l_msg_count;
1752 RAISE FND_API.G_EXC_ERROR;
1753 END IF;
1754
1755 IF l_plan_res_raw_rate <> l_plan_res_burden_rate
1756 THEN
1757 PA_PROGRESS_UTILS.CONVERT_CURRENCY_AMOUNTS(
1758 p_project_id => p_project_id
1759 ,p_task_id => p_task_id
1760 -- ,p_as_of_date => SYSDATE
1761 ,p_as_of_date => p_as_of_date --bug 3901289
1762 ,p_txn_cost => l_plan_res_burden_rate
1763 ,p_txn_curr_code => l_plan_res_cur_code
1764 ,p_project_curr_code => l_txn_currency_code
1765 ,p_project_rate_type => l_project_rate_type
1766 ,p_project_rate_date => l_project_rate_date
1767 ,p_project_exch_rate => l_project_exch_rate
1768 ,p_project_raw_cost => l_proj_res_burden_rate
1769 --,p_projfunc_curr_code => l_txn_currency_code
1770 ,p_projfunc_curr_code => l_projfunc_curr_code --BUG 4354031
1771 ,p_projfunc_cost_rate_type => l_projfunc_cost_rate_type
1772 ,p_projfunc_cost_rate_date => l_projfunc_cost_rate_date
1773 ,p_projfunc_cost_exch_rate => l_projfunc_cost_exch_rate
1774 ,p_projfunc_raw_cost => l_projfunc_res_burden_rate
1775 ,p_structure_version_id => p_structure_version_id
1776 ,x_return_status => l_return_status
1777 ,x_msg_count => l_msg_count
1778 ,x_msg_data => l_msg_data
1779 );
1780 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1781 x_return_status := 'E';
1782 x_msg_data := l_msg_data;
1783 x_msg_count := l_msg_count;
1784 RAISE FND_API.G_EXC_ERROR;
1785 END IF;
1786
1787 l_plan_res_burden_rate := l_proj_res_burden_rate;
1788 ELSE
1789 l_plan_res_burden_rate := l_proj_res_raw_rate;
1790 END IF;
1791 l_plan_res_raw_rate := l_proj_res_raw_rate;
1792 END IF; -- l_txn_currency_code <> l_plan_res_cur_code
1793
1794 END IF;
1795 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
1796
1797 IF g1_debug_mode = 'Y' THEN
1798 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => ' After conversion l_plan_res_raw_rate'||l_plan_res_raw_rate, x_Log_Level=> 3);
1799 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'After conversion l_plan_res_burden_rate'||l_plan_res_burden_rate, x_Log_Level=> 3);
1800 END IF;
1801
1802 -- Bug 3784874 currency conversion for ETC End
1803
1804 /* l_burden_cost := pa_currency.round_trans_currency_amt(
1805 l_txn_raw_cost * l_burden_multiplier, l_txn_curr_code) +
1806 l_txn_raw_cost ;*/
1807
1808 -- Raw Cost Changes End
1809
1810 IF l_rate_based_flag = 'Y' --maansari7/6 bug 3742356
1811 THEN
1812
1813 --IF p_actual_cost_this_period = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR p_actual_cost_this_period IS NULL
1814 --THEN --maansari4/10
1815 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
1816
1817 --maansari4/10
1818 IF l_actual_effort_this_period IS NOT NULL
1819 THEN
1820 --maansari4/10
1821 /* Raw Cost Changes Begin
1822 l_res_effort_tbl.extend(1);
1823 l_res_effort_tbl(1) := l_actual_effort_this_period; -- This is incremental
1824
1825 IF g1_debug_mode = 'Y' THEN
1826 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'calling effort to cost for actual', x_Log_Level=> 3);
1827 END IF;
1828
1829 PA_PROGRESS_UTILS.GET_TXN_COST_FOR_EFFORT(
1830 p_project_id => p_project_id
1831 ,p_res_list_memb_id_tbl => l_res_list_memb_id_tbl
1832 ,P_res_effort_tbl => l_res_effort_tbl
1833 ,P_res_txn_cost_tbl => l_res_txn_cost_tbl
1834 ,x_return_status => x_return_status
1835 ,x_msg_count => x_msg_count
1836 ,x_msg_data => x_msg_data
1837 );
1838 IF x_return_status <> FND_API.G_RET_STS_SUCCESS -- FPM Dev CR 8
1839 THEN
1840 RAISE FND_API.G_EXC_ERROR;
1841 END IF;
1842
1843 l_act_txn_cost := l_res_txn_cost_tbl(1); -- This is incremental
1844 */
1845 l_act_txn_raw_cost := nvl(l_actual_effort_this_period,0) * l_res_raw_rate; -- This is incremental
1846 l_act_txn_bur_cost := nvl(l_actual_effort_this_period,0) * l_res_burden_rate; -- This is incremental
1847 -- Raw Cost Changes End
1851 --bug 3824042 end
1848 --bug 3824042 start
1849 l_act_txn_raw_cost := pa_currency.round_trans_currency_amt(l_act_txn_raw_cost, l_txn_currency_code);
1850 l_act_txn_bur_cost := pa_currency.round_trans_currency_amt(l_act_txn_bur_cost, l_txn_currency_code);
1852 ELSE
1853 --l_act_txn_cost := null; -- This is incremental
1854 l_act_txn_raw_cost := null; -- Raw Cost Changes
1855 l_act_txn_bur_cost := null; -- Raw Cost Changes
1856 END IF; --if effort not equal to g_miss_num and not null --maansari4/10
1857 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
1858
1859 --bug 3851528
1860 if p_action = 'PUBLISH'
1861 then
1862 --If etc is not passed then it can be taken as not overidden and let summarization do plann - actual.
1863 IF l_etc_effort_this_period IS NOT NULL OR
1864 l_etc_raw_cost_this_period IS NOT NULL
1865 THEN
1866 UPDATE pa_proj_elements
1867 SET progress_outdated_flag = 'Y'
1868 WHERE project_id=p_project_id
1869 and object_type= 'PA_TASKS'
1870 and proj_element_id = p_task_id
1871 and progress_outdated_flag = 'N'
1872 ;
1873 END IF;
1874 end if;
1875
1876 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
1877 --maansari4/10
1878 IF l_etc_effort_this_period IS NOT NULL
1879 THEN
1880 --maansari4/10
1881 /* Raw Cost Changes Begin
1882 l_etc_res_effort_tbl.extend(1);
1883 l_etc_res_effort_tbl(1) := l_etc_effort_this_period; -- This is cumulative
1884
1885 IF g1_debug_mode = 'Y' THEN
1886 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'calling effort to cost for etc', x_Log_Level=> 3);
1887 END IF;
1888
1889 PA_PROGRESS_UTILS.GET_TXN_COST_FOR_EFFORT(
1890 p_project_id => p_project_id
1891 ,p_res_list_memb_id_tbl => l_res_list_memb_id_tbl
1892 ,P_res_effort_tbl => l_etc_res_effort_tbl
1893 ,P_res_txn_cost_tbl => l_etc_res_txn_cost_tbl
1894 ,x_return_status => x_return_status
1895 ,x_msg_count => x_msg_count
1896 ,x_msg_data => x_msg_data
1897 );
1898
1899 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1900 THEN
1901 RAISE FND_API.G_EXC_ERROR;
1902 END IF;
1903 l_etc_txn_cost := l_etc_res_txn_cost_tbl(1); -- This is cumulative
1904 */
1905 -- Bug 3627315 : Using plan rates instead of actual rates
1906 l_etc_txn_raw_cost := nvl(l_etc_effort_this_period,0)*l_plan_res_raw_rate; -- This is cumulative
1907 l_etc_txn_bur_cost := nvl(l_etc_effort_this_period,0)*l_plan_res_burden_rate; -- This is cumulative
1908 -- Raw Cost Changes End
1909 --bug 3824042 start
1910 l_etc_txn_raw_cost := pa_currency.round_trans_currency_amt(l_etc_txn_raw_cost, l_txn_currency_code);
1911 l_etc_txn_bur_cost := pa_currency.round_trans_currency_amt(l_etc_txn_bur_cost, l_txn_currency_code);
1912 --bug 3824042 end
1913 ELSE
1914 --l_etc_txn_cost := null; -- This is cumulative
1915 l_etc_txn_raw_cost := null; -- Raw Cost Changes
1916 l_etc_txn_bur_cost := null; -- Raw Cost Changes
1917 END IF; --if etc effort not equal to g_miss_num and not null --maansari4/10
1918 End IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
1919
1920 --maansari4/10
1921 --ELSE
1922 -- l_act_txn_cost := p_actual_cost_this_period; -- This is incremental
1923 -- l_etc_txn_cost := p_etc_cost_this_period; -- This is cumulative
1924 --END IF;
1925 --maansari4/10
1926
1927
1928 IF g1_debug_mode = 'Y' THEN
1929 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_txn_raw_cost'||l_etc_txn_raw_cost, x_Log_Level=> 3);
1930 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_txn_bur_cost'||l_etc_txn_bur_cost, x_Log_Level=> 3);
1931 END IF;
1932
1933 ELSIF l_rate_based_flag = 'N' --maansari7/6 bug 3742356
1934 THEN
1935 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
1936 --Bug 3606627 : Used l_ varable instead of p_
1937
1938 -- Bug 3801745 Begin : Commented below code and added new
1939 -- l_act_txn_raw_cost := l_actual_raw_cost_this_period; -- This is incremental.
1940 -- l_etc_txn_raw_cost := l_etc_raw_cost_this_period; -- This is cumulative.
1941 -- l_actual_effort_this_period := null; -- This is incremental
1942 -- l_etc_effort_this_period := null; -- This is cumulative
1943 -- Raw Cost Changes Begin
1944 l_act_txn_raw_cost := l_actual_effort_this_period;
1945 l_etc_txn_raw_cost := l_etc_effort_this_period;
1946 -- Bug 3801745 End
1947
1948 l_act_txn_bur_cost := nvl( pa_currency.round_trans_currency_amt(
1949 l_act_txn_raw_cost * l_burden_multiplier, l_txn_currency_code),0) +
1950 l_act_txn_raw_cost ;
1951 -- Bug 3627315 : Using plan burden multiplier instead of actual
1952 l_etc_txn_bur_cost := nvl( pa_currency.round_trans_currency_amt(
1953 l_etc_txn_raw_cost * l_plan_burden_multiplier, l_txn_currency_code),0) +
1957
1954 l_etc_txn_raw_cost ;
1955 End IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
1956 -- Raw Cost Changes End
1958 END IF;
1959
1960 --bug no. 3586648
1961 -- IF ((l_etc_txn_raw_cost = 0 OR l_etc_txn_raw_cost = null) AND l_actual_finish_date = null) Bug 3801745
1962 -- IF ((l_etc_effort_this_period = 0 OR l_etc_effort_this_period = null) AND l_actual_finish_date = null) Bug 3956299
1963 --bug 4341100, moved this code below after defaulting etc as planned - actual
1964 /*IF ((l_etc_effort_this_period = 0 OR l_etc_effort_this_period is null) AND l_actual_finish_date is null) -- Bug 3956299
1965 THEN
1966 -- Bug 3956299 : We can default actual finish date in this case from scheduled_finish_date
1967 l_actual_finish_date := l_scheduled_finish_date;
1968 IF l_actual_finish_date is null THEN
1969 --You have to pass actual finish date if etc is 0
1970 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1971 ,p_msg_name => 'PA_TP_ASSG_NTER_FIN_DATE');
1972 x_msg_data := 'PA_TP_ASSG_NTER_FIN_DATE';
1973 x_return_status := 'E';
1974 RAISE FND_API.G_EXC_ERROR;
1975 END IF;
1976 END IF;*/
1977 --bug no. 3586648
1978
1979 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
1980
1981 IF l_act_txn_raw_cost IS NOT NULL AND l_act_txn_raw_cost <> 0 THEN
1982 PA_PROGRESS_UTILS.CONVERT_CURRENCY_AMOUNTS(
1983 p_project_id => p_project_id
1984 ,p_task_id => p_task_id
1985 -- ,p_as_of_date => SYSDATE
1986 ,p_as_of_date => p_as_of_date --bug 3901289
1987 ,p_txn_cost => l_act_txn_raw_cost
1988 ,p_txn_curr_code => l_txn_currency_code
1989 ,p_project_curr_code => l_project_curr_code
1990 ,p_project_rate_type => l_project_rate_type
1991 ,p_project_rate_date => l_project_rate_date
1992 ,p_project_exch_rate => l_project_exch_rate
1993 ,p_project_raw_cost => l_act_project_raw_cost
1994 ,p_projfunc_curr_code => l_projfunc_curr_code
1995 ,p_projfunc_cost_rate_type => l_projfunc_cost_rate_type
1996 ,p_projfunc_cost_rate_date => l_projfunc_cost_rate_date
1997 ,p_projfunc_cost_exch_rate => l_projfunc_cost_exch_rate
1998 ,p_projfunc_raw_cost => l_act_projfunc_raw_cost
1999 ,p_structure_version_id => p_structure_version_id -- 3627787
2000 ,x_return_status => x_return_status
2001 ,x_msg_count => x_msg_count
2002 ,x_msg_data => x_msg_data
2003 );
2004 IF x_return_status <> FND_API.G_RET_STS_SUCCESS -- FPM Dev CR 8
2005 THEN
2006 RAISE FND_API.G_EXC_ERROR;
2007 END IF;
2008 -- Raw Cost Changes Begin
2009 PA_PROGRESS_UTILS.CONVERT_CURRENCY_AMOUNTS(
2010 p_project_id => p_project_id
2011 ,p_task_id => p_task_id
2012 -- ,p_as_of_date => SYSDATE
2013 ,p_as_of_date => p_as_of_date --bug 3901289
2014 ,p_txn_cost => l_act_txn_bur_cost
2015 ,p_txn_curr_code => l_txn_currency_code
2016 ,p_project_curr_code => l_project_curr_code
2017 ,p_project_rate_type => l_project_rate_type
2018 ,p_project_rate_date => l_project_rate_date
2019 ,p_project_exch_rate => l_project_exch_rate
2020 ,p_project_raw_cost => l_act_project_bur_cost
2021 ,p_projfunc_curr_code => l_projfunc_curr_code
2022 ,p_projfunc_cost_rate_type => l_projfunc_cost_rate_type
2023 ,p_projfunc_cost_rate_date => l_projfunc_cost_rate_date
2024 ,p_projfunc_cost_exch_rate => l_projfunc_cost_exch_rate
2025 ,p_projfunc_raw_cost => l_act_projfunc_bur_cost
2026 ,p_structure_version_id => p_structure_version_id -- 3627787
2027 ,x_return_status => x_return_status
2028 ,x_msg_count => x_msg_count
2029 ,x_msg_data => x_msg_data
2030 );
2031 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2032 THEN
2033 RAISE FND_API.G_EXC_ERROR;
2034 END IF;
2035 -- Raw Cost Changes End
2036
2037 ELSE
2038 l_act_project_raw_cost := null;
2039 l_act_projfunc_raw_cost := null;
2040 l_act_project_bur_cost := null; --Raw Cost Changes
2041 l_act_projfunc_bur_cost := null; --Raw Cost Changes
2042 END IF;
2043
2044 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2045 THEN
2046 RAISE FND_API.G_EXC_ERROR;
2047 END IF;
2048
2049 IF g1_debug_mode = 'Y' THEN
2050 -- pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_act_txn_cost'||l_act_txn_cost, x_Log_Level=> 3);
2051 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_act_project_raw_cost'||l_act_project_raw_cost, x_Log_Level=> 3);
2052 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_act_projfunc_raw_cost'||l_act_projfunc_raw_cost, x_Log_Level=> 3);
2053 END IF;
2054
2055 IF l_etc_txn_raw_cost IS NOT NULL AND l_etc_txn_raw_cost <> 0 THEN
2056 PA_PROGRESS_UTILS.CONVERT_CURRENCY_AMOUNTS(
2057 p_project_id => p_project_id
2061 ,p_txn_cost => l_etc_txn_raw_cost
2058 ,p_task_id => p_task_id
2059 -- ,p_as_of_date => SYSDATE
2060 ,p_as_of_date => p_as_of_date --bug 3901289
2062 ,p_txn_curr_code => l_txn_currency_code
2063 ,p_calling_mode => 'PLAN_RATES' ---4372462
2064 ,p_res_assignment_id => l_assignment_id ---4372462
2065 ,p_budget_version_id => l_budget_version_id ---4372462
2066 ,p_project_curr_code => l_project_curr_code
2067 ,p_project_rate_type => l_project_rate_type
2068 ,p_project_rate_date => l_project_rate_date
2069 ,p_project_exch_rate => l_project_exch_rate
2070 ,p_project_raw_cost => l_etc_project_raw_cost
2071 ,p_projfunc_curr_code => l_projfunc_curr_code
2072 ,p_projfunc_cost_rate_type => l_projfunc_cost_rate_type
2073 ,p_projfunc_cost_rate_date => l_projfunc_cost_rate_date
2074 ,p_projfunc_cost_exch_rate => l_projfunc_cost_exch_rate
2075 ,p_projfunc_raw_cost => l_etc_projfunc_raw_cost
2076 ,p_structure_version_id => p_structure_version_id -- 3627787
2077 ,x_return_status => x_return_status
2078 ,x_msg_count => x_msg_count
2079 ,x_msg_data => x_msg_data
2080 );
2081 IF x_return_status <> FND_API.G_RET_STS_SUCCESS -- FPM Dev CR 8
2082 THEN
2083 RAISE FND_API.G_EXC_ERROR;
2084 END IF;
2085 -- Raw Cost Changes Begin
2086 PA_PROGRESS_UTILS.CONVERT_CURRENCY_AMOUNTS(
2087 p_project_id => p_project_id
2088 ,p_task_id => p_task_id
2089 -- ,p_as_of_date => SYSDATE
2090 ,p_as_of_date => p_as_of_date --bug 3901289
2091 ,p_txn_cost => l_etc_txn_bur_cost
2092 ,p_txn_curr_code => l_txn_currency_code
2093 ,p_calling_mode => 'PLAN_RATES' ---4372462
2094 ,p_res_assignment_id => l_assignment_id ---4372462
2095 ,p_budget_version_id => l_budget_version_id ---4372462
2096 ,p_project_curr_code => l_project_curr_code
2097 ,p_project_rate_type => l_project_rate_type
2098 ,p_project_rate_date => l_project_rate_date
2099 ,p_project_exch_rate => l_project_exch_rate
2100 ,p_project_raw_cost => l_etc_project_bur_cost
2101 ,p_projfunc_curr_code => l_projfunc_curr_code
2102 ,p_projfunc_cost_rate_type => l_projfunc_cost_rate_type
2103 ,p_projfunc_cost_rate_date => l_projfunc_cost_rate_date
2104 ,p_projfunc_cost_exch_rate => l_projfunc_cost_exch_rate
2105 ,p_projfunc_raw_cost => l_etc_projfunc_bur_cost
2106 ,p_structure_version_id => p_structure_version_id -- 3627787
2107 ,x_return_status => x_return_status
2108 ,x_msg_count => x_msg_count
2109 ,x_msg_data => x_msg_data
2110 );
2111 IF x_return_status <> FND_API.G_RET_STS_SUCCESS -- FPM Dev CR 8
2112 THEN
2113 RAISE FND_API.G_EXC_ERROR;
2114 END IF;
2115 -- Raw Cost Changes End
2116 ELSE
2117 l_etc_project_raw_cost := null;
2118 l_etc_projfunc_raw_cost := null;
2119 l_etc_project_bur_cost := null;
2120 l_etc_projfunc_bur_cost := null;
2121 -- Bug 4116080 : Begin
2122 IF l_etc_txn_raw_cost = 0 THEN
2123 l_etc_project_raw_cost := 0;
2124 l_etc_projfunc_raw_cost := 0;
2125 l_etc_project_bur_cost := 0;
2126 l_etc_projfunc_bur_cost := 0;
2127 END IF;
2128 -- Bug 4116080 : End
2129 END IF;
2130 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2131
2132 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2133 THEN
2134 RAISE FND_API.G_EXC_ERROR;
2135 END IF;
2136
2137
2138 IF g1_debug_mode = 'Y' THEN
2139 -- pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_txn_cost'||l_etc_txn_cost, x_Log_Level=> 3);
2140 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_project_raw_cost'||l_etc_project_raw_cost, x_Log_Level=> 3);
2141 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_projfunc_raw_cost'||l_etc_projfunc_raw_cost, x_Log_Level=> 3);
2142 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_project_rate_type'||l_project_rate_type, x_Log_Level=> 3);
2143 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_project_rate_date'||l_project_rate_date, x_Log_Level=> 3);
2144 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_project_exch_rate'||l_project_exch_rate, x_Log_Level=> 3);
2145 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_projfunc_cost_rate_type'||l_projfunc_cost_rate_type, x_Log_Level=> 3);
2146 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_projfunc_cost_rate_date'||l_projfunc_cost_rate_date, x_Log_Level=> 3);
2147 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_projfunc_cost_exch_rate'||l_projfunc_cost_exch_rate, x_Log_Level=> 3);
2151
2148 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_act_effort_last_subm'||l_act_effort_last_subm, x_Log_Level=> 3);
2149 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_act_raw_cost_last_subm_tc'||l_act_raw_cost_last_subm_tc, x_Log_Level=> 3);
2150 END IF;
2152 /* Bug 3606627 : Shifted this code up
2153 -- FPM Dev CR 3 : Added Logic to get total cost from last submission and this period.
2154 OPEN cur_task_cost(p_project_id, l_assignment_id, p_structure_type, l_resource_class_code, p_as_of_date);
2155 FETCH cur_task_cost INTO l_act_cost_last_subm_tc, l_act_cost_last_subm_pc, l_act_cost_last_subm_fc, l_act_effort_last_subm;
2156 CLOSE cur_task_cost;
2157 */
2158
2159 /* MOVING THIS CODE ABOVE TO GET BUDGET_VERSION_ID FOR CONVERSION to fix 4372462
2160 --BUG3630743 (Get all planned values)
2161 -- Bug 3696572 : Wrongly l_project_id was passed to below cursor instead of p_project_id
2162 OPEN c_get_planned_values (l_resource_list_member_id , l_assignment_id, p_project_id ) ;
2163 FETCH c_get_planned_values INTO
2164 l_planned_quantity ,
2165 l_planned_bur_cost_txn_cur,
2166 l_planned_bur_cost_projfunc,
2167 l_planned_bur_cost_proj_cur,
2168 l_planned_raw_cost_txn_cur,
2169 l_planned_raw_cost_proj_cur,
2170 l_planned_raw_cost_projfunc;
2171 CLOSE c_get_planned_values;
2172 --BUG3630743
2173 */
2174
2175
2176 --bug 3824042, round all the cost figures by calling pa_currency api, start
2177
2178 l_act_projfunc_raw_cost := pa_currency.round_trans_currency_amt(l_act_projfunc_raw_cost, l_prjfunc_currency_code);
2179 l_act_project_raw_cost := pa_currency.round_trans_currency_amt(l_act_project_raw_cost, l_prj_currency_code);
2180 l_act_projfunc_bur_cost := pa_currency.round_trans_currency_amt(l_act_projfunc_bur_cost, l_prjfunc_currency_code);
2181 l_act_project_bur_cost := pa_currency.round_trans_currency_amt(l_act_project_bur_cost, l_prj_currency_code);
2182
2183 l_etc_projfunc_raw_cost := pa_currency.round_trans_currency_amt(l_etc_projfunc_raw_cost, l_prjfunc_currency_code);
2184 l_etc_project_raw_cost := pa_currency.round_trans_currency_amt(l_etc_project_raw_cost, l_prj_currency_code);
2185 l_etc_projfunc_bur_cost := pa_currency.round_trans_currency_amt(l_etc_projfunc_bur_cost, l_prjfunc_currency_code);
2186 l_etc_project_bur_cost := pa_currency.round_trans_currency_amt(l_etc_project_bur_cost, l_prj_currency_code);
2187
2188 --bug 3824042, round all the cost figures by calling pa_currency api, end
2189
2190
2191 IF l_resource_class_code = 'PEOPLE' --maansari7/6 bug 3742356
2192 THEN
2193
2194 -- Actuals are incremental amounts
2195 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2196 l_ppl_act_raw_cost_to_date_tc := NVL( l_act_raw_cost_last_subm_tc, 0) + NVL( l_act_txn_raw_cost, 0);
2197 l_ppl_act_raw_cost_to_date_fc := NVL( l_act_raw_cost_last_subm_fc, 0) + NVL( l_act_projfunc_raw_cost, 0);
2198 l_ppl_act_raw_cost_to_date_pc := NVL( l_act_raw_cost_last_subm_pc, 0) + NVL( l_act_project_raw_cost, 0);
2199 -- Raw Cost Changes
2200 l_ppl_act_bur_cost_to_date_tc := NVL( l_act_bur_cost_last_subm_tc, 0) + NVL( l_act_txn_bur_cost, 0);
2201 l_ppl_act_bur_cost_to_date_fc := NVL( l_act_bur_cost_last_subm_fc, 0) + NVL( l_act_projfunc_bur_cost, 0);
2202 l_ppl_act_bur_cost_to_date_pc := NVL( l_act_bur_cost_last_subm_pc, 0) + NVL( l_act_project_bur_cost, 0);
2203 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2204
2205 l_ppl_act_effort_to_date := NVL( l_act_effort_last_subm, 0 ) + NVL( l_actual_effort_this_period, 0 ); --added NVL maansari4/8
2206
2207 -- here we need to decide what is the correct value of actual this period
2208 if (subm_prog_exists_aod = 'Y') then
2209 l_act_txn_raw_cost := l_ppl_act_raw_cost_to_date_tc - nvl(l_act_raw_cost_latest_subm_tc,0);
2210 l_act_projfunc_raw_cost := l_ppl_act_raw_cost_to_date_fc - nvl(l_act_raw_cost_latest_subm_fc,0);
2211 l_act_project_raw_cost := l_ppl_act_raw_cost_to_date_pc - nvl(l_act_raw_cost_latest_subm_pc,0);
2212 l_act_txn_bur_cost := l_ppl_act_bur_cost_to_date_tc - nvl(l_act_bur_cost_latest_subm_tc,0);
2213 l_act_projfunc_bur_cost := l_ppl_act_bur_cost_to_date_fc - nvl(l_act_bur_cost_latest_subm_fc,0);
2214 l_act_project_bur_cost := l_ppl_act_bur_cost_to_date_pc - nvl(l_act_bur_cost_latest_subm_pc,0);
2215 l_actual_effort_this_period := l_ppl_act_effort_to_date - nvl(l_act_effort_latest_subm,0);
2216 end if;
2217
2218 --BUG3630743 ( If etc is passed as null , default it with planned-actual )
2219 -- Added If part
2220 --IF (l_etc_txn_raw_cost IS NULL OR l_etc_effort_this_period IS NULL) THEN Bug 3801745
2221 IF l_etc_effort_this_period IS NULL THEN
2222 -- Default Etc as planned - Actual
2223 -- Since ETC is always cumulative , use to_date values of actuals to subtract from planned
2224 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2225 l_ppl_etc_raw_cost_tc := NVL (l_planned_raw_cost_txn_cur,0) - NVL(l_ppl_act_raw_cost_to_date_tc,0);
2226 l_ppl_etc_raw_cost_fc := NVL(l_planned_raw_cost_projfunc,0) - NVL(l_ppl_act_raw_cost_to_date_fc,0);
2227 l_ppl_etc_raw_cost_pc := NVL(l_planned_raw_cost_proj_cur,0) - NVL(l_ppl_act_raw_cost_to_date_pc,0);
2228
2229 l_ppl_etc_bur_cost_tc := NVL(l_planned_bur_cost_txn_cur,0) - NVL(l_ppl_act_bur_cost_to_date_tc,0);
2230 l_ppl_etc_bur_cost_fc := NVL(l_planned_bur_cost_projfunc,0) - NVL(l_ppl_act_bur_cost_to_date_fc,0);
2234 IF l_ppl_etc_raw_cost_tc < 0
2231 l_ppl_etc_bur_cost_pc := NVL(l_planned_bur_cost_proj_cur,0) - NVL(l_ppl_act_bur_cost_to_date_pc,0);
2232
2233 --bug 3968789, if planned - actual is -ve then make it 0, start
2235 THEN
2236 l_ppl_etc_raw_cost_tc := 0;
2237 l_ppl_etc_raw_cost_fc := 0;
2238 l_ppl_etc_raw_cost_pc := 0;
2239 l_ppl_etc_bur_cost_tc := 0;
2240 l_ppl_etc_bur_cost_fc := 0;
2241 l_ppl_etc_bur_cost_pc := 0;
2242 END IF;
2243 --bug 3968789, if planned - actual is -ve then make it 0, end
2244 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2245
2246 l_ppl_etc_effort := NVL(l_planned_quantity,0) - NVL(l_ppl_act_effort_to_date,0);
2247 --bug 3968789,
2248 IF l_ppl_etc_effort < 0
2249 THEN
2250 l_ppl_etc_effort := 0;
2251 END IF;
2252 --Added Else
2253 ELSE
2254 -- ETC's are cumulative amounts
2255 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2256 l_ppl_etc_raw_cost_tc := l_etc_txn_raw_cost;
2257 l_ppl_etc_raw_cost_fc := l_etc_projfunc_raw_cost;
2258 l_ppl_etc_raw_cost_pc := l_etc_project_raw_cost;
2259 -- Raw Cost Changes
2260
2261 l_ppl_etc_bur_cost_tc := l_etc_txn_bur_cost;
2262 l_ppl_etc_bur_cost_fc := l_etc_projfunc_bur_cost;
2263 l_ppl_etc_bur_cost_pc := l_etc_project_bur_cost;
2264 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2265
2266 l_ppl_etc_effort := l_etc_effort_this_period;
2267 END IF;
2268 --maansari4/28
2269 ELSIF l_resource_class_code = 'EQUIPMENT' --maansari7/6 bug 3742356
2270 THEN
2271 -- Actuals are incremental amounts
2272 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2273 l_eqp_act_raw_cost_to_date_tc := NVL(l_act_raw_cost_last_subm_tc,0) + NVL(l_act_txn_raw_cost,0);
2274 l_eqp_act_raw_cost_to_date_fc := NVL(l_act_raw_cost_last_subm_fc,0) + NVL(l_act_projfunc_raw_cost,0);
2275 l_eqp_act_raw_cost_to_date_pc := NVL(l_act_raw_cost_last_subm_pc,0) + NVL(l_act_project_raw_cost,0);
2276 -- Raw Cost Changes
2277 l_eqp_act_bur_cost_to_date_tc := NVL(l_act_bur_cost_last_subm_tc,0) + NVL(l_act_txn_bur_cost,0);
2278 l_eqp_act_bur_cost_to_date_fc := NVL(l_act_bur_cost_last_subm_fc,0) + NVL(l_act_projfunc_bur_cost,0);
2279 l_eqp_act_bur_cost_to_date_pc := NVL(l_act_bur_cost_last_subm_pc,0) + NVL(l_act_project_bur_cost,0);
2280 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2281
2282 l_eqpmt_act_effort_to_date := NVL(l_act_effort_last_subm,0) + NVL(l_actual_effort_this_period,0);
2283
2284 -- here we need to decide what is the correct value of actual this period
2285 if (subm_prog_exists_aod = 'Y') then
2286 l_act_txn_raw_cost := l_eqp_act_raw_cost_to_date_tc - nvl(l_act_raw_cost_latest_subm_tc,0);
2287 l_act_projfunc_raw_cost := l_eqp_act_raw_cost_to_date_fc - nvl(l_act_raw_cost_latest_subm_fc,0);
2288 l_act_project_raw_cost := l_eqp_act_raw_cost_to_date_pc - nvl(l_act_raw_cost_latest_subm_pc,0);
2289 l_act_txn_bur_cost := l_eqp_act_bur_cost_to_date_tc - nvl(l_act_bur_cost_latest_subm_tc,0);
2290 l_act_projfunc_bur_cost := l_eqp_act_bur_cost_to_date_fc - nvl(l_act_bur_cost_latest_subm_fc,0);
2291 l_act_project_bur_cost := l_eqp_act_bur_cost_to_date_pc - nvl(l_act_bur_cost_latest_subm_pc,0);
2292 l_actual_effort_this_period := l_eqpmt_act_effort_to_date - nvl(l_act_effort_latest_subm,0);
2293 end if;
2294
2295 --BUG3630743 Added If part Default Etc as planned - Actual
2296 -- IF (l_etc_txn_raw_cost IS NULL OR l_etc_effort_this_period IS NULL) THEN Bug 3801745
2297 IF l_etc_effort_this_period IS NULL THEN
2298 -- Since ETC is always cumulative , use to_date values of actuals to subtract from planned
2299 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2300 l_eqpmt_etc_raw_cost_tc := NVL(l_planned_raw_cost_txn_cur,0) - NVL(l_eqp_act_raw_cost_to_date_tc,0);
2301 l_eqpmt_etc_raw_cost_fc := NVL(l_planned_raw_cost_projfunc,0) - NVL(l_eqp_act_raw_cost_to_date_fc,0);
2302 l_eqpmt_etc_raw_cost_pc := NVL(l_planned_raw_cost_proj_cur,0) - NVL(l_eqp_act_raw_cost_to_date_pc,0);
2303
2304 l_eqpmt_etc_bur_cost_tc := NVL(l_planned_bur_cost_txn_cur,0) - NVL(l_eqp_act_bur_cost_to_date_tc,0);
2305 l_eqpmt_etc_bur_cost_fc := NVL(l_planned_bur_cost_projfunc,0) - NVL(l_eqp_act_bur_cost_to_date_fc,0);
2306 l_eqpmt_etc_bur_cost_pc := NVL(l_planned_bur_cost_proj_cur ,0)- NVL(l_eqp_act_bur_cost_to_date_pc,0);
2307
2308 --bug 3968789, if planned - actual is -ve then make it 0, start
2309 IF l_eqpmt_etc_raw_cost_tc < 0
2310 THEN
2311 l_eqpmt_etc_raw_cost_tc := 0;
2312 l_eqpmt_etc_raw_cost_fc := 0;
2313 l_eqpmt_etc_raw_cost_pc := 0;
2314 l_eqpmt_etc_bur_cost_tc := 0;
2315 l_eqpmt_etc_bur_cost_fc := 0;
2316 l_eqpmt_etc_bur_cost_pc := 0;
2317 END IF;
2318 --bug 3968789, if planned - actual is -ve then make it 0, end
2319 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2320
2321 l_eqpmt_etc_effort := NVL(l_planned_quantity,0) - NVL(l_eqpmt_act_effort_to_date,0);
2322 --bug 3968789
2323 IF l_eqpmt_etc_effort < 0
2324 THEN
2325 l_eqpmt_etc_effort := 0;
2326 END IF;
2327
2328 ELSE
2329 -- ETC's are cumulative amounts
2330 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2331 l_eqpmt_etc_raw_cost_tc := l_etc_txn_raw_cost;
2332 l_eqpmt_etc_raw_cost_fc := l_etc_projfunc_raw_cost;
2333 l_eqpmt_etc_raw_cost_pc := l_etc_project_raw_cost;
2334 -- Raw Cost Changes
2335 l_eqpmt_etc_bur_cost_tc := l_etc_txn_bur_cost;
2339
2336 l_eqpmt_etc_bur_cost_fc := l_etc_projfunc_bur_cost;
2337 l_eqpmt_etc_bur_cost_pc := l_etc_project_bur_cost;
2338 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2340 l_eqpmt_etc_effort := l_etc_effort_this_period;
2341 END IF;
2342
2343 ELSIF l_resource_class_code = 'FINANCIAL_ELEMENTS' OR l_resource_class_code = 'MATERIAL_ITEMS' --maansari7/6 bug 3742356
2344 THEN
2345 -- Actuals are incremental amounts
2346 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2347 l_oth_act_raw_cost_to_date_tc := NVL(l_act_raw_cost_last_subm_tc,0) + NVL(l_act_txn_raw_cost,0);
2348 l_oth_act_raw_cost_to_date_fc := NVL(l_act_raw_cost_last_subm_fc,0) + NVL(l_act_projfunc_raw_cost,0);
2349 l_oth_act_raw_cost_to_date_pc := NVL(l_act_raw_cost_last_subm_pc,0) + NVL(l_act_project_raw_cost,0);
2350 -- Raw Cost Changes
2351 l_oth_act_bur_cost_to_date_tc := NVL(l_act_bur_cost_last_subm_tc,0) + NVL(l_act_txn_bur_cost,0);
2352 l_oth_act_bur_cost_to_date_fc := NVL(l_act_bur_cost_last_subm_fc,0) + NVL(l_act_projfunc_bur_cost,0);
2353 l_oth_act_bur_cost_to_date_pc := NVL(l_act_bur_cost_last_subm_pc,0) + NVL(l_act_project_bur_cost,0);
2354 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2355
2356 l_oth_quantity_to_date := NVL(l_act_effort_last_subm,0) + NVL(l_actual_effort_this_period,0); --bug 3608801
2357
2358 -- here we need to decide what is the correct value of actual this period
2359 if (subm_prog_exists_aod = 'Y') then
2360 l_act_txn_raw_cost := l_oth_act_raw_cost_to_date_tc - nvl(l_act_raw_cost_latest_subm_tc,0);
2361 l_act_projfunc_raw_cost := l_oth_act_raw_cost_to_date_fc - nvl(l_act_raw_cost_latest_subm_fc,0);
2362 l_act_project_raw_cost := l_oth_act_raw_cost_to_date_pc - nvl(l_act_raw_cost_latest_subm_pc,0);
2363 l_act_txn_bur_cost := l_oth_act_bur_cost_to_date_tc - nvl(l_act_bur_cost_latest_subm_tc,0);
2364 l_act_projfunc_bur_cost := l_oth_act_bur_cost_to_date_fc - nvl(l_act_bur_cost_latest_subm_fc,0);
2365 l_act_project_bur_cost := l_oth_act_bur_cost_to_date_pc - nvl(l_act_bur_cost_latest_subm_pc,0);
2366 l_actual_effort_this_period := l_oth_quantity_to_date - nvl(l_act_effort_latest_subm,0);
2367 end if;
2368
2369 --IF (l_etc_txn_raw_cost IS NULL OR Bug 3801745
2370 -- ( l_etc_effort_this_period IS NULL AND l_rate_based_flag = 'Y')) THEN --bug 3779387 aded to prevent -ve ETC computation.
2371 IF l_etc_effort_this_period IS NULL THEN
2372 -- Default Etc as planned - Actual
2373 -- Since ETC is always cumulative , use to_date values of actuals to subtract from planned
2374 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2375 l_oth_etc_raw_cost_tc := NVL(l_planned_raw_cost_txn_cur,0) - NVL(l_oth_act_raw_cost_to_date_tc,0);
2376 l_oth_etc_raw_cost_fc := NVL(l_planned_raw_cost_projfunc,0) - NVL(l_oth_act_raw_cost_to_date_fc,0);
2377 l_oth_etc_raw_cost_pc := NVL(l_planned_raw_cost_proj_cur,0) - NVL(l_oth_act_raw_cost_to_date_pc,0);
2378
2379 l_oth_etc_bur_cost_tc := NVL(l_planned_bur_cost_txn_cur,0) - NVL(l_oth_act_bur_cost_to_date_tc,0);
2380 l_oth_etc_bur_cost_fc := NVL(l_planned_bur_cost_projfunc,0) - NVL(l_oth_act_bur_cost_to_date_fc,0);
2381 l_oth_etc_bur_cost_pc := NVL(l_planned_bur_cost_proj_cur ,0)- NVL(l_oth_act_bur_cost_to_date_pc,0);
2382
2383 --bug 3968789, if planned - actual is -ve then make it 0, start
2384 IF l_oth_etc_raw_cost_tc < 0
2385 THEN
2386 l_oth_etc_raw_cost_tc := 0;
2387 l_oth_etc_raw_cost_fc := 0;
2388 l_oth_etc_raw_cost_pc := 0;
2389 l_oth_etc_bur_cost_tc := 0;
2390 l_oth_etc_bur_cost_fc := 0;
2391 l_oth_etc_bur_cost_pc := 0;
2392 END IF;
2393 --bug 3968789, if planned - actual is -ve then make it 0, end
2394
2395 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2396
2397 l_oth_etc_quantity := NVL(l_planned_quantity,0)- NVL(l_oth_quantity_to_date,0);
2398 --bug 3968789
2399 IF l_oth_etc_quantity < 0
2400 THEN
2401 l_oth_etc_quantity := 0;
2402 END IF;
2403
2404 ELSE
2405 -- ETC's are cumulative amounts
2406 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2407 l_oth_etc_raw_cost_tc := l_etc_txn_raw_cost;
2408 l_oth_etc_raw_cost_fc := l_etc_projfunc_raw_cost;
2409 l_oth_etc_raw_cost_pc := l_etc_project_raw_cost;
2410 -- Raw Cost Changes
2411 l_oth_etc_bur_cost_tc := l_etc_txn_bur_cost;
2412 l_oth_etc_bur_cost_fc := l_etc_projfunc_bur_cost;
2413 l_oth_etc_bur_cost_pc := l_etc_project_bur_cost;
2414 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2415
2416 l_oth_etc_quantity := l_etc_effort_this_period; --bug 3608801
2417 END IF;
2418 END IF;
2419 --maansari4/28
2420 IF g1_debug_mode = 'Y' THEN
2421 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'calling api to push actuals ', x_Log_Level=> 3);
2422 END IF;
2423
2424
2425 --bug no. 3586648
2426 --IF l_structure_shared = 'N' AND (l_act_txn_cost IS NOT NULL OR l_act_effort_this_period IS NOT NULL)
2427 --bug no. 3595585 moved the call after updating progress rollup Satish start
2428 /*IF ((l_structure_sharing_code <> 'SHARE_FULL') AND (l_act_txn_cost IS NOT NULL OR l_act_effort_this_period IS NOT NULL))
2429 THEN
2430 PA_PROGRESS_PUB.push_workplan_actuals(
2431 p_project_Id => p_project_id,
2432 p_structure_version_id => p_structure_version_id,
2433 p_proj_element_id => p_task_id,
2434 p_object_id => p_object_id,
2438 p_rate_based_flag => l_rate_based_flag, --maansari7/6 bug 3742356
2435 p_object_type => l_object_type,
2436 p_as_of_date => p_as_of_date,
2437 p_rbs_element_id => l_rbs_element_id, --maansari7/6 bug 3742356
2439 p_resource_class_code => l_resource_class_code, --maansari7/6 bug 3742356
2440 p_TXN_CURRENCY_CODE => l_txn_currency_code, -- Fix for Bug # 3988457.
2441 -- p_txn_currency_code,
2442 p_act_TXN_COST_this_period => l_act_txn_cost,
2443 p_act_PRJ_COST_this_period => l_act_project_raw_cost,
2444 p_act_POU_COST_this_period => l_act_projfunc_raw_cost,
2445 p_act_effort_this_period => l_act_effort_this_period,
2446 -- BUG # 3659659.
2447 --p_txn_currency_code => l_txn_currency_code, * already we are passing this parameter Bug: 4537865
2448 p_prj_currency_code => l_project_curr_code,
2449 p_pfn_currency_code => l_projfunc_curr_code,
2450 -- BUG # 3659659.
2451 x_return_status => x_return_status,
2452 x_msg_count => x_msg_count,
2453 x_msg_data => x_msg_data
2454 );
2455 END IF;
2456
2457 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2458 THEN
2459 RAISE FND_API.G_EXC_ERROR;
2460 END IF;*/
2461
2462 IF (p_action='PUBLISH' AND (l_act_txn_raw_cost IS NOT NULL OR l_actual_effort_this_period IS NOT NULL
2463 OR l_etc_txn_raw_cost IS NOT NULL OR l_etc_effort_this_period IS NOT NULL)) --bug etc was not getting pushed to PJI due to this statement.
2464 THEN
2465 -- Raw Cost Changes : Added Burden parameters
2466 PA_PROGRESS_UTILS.get_last_etc_all(
2467 p_project_id => p_project_id
2468 ,p_object_id => p_object_id
2469 ,p_object_type => l_object_type
2470 ,p_as_of_date => p_as_of_date
2471 ,p_structure_type => 'WORKPLAN'
2472 ,x_etc_txn_raw_cost_last_subm => l_etc_txn_raw_cost_last
2473 ,x_etc_prj_raw_cost_last_subm => l_etc_prj_raw_cost_last
2474 ,x_etc_pfc_raw_cost_last_subm => l_etc_pfc_raw_cost_last
2475 ,x_etc_effort_last_subm => l_etc_effort_last
2476 ,x_etc_txn_bur_cost_last_subm => l_etc_txn_bur_cost_last
2477 ,x_etc_prj_bur_cost_last_subm => l_etc_prj_bur_cost_last
2478 ,x_etc_pfc_bur_cost_last_subm => l_etc_pfc_bur_cost_last
2479 ,x_return_status => x_return_status
2480 ,x_msg_count => x_msg_count
2481 ,x_msg_data => x_msg_data
2482 ,p_proj_element_id => p_task_id --bug# 3764224 Added for RLM
2483 ,p_resource_class_code => l_resource_class_code -- Bug 3836485
2484 );
2485
2486 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2487 THEN
2488 RAISE FND_API.G_EXC_ERROR;
2489 END IF;
2490 --BUG 3630743 (If No progress exists default ETC this period as etc - planned)
2491 -- OPEN c_if_progress_exists(l_assignment_id , l_project_id , l_structure_version_id ); --bug# 3764224 Changes for RLM
2492
2493 IF g1_debug_mode = 'Y' THEN
2494 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_effort_last ='||l_etc_effort_last, x_Log_Level=> 3);
2495 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_txn_raw_cost_last ='||l_etc_txn_raw_cost_last, x_Log_Level=> 3);
2496 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_txn_bur_cost_last ='||l_etc_txn_bur_cost_last, x_Log_Level=> 3);
2497 END IF;
2498
2499 --bug# 3814545 Satish
2500 OPEN c_if_progress_exists(l_resource_list_member_id , p_project_id , l_structure_version_id );
2501 FETCH c_if_progress_exists INTO l_progress_exists;
2502 CLOSE c_if_progress_exists;
2503
2504 IF g1_debug_mode = 'Y' THEN
2505 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Checking the cursor l_progress_exists='||l_progress_exists, x_Log_Level=> 3);
2506 END IF;
2507
2508 -- IF c_if_progress_exists%NOTFOUND THEN
2509 IF nvl(l_progress_exists, 'N')='N' THEN
2510
2511 -- Bug 3696572 , we shd not be using l_etc_txn_raw_cost varables instead we shd sum up individual buckets
2512 -- this is because l_etc_txn_raw_cost will be null if not passed from UI, in this case
2513 -- up in the code we default individual etc buckets, we shd use them
2514 /*l_etc_txn_raw_cost_this_period := NVL(l_etc_txn_raw_cost,0) - NVL(l_planned_raw_cost_txn_cur,0);
2515 l_etc_prj_raw_cost_this_period := NVL(l_etc_project_raw_cost,0) - NVL(l_planned_raw_cost_proj_cur,0);
2516 l_etc_pfc_raw_cost_this_period := NVL(l_etc_projfunc_raw_cost,0) - NVL(l_planned_raw_cost_projfunc,0);
2517 l_etc_txn_bur_cost_this_period := NVL(l_etc_txn_bur_cost,0) - NVL(l_planned_bur_cost_txn_cur,0);
2518 l_etc_prj_bur_cost_this_period := NVL(l_etc_project_bur_cost,0) - NVL(l_planned_bur_cost_projfunc,0);
2519 l_etc_pfc_bur_cost_this_period := NVL(l_etc_projfunc_bur_cost ,0)- NVL(l_planned_bur_cost_proj_cur,0);
2520 l_etc_effort_incr := NVL(l_etc_effort_this_period,0) - NVL(l_planned_quantity,0);
2521 */
2522 IF g1_debug_mode = 'Y' THEN
2526 l_etc_txn_raw_cost_this_period := NVL(l_ppl_etc_raw_cost_tc,0) + NVL(l_eqpmt_etc_raw_cost_tc,0) + NVL(l_oth_etc_raw_cost_tc,0) - NVL(l_planned_raw_cost_txn_cur,0);
2523 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Progress does not exist', x_Log_Level=> 3);
2524 END IF;
2525 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2527 l_etc_prj_raw_cost_this_period := NVL(l_ppl_etc_raw_cost_pc,0) + NVL(l_eqpmt_etc_raw_cost_pc,0) + NVL(l_oth_etc_raw_cost_pc,0) - NVL(l_planned_raw_cost_proj_cur,0);
2528 l_etc_pfc_raw_cost_this_period := NVL(l_ppl_etc_raw_cost_fc,0) + NVL(l_eqpmt_etc_raw_cost_fc,0) + NVL(l_oth_etc_raw_cost_fc,0) - NVL(l_planned_raw_cost_projfunc,0);
2529 l_etc_txn_bur_cost_this_period := NVL(l_ppl_etc_bur_cost_tc,0) + NVL(l_eqpmt_etc_bur_cost_tc,0) + NVL(l_oth_etc_bur_cost_tc,0) - NVL(l_planned_bur_cost_txn_cur,0);
2530 l_etc_prj_bur_cost_this_period := NVL(l_ppl_etc_bur_cost_pc,0) + NVL(l_eqpmt_etc_bur_cost_pc,0) + NVL(l_oth_etc_bur_cost_pc,0) - NVL(l_planned_bur_cost_projfunc,0);
2531 l_etc_pfc_bur_cost_this_period := NVL(l_ppl_etc_bur_cost_fc,0) + NVL(l_eqpmt_etc_bur_cost_fc,0) + NVL(l_oth_etc_bur_cost_fc,0) - NVL(l_planned_bur_cost_proj_cur,0);
2532 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2533 l_etc_effort_incr := NVL(l_ppl_etc_effort,0) + NVL(l_eqpmt_etc_effort,0) + NVL(l_oth_etc_quantity,0) - NVL(l_planned_quantity,0);
2534 ELSE
2535 /*l_etc_txn_raw_cost_this_period := NVL(l_etc_txn_raw_cost,0) - NVL(l_etc_txn_raw_cost_last,0);
2536 l_etc_prj_raw_cost_this_period := NVL(l_etc_project_raw_cost,0) - NVL(l_etc_prj_raw_cost_last,0);
2537 l_etc_pfc_raw_cost_this_period := NVL(l_etc_projfunc_raw_cost,0) - NVL(l_etc_pfc_raw_cost_last,0);
2538 l_etc_txn_bur_cost_this_period := NVL(l_etc_txn_bur_cost,0) - NVL(l_etc_txn_bur_cost_last,0);
2539 l_etc_prj_bur_cost_this_period := NVL(l_etc_project_bur_cost,0) - NVL(l_etc_prj_bur_cost_last,0);
2540 l_etc_pfc_bur_cost_this_period := NVL(l_etc_projfunc_bur_cost,0) - NVL(l_etc_pfc_bur_cost_last,0);
2541 l_etc_effort_incr := NVL(l_etc_effort_this_period,0) - NVL(l_etc_effort_last,0);
2542 */
2543 IF g1_debug_mode = 'Y' THEN
2544 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'Progress exists', x_Log_Level=> 3);
2545 END IF;
2546 IF NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2547 -- 3818384 (etc = current_etc - nvl(last etc, (planned - last act))
2548 if (l_planned_raw_cost_txn_cur >= l_act_raw_cost_last_subm_tc) then
2549 l_etc_txn_raw_cost_this_period := NVL(l_ppl_etc_raw_cost_tc,0) + NVL(l_eqpmt_etc_raw_cost_tc,0) + NVL(l_oth_etc_raw_cost_tc,0) - NVL(l_etc_txn_raw_cost_last,nvl(l_planned_raw_cost_txn_cur,0)-nvl(l_act_raw_cost_last_subm_tc,0));
2550 else
2551 l_etc_txn_raw_cost_this_period := NVL(l_ppl_etc_raw_cost_tc,0) + NVL(l_eqpmt_etc_raw_cost_tc,0) + NVL(l_oth_etc_raw_cost_tc,0) - NVL(l_etc_txn_raw_cost_last,0);
2552 end if;
2553 if (l_planned_raw_cost_proj_cur >= l_act_raw_cost_last_subm_pc) then
2554 l_etc_prj_raw_cost_this_period := NVL(l_ppl_etc_raw_cost_pc,0) + NVL(l_eqpmt_etc_raw_cost_pc,0) + NVL(l_oth_etc_raw_cost_pc,0) - NVL(l_etc_prj_raw_cost_last,nvl(l_planned_raw_cost_proj_cur,0)-nvl(l_act_raw_cost_last_subm_pc,0));
2555 else
2556 l_etc_prj_raw_cost_this_period := NVL(l_ppl_etc_raw_cost_pc,0) + NVL(l_eqpmt_etc_raw_cost_pc,0) + NVL(l_oth_etc_raw_cost_pc,0) - NVL(l_etc_prj_raw_cost_last,0);
2557 end if;
2558 if (l_planned_raw_cost_projfunc >= l_act_raw_cost_last_subm_fc) then
2559 l_etc_pfc_raw_cost_this_period := NVL(l_ppl_etc_raw_cost_fc,0) + NVL(l_eqpmt_etc_raw_cost_fc,0) + NVL(l_oth_etc_raw_cost_fc,0) - NVL(l_etc_pfc_raw_cost_last,nvl(l_planned_raw_cost_projfunc,0)-nvl(l_act_raw_cost_last_subm_fc,0));
2560 else
2561 l_etc_pfc_raw_cost_this_period := NVL(l_ppl_etc_raw_cost_fc,0) + NVL(l_eqpmt_etc_raw_cost_fc,0) + NVL(l_oth_etc_raw_cost_fc,0) - NVL(l_etc_pfc_raw_cost_last,0);
2562 end if;
2563 if (l_planned_bur_cost_txn_cur >= l_act_bur_cost_last_subm_tc) then
2564 l_etc_txn_bur_cost_this_period := NVL(l_ppl_etc_bur_cost_tc,0) + NVL(l_eqpmt_etc_bur_cost_tc,0) + NVL(l_oth_etc_bur_cost_tc,0) - NVL(l_etc_txn_bur_cost_last,nvl(l_planned_bur_cost_txn_cur,0)-nvl(l_act_bur_cost_last_subm_tc,0));
2565 else
2566 l_etc_txn_bur_cost_this_period := NVL(l_ppl_etc_bur_cost_tc,0) + NVL(l_eqpmt_etc_bur_cost_tc,0) + NVL(l_oth_etc_bur_cost_tc,0) - NVL(l_etc_txn_bur_cost_last,0);
2567 end if;
2568 if (l_planned_bur_cost_proj_cur >= l_act_bur_cost_last_subm_pc) then
2569 l_etc_prj_bur_cost_this_period := NVL(l_ppl_etc_bur_cost_pc,0) + NVL(l_eqpmt_etc_bur_cost_pc,0) + NVL(l_oth_etc_bur_cost_pc,0) - NVL(l_etc_prj_bur_cost_last,nvl(l_planned_bur_cost_proj_cur,0)-nvl(l_act_bur_cost_last_subm_pc,0));
2570 else
2571 l_etc_prj_bur_cost_this_period := NVL(l_ppl_etc_bur_cost_pc,0) + NVL(l_eqpmt_etc_bur_cost_pc,0) + NVL(l_oth_etc_bur_cost_pc,0) - NVL(l_etc_prj_bur_cost_last,0);
2572 end if;
2573 if (l_planned_bur_cost_projfunc >= l_act_bur_cost_last_subm_fc) then
2574 l_etc_pfc_bur_cost_this_period := NVL(l_ppl_etc_bur_cost_fc,0) + NVL(l_eqpmt_etc_bur_cost_fc,0) + NVL(l_oth_etc_bur_cost_fc,0) - NVL(l_etc_pfc_bur_cost_last,nvl(l_planned_bur_cost_projfunc,0)-nvl(l_act_bur_cost_last_subm_fc,0));
2575 else
2576 l_etc_pfc_bur_cost_this_period := NVL(l_ppl_etc_bur_cost_fc,0) + NVL(l_eqpmt_etc_bur_cost_fc,0) + NVL(l_oth_etc_bur_cost_fc,0) - NVL(l_etc_pfc_bur_cost_last,0);
2577 end if;
2578 END IF;-- NVL(l_track_wp_cost_flag, 'Y') = 'Y' THEN -- Bug 3801745
2579 if (l_planned_quantity >= l_act_effort_last_subm) then
2583 end if;
2580 l_etc_effort_incr := NVL(l_ppl_etc_effort,0) + NVL(l_eqpmt_etc_effort,0) + NVL(l_oth_etc_quantity,0) - NVL(l_etc_effort_last,nvl(l_planned_quantity,0)-nvl(l_act_effort_last_subm,0));
2581 else
2582 l_etc_effort_incr := NVL(l_ppl_etc_effort,0) + NVL(l_eqpmt_etc_effort,0) + NVL(l_oth_etc_quantity,0) - NVL(l_etc_effort_last,0);
2584 END IF;
2585
2586 -- CLOSE c_if_progress_exists;
2587
2588 IF g1_debug_mode = 'Y' THEN
2589 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_txn_raw_cost_this_period '||l_etc_txn_raw_cost_this_period, x_Log_Level=> 3);
2590 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_txn_bur_cost_this_period '||l_etc_txn_bur_cost_this_period, x_Log_Level=> 3);
2591 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_etc_effort_incr '||l_etc_effort_incr, x_Log_Level=> 3);
2592 END IF;
2593
2594 -- Raw Cost Changes : Modified push_workplan actuals call
2595 PA_PROGRESS_PUB.push_workplan_actuals(
2596 p_project_Id => p_project_id,
2597 p_structure_version_id => p_structure_version_id,
2598 p_proj_element_id => p_task_id,
2599 p_object_id => p_object_id,
2600 p_object_type => l_object_type,
2601 p_as_of_date => p_as_of_date,
2602 p_resource_assignment_id => l_assignment_id, -- Bug 4186007
2603 p_resource_list_member_id => l_resource_list_member_id, -- Bug 4186007
2604 p_rbs_element_id => l_rbs_element_id, --maansari7/6 bug 3742356
2605 p_rate_based_flag => l_rate_based_flag, --maansari7/6 bug 3742356
2606 p_resource_class_code => l_resource_class_code, --maansari7/6 bug 3742356
2607 p_act_TXN_COST_this_period => l_act_txn_bur_cost,
2608 p_act_PRJ_COST_this_period => l_act_project_bur_cost,
2609 p_act_POU_COST_this_period => l_act_projfunc_bur_cost,
2610 p_act_effort_this_period => l_actual_effort_this_period,
2611 p_etc_TXN_COST_this_period => l_etc_txn_bur_cost_this_period,
2612 p_etc_PRJ_COST_this_period => l_etc_prj_bur_cost_this_period,
2613 p_etc_POU_COST_this_period => l_etc_pfc_bur_cost_this_period,
2614 p_etc_effort_this_period => l_etc_effort_incr,
2615 p_act_TXN_raw_COST_this_period => l_act_txn_raw_cost,
2616 p_act_PRJ_raw_COST_this_period => l_act_project_raw_cost,
2617 p_act_POU_raw_COST_this_period => l_act_projfunc_raw_cost,
2618 p_etc_TXN_raw_COST_this_period => l_etc_txn_raw_cost_this_period,
2619 p_etc_PRJ_raw_COST_this_period => l_etc_prj_raw_cost_this_period,
2620 p_etc_POU_raw_COST_this_period => l_etc_pfc_raw_cost_this_period,
2621 -- BUG # 3659659.
2622 p_txn_currency_code => l_txn_currency_code,
2623 p_prj_currency_code => l_project_curr_code,
2624 p_pfn_currency_code => l_projfunc_curr_code,
2625 -- BUG # 3659659.
2626 --bug 3675107
2627 p_pa_period_name => l_prog_pa_period_name,
2628 p_gl_period_name => l_prog_gl_period_name,
2629 --bug 3675107
2630 x_return_status => x_return_status,
2631 x_msg_count => x_msg_count,
2632 x_msg_data => x_msg_data
2633 );
2634
2635 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2636 THEN
2637 RAISE FND_API.G_EXC_ERROR;
2638 END IF;
2639
2640 END IF;
2641 --bug no. 3595585 Satish end
2642
2643 --bug 4341100, start
2644 IF l_etc_effort_this_period is null
2645 THEN
2646 IF l_resource_class_code = 'PEOPLE'
2647 THEN
2648 l_etc_effort_this_period := l_ppl_etc_effort;
2649 ELSIF l_resource_class_code = 'EQUIPMENT'
2650 THEN
2651 l_etc_effort_this_period := l_eqpmt_etc_effort;
2652 ELSIF l_resource_class_code = 'FINANCIAL_ELEMENTS' OR l_resource_class_code = 'MATERIAL_ITEMS'
2653 THEN
2654 l_etc_effort_this_period := l_oth_etc_quantity;
2655 END IF;
2656 END IF;
2657
2658 IF ((l_etc_effort_this_period = 0 OR l_etc_effort_this_period is null) AND l_actual_finish_date is null) -- Bug 3956299
2659 THEN
2660 -- Bug 3956299 : We can default actual finish date in this case from scheduled_finish_date
2661 l_actual_finish_date := l_scheduled_finish_date;
2662 IF l_actual_finish_date is null THEN
2663 --You have to pass actual finish date if etc is 0
2664 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
2665 ,p_msg_name => 'PA_TP_ASSG_NTER_FIN_DATE');
2666 x_msg_data := 'PA_TP_ASSG_NTER_FIN_DATE';
2667 x_return_status := 'E';
2668 RAISE FND_API.G_EXC_ERROR;
2669 END IF;
2670 END IF;
2671 --bug 4341100, end
2672
2673
2674 IF l_db_action = 'CREATE'
2675 THEN
2676 /* FPM Dev CR 3
2677 IF p_percent_complete_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2678 THEN
2679 l_percent_complete_id := null;
2680 ELSE
2681 l_percent_complete_id := p_percent_complete_id;
2682 END IF;
2683 */
2684 l_percent_complete_id := null;
2688 p_TASK_ID => l_task_id
2685
2686
2687 PA_PERCENT_COMPLETES_PKG.INSERT_ROW(
2689 ,p_DATE_COMPUTED => p_as_of_date
2690 ,p_LAST_UPDATE_DATE => SYSDATE
2691 ,p_LAST_UPDATED_BY => l_user_id
2692 ,p_CREATION_DATE => SYSDATE
2693 ,p_CREATED_BY => l_user_id
2694 ,p_LAST_UPDATE_LOGIN => l_login_id
2695 ,p_COMPLETED_PERCENTAGE => l_percent_complete
2696 ,p_DESCRIPTION => l_brief_overview
2697 ,p_PROJECT_ID => p_project_id
2698 ,p_PM_PRODUCT_CODE => l_pm_product_code
2699 ,p_CURRENT_FLAG => l_current_flag
2700 ,p_OBJECT_TYPE => l_object_type
2701 --,p_OBJECT_ID => l_assignment_id --bug# 3764224 Changes for RLM
2702 ,p_OBJECT_ID => l_resource_list_member_id
2703 ,p_OBJECT_VERSION_ID => l_object_version_id
2704 ,p_PROGRESS_STATUS_CODE => l_progress_status_code
2705 ,p_ACTUAL_START_DATE => l_actual_start_date
2706 ,p_ACTUAL_FINISH_DATE => l_actual_finish_date
2707 ,p_ESTIMATED_START_DATE => l_estimated_start_date
2708 ,p_ESTIMATED_FINISH_DATE => l_estimated_finish_date
2709 ,p_PUBLISHED_FLAG => l_published_flag
2710 ,p_PUBLISHED_BY_PARTY_ID => l_published_by_party_id
2711 ,p_PROGRESS_COMMENT => l_progress_comment
2712 ,p_HISTORY_FLAG => 'N'
2713 ,p_status_code => l_task_status
2714 ,x_PERCENT_COMPLETE_ID => l_percent_complete_id
2715 ,p_ATTRIBUTE_CATEGORY => null
2716 ,p_ATTRIBUTE1 => null
2717 ,p_ATTRIBUTE2 => null
2718 ,p_ATTRIBUTE3 => null
2719 ,p_ATTRIBUTE4 => null
2720 ,p_ATTRIBUTE5 => null
2721 ,p_ATTRIBUTE6 => null
2722 ,p_ATTRIBUTE7 => null
2723 ,p_ATTRIBUTE8 => null
2724 ,p_ATTRIBUTE9 => null
2725 ,p_ATTRIBUTE10 => null
2726 ,p_ATTRIBUTE11 => null
2727 ,p_ATTRIBUTE12 => null
2728 ,p_ATTRIBUTE13 => null
2729 ,p_ATTRIBUTE14 => null
2730 ,p_ATTRIBUTE15 => null
2731 ,p_structure_type => p_structure_type
2732 );
2733
2734 IF Fnd_Msg_Pub.count_msg > 0 THEN -- FPM Dev CR 8
2735 RAISE FND_API.G_EXC_ERROR;
2736 END IF;
2737
2738 l_PROGRESS_ROLLUP_ID := null;
2739 --Create record in progress rollup
2740
2741 IF g1_debug_mode = 'Y' THEN
2742 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'INSERTED IN PPC', x_Log_Level=> 3);
2743 END IF;
2744
2745
2746 l_PROGRESS_ROLLUP_ID := PA_PROGRESS_UTILS.get_prog_rollup_id(
2747 p_project_id => p_project_id
2748 --,p_object_id => l_assignment_id --bug# 3764224 Changes for RLM
2749 ,p_object_id => l_resource_list_member_id
2750 ,p_object_type => l_object_type
2751 ,p_object_version_id => l_object_version_id
2752 ,p_as_of_date => p_as_of_date
2753 ,p_structure_version_id => l_structure_version_id
2754 ,p_action => p_action -- Bug 3879461
2755 ,x_record_version_number => l_rollup_rec_ver_number
2756 ,p_proj_element_id => p_task_id --bug# 3764224 Added for RLM
2757 );
2758 IF Fnd_Msg_Pub.count_msg > 0 THEN
2759 RAISE FND_API.G_EXC_ERROR;
2760 END IF;
2761
2762
2763 IF g1_debug_mode = 'Y' THEN
2764 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_PROGRESS_ROLLUP_ID '||l_PROGRESS_ROLLUP_ID, x_Log_Level=> 3);
2765 END IF;
2766
2767
2768 IF l_PROGRESS_ROLLUP_ID IS NULL
2769 THEN
2770 IF g1_debug_mode = 'Y' THEN
2771 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'INSERTING IN PPR', x_Log_Level=> 3);
2772 END IF;
2773
2774 PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
2775 X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
2776 ,X_PROJECT_ID => p_project_id
2777 --,X_OBJECT_ID => l_assignment_id --bug# 3764224 Changes for RLM
2778 ,X_OBJECT_ID => l_resource_list_member_id
2779 ,X_OBJECT_TYPE => l_object_type
2783 ,X_LAST_UPDATED_BY => l_user_id
2780 ,X_AS_OF_DATE => p_as_of_date
2781 ,X_OBJECT_VERSION_ID => l_object_version_id
2782 ,X_LAST_UPDATE_DATE => SYSDATE
2784 ,X_CREATION_DATE => SYSDATE
2785 ,X_CREATED_BY => l_user_id
2786 ,X_PROGRESS_STATUS_CODE => l_rollup_progress_status
2787 ,X_LAST_UPDATE_LOGIN => l_login_id
2788 ,X_INCREMENTAL_WORK_QTY => l_INCREMENTAL_WORK_QTY
2789 ,X_CUMULATIVE_WORK_QTY => l_CUMULATIVE_WORK_QTY
2790 ,X_BASE_PERCENT_COMPLETE => l_BASE_PERCENT_COMPLETE
2791 ,X_EFF_ROLLUP_PERCENT_COMP => l_EFF_ROLLUP_PERCENT_COMP
2792 ,X_COMPLETED_PERCENTAGE => l_rollup_completed_percentage
2793 ,X_ESTIMATED_START_DATE => l_estimated_start_date
2794 ,X_ESTIMATED_FINISH_DATE => l_estimated_finish_date
2795 ,X_ACTUAL_START_DATE => l_actual_start_date
2796 ,X_ACTUAL_FINISH_DATE => l_actual_finish_date
2797 ,X_EST_REMAINING_EFFORT => l_ppl_etc_effort
2798 ,X_BASE_PERCENT_COMP_DERIV_CODE => l_BASE_PERCENT_COMP_DERIV_CODE
2799 ,X_BASE_PROGRESS_STATUS_CODE => l_date_override_flag -- 4533112 l_BASE_PROGRESS_STATUS_CODE
2800 ,X_EFF_ROLLUP_PROG_STAT_CODE => l_EFF_ROLLUP_PROG_STAT_CODE
2801 ,x_percent_complete_id => l_percent_complete_id
2802 ,X_STRUCTURE_TYPE => p_structure_type
2803 ,X_PROJ_ELEMENT_ID => p_task_id
2804 ,X_STRUCTURE_VERSION_ID => l_structure_version_id
2805 ,X_PPL_ACT_EFFORT_TO_DATE => l_ppl_act_effort_to_date
2806 ,X_EQPMT_ACT_EFFORT_TO_DATE => l_eqpmt_act_effort_to_date
2807 ,X_EQPMT_ETC_EFFORT => l_eqpmt_etc_effort
2808 ,X_OTH_ACT_COST_TO_DATE_TC => l_oth_act_bur_cost_to_date_tc
2809 ,X_OTH_ACT_COST_TO_DATE_FC => l_oth_act_bur_cost_to_date_fc
2810 ,X_OTH_ACT_COST_TO_DATE_PC => l_oth_act_bur_cost_to_date_pc
2811 ,X_OTH_ETC_COST_TC => l_oth_etc_bur_cost_tc
2812 ,X_OTH_ETC_COST_FC => l_oth_etc_bur_cost_fc
2813 ,X_OTH_ETC_COST_PC => l_oth_etc_bur_cost_pc
2814 ,X_PPL_ACT_COST_TO_DATE_TC => l_ppl_act_bur_cost_to_date_tc
2815 ,X_PPL_ACT_COST_TO_DATE_FC => l_ppl_act_bur_cost_to_date_fc
2816 ,X_PPL_ACT_COST_TO_DATE_PC => l_ppl_act_bur_cost_to_date_pc
2817 ,X_PPL_ETC_COST_TC => l_ppl_etc_bur_cost_tc
2818 ,X_PPL_ETC_COST_FC => l_ppl_etc_bur_cost_fc
2819 ,X_PPL_ETC_COST_PC => l_ppl_etc_bur_cost_pc
2820 ,X_EQPMT_ACT_COST_TO_DATE_TC => l_eqp_act_bur_cost_to_date_tc
2821 ,X_EQPMT_ACT_COST_TO_DATE_FC => l_eqp_act_bur_cost_to_date_fc
2822 ,X_EQPMT_ACT_COST_TO_DATE_PC => l_eqp_act_bur_cost_to_date_pc
2823 ,X_EQPMT_ETC_COST_TC => l_eqpmt_etc_bur_cost_tc
2824 ,X_EQPMT_ETC_COST_FC => l_eqpmt_etc_bur_cost_fc
2825 ,X_EQPMT_ETC_COST_PC => l_eqpmt_etc_bur_cost_pc
2826 ,X_EARNED_VALUE => null
2827 ,X_TASK_WT_BASIS_CODE => null
2828 ,X_SUBPRJ_PPL_ACT_EFFORT => null
2829 ,X_SUBPRJ_EQPMT_ACT_EFFORT => null
2830 ,X_SUBPRJ_PPL_ETC_EFFORT => null
2831 ,X_SUBPRJ_EQPMT_ETC_EFFORT => null
2832 ,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
2833 ,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
2834 ,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
2835 ,X_SUBPRJ_PPL_ACT_COST_TC => null
2836 ,X_SUBPRJ_PPL_ACT_COST_FC => null
2837 ,X_SUBPRJ_PPL_ACT_COST_PC => null
2838 ,X_SUBPRJ_EQPMT_ACT_COST_TC => null
2839 ,X_SUBPRJ_EQPMT_ACT_COST_FC => null
2840 ,X_SUBPRJ_EQPMT_ACT_COST_PC => null
2841 ,X_SUBPRJ_OTH_ETC_COST_TC => null
2842 ,X_SUBPRJ_OTH_ETC_COST_FC => null
2843 ,X_SUBPRJ_OTH_ETC_COST_PC => null
2844 ,X_SUBPRJ_PPL_ETC_COST_TC => null
2845 ,X_SUBPRJ_PPL_ETC_COST_FC => null
2846 ,X_SUBPRJ_PPL_ETC_COST_PC => null
2847 ,X_SUBPRJ_EQPMT_ETC_COST_TC => null
2848 ,X_SUBPRJ_EQPMT_ETC_COST_FC => null
2849 ,X_SUBPRJ_EQPMT_ETC_COST_PC => null
2850 ,X_SUBPRJ_EARNED_VALUE => null
2851 ,X_CURRENT_FLAG => l_rollup_current_flag -- Bug 3879461 l_current_flag
2852 ,X_PROJFUNC_COST_RATE_TYPE => l_projfunc_cost_rate_type
2853 ,X_PROJFUNC_COST_EXCHANGE_RATE => l_projfunc_cost_exch_rate
2857 ,X_PROJ_COST_RATE_DATE => l_project_rate_date
2854 ,X_PROJFUNC_COST_RATE_DATE => l_projfunc_cost_rate_date
2855 ,X_PROJ_COST_RATE_TYPE => l_project_rate_type
2856 ,X_PROJ_COST_EXCHANGE_RATE => l_project_exch_rate
2858 ,X_TXN_CURRENCY_CODE => l_txn_currency_code -- Fix for Bug # 3988457.
2859 -- p_txn_currency_code --maansari4/30
2860 ,X_PROG_PA_PERIOD_NAME => l_prog_pa_period_name
2861 ,X_PROG_GL_PERIOD_NAME => l_prog_gl_period_name
2862 --bug 3608801
2863 ,X_OTH_QUANTITY_TO_DATE => l_oth_quantity_to_date -- bug no.3608801
2864 ,X_OTH_ETC_QUANTITY => l_oth_etc_quantity
2865 --bug 3608801
2866 ,X_OTH_ACT_RAWCOST_TO_DATE_TC => l_oth_act_raw_cost_to_date_tc
2867 ,X_OTH_ACT_RAWCOST_TO_DATE_FC => l_oth_act_raw_cost_to_date_fc
2868 ,X_OTH_ACT_RAWCOST_TO_DATE_PC => l_oth_act_raw_cost_to_date_pc
2869 ,X_OTH_ETC_RAWCOST_TC => l_oth_etc_raw_cost_tc
2870 ,X_OTH_ETC_RAWCOST_FC => l_oth_etc_raw_cost_fc
2871 ,X_OTH_ETC_RAWCOST_PC => l_oth_etc_raw_cost_pc
2872 ,X_PPL_ACT_RAWCOST_TO_DATE_TC => l_ppl_act_raw_cost_to_date_tc
2873 ,X_PPL_ACT_RAWCOST_TO_DATE_FC => l_ppl_act_raw_cost_to_date_fc
2874 ,X_PPL_ACT_RAWCOST_TO_DATE_PC => l_ppl_act_raw_cost_to_date_pc
2875 ,X_PPL_ETC_RAWCOST_TC => l_ppl_etc_raw_cost_tc
2876 ,X_PPL_ETC_RAWCOST_FC => l_ppl_etc_raw_cost_fc
2877 ,X_PPL_ETC_RAWCOST_PC => l_ppl_etc_raw_cost_pc
2878 ,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => l_eqp_act_raw_cost_to_date_tc
2879 ,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => l_eqp_act_raw_cost_to_date_fc
2880 ,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => l_eqp_act_raw_cost_to_date_pc
2881 ,X_EQPMT_ETC_RAWCOST_TC => l_eqpmt_etc_raw_cost_tc
2882 ,X_EQPMT_ETC_RAWCOST_FC => l_eqpmt_etc_raw_cost_fc
2883 ,X_EQPMT_ETC_RAWCOST_PC => l_eqpmt_etc_raw_cost_pc
2884 ,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
2885 ,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
2886 ,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
2887 ,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
2888 ,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
2889 ,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
2890 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
2891 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
2892 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
2893 ,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
2894 ,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
2895 ,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
2896 ,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
2897 ,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
2898 ,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
2899 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
2900 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
2901 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
2902 );
2903
2904 IF Fnd_Msg_Pub.count_msg > 0 THEN
2905 RAISE FND_API.G_EXC_ERROR;
2906 END IF;
2907
2908 IF g1_debug_mode = 'Y' THEN
2909 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'INSERTED IN PPR', x_Log_Level=> 3);
2910 END IF;
2911
2912 ELSE
2913 --update progress rollup
2914 --This case is not possible for Assignments but the code has been kept.
2915 IF g1_debug_mode = 'Y' THEN
2916 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'UPDATING PPR', x_Log_Level=> 3);
2917 END IF;
2918
2919 PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
2920 X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
2921 ,X_PROJECT_ID => p_project_id
2922 --,X_OBJECT_ID => l_assignment_id --bug# 3764224 Changes for RLM
2923 ,X_OBJECT_ID => l_resource_list_member_id
2924 ,X_OBJECT_TYPE => l_object_type
2925 ,X_AS_OF_DATE => p_as_of_date
2926 ,X_OBJECT_VERSION_ID => l_object_version_id
2927 ,X_LAST_UPDATE_DATE => SYSDATE
2928 ,X_LAST_UPDATED_BY => l_user_id
2929 ,X_PROGRESS_STATUS_CODE => l_rollup_progress_status
2930 ,X_LAST_UPDATE_LOGIN => l_login_id
2931 ,X_INCREMENTAL_WORK_QTY => l_INCREMENTAL_WORK_QTY
2932 ,X_CUMULATIVE_WORK_QTY => l_CUMULATIVE_WORK_QTY
2933 ,X_BASE_PERCENT_COMPLETE => l_BASE_PERCENT_COMPLETE
2934 ,X_EFF_ROLLUP_PERCENT_COMP => l_EFF_ROLLUP_PERCENT_COMP
2935 ,X_COMPLETED_PERCENTAGE => l_rollup_completed_percentage
2936 ,X_ESTIMATED_START_DATE => l_estimated_start_date
2940 ,X_EST_REMAINING_EFFORT => l_ppl_etc_effort -- need to populate the buckets
2937 ,X_ESTIMATED_FINISH_DATE => l_estimated_finish_date
2938 ,X_ACTUAL_START_DATE => l_actual_start_date
2939 ,X_ACTUAL_FINISH_DATE => l_actual_finish_date
2941 ,X_BASE_PERCENT_COMP_DERIV_CODE => l_BASE_PERCENT_COMP_DERIV_CODE
2942 ,X_BASE_PROGRESS_STATUS_CODE => l_date_override_flag -- 4533112 l_BASE_PROGRESS_STATUS_CODE
2943 ,X_EFF_ROLLUP_PROG_STAT_CODE => l_EFF_ROLLUP_PROG_STAT_CODE
2944 ,X_RECORD_VERSION_NUMBER => l_rollup_rec_ver_number
2945 ,x_percent_complete_id => l_percent_complete_id
2946 ,X_STRUCTURE_TYPE => p_structure_type
2947 ,X_PROJ_ELEMENT_ID => p_task_id
2948 ,X_STRUCTURE_VERSION_ID => l_structure_version_id
2949 ,X_PPL_ACT_EFFORT_TO_DATE => l_ppl_act_effort_to_date
2950 ,X_EQPMT_ACT_EFFORT_TO_DATE => l_eqpmt_act_effort_to_date
2951 ,X_EQPMT_ETC_EFFORT => l_eqpmt_etc_effort
2952 ,X_OTH_ACT_COST_TO_DATE_TC => l_oth_act_bur_cost_to_date_tc
2953 ,X_OTH_ACT_COST_TO_DATE_FC => l_oth_act_bur_cost_to_date_fc
2954 ,X_OTH_ACT_COST_TO_DATE_PC => l_oth_act_bur_cost_to_date_pc
2955 ,X_OTH_ETC_COST_TC => l_oth_etc_bur_cost_tc
2956 ,X_OTH_ETC_COST_FC => l_oth_etc_bur_cost_fc
2957 ,X_OTH_ETC_COST_PC => l_oth_etc_bur_cost_pc
2958 ,X_PPL_ACT_COST_TO_DATE_TC => l_ppl_act_bur_cost_to_date_tc
2959 ,X_PPL_ACT_COST_TO_DATE_FC => l_ppl_act_bur_cost_to_date_fc
2960 ,X_PPL_ACT_COST_TO_DATE_PC => l_ppl_act_bur_cost_to_date_pc
2961 ,X_PPL_ETC_COST_TC => l_ppl_etc_bur_cost_tc
2962 ,X_PPL_ETC_COST_FC => l_ppl_etc_bur_cost_fc
2963 ,X_PPL_ETC_COST_PC => l_ppl_etc_bur_cost_pc
2964 ,X_EQPMT_ACT_COST_TO_DATE_TC => l_eqp_act_bur_cost_to_date_tc
2965 ,X_EQPMT_ACT_COST_TO_DATE_FC => l_eqp_act_bur_cost_to_date_fc
2966 ,X_EQPMT_ACT_COST_TO_DATE_PC => l_eqp_act_bur_cost_to_date_pc
2967 ,X_EQPMT_ETC_COST_TC => l_eqpmt_etc_bur_cost_tc
2968 ,X_EQPMT_ETC_COST_FC => l_eqpmt_etc_bur_cost_fc
2969 ,X_EQPMT_ETC_COST_PC => l_eqpmt_etc_bur_cost_pc
2970 ,X_EARNED_VALUE => null
2971 ,X_TASK_WT_BASIS_CODE => null
2972 ,X_SUBPRJ_PPL_ACT_EFFORT => null
2973 ,X_SUBPRJ_EQPMT_ACT_EFFORT => null
2974 ,X_SUBPRJ_PPL_ETC_EFFORT => null
2975 ,X_SUBPRJ_EQPMT_ETC_EFFORT => null
2976 ,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
2977 ,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
2978 ,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
2979 ,X_SUBPRJ_PPL_ACT_COST_TC => null
2980 ,X_SUBPRJ_PPL_ACT_COST_FC => null
2981 ,X_SUBPRJ_PPL_ACT_COST_PC => null
2982 ,X_SUBPRJ_EQPMT_ACT_COST_TC => null
2983 ,X_SUBPRJ_EQPMT_ACT_COST_FC => null
2984 ,X_SUBPRJ_EQPMT_ACT_COST_PC => null
2985 ,X_SUBPRJ_OTH_ETC_COST_TC => null
2986 ,X_SUBPRJ_OTH_ETC_COST_FC => null
2987 ,X_SUBPRJ_OTH_ETC_COST_PC => null
2988 ,X_SUBPRJ_PPL_ETC_COST_TC => null
2989 ,X_SUBPRJ_PPL_ETC_COST_FC => null
2990 ,X_SUBPRJ_PPL_ETC_COST_PC => null
2991 ,X_SUBPRJ_EQPMT_ETC_COST_TC => null
2992 ,X_SUBPRJ_EQPMT_ETC_COST_FC => null
2993 ,X_SUBPRJ_EQPMT_ETC_COST_PC => null
2994 ,X_SUBPRJ_EARNED_VALUE => null
2995 ,X_CURRENT_FLAG => l_rollup_current_flag -- Bug 3879461 l_current_flag
2996 ,X_PROJFUNC_COST_RATE_TYPE => l_projfunc_cost_rate_type
2997 ,X_PROJFUNC_COST_EXCHANGE_RATE => l_projfunc_cost_exch_rate
2998 ,X_PROJFUNC_COST_RATE_DATE => l_projfunc_cost_rate_date
2999 ,X_PROJ_COST_RATE_TYPE => l_project_rate_type
3000 ,X_PROJ_COST_EXCHANGE_RATE => l_project_exch_rate
3001 ,X_PROJ_COST_RATE_DATE => l_project_rate_date
3002 ,X_TXN_CURRENCY_CODE => l_txn_currency_code -- Fix for Bug # 3988457.
3003 -- p_txn_currency_code --maansari4/30
3004 ,X_PROG_PA_PERIOD_NAME => l_prog_pa_period_name
3005 ,X_PROG_GL_PERIOD_NAME => l_prog_gl_period_name
3006 --bug 3608801
3007 ,X_OTH_QUANTITY_TO_DATE => l_oth_quantity_to_date -- bug no.3608801
3008 ,X_OTH_ETC_QUANTITY => l_oth_etc_quantity
3009 --bug 3608801
3010 ,X_OTH_ACT_RAWCOST_TO_DATE_TC => l_oth_act_raw_cost_to_date_tc
3014 ,X_OTH_ETC_RAWCOST_FC => l_oth_etc_raw_cost_fc
3011 ,X_OTH_ACT_RAWCOST_TO_DATE_FC => l_oth_act_raw_cost_to_date_fc
3012 ,X_OTH_ACT_RAWCOST_TO_DATE_PC => l_oth_act_raw_cost_to_date_pc
3013 ,X_OTH_ETC_RAWCOST_TC => l_oth_etc_raw_cost_tc
3015 ,X_OTH_ETC_RAWCOST_PC => l_oth_etc_raw_cost_pc
3016 ,X_PPL_ACT_RAWCOST_TO_DATE_TC => l_ppl_act_raw_cost_to_date_tc
3017 ,X_PPL_ACT_RAWCOST_TO_DATE_FC => l_ppl_act_raw_cost_to_date_fc
3018 ,X_PPL_ACT_RAWCOST_TO_DATE_PC => l_ppl_act_raw_cost_to_date_pc
3019 ,X_PPL_ETC_RAWCOST_TC => l_ppl_etc_raw_cost_tc
3020 ,X_PPL_ETC_RAWCOST_FC => l_ppl_etc_raw_cost_fc
3021 ,X_PPL_ETC_RAWCOST_PC => l_ppl_etc_raw_cost_pc
3022 ,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => l_eqp_act_raw_cost_to_date_tc
3023 ,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => l_eqp_act_raw_cost_to_date_fc
3024 ,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => l_eqp_act_raw_cost_to_date_pc
3025 ,X_EQPMT_ETC_RAWCOST_TC => l_eqpmt_etc_raw_cost_tc
3026 ,X_EQPMT_ETC_RAWCOST_FC => l_eqpmt_etc_raw_cost_fc
3027 ,X_EQPMT_ETC_RAWCOST_PC => l_eqpmt_etc_raw_cost_pc
3028 ,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
3029 ,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
3030 ,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
3031 ,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
3032 ,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
3033 ,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
3034 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
3035 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
3036 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
3037 ,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
3038 ,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
3039 ,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
3040 ,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
3041 ,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
3042 ,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
3043 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
3044 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
3045 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
3046 );
3047 IF Fnd_Msg_Pub.count_msg > 0 THEN
3048 RAISE FND_API.G_EXC_ERROR;
3049 END IF;
3050
3051
3052 END IF;
3053
3054 ELSIF l_db_action = 'UPDATE'
3055 THEN
3056 IF l_working_aod IS NOT NULL
3057 THEN
3058 l_aod := l_working_aod;
3059 ELSE
3060 l_aod := p_as_of_date;
3061 END IF;
3062
3063 -- Bug 3879461 : Temporary getting percent complete id and record version number from database
3064 -- So no locking.
3065 -- There is issue in UI, after calling API under Recalculate, it should incrment the VO record version number
3066
3067 -- IF p_percent_complete_id IS NULL OR p_percent_complete_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3068 -- THEN
3069 l_percent_complete_id := PA_PROGRESS_UTILS.get_ppc_id(
3070 p_project_id => p_project_id
3071 --,p_object_id => l_assignment_id --bug# 3764224 Changes for RLM
3072 ,p_object_id => l_resource_list_member_id
3073 ,p_object_type => l_object_type
3074 ,p_object_version_id => l_object_version_id
3075 ,p_as_of_date => l_aod
3076 ,p_structure_type => p_structure_type
3077 ,p_task_id => p_task_id --bug# 3764224 Added for RLM
3078 );
3079 -- FPM Dev CR 3 Getting Record Version Number too
3080 BEGIN
3081 SELECT record_version_number into l_record_version_number
3082 FROM pa_percent_completes
3083 where percent_complete_id = l_percent_complete_id;
3084 END;
3085
3086 -- ELSE
3087 -- l_percent_complete_id := p_percent_complete_id;
3088 -- l_record_version_number := p_record_version_number;
3089 -- END IF;
3090
3091 IF g1_debug_mode = 'Y' THEN
3092 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_percent_complete_id: '||l_percent_complete_id, x_Log_Level=> 3);
3093 END IF;
3094
3095
3096 PA_PERCENT_COMPLETES_PKG.UPDATE_ROW(
3097 p_TASK_ID => l_task_id
3098 ,p_DATE_COMPUTED => p_as_of_date
3099 ,p_LAST_UPDATE_DATE => SYSDATE
3100 ,p_LAST_UPDATED_BY => l_user_id
3101 ,p_LAST_UPDATE_LOGIN => l_login_id
3102 ,p_COMPLETED_PERCENTAGE => l_percent_complete
3103 ,p_DESCRIPTION => l_brief_overview
3104 ,p_PM_PRODUCT_CODE => l_pm_product_code
3105 ,p_CURRENT_FLAG => l_current_flag
3106 ,p_PERCENT_COMPLETE_ID => l_percent_complete_id
3107 ,p_project_id => p_project_id
3108 ,p_OBJECT_TYPE => l_object_type
3112 ,p_PROGRESS_STATUS_CODE => l_progress_status_code
3109 --,p_OBJECT_ID => l_assignment_id --bug# 3764224 Changes for RLM
3110 ,p_OBJECT_ID => l_resource_list_member_id
3111 ,p_OBJECT_VERSION_ID => l_object_version_id
3113 ,p_ACTUAL_START_DATE => l_actual_start_date
3114 ,p_ACTUAL_FINISH_DATE => l_actual_finish_date
3115 ,p_ESTIMATED_START_DATE => l_estimated_start_date
3116 ,p_ESTIMATED_FINISH_DATE => l_estimated_finish_date
3117 ,p_PUBLISHED_FLAG => l_published_flag
3118 ,p_PUBLISHED_BY_PARTY_ID => l_published_by_party_id
3119 ,p_PROGRESS_COMMENT => l_progress_comment
3120 ,p_HISTORY_FLAG => 'N'
3121 ,p_status_code => l_task_status
3122 ,p_RECORD_VERSION_NUMBER => l_record_version_number
3123 ,p_ATTRIBUTE_CATEGORY => null
3124 ,p_ATTRIBUTE1 => null
3125 ,p_ATTRIBUTE2 => null
3126 ,p_ATTRIBUTE3 => null
3127 ,p_ATTRIBUTE4 => null
3128 ,p_ATTRIBUTE5 => null
3129 ,p_ATTRIBUTE6 => null
3130 ,p_ATTRIBUTE7 => null
3131 ,p_ATTRIBUTE8 => null
3132 ,p_ATTRIBUTE9 => null
3133 ,p_ATTRIBUTE10 => null
3134 ,p_ATTRIBUTE11 => null
3135 ,p_ATTRIBUTE12 => null
3136 ,p_ATTRIBUTE13 => null
3137 ,p_ATTRIBUTE14 => null
3138 ,p_ATTRIBUTE15 => null
3139 ,p_structure_type => p_structure_type
3140
3141 );
3142 IF Fnd_Msg_Pub.count_msg > 0 THEN
3143 RAISE FND_API.G_EXC_ERROR;
3144 END IF;
3145
3146
3147
3148 --update progress rollup
3149 l_PROGRESS_ROLLUP_ID := PA_PROGRESS_UTILS.get_prog_rollup_id(
3150 p_project_id => p_project_id
3151 --,p_object_id => l_assignment_id --bug# 3764224 Changes for RLM
3152 ,p_object_id => l_resource_list_member_id
3153 ,p_object_type => l_object_type
3154 ,p_object_version_id => l_object_version_id
3155 ,p_as_of_date => l_aod -- FPM Dev CR 3 : Using l_aod instead of p_as_of_date
3156 ,p_structure_type => p_structure_type
3157 ,p_structure_version_id => l_structure_version_id
3158 ,p_action => p_action -- Bug 3879461
3159 ,x_record_version_number => l_rollup_rec_ver_number
3160 ,p_proj_element_id => p_task_id --bug# 3764224 Added for RLM
3161 );
3162
3163 IF g1_debug_mode = 'Y' THEN
3164 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'l_PROGRESS_ROLLUP_ID: '||l_PROGRESS_ROLLUP_ID, x_Log_Level=> 3);
3165 END IF;
3166
3167
3168 IF l_PROGRESS_ROLLUP_ID IS NOT NULL
3169 THEN
3170 PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
3171 X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
3172 ,X_PROJECT_ID => p_project_id
3173 --,X_OBJECT_ID => l_assignment_id --bug# 3764224 Changes for RLM
3174 ,X_OBJECT_ID => l_resource_list_member_id
3175 ,X_OBJECT_TYPE => l_object_type
3176 ,X_AS_OF_DATE => p_as_of_date
3177 ,X_OBJECT_VERSION_ID => l_object_version_id
3178 ,X_LAST_UPDATE_DATE => SYSDATE
3179 ,X_LAST_UPDATED_BY => l_user_id
3180 ,X_PROGRESS_STATUS_CODE => l_rollup_progress_status
3181 ,X_LAST_UPDATE_LOGIN => l_login_id
3182 ,X_INCREMENTAL_WORK_QTY => l_INCREMENTAL_WORK_QTY
3183 ,X_CUMULATIVE_WORK_QTY => l_CUMULATIVE_WORK_QTY
3184 ,X_BASE_PERCENT_COMPLETE => l_BASE_PERCENT_COMPLETE
3185 ,X_EFF_ROLLUP_PERCENT_COMP => l_EFF_ROLLUP_PERCENT_COMP
3186 ,X_COMPLETED_PERCENTAGE => l_rollup_completed_percentage
3187 ,X_ESTIMATED_START_DATE => l_estimated_start_date
3188 ,X_ESTIMATED_FINISH_DATE => l_estimated_finish_date
3189 ,X_ACTUAL_START_DATE => l_actual_start_date
3190 ,X_ACTUAL_FINISH_DATE => l_actual_finish_date
3191 ,X_EST_REMAINING_EFFORT => l_ppl_etc_effort -- need to populate the buckets
3192 ,X_BASE_PERCENT_COMP_DERIV_CODE => l_BASE_PERCENT_COMP_DERIV_CODE
3193 ,X_BASE_PROGRESS_STATUS_CODE => l_date_override_flag -- 4533112 l_BASE_PROGRESS_STATUS_CODE
3194 ,X_EFF_ROLLUP_PROG_STAT_CODE => l_EFF_ROLLUP_PROG_STAT_CODE
3195 ,X_RECORD_VERSION_NUMBER => l_rollup_rec_ver_number
3196 ,x_percent_complete_id => l_percent_complete_id
3200 ,X_PPL_ACT_EFFORT_TO_DATE => l_ppl_act_effort_to_date
3197 ,X_STRUCTURE_TYPE => p_structure_type
3198 ,X_PROJ_ELEMENT_ID => p_task_id
3199 ,X_STRUCTURE_VERSION_ID => l_structure_version_id
3201 ,X_EQPMT_ACT_EFFORT_TO_DATE => l_eqpmt_act_effort_to_date
3202 ,X_EQPMT_ETC_EFFORT => l_eqpmt_etc_effort
3203 ,X_OTH_ACT_COST_TO_DATE_TC => l_oth_act_bur_cost_to_date_tc
3204 ,X_OTH_ACT_COST_TO_DATE_FC => l_oth_act_bur_cost_to_date_fc
3205 ,X_OTH_ACT_COST_TO_DATE_PC => l_oth_act_bur_cost_to_date_pc
3206 ,X_OTH_ETC_COST_TC => l_oth_etc_bur_cost_tc
3207 ,X_OTH_ETC_COST_FC => l_oth_etc_bur_cost_fc
3208 ,X_OTH_ETC_COST_PC => l_oth_etc_bur_cost_pc
3209 ,X_PPL_ACT_COST_TO_DATE_TC => l_ppl_act_bur_cost_to_date_tc
3210 ,X_PPL_ACT_COST_TO_DATE_FC => l_ppl_act_bur_cost_to_date_fc
3211 ,X_PPL_ACT_COST_TO_DATE_PC => l_ppl_act_bur_cost_to_date_pc
3212 ,X_PPL_ETC_COST_TC => l_ppl_etc_bur_cost_tc
3213 ,X_PPL_ETC_COST_FC => l_ppl_etc_bur_cost_fc
3214 ,X_PPL_ETC_COST_PC => l_ppl_etc_bur_cost_pc
3215 ,X_EQPMT_ACT_COST_TO_DATE_TC => l_eqp_act_bur_cost_to_date_tc
3216 ,X_EQPMT_ACT_COST_TO_DATE_FC => l_eqp_act_bur_cost_to_date_fc
3217 ,X_EQPMT_ACT_COST_TO_DATE_PC => l_eqp_act_bur_cost_to_date_pc
3218 ,X_EQPMT_ETC_COST_TC => l_eqpmt_etc_bur_cost_tc
3219 ,X_EQPMT_ETC_COST_FC => l_eqpmt_etc_bur_cost_fc
3220 ,X_EQPMT_ETC_COST_PC => l_eqpmt_etc_bur_cost_pc
3221 ,X_EARNED_VALUE => null
3222 ,X_TASK_WT_BASIS_CODE => null
3223 ,X_SUBPRJ_PPL_ACT_EFFORT => null
3224 ,X_SUBPRJ_EQPMT_ACT_EFFORT => null
3225 ,X_SUBPRJ_PPL_ETC_EFFORT => null
3226 ,X_SUBPRJ_EQPMT_ETC_EFFORT => null
3227 ,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
3228 ,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
3229 ,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
3230 ,X_SUBPRJ_PPL_ACT_COST_TC => null
3231 ,X_SUBPRJ_PPL_ACT_COST_FC => null
3232 ,X_SUBPRJ_PPL_ACT_COST_PC => null
3233 ,X_SUBPRJ_EQPMT_ACT_COST_TC => null
3234 ,X_SUBPRJ_EQPMT_ACT_COST_FC => null
3235 ,X_SUBPRJ_EQPMT_ACT_COST_PC => null
3236 ,X_SUBPRJ_OTH_ETC_COST_TC => null
3237 ,X_SUBPRJ_OTH_ETC_COST_FC => null
3238 ,X_SUBPRJ_OTH_ETC_COST_PC => null
3239 ,X_SUBPRJ_PPL_ETC_COST_TC => null
3240 ,X_SUBPRJ_PPL_ETC_COST_FC => null
3241 ,X_SUBPRJ_PPL_ETC_COST_PC => null
3242 ,X_SUBPRJ_EQPMT_ETC_COST_TC => null
3243 ,X_SUBPRJ_EQPMT_ETC_COST_FC => null
3244 ,X_SUBPRJ_EQPMT_ETC_COST_PC => null
3245 ,X_SUBPRJ_EARNED_VALUE => null
3246 ,X_CURRENT_FLAG => l_rollup_current_flag -- Bug 3879461 l_current_flag
3247 ,X_PROJFUNC_COST_RATE_TYPE => l_projfunc_cost_rate_type
3248 ,X_PROJFUNC_COST_EXCHANGE_RATE => l_projfunc_cost_exch_rate
3249 ,X_PROJFUNC_COST_RATE_DATE => l_projfunc_cost_rate_date
3250 ,X_PROJ_COST_RATE_TYPE => l_project_rate_type
3251 ,X_PROJ_COST_EXCHANGE_RATE => l_project_exch_rate
3252 ,X_PROJ_COST_RATE_DATE => l_project_rate_date
3253 ,X_TXN_CURRENCY_CODE => l_txn_currency_code -- Fix for Bug # 3988457.
3254 -- p_txn_currency_code --maansari4/30
3255 ,X_PROG_PA_PERIOD_NAME => l_prog_pa_period_name
3256 ,X_PROG_GL_PERIOD_NAME => l_prog_gl_period_name
3257 --bug 3608801
3258 ,X_OTH_QUANTITY_TO_DATE => l_oth_quantity_to_date -- bug no.3608801
3259 ,X_OTH_ETC_QUANTITY => l_oth_etc_quantity
3260 --bug 3608801
3261 ,X_OTH_ACT_RAWCOST_TO_DATE_TC => l_oth_act_raw_cost_to_date_tc
3262 ,X_OTH_ACT_RAWCOST_TO_DATE_FC => l_oth_act_raw_cost_to_date_fc
3263 ,X_OTH_ACT_RAWCOST_TO_DATE_PC => l_oth_act_raw_cost_to_date_pc
3264 ,X_OTH_ETC_RAWCOST_TC => l_oth_etc_raw_cost_tc
3265 ,X_OTH_ETC_RAWCOST_FC => l_oth_etc_raw_cost_fc
3266 ,X_OTH_ETC_RAWCOST_PC => l_oth_etc_raw_cost_pc
3267 ,X_PPL_ACT_RAWCOST_TO_DATE_TC => l_ppl_act_raw_cost_to_date_tc
3268 ,X_PPL_ACT_RAWCOST_TO_DATE_FC => l_ppl_act_raw_cost_to_date_fc
3269 ,X_PPL_ACT_RAWCOST_TO_DATE_PC => l_ppl_act_raw_cost_to_date_pc
3270 ,X_PPL_ETC_RAWCOST_TC => l_ppl_etc_raw_cost_tc
3271 ,X_PPL_ETC_RAWCOST_FC => l_ppl_etc_raw_cost_fc
3272 ,X_PPL_ETC_RAWCOST_PC => l_ppl_etc_raw_cost_pc
3273 ,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => l_eqp_act_raw_cost_to_date_tc
3277 ,X_EQPMT_ETC_RAWCOST_FC => l_eqpmt_etc_raw_cost_fc
3274 ,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => l_eqp_act_raw_cost_to_date_fc
3275 ,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => l_eqp_act_raw_cost_to_date_pc
3276 ,X_EQPMT_ETC_RAWCOST_TC => l_eqpmt_etc_raw_cost_tc
3278 ,X_EQPMT_ETC_RAWCOST_PC => l_eqpmt_etc_raw_cost_pc
3279 ,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
3280 ,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
3281 ,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
3282 ,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
3283 ,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
3284 ,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
3285 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
3286 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
3287 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
3288 ,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
3289 ,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
3290 ,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
3291 ,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
3292 ,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
3293 ,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
3294 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
3295 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
3296 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
3297 );
3298 IF Fnd_Msg_Pub.count_msg > 0 THEN
3299 RAISE FND_API.G_EXC_ERROR;
3300 END IF;
3301
3302 ELSE
3303 PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
3304 X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
3305 ,X_PROJECT_ID => p_project_id
3306 --,X_OBJECT_ID => l_assignment_id --bug# 3764224 Changes for RLM
3307 ,X_OBJECT_ID => l_resource_list_member_id
3308 ,X_OBJECT_TYPE => l_object_type
3309 ,X_AS_OF_DATE => p_as_of_date
3310 ,X_OBJECT_VERSION_ID => l_object_version_id
3311 ,X_LAST_UPDATE_DATE => SYSDATE
3312 ,X_LAST_UPDATED_BY => l_user_id
3313 ,X_CREATION_DATE => SYSDATE
3314 ,X_CREATED_BY => l_user_id
3315 ,X_PROGRESS_STATUS_CODE => l_rollup_progress_status
3316 ,X_LAST_UPDATE_LOGIN => l_login_id
3317 ,X_INCREMENTAL_WORK_QTY => l_INCREMENTAL_WORK_QTY
3318 ,X_CUMULATIVE_WORK_QTY => l_CUMULATIVE_WORK_QTY
3319 ,X_BASE_PERCENT_COMPLETE => l_BASE_PERCENT_COMPLETE
3320 ,X_EFF_ROLLUP_PERCENT_COMP => l_EFF_ROLLUP_PERCENT_COMP
3321 ,X_COMPLETED_PERCENTAGE => l_rollup_completed_percentage
3322 ,X_ESTIMATED_START_DATE => l_estimated_start_date
3323 ,X_ESTIMATED_FINISH_DATE => l_estimated_finish_date
3324 ,X_ACTUAL_START_DATE => l_actual_start_date
3325 ,X_ACTUAL_FINISH_DATE => l_actual_finish_date
3326 ,X_EST_REMAINING_EFFORT => l_ppl_etc_effort
3327 ,X_BASE_PERCENT_COMP_DERIV_CODE => l_BASE_PERCENT_COMP_DERIV_CODE
3328 ,X_BASE_PROGRESS_STATUS_CODE => l_date_override_flag -- 4533112 l_BASE_PROGRESS_STATUS_CODE
3329 ,X_EFF_ROLLUP_PROG_STAT_CODE => l_EFF_ROLLUP_PROG_STAT_CODE
3330 ,x_percent_complete_id => l_percent_complete_id
3331 ,X_STRUCTURE_TYPE => p_structure_type
3332 ,X_PROJ_ELEMENT_ID => p_task_id
3333 ,X_STRUCTURE_VERSION_ID => l_structure_version_id
3334 ,X_PPL_ACT_EFFORT_TO_DATE => l_ppl_act_effort_to_date
3335 ,X_EQPMT_ACT_EFFORT_TO_DATE => l_eqpmt_act_effort_to_date
3336 ,X_EQPMT_ETC_EFFORT => l_eqpmt_etc_effort
3337 ,X_OTH_ACT_COST_TO_DATE_TC => l_oth_act_bur_cost_to_date_tc
3338 ,X_OTH_ACT_COST_TO_DATE_FC => l_oth_act_bur_cost_to_date_fc
3339 ,X_OTH_ACT_COST_TO_DATE_PC => l_oth_act_bur_cost_to_date_pc
3340 ,X_OTH_ETC_COST_TC => l_oth_etc_bur_cost_tc
3341 ,X_OTH_ETC_COST_FC => l_oth_etc_bur_cost_fc
3342 ,X_OTH_ETC_COST_PC => l_oth_etc_bur_cost_pc
3343 ,X_PPL_ACT_COST_TO_DATE_TC => l_ppl_act_bur_cost_to_date_tc
3344 ,X_PPL_ACT_COST_TO_DATE_FC => l_ppl_act_bur_cost_to_date_fc
3345 ,X_PPL_ACT_COST_TO_DATE_PC => l_ppl_act_bur_cost_to_date_pc
3346 ,X_PPL_ETC_COST_TC => l_ppl_etc_bur_cost_tc
3347 ,X_PPL_ETC_COST_FC => l_ppl_etc_bur_cost_fc
3348 ,X_PPL_ETC_COST_PC => l_ppl_etc_bur_cost_pc
3349 ,X_EQPMT_ACT_COST_TO_DATE_TC => l_eqp_act_bur_cost_to_date_tc
3350 ,X_EQPMT_ACT_COST_TO_DATE_FC => l_eqp_act_bur_cost_to_date_fc
3351 ,X_EQPMT_ACT_COST_TO_DATE_PC => l_eqp_act_bur_cost_to_date_pc
3352 ,X_EQPMT_ETC_COST_TC => l_eqpmt_etc_bur_cost_tc
3356 ,X_TASK_WT_BASIS_CODE => null
3353 ,X_EQPMT_ETC_COST_FC => l_eqpmt_etc_bur_cost_fc
3354 ,X_EQPMT_ETC_COST_PC => l_eqpmt_etc_bur_cost_pc
3355 ,X_EARNED_VALUE => null
3357 ,X_SUBPRJ_PPL_ACT_EFFORT => null
3358 ,X_SUBPRJ_EQPMT_ACT_EFFORT => null
3359 ,X_SUBPRJ_PPL_ETC_EFFORT => null
3360 ,X_SUBPRJ_EQPMT_ETC_EFFORT => null
3361 ,X_SBPJ_OTH_ACT_COST_TO_DATE_TC => null
3362 ,X_SBPJ_OTH_ACT_COST_TO_DATE_FC => null
3363 ,X_SBPJ_OTH_ACT_COST_TO_DATE_PC => null
3364 ,X_SUBPRJ_PPL_ACT_COST_TC => null
3365 ,X_SUBPRJ_PPL_ACT_COST_FC => null
3366 ,X_SUBPRJ_PPL_ACT_COST_PC => null
3367 ,X_SUBPRJ_EQPMT_ACT_COST_TC => null
3368 ,X_SUBPRJ_EQPMT_ACT_COST_FC => null
3369 ,X_SUBPRJ_EQPMT_ACT_COST_PC => null
3370 ,X_SUBPRJ_OTH_ETC_COST_TC => null
3371 ,X_SUBPRJ_OTH_ETC_COST_FC => null
3372 ,X_SUBPRJ_OTH_ETC_COST_PC => null
3373 ,X_SUBPRJ_PPL_ETC_COST_TC => null
3374 ,X_SUBPRJ_PPL_ETC_COST_FC => null
3375 ,X_SUBPRJ_PPL_ETC_COST_PC => null
3376 ,X_SUBPRJ_EQPMT_ETC_COST_TC => null
3377 ,X_SUBPRJ_EQPMT_ETC_COST_FC => null
3378 ,X_SUBPRJ_EQPMT_ETC_COST_PC => null
3379 ,X_SUBPRJ_EARNED_VALUE => null
3380 ,X_CURRENT_FLAG => l_rollup_current_flag -- Bug 3879461 l_current_flag
3381 ,X_PROJFUNC_COST_RATE_TYPE => l_projfunc_cost_rate_type
3382 ,X_PROJFUNC_COST_EXCHANGE_RATE => l_projfunc_cost_exch_rate
3383 ,X_PROJFUNC_COST_RATE_DATE => l_projfunc_cost_rate_date
3384 ,X_PROJ_COST_RATE_TYPE => l_project_rate_type
3385 ,X_PROJ_COST_EXCHANGE_RATE => l_project_exch_rate
3386 ,X_PROJ_COST_RATE_DATE => l_project_rate_date
3387 ,X_TXN_CURRENCY_CODE => l_txn_currency_code -- Fix for Bug # 3988457.
3388 -- p_txn_currency_code --maansari4/30
3389 ,X_PROG_PA_PERIOD_NAME => l_prog_pa_period_name
3390 ,X_PROG_GL_PERIOD_NAME => l_prog_gl_period_name
3391 --bug 3608801
3392 ,X_OTH_QUANTITY_TO_DATE => l_oth_quantity_to_date -- bug no.3608801
3393 ,X_OTH_ETC_QUANTITY => l_oth_etc_quantity
3394 --bug 3608801
3395 ,X_OTH_ACT_RAWCOST_TO_DATE_TC => l_oth_act_raw_cost_to_date_tc
3396 ,X_OTH_ACT_RAWCOST_TO_DATE_FC => l_oth_act_raw_cost_to_date_fc
3397 ,X_OTH_ACT_RAWCOST_TO_DATE_PC => l_oth_act_raw_cost_to_date_pc
3398 ,X_OTH_ETC_RAWCOST_TC => l_oth_etc_raw_cost_tc
3399 ,X_OTH_ETC_RAWCOST_FC => l_oth_etc_raw_cost_fc
3400 ,X_OTH_ETC_RAWCOST_PC => l_oth_etc_raw_cost_pc
3401 ,X_PPL_ACT_RAWCOST_TO_DATE_TC => l_ppl_act_raw_cost_to_date_tc
3402 ,X_PPL_ACT_RAWCOST_TO_DATE_FC => l_ppl_act_raw_cost_to_date_fc
3403 ,X_PPL_ACT_RAWCOST_TO_DATE_PC => l_ppl_act_raw_cost_to_date_pc
3404 ,X_PPL_ETC_RAWCOST_TC => l_ppl_etc_raw_cost_tc
3405 ,X_PPL_ETC_RAWCOST_FC => l_ppl_etc_raw_cost_fc
3406 ,X_PPL_ETC_RAWCOST_PC => l_ppl_etc_raw_cost_pc
3407 ,X_EQPMT_ACT_RAWCOST_TO_DATE_TC => l_eqp_act_raw_cost_to_date_tc
3408 ,X_EQPMT_ACT_RAWCOST_TO_DATE_FC => l_eqp_act_raw_cost_to_date_fc
3409 ,X_EQPMT_ACT_RAWCOST_TO_DATE_PC => l_eqp_act_raw_cost_to_date_pc
3410 ,X_EQPMT_ETC_RAWCOST_TC => l_eqpmt_etc_raw_cost_tc
3411 ,X_EQPMT_ETC_RAWCOST_FC => l_eqpmt_etc_raw_cost_fc
3412 ,X_EQPMT_ETC_RAWCOST_PC => l_eqpmt_etc_raw_cost_pc
3413 ,X_SP_OTH_ACT_RAWCOST_TODATE_TC => null
3414 ,X_SP_OTH_ACT_RAWCOST_TODATE_FC => null
3415 ,X_SP_OTH_ACT_RAWCOST_TODATE_PC => null
3419 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_TC => null
3416 ,X_SUBPRJ_PPL_ACT_RAWCOST_TC => null
3417 ,X_SUBPRJ_PPL_ACT_RAWCOST_FC => null
3418 ,X_SUBPRJ_PPL_ACT_RAWCOST_PC => null
3420 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_FC => null
3421 ,X_SUBPRJ_EQPMT_ACT_RAWCOST_PC => null
3422 ,X_SUBPRJ_OTH_ETC_RAWCOST_TC => null
3423 ,X_SUBPRJ_OTH_ETC_RAWCOST_FC => null
3424 ,X_SUBPRJ_OTH_ETC_RAWCOST_PC => null
3425 ,X_SUBPRJ_PPL_ETC_RAWCOST_TC => null
3426 ,X_SUBPRJ_PPL_ETC_RAWCOST_FC => null
3427 ,X_SUBPRJ_PPL_ETC_RAWCOST_PC => null
3428 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
3429 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
3430 ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null
3431 );
3432 IF Fnd_Msg_Pub.count_msg > 0 THEN
3433 RAISE FND_API.G_EXC_ERROR;
3434 END IF;
3435
3436 END IF;
3437
3438 IF g1_debug_mode = 'Y' THEN
3439 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'COMPLETED ', x_Log_Level=> 3);
3440 END IF;
3441
3442 END IF; --<l_db_action>
3443
3444
3445 -- FPM Dev CR 3 Begin
3446
3447 --bug no.3595585 Satish start
3448 /*--bug no.3586648
3449 IF l_structure_sharing_code <> 'SHARE_FULL'
3450 THEN
3451 l_total_effort := nvl(l_ppl_act_effort_to_date,0) + nvl(l_ppl_etc_effort,0) + nvl(l_eqpmt_act_effort_to_date,0) + nvl(l_eqpmt_etc_effort,0);
3452
3453 IF p_structure_type = 'WORKPLAN' AND p_progress_mode = 'FUTURE'
3454 AND l_total_effort > 0
3455 AND l_total_effort > p_planned_effort
3456 AND PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(p_project_id) = 'N'
3457 AND p_action = 'PUBLISH'
3458 AND l_rate_based_flag = 'Y' --maansari7/6 bug 3742356
3459 THEN
3460
3461 l_task_elem_version_id_tbl.extend(1);
3462 l_planned_people_effort_tbl.extend(1);
3463 l_planned_equip_effort_tbl.extend(1);
3464 l_resource_assignment_id_tbl.extend(1);
3465 l_resource_list_member_id_tbl.extend(1);
3466 l_resource_class_code_tbl.extend(1);
3467 -- l_start_date_tbl.extend(1);
3468 -- l_end_date_tbl.extend(1);
3469
3470 l_task_elem_version_id_tbl(1) := p_object_version_id;
3471 l_planned_people_effort_tbl(1) := l_ppl_act_effort_to_date + l_ppl_etc_effort;
3472 l_planned_equip_effort_tbl(1) := l_eqpmt_act_effort_to_date + l_eqpmt_etc_effort;
3473 l_resource_assignment_id_tbl(1) := p_object_id;
3474 l_resource_list_member_id_tbl(1) := p_resource_list_member_id;
3475 l_resource_class_code_tbl(1) := l_resource_class_code; --maansari7/6 bug 3742356
3476 -- l_start_date_tbl(1) := l_actual_start_date;
3477 -- l_end_date_tbl(1) := l_actual_finish_date;
3478
3479 BEGIN
3480 pa_fp_planning_transaction_pub.update_planning_transactions
3481 (
3482 p_context => 'WORKPLAN'
3483 ,p_struct_elem_version_id => p_structure_version_id
3484 ,p_task_elem_version_id_tbl => l_task_elem_version_id_tbl
3485 ,p_planned_people_effort_tbl => l_planned_people_effort_tbl
3486 ,p_planned_equip_effort_tbl => l_planned_equip_effort_tbl
3487 ,p_resource_assignment_id_tbl => l_resource_assignment_id_tbl
3488 ,p_resource_list_member_id_tbl => l_resource_list_member_id_tbl
3489 ,p_resource_class_code_tbl => l_resource_class_code_tbl
3490 -- ,p_start_date_tbl => l_start_date_tbl
3491 -- ,p_end_date_tbl => l_end_date_tbl
3492 ,x_return_status => l_return_status
3493 ,x_msg_count => l_msg_count
3494 ,x_msg_data => l_msg_data
3495 );
3496 EXCEPTION
3497 WHEN OTHERS THEN
3498 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSIGNMENT_PROGRESS_PUB',
3499 p_procedure_name => 'UPDATE_ASSIGNMENT_PROGRESS',
3500 p_error_text => SUBSTRB('pa_fp_planning_transaction_pub.update_planning_transactions:'||SQLERRM,1,240));
3501 RAISE FND_API.G_EXC_ERROR;
3502 END;
3503 END IF;
3504 END IF;*/
3505 --bug no.3595585 Satish end
3506 -- FPM Dev CR 3 End
3507
3508 IF g1_debug_mode = 'Y' THEN
3509 pa_debug.write(x_Module=>'PA_ASSIGNMENT_PROGRESS_PUB.UPDATE_ASSIGNMENT_PROGRESS', x_Msg => 'EXITING ', x_Log_Level=> 3);
3510 END IF;
3511
3512 EXCEPTION
3513 when FND_API.G_EXC_ERROR then
3514 x_return_status := FND_API.G_RET_STS_ERROR;
3515 l_msg_count := Fnd_Msg_Pub.count_msg;
3516
3517 if p_commit = FND_API.G_TRUE then
3518 rollback to UPDATE_ASSIGNMENT_PROGRESS;
3519 end if;
3520
3521 IF l_msg_count = 1 AND x_msg_data IS NULL
3522 THEN
3523 Pa_Interface_Utils_Pub.get_messages
3524 ( p_encoded => Fnd_Api.G_TRUE
3525 , p_msg_index => 1
3526 , p_msg_count => l_msg_count
3527 , p_msg_data => l_msg_data
3528 , p_data => l_data
3529 , p_msg_index_out => l_msg_index_out);
3530 x_msg_data := l_data;
3531 x_msg_count := l_msg_count;
3532 ELSE
3533 x_msg_count := l_msg_count;
3534 END IF;
3535
3536 when FND_API.G_EXC_UNEXPECTED_ERROR then
3537 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3538 x_msg_count := 1;
3539 x_msg_data := SQLERRM;
3540
3541 if p_commit = FND_API.G_TRUE then
3542 rollback to UPDATE_ASSIGNMENT_PROGRESS;
3543 end if;
3544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3545 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSIGNMENT_PROGRESS_PUB',
3546 p_procedure_name => 'UPDATE_ASSIGNMENT_PROGRESS',
3547 p_error_text => SUBSTRB(SQLERRM,1,240));
3548 when OTHERS then
3549 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3550 x_msg_count := 1;
3551 x_msg_data := SUBSTRB(SQLERRM,1,240);
3552 if p_commit = FND_API.G_TRUE then
3553 rollback to UPDATE_DELIVERABLE_PROGRESS;
3554 end if;
3555
3556 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_ASSIGNMENT_PROGRESS_PUB',
3557 p_procedure_name => 'UPDATE_ASSIGNMENT_PROGRESS',
3558 p_error_text => SUBSTRB(SQLERRM,1,240));
3559 raise;
3560
3561
3562 END UPDATE_ASSIGNMENT_PROGRESS;
3563
3564 END PA_ASSIGNMENT_PROGRESS_PUB;