[Home] [Help]
PACKAGE BODY: APPS.PA_FORECAST_HDR_PKG
Source
1 PACKAGE BODY PA_FORECAST_HDR_PKG as
2 --/* $Header: PARFFIHB.pls 120.2 2005/08/19 16:51:21 mwasowic noship $ */
3 l_empty_tab_record EXCEPTION; -- Variable to raise the exception if the passing table of records is empty
4
5
6 -- This procedure will insert the record in pa_forecast_items table
7 -- Input parameters
8 -- Parameters Type Required Description
9 -- P_Forecast_Hdr_Tab FIHDRTABTYP YES It contains the forecast items record for header
10 --
11
12 PROCEDURE insert_rows ( p_forecast_hdr_tab IN PA_FORECAST_GLOB.FIHdrTabTyp,
13 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
14 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
15 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
16 IS
17 l_forecast_item_id PA_PLSQL_DATATYPES.IdTabTyp;
18 l_forecast_item_type PA_PLSQL_DATATYPES.Char30TabTyp;
19 l_project_org_id PA_PLSQL_DATATYPES.IdTabTyp;
20 l_expenditure_org_id PA_PLSQL_DATATYPES.IdTabTyp;
21 l_expenditure_organization_id PA_PLSQL_DATATYPES.IdTabTyp;
22 l_project_organization_id PA_PLSQL_DATATYPES.IdTabTyp;
23 l_project_id PA_PLSQL_DATATYPES.IdTabTyp;
24 l_project_type_class PA_PLSQL_DATATYPES.Char30TabTyp;
25 l_person_id PA_PLSQL_DATATYPES.IdTabTyp;
26 l_resource_id PA_PLSQL_DATATYPES.IdTabTyp;
27 l_borrowed_flag PA_PLSQL_DATATYPES.Char1TabTyp;
28 l_assignment_id PA_PLSQL_DATATYPES.IdTabTyp;
29 l_item_date PA_PLSQL_DATATYPES.DateTabTyp;
30 l_item_uom PA_PLSQL_DATATYPES.Char30TabTyp;
31 l_item_quantity PA_PLSQL_DATATYPES.NumTabTyp;
32 l_pvdr_period_set_name PA_PLSQL_DATATYPES.Char30TabTyp;
33 l_pvdr_pa_period_name PA_PLSQL_DATATYPES.Char30TabTyp;
34 l_pvdr_gl_period_name PA_PLSQL_DATATYPES.Char30TabTyp;
35 l_rcvr_period_set_name PA_PLSQL_DATATYPES.Char30TabTyp;
36 l_rcvr_pa_period_name PA_PLSQL_DATATYPES.Char30TabTyp;
37 l_rcvr_gl_period_name PA_PLSQL_DATATYPES.Char30TabTyp;
38 l_global_exp_period_end_date PA_PLSQL_DATATYPES.DateTabTyp;
39 l_expenditure_type PA_PLSQL_DATATYPES.Char30TabTyp;
40 l_expenditure_type_class PA_PLSQL_DATATYPES.Char30TabTyp;
41 l_cost_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
42 l_rev_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
43 l_tp_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
44 l_burden_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
45 l_other_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
46 l_delete_flag PA_PLSQL_DATATYPES.Char1TabTyp;
47 l_provisional_flag PA_PLSQL_DATATYPES.Char1TabTyp;
48 l_error_flag PA_PLSQL_DATATYPES.Char1TabTyp;
49 l_JOB_ID PA_PLSQL_DATATYPES.NumTabTyp;
50 l_TP_AMOUNT_TYPE PA_PLSQL_DATATYPES.Char30TabTyp;
51 l_OVERPROVISIONAL_QTY PA_PLSQL_DATATYPES.NumTabTyp;
52 l_OVER_PROV_CONF_QTY PA_PLSQL_DATATYPES.NumTabTyp;
53 l_CONFIRMED_QTY PA_PLSQL_DATATYPES.NumTabTyp;
54 l_PROVISIONAL_QTY PA_PLSQL_DATATYPES.NumTabTyp;
55 l_asgmt_sys_status_code PA_PLSQL_DATATYPES.Char30TabTyp;
56 l_capacity_quantity PA_PLSQL_DATATYPES.NumTabTyp;
57 l_overcommitment_quantity PA_PLSQL_DATATYPES.NumTabTyp;
58 l_availability_quantity PA_PLSQL_DATATYPES.NumTabTyp;
59 l_overcommitment_flag PA_PLSQL_DATATYPES.Char1TabTyp;
60 l_availability_flag PA_PLSQL_DATATYPES.Char1TabTyp;
61
62 l_fi_rejected EXCEPTION;
63 lv_rejection_code VARCHAR2(30);
64 l_msg_index_out NUMBER;
65 -- added for Bug Fix 4537865
66 l_new_msg_data VARCHAR2(2000);
67 -- added for Bug Fix 4537865
68 /* Bug 2390990 Begin */
69 l_tmp_forecast_item_type pa_forecast_items.forecast_item_type%TYPE;
70 l_start_date_found BOOLEAN;
71 l_end_date_found BOOLEAN;
72 l_start_date pa_forecast_items.item_date%TYPE;
73 l_end_date pa_forecast_items.item_date%TYPE;
74 l_token VARCHAR2(1000);
75 l_fi_rejected_prd_missing EXCEPTION;
76 /* Bug 2390990 End */
77
78 BEGIN
79
80 PA_DEBUG.Init_err_stack( 'PA_FORECAST_HDR_PKG.Insert_Rows');
81
82 x_return_status := FND_API.G_RET_STS_SUCCESS;
83
84 /* Checking for the empty table of record */
85 IF (nvl(p_forecast_hdr_tab.count,0) = 0 ) THEN
86 PA_FORECASTITEM_PVT.print_message('count 0 ... before return ... ');
87 RAISE l_empty_tab_record;
88 END IF;
89
90 PA_FORECASTITEM_PVT.print_message('start of the forecast inser row .... ');
91
92 FOR l_j IN p_forecast_hdr_tab.FIRST .. p_forecast_hdr_tab.LAST LOOP
93 if (p_forecast_hdr_tab(l_j).error_flag = 'Y') then
94 PA_FORECASTITEM_PVT.print_message('Errors');
95 if (p_forecast_hdr_tab(l_j).cost_rejection_code is not null) then
96 lv_rejection_code := p_forecast_hdr_tab(l_j).cost_rejection_code;
97 raise l_fi_rejected;
98 end if;
99 if (p_forecast_hdr_tab(l_j).rev_rejection_code is not null) then
100 lv_rejection_code := p_forecast_hdr_tab(l_j).rev_rejection_code;
101 raise l_fi_rejected;
102 end if;
103 if (p_forecast_hdr_tab(l_j).tp_rejection_code is not null) then
104 lv_rejection_code := p_forecast_hdr_tab(l_j).tp_rejection_code;
105 raise l_fi_rejected;
106 end if;
107 if (p_forecast_hdr_tab(l_j).burden_rejection_code is not null) then
108 lv_rejection_code := p_forecast_hdr_tab(l_j).burden_rejection_code;
109 raise l_fi_rejected;
110 end if;
111 if (p_forecast_hdr_tab(l_j).other_rejection_code is not null) then
112 lv_rejection_code := p_forecast_hdr_tab(l_j).other_rejection_code;
113
114 /* Bug239090 Begin */
115 l_tmp_forecast_item_type := p_forecast_hdr_tab(l_j).forecast_item_type;
116 l_start_date_found := FALSE;
117 IF (lv_rejection_code = 'PVDR_GL_PRD_NAME_NOT_FOUND' OR lv_rejection_code='PVDR_PA_PRD_NAME_NOT_FOUND'
118 OR lv_rejection_code='RCVR_GL_PRD_NAME_NOT_FOUND' OR lv_rejection_code='RCVR_PA_PRD_NAME_NOT_FOUND') THEN
119
120 FOR l_k IN l_j .. p_forecast_hdr_tab.LAST LOOP
121
122 IF (p_forecast_hdr_tab(l_k).other_rejection_code = lv_rejection_code ) THEN
123 IF (l_start_date_found =FALSE) THEN
124 l_start_date_found := TRUE;
125 l_start_date := p_forecast_hdr_tab(l_k).item_date;
126 l_end_date_found := FALSE;
127 END IF;
128 IF l_k=p_forecast_hdr_tab.LAST THEN
129 l_end_date := p_forecast_hdr_tab(l_k).item_date;
130 IF l_end_date <> l_start_date THEN
131 IF l_token is null THEN
132 l_token := l_start_date||' - '||l_end_date;
133 ELSE
134 l_token := l_token||', '||l_start_date||' - '||l_end_date;
135 END IF;
136 ELSE
137 IF l_token is null THEN
138 l_token := l_start_date;
139 ELSE
140 l_token := l_token||', '||l_start_date;
141 END IF;
142 END IF;
143 END IF;
144 ELSE
145 IF (l_end_date_found = FALSE) THEN
146 l_start_date_found:=FALSE;
147 l_end_date_found := TRUE;
148 l_end_date := p_forecast_hdr_tab(l_k-1).item_date;
149 IF l_end_date <> l_start_date THEN
150 IF l_token is null THEN
151 l_token := l_start_date||' - '||l_end_date;
152 ELSE
153 l_token := l_token||', '||l_start_date||' - '||l_end_date;
154 END IF;
155 ELSE
156 IF l_token is null THEN
157 l_token := l_start_date;
158 ELSE
159 l_token := l_token||', '||l_start_date;
160 END IF;
161 END IF;
162 END IF;
163 END IF;
164 END LOOP;
165
166 IF lv_rejection_code = 'PVDR_PA_PRD_NAME_NOT_FOUND' THEN
167 IF l_tmp_forecast_item_type = 'A' THEN
168 lv_rejection_code := 'PVDR_PA_PRD_A_NOT_FOUND_DTS';
169 ELSIF l_tmp_forecast_item_type = 'R' THEN
170 lv_rejection_code := 'PVDR_PA_PRD_R_NOT_FOUND_DTS';
171 ELSIF l_tmp_forecast_item_type = 'U' THEN
172 lv_rejection_code := 'PVDR_PA_PRD_U_NOT_FOUND_DTS';
173 END IF;
174 ELSIF lv_rejection_code = 'PVDR_GL_PRD_NAME_NOT_FOUND' THEN
175 IF l_tmp_forecast_item_type = 'A' THEN
176 lv_rejection_code := 'PVDR_GL_PRD_A_NOT_FOUND_DTS';
177 ELSIF l_tmp_forecast_item_type = 'R' THEN
178 lv_rejection_code := 'PVDR_GL_PRD_R_NOT_FOUND_DTS';
179 ELSIF l_tmp_forecast_item_type = 'U' THEN
180 lv_rejection_code := 'PVDR_GL_PRD_U_NOT_FOUND_DTS';
181 END IF;
182 ELSIF lv_rejection_code = 'RCVR_PA_PRD_NAME_NOT_FOUND' THEN
183 IF l_tmp_forecast_item_type = 'A' THEN
184 lv_rejection_code := 'RCVR_PA_PRD_A_NOT_FOUND_DTS';
185 ELSIF l_tmp_forecast_item_type = 'R' THEN
186 lv_rejection_code := 'RCVR_PA_PRD_R_NOT_FOUND_DTS';
187 ELSIF l_tmp_forecast_item_type = 'U' THEN
188 lv_rejection_code := 'RCVR_PA_PRD_U_NOT_FOUND_DTS';
189 END IF;
190 ELSIF lv_rejection_code = 'RCVR_GL_PRD_NAME_NOT_FOUND' THEN
191 IF l_tmp_forecast_item_type = 'A' THEN
192 lv_rejection_code := 'RCVR_GL_PRD_A_NOT_FOUND_DTS';
193 ELSIF l_tmp_forecast_item_type = 'R' THEN
194 lv_rejection_code := 'RCVR_GL_PRD_R_NOT_FOUND_DTS';
195 ELSIF l_tmp_forecast_item_type = 'U' THEN
196 lv_rejection_code := 'RCVR_GL_PRD_U_NOT_FOUND_DTS';
197 END IF;
198 END IF;
199
200 raise l_fi_rejected_prd_missing;
201 END IF;
202 /* Bug2390990 End */
203 raise l_fi_rejected;
204 end if;
205 end if;
206
207 l_forecast_item_id(l_J) := p_forecast_hdr_tab(l_j).forecast_item_id;
208 l_forecast_item_type(l_j) := p_forecast_hdr_tab(l_j).forecast_item_type;
209 l_project_org_id(l_j) := p_forecast_hdr_tab(l_j).project_org_id;
210 l_expenditure_org_id(l_j) := p_forecast_hdr_tab(l_j).expenditure_org_id;
211 l_expenditure_organization_id(l_j) := p_forecast_hdr_tab(l_j).expenditure_organization_id;
212 l_project_organization_id(l_j) := p_forecast_hdr_tab(l_j).project_organization_id;
213 l_project_id(l_j) := p_forecast_hdr_tab(l_j).project_id;
214 l_project_type_class(l_j) := p_forecast_hdr_tab(l_j).project_type_class;
215 l_person_id(l_j) := p_forecast_hdr_tab(l_j).person_id;
216 l_resource_id(l_j) := p_forecast_hdr_tab(l_j).resource_id;
217 l_borrowed_flag(l_j) := p_forecast_hdr_tab(l_j).borrowed_flag;
218 l_assignment_id(l_j) := p_forecast_hdr_tab(l_j).assignment_id;
219 l_item_date(l_j) := trunc(p_forecast_hdr_tab(l_j).item_date);
220 l_item_uom(l_j) := p_forecast_hdr_tab(l_j).item_uom;
221 l_item_quantity(l_j) := p_forecast_hdr_tab(l_j).item_quantity;
222 l_pvdr_period_set_name(l_j) := p_forecast_hdr_tab(l_j).pvdr_period_set_name;
223 l_pvdr_pa_period_name(l_j) := p_forecast_hdr_tab(l_j).pvdr_pa_period_name;
224 l_pvdr_gl_period_name(l_j) := p_forecast_hdr_tab(l_j).pvdr_gl_period_name;
225 l_rcvr_period_set_name(l_j) := p_forecast_hdr_tab(l_j).rcvr_period_set_name;
226 l_rcvr_pa_period_name(l_j) := p_forecast_hdr_tab(l_j).rcvr_pa_period_name;
227 l_rcvr_gl_period_name(l_j) := p_forecast_hdr_tab(l_j).rcvr_gl_period_name;
228 l_global_exp_period_end_date(l_j) := trunc(p_forecast_hdr_tab(l_j).global_exp_period_end_date);
229 l_expenditure_type(l_j) := p_forecast_hdr_tab(l_j).expenditure_type;
230 l_expenditure_type_class(l_j) := p_forecast_hdr_tab(l_j).expenditure_type_class;
231 l_cost_rejection_code(l_j) := p_forecast_hdr_tab(l_j).cost_rejection_code;
232 l_rev_rejection_code(l_j) := p_forecast_hdr_tab(l_j).rev_rejection_code;
233 l_tp_rejection_code(l_j) := p_forecast_hdr_tab(l_j).tp_rejection_code;
234 l_burden_rejection_code(l_j) := p_forecast_hdr_tab(l_j).burden_rejection_code;
235 l_other_rejection_code(l_j) := p_forecast_hdr_tab(l_j).other_rejection_code;
236 l_delete_flag(l_j) := p_forecast_hdr_tab(l_j).delete_flag;
237 l_provisional_flag(l_j) := p_forecast_hdr_tab(l_j).provisional_flag;
238 l_error_flag(l_j) := p_forecast_hdr_tab(l_j).error_flag;
239 l_JOB_ID(l_j) := p_forecast_hdr_tab(l_j).JOB_ID;
240 l_TP_AMOUNT_TYPE(l_j) := p_forecast_hdr_tab(l_j).TP_AMOUNT_TYPE;
241 l_OVERPROVISIONAL_QTY(l_j) := p_forecast_hdr_tab(l_j).OVERPROVISIONAL_QTY;
242 l_OVER_PROV_CONF_QTY(l_j) := p_forecast_hdr_tab(l_j).OVER_PROV_CONF_QTY;
243 l_CONFIRMED_QTY(l_j) := p_forecast_hdr_tab(l_j).CONFIRMED_QTY;
244 l_PROVISIONAL_QTY(l_j) := p_forecast_hdr_tab(l_j).PROVISIONAL_QTY;
245 l_asgmt_sys_status_code(l_j) := p_forecast_hdr_tab(l_j).asgmt_sys_status_code;
246 l_capacity_quantity(l_j) := p_forecast_hdr_tab(l_j).capacity_quantity;
247 l_overcommitment_quantity(l_j) := p_forecast_hdr_tab(l_j).overcommitment_quantity;
248 l_availability_quantity(l_j) := p_forecast_hdr_tab(l_j).availability_quantity;
249 l_overcommitment_flag(l_j) := p_forecast_hdr_tab(l_j).overcommitment_flag;
250 l_availability_flag(l_j) := p_forecast_hdr_tab(l_j).availability_flag;
251 END LOOP;
252
253
254 PA_FORECASTITEM_PVT.print_message('act ins ');
255 FORALL l_j IN p_forecast_hdr_tab.FIRST..p_forecast_hdr_tab.LAST
256 INSERT INTO PA_FORECAST_ITEMS
257 (
258 forecast_item_id ,
259 forecast_item_type ,
260 project_org_id ,
261 expenditure_org_id ,
262 expenditure_organization_id ,
263 project_organization_id ,
264 project_id ,
265 project_type_class ,
266 person_id ,
267 resource_id ,
268 borrowed_flag ,
269 assignment_id ,
270 item_date ,
271 item_uom ,
272 item_quantity ,
273 pvdr_period_set_name ,
274 pvdr_pa_period_name ,
275 pvdr_gl_period_name ,
276 rcvr_period_set_name ,
277 rcvr_pa_period_name ,
278 rcvr_gl_period_name ,
279 global_exp_period_end_date ,
280 expenditure_type ,
281 expenditure_type_class ,
282 cost_rejection_code ,
283 rev_rejection_code ,
284 tp_rejection_code ,
285 burden_rejection_code ,
286 other_rejection_code ,
287 delete_flag ,
288 provisional_flag ,
289 error_flag ,
290 JOB_ID ,
291 TP_AMOUNT_TYPE ,
292 OVERPROVISIONAL_QTY ,
293 OVER_PROV_CONF_QTY ,
294 CONFIRMED_QTY ,
295 PROVISIONAL_QTY ,
296 asgmt_sys_status_code ,
297 capacity_quantity ,
298 overcommitment_quantity ,
299 availability_quantity ,
300 overcommitment_flag ,
301 availability_flag ,
302 creation_date ,
303 created_by ,
304 last_update_date ,
305 last_updated_by ,
306 last_update_login ,
307 request_id ,
308 program_application_id ,
309 program_id ,
310 program_update_date,
311 FORECAST_AMT_CALC_FLAG)
312 VALUES (
313 l_forecast_item_id(l_J) ,
314 l_forecast_item_type(l_j) ,
315 l_project_org_id(l_j) ,
316 l_expenditure_org_id(l_j) ,
317 l_expenditure_organization_id(l_j) ,
318 l_project_organization_id(l_j) ,
319 l_project_id(l_j) ,
320 l_project_type_class(l_j) ,
321 l_person_id(l_j) ,
322 l_resource_id(l_j) ,
323 l_borrowed_flag(l_j) ,
324 l_assignment_id(l_j) ,
325 l_item_date(l_j) ,
326 l_item_uom(l_j) ,
327 l_item_quantity(l_j) ,
328 l_pvdr_period_set_name(l_j) ,
329 l_pvdr_pa_period_name(l_j) ,
330 l_pvdr_gl_period_name(l_j) ,
331 l_rcvr_period_set_name(l_j) ,
332 l_rcvr_pa_period_name(l_j) ,
333 l_rcvr_gl_period_name(l_j) ,
334 l_global_exp_period_end_date(l_j) ,
335 l_expenditure_type(l_j) ,
336 l_expenditure_type_class(l_j) ,
337 l_cost_rejection_code(l_j) ,
338 l_rev_rejection_code(l_j) ,
339 l_tp_rejection_code(l_j) ,
340 l_burden_rejection_code(l_j) ,
341 l_other_rejection_code(l_j) ,
342 l_delete_flag(l_j) ,
343 l_provisional_flag(l_j) ,
344 l_error_flag(l_j) ,
345 l_JOB_ID(l_j) ,
346 l_TP_AMOUNT_TYPE(l_j) ,
347 l_OVERPROVISIONAL_QTY(l_j) ,
348 l_OVER_PROV_CONF_QTY(l_j) ,
349 l_CONFIRMED_QTY(l_j) ,
350 l_PROVISIONAL_QTY(l_j) ,
351 l_asgmt_sys_status_code(l_j) ,
352 nvl(l_capacity_quantity(l_j),0) ,
353 l_overcommitment_quantity(l_j) ,
354 l_availability_quantity(l_j) ,
355 l_overcommitment_flag(l_j) ,
356 l_availability_flag(l_j) ,
357 sysdate ,
358 fnd_global.user_id ,
359 sysdate ,
360 fnd_global.user_id ,
361 fnd_global.login_id ,
362 fnd_global.conc_request_id() ,
363 fnd_global.prog_appl_id () ,
364 fnd_global.conc_program_id() ,
365 trunc(sysdate) ,
366 'N');
367
368 -- PA_FORECAST_ITEMS_UTLS.log_message('end of the forecast inser row .... ');
369 PA_DEBUG.Reset_Err_Stack;
370 EXCEPTION
371 WHEN l_empty_tab_record THEN
372 NULL;
373 /* Bug 2390990 Begin */
374 WHEN l_fi_rejected_prd_missing THEN
375 PA_UTILS.add_message(p_app_short_name => 'PA',
376 p_msg_name => lv_rejection_code,
377 p_token1 => 'DATE_LIST',
378 p_value1 => l_token);
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 x_msg_data := lv_rejection_code;
381 x_msg_count := FND_MSG_PUB.Count_Msg;
382 If x_msg_count = 1 THEN
383 pa_interface_utils_pub.get_messages
384 (p_encoded => FND_API.G_TRUE,
385 p_msg_index => 1,
386 p_msg_count => x_msg_count,
387 p_msg_data => x_msg_data,
388 --p_data => x_msg_data, * commenetd for Bug: 4537865
389 p_data => l_new_msg_data, --added for Bug 4537865
390 p_msg_index_out => l_msg_index_out );
391 -- added for Bug Fix: 4537865
392 x_msg_data := l_new_msg_data;
393 -- added for Bug Fix: 4537865
394 End If;
395 /* Bug 2390990 End */
396 WHEN l_fi_rejected then
397 PA_UTILS.add_message('PA',lv_rejection_code);
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 x_msg_data := lv_rejection_code;
400 x_msg_count := FND_MSG_PUB.Count_Msg;
401 If x_msg_count = 1 THEN
402 pa_interface_utils_pub.get_messages
403 (p_encoded => FND_API.G_TRUE,
404 p_msg_index => 1,
405 p_msg_count => x_msg_count,
406 p_msg_data => x_msg_data,
407 -- p_data => x_msg_data, * added for Bug 4537865
408 p_data => l_new_msg_data, -- added for bug 4537865
409 p_msg_index_out => l_msg_index_out );
410 -- added for bug 4537865
411 x_msg_data := l_new_msg_data;
412 -- added for bug 4537865
413 End If;
414 WHEN OTHERS THEN
415 x_msg_count := 1;
416 x_msg_data := SQLERRM;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 FND_MSG_PUB.add_exc_msg
419 (p_pkg_name => 'PA_FORECAST_HDR_PKG.Insert_Rows',
420 p_procedure_name => PA_DEBUG.G_Err_Stack);
421
422 RAISE;
423
424 -- PA_FORECAST_ITEMS_UTLS.log_message('ERROR ....'||sqlerrm);
425 END insert_rows;
426
427 -- This procedure will update the record in pa_forecast_items table
428 -- Input parameters
429 -- Parameters Type Required Description
430 -- P_Forecast_Hdr_Tab FIHDRTABTYP YES It contains the forecast items record for header
431 --
432 PROCEDURE update_rows ( p_forecast_hdr_tab IN PA_FORECAST_GLOB.FIHdrTabTyp,
433 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
434 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
435 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
436
437 IS
438 l_forecast_item_id PA_PLSQL_DATATYPES.IdTabTyp;
439 l_forecast_item_type PA_PLSQL_DATATYPES.Char30TabTyp;
440 l_project_org_id PA_PLSQL_DATATYPES.IdTabTyp;
441 l_expenditure_org_id PA_PLSQL_DATATYPES.IdTabTyp;
442 l_expenditure_organization_id PA_PLSQL_DATATYPES.IdTabTyp;
443 l_project_organization_id PA_PLSQL_DATATYPES.IdTabTyp;
444 l_project_id PA_PLSQL_DATATYPES.IdTabTyp;
445 l_project_type_class PA_PLSQL_DATATYPES.Char30TabTyp;
446 l_person_id PA_PLSQL_DATATYPES.IdTabTyp;
447 l_resource_id PA_PLSQL_DATATYPES.IdTabTyp;
448 l_borrowed_flag PA_PLSQL_DATATYPES.Char1TabTyp;
449 l_assignment_id PA_PLSQL_DATATYPES.IdTabTyp;
450 l_item_date PA_PLSQL_DATATYPES.DateTabTyp;
451 l_item_uom PA_PLSQL_DATATYPES.Char30TabTyp;
452 l_item_quantity PA_PLSQL_DATATYPES.NumTabTyp;
453 l_pvdr_period_set_name PA_PLSQL_DATATYPES.Char30TabTyp;
454 l_pvdr_pa_period_name PA_PLSQL_DATATYPES.Char30TabTyp;
455 l_pvdr_gl_period_name PA_PLSQL_DATATYPES.Char30TabTyp;
456 l_rcvr_period_set_name PA_PLSQL_DATATYPES.Char30TabTyp;
457 l_rcvr_pa_period_name PA_PLSQL_DATATYPES.Char30TabTyp;
458 l_rcvr_gl_period_name PA_PLSQL_DATATYPES.Char30TabTyp;
459 l_global_exp_period_end_date PA_PLSQL_DATATYPES.DateTabTyp;
460 l_expenditure_type PA_PLSQL_DATATYPES.Char30TabTyp;
461 l_expenditure_type_class PA_PLSQL_DATATYPES.Char30TabTyp;
462 l_cost_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
463 l_rev_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
464 l_tp_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
465 l_burden_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
466 l_other_rejection_code PA_PLSQL_DATATYPES.Char30TabTyp;
467 l_delete_flag PA_PLSQL_DATATYPES.Char1TabTyp;
468 l_provisional_flag PA_PLSQL_DATATYPES.Char1TabTyp;
469 l_error_flag PA_PLSQL_DATATYPES.Char1TabTyp;
470 l_JOB_ID PA_PLSQL_DATATYPES.NumTabTyp;
471 l_TP_AMOUNT_TYPE PA_PLSQL_DATATYPES.Char30TabTyp;
472 l_OVERPROVISIONAL_QTY PA_PLSQL_DATATYPES.NumTabTyp;
473 l_OVER_PROV_CONF_QTY PA_PLSQL_DATATYPES.NumTabTyp;
474 l_CONFIRMED_QTY PA_PLSQL_DATATYPES.NumTabTyp;
475 l_PROVISIONAL_QTY PA_PLSQL_DATATYPES.NumTabTyp;
476 l_asgmt_sys_status_code PA_PLSQL_DATATYPES.Char30TabTyp;
477 l_capacity_quantity PA_PLSQL_DATATYPES.NumTabTyp;
478 l_overcommitment_quantity PA_PLSQL_DATATYPES.NumTabTyp;
479 l_availability_quantity PA_PLSQL_DATATYPES.NumTabTyp;
480 l_overcommitment_flag PA_PLSQL_DATATYPES.Char1TabTyp;
481 l_availability_flag PA_PLSQL_DATATYPES.Char1TabTyp;
482
483 l_fi_rejected EXCEPTION;
484 lv_rejection_code VARCHAR2(30);
485 l_msg_index_out NUMBER;
486 -- added for bug 4537865
487 l_new_msg_data VARCHAR2(2000);
488 -- added for bug 4537865
489 /* Bug 2390990 Begin */
490 l_tmp_forecast_item_type pa_forecast_items.forecast_item_type%TYPE;
491 l_start_date_found BOOLEAN;
492 l_end_date_found BOOLEAN;
493 l_start_date pa_forecast_items.item_date%TYPE;
494 l_end_date pa_forecast_items.item_date%TYPE;
495 l_token VARCHAR2(1000);
496 l_fi_rejected_prd_missing EXCEPTION;
497 /* Bug 2390990 End */
498
499
500 BEGIN
501 PA_DEBUG.Init_err_stack( 'PA_FORECAST_HDR_PKG.Update_Rows');
502 x_return_status := FND_API.G_RET_STS_SUCCESS;
503
504 /* Checking for the empty table of record */
505 IF (nvl(p_forecast_hdr_tab.count,0) = 0 ) THEN
506 PA_FORECASTITEM_PVT.print_message('count 0 ... before return ... ');
507 RAISE l_empty_tab_record;
508 END IF;
509
510 PA_FORECASTITEM_PVT.print_message('start of the forecast update row .... ');
511
512 FOR l_J IN p_forecast_hdr_tab.FIRST..p_forecast_hdr_tab.LAST LOOP
513 if (p_forecast_hdr_tab(l_j).error_flag = 'Y' and nvl(p_forecast_hdr_tab(l_j).delete_flag,'N') <> 'Y') then -- added second condition for bug 4254376
514 PA_FORECASTITEM_PVT.print_message('Errors');
515 if (p_forecast_hdr_tab(l_j).cost_rejection_code is not null) then
516 lv_rejection_code := p_forecast_hdr_tab(l_j).cost_rejection_code;
517 raise l_fi_rejected;
518 end if;
519 if (p_forecast_hdr_tab(l_j).rev_rejection_code is not null) then
520 lv_rejection_code := p_forecast_hdr_tab(l_j).rev_rejection_code;
521 raise l_fi_rejected;
522 end if;
523 if (p_forecast_hdr_tab(l_j).tp_rejection_code is not null) then
524 lv_rejection_code := p_forecast_hdr_tab(l_j).tp_rejection_code;
525 raise l_fi_rejected;
526 end if;
527 if (p_forecast_hdr_tab(l_j).burden_rejection_code is not null) then
528 lv_rejection_code := p_forecast_hdr_tab(l_j).burden_rejection_code;
529 raise l_fi_rejected;
530 end if;
531 if (p_forecast_hdr_tab(l_j).other_rejection_code is not null) then
532 lv_rejection_code := p_forecast_hdr_tab(l_j).other_rejection_code;
533 /* Bug239090 Begin */
534 l_tmp_forecast_item_type := p_forecast_hdr_tab(l_j).forecast_item_type;
535 l_start_date_found := FALSE;
536 IF (lv_rejection_code = 'PVDR_GL_PRD_NAME_NOT_FOUND' OR lv_rejection_code='PVDR_PA_PRD_NAME_NOT_FOUND'
537 OR lv_rejection_code='RCVR_GL_PRD_NAME_NOT_FOUND' OR lv_rejection_code='RCVR_PA_PRD_NAME_NOT_FOUND') THEN
538
539 FOR l_k IN l_j .. p_forecast_hdr_tab.LAST LOOP
540
541 IF (p_forecast_hdr_tab(l_k).other_rejection_code = lv_rejection_code ) THEN
542 IF (l_start_date_found =FALSE) THEN
543 l_start_date_found := TRUE;
544 l_start_date := p_forecast_hdr_tab(l_k).item_date;
545 l_end_date_found := FALSE;
546 END IF;
547 IF l_k=p_forecast_hdr_tab.LAST THEN
548 l_end_date := p_forecast_hdr_tab(l_k).item_date;
549 IF l_end_date <> l_start_date THEN
550 IF l_token is null THEN
551 l_token := l_start_date||' - '||l_end_date;
552 ELSE
553 l_token := l_token||', '||l_start_date||' - '||l_end_date;
554 END IF;
555 ELSE
556 IF l_token is null THEN
557 l_token := l_start_date;
558 ELSE
559 l_token := l_token||', '||l_start_date;
560 END IF;
561 END IF;
562 END IF;
563 ELSE
564 IF (l_end_date_found = FALSE) THEN
565 l_start_date_found:=FALSE;
566 l_end_date_found := TRUE;
567 l_end_date := p_forecast_hdr_tab(l_k-1).item_date;
568 IF l_end_date <> l_start_date THEN
569 IF l_token is null THEN
570 l_token := l_start_date||' - '||l_end_date;
571 ELSE
572 l_token := l_token||', '||l_start_date||' - '||l_end_date;
573 END IF;
574 ELSE
575 IF l_token is null THEN
576 l_token := l_start_date;
577 ELSE
578 l_token := l_token||', '||l_start_date;
579 END IF;
580 END IF;
581 END IF;
582 END IF;
583 END LOOP;
584
585 IF lv_rejection_code = 'PVDR_PA_PRD_NAME_NOT_FOUND' THEN
586 IF l_tmp_forecast_item_type = 'A' THEN
587 lv_rejection_code := 'PVDR_PA_PRD_A_NOT_FOUND_DTS';
588 ELSIF l_tmp_forecast_item_type = 'R' THEN
589 lv_rejection_code := 'PVDR_PA_PRD_R_NOT_FOUND_DTS';
590 ELSIF l_tmp_forecast_item_type = 'U' THEN
591 lv_rejection_code := 'PVDR_PA_PRD_U_NOT_FOUND_DTS';
592 END IF;
593 ELSIF lv_rejection_code = 'PVDR_GL_PRD_NAME_NOT_FOUND' THEN
594 IF l_tmp_forecast_item_type = 'A' THEN
595 lv_rejection_code := 'PVDR_GL_PRD_A_NOT_FOUND_DTS';
596 ELSIF l_tmp_forecast_item_type = 'R' THEN
597 lv_rejection_code := 'PVDR_GL_PRD_R_NOT_FOUND_DTS';
598 ELSIF l_tmp_forecast_item_type = 'U' THEN
599 lv_rejection_code := 'PVDR_GL_PRD_U_NOT_FOUND_DTS';
600 END IF;
601 ELSIF lv_rejection_code = 'RCVR_PA_PRD_NAME_NOT_FOUND' THEN
602 IF l_tmp_forecast_item_type = 'A' THEN
603 lv_rejection_code := 'RCVR_PA_PRD_A_NOT_FOUND_DTS';
604 ELSIF l_tmp_forecast_item_type = 'R' THEN
605 lv_rejection_code := 'RCVR_PA_PRD_R_NOT_FOUND_DTS';
606 ELSIF l_tmp_forecast_item_type = 'U' THEN
607 lv_rejection_code := 'RCVR_PA_PRD_U_NOT_FOUND_DTS';
608 END IF;
609 ELSIF lv_rejection_code = 'RCVR_GL_PRD_NAME_NOT_FOUND' THEN
610 IF l_tmp_forecast_item_type = 'A' THEN
611 lv_rejection_code := 'RCVR_GL_PRD_A_NOT_FOUND_DTS';
612 ELSIF l_tmp_forecast_item_type = 'R' THEN
613 lv_rejection_code := 'RCVR_GL_PRD_R_NOT_FOUND_DTS';
614 ELSIF l_tmp_forecast_item_type = 'U' THEN
615 lv_rejection_code := 'RCVR_GL_PRD_U_NOT_FOUND_DTS';
616 END IF;
617 END IF;
618
619 raise l_fi_rejected_prd_missing;
620 END IF;
621 /* Bug2390990 End */
622
623 raise l_fi_rejected;
624 end if;
625 end if;
626
627 l_forecast_item_id(l_J) := p_forecast_hdr_tab(l_j).forecast_item_id;
628 l_forecast_item_type(l_j) := p_forecast_hdr_tab(l_j).forecast_item_type;
629 l_project_org_id(l_j) := p_forecast_hdr_tab(l_j).project_org_id;
630 l_expenditure_org_id(l_j) := p_forecast_hdr_tab(l_j).expenditure_org_id;
631 l_expenditure_organization_id(l_j) := p_forecast_hdr_tab(l_j).expenditure_organization_id;
632 l_project_organization_id(l_j) := p_forecast_hdr_tab(l_j).project_organization_id;
633 l_project_id(l_j) := p_forecast_hdr_tab(l_j).project_id;
634 l_project_type_class(l_j) := p_forecast_hdr_tab(l_j).project_type_class;
635 l_person_id(l_j) := p_forecast_hdr_tab(l_j).person_id;
636 l_resource_id(l_j) := p_forecast_hdr_tab(l_j).resource_id;
637 l_borrowed_flag(l_j) := p_forecast_hdr_tab(l_j).borrowed_flag;
638 l_assignment_id(l_j) := p_forecast_hdr_tab(l_j).assignment_id;
639 l_item_date(l_j) := trunc(p_forecast_hdr_tab(l_j).item_date);
640 l_item_uom(l_j) := p_forecast_hdr_tab(l_j).item_uom;
641 l_item_quantity(l_j) := p_forecast_hdr_tab(l_j).item_quantity;
642 l_pvdr_period_set_name(l_j) := p_forecast_hdr_tab(l_j).pvdr_period_set_name;
643 l_pvdr_pa_period_name(l_j) := p_forecast_hdr_tab(l_j).pvdr_pa_period_name;
644 l_pvdr_gl_period_name(l_j) := p_forecast_hdr_tab(l_j).pvdr_gl_period_name;
645 l_rcvr_period_set_name(l_j) := p_forecast_hdr_tab(l_j).rcvr_period_set_name;
646 l_rcvr_pa_period_name(l_j) := p_forecast_hdr_tab(l_j).rcvr_pa_period_name;
647 l_rcvr_gl_period_name(l_j) := p_forecast_hdr_tab(l_j).rcvr_gl_period_name;
648 l_global_exp_period_end_date(l_j) := trunc(p_forecast_hdr_tab(l_j).global_exp_period_end_date);
649 l_expenditure_type(l_j) := p_forecast_hdr_tab(l_j).expenditure_type;
650 l_expenditure_type_class(l_j) := p_forecast_hdr_tab(l_j).expenditure_type_class;
651 l_cost_rejection_code(l_j) := p_forecast_hdr_tab(l_j).cost_rejection_code;
652 l_rev_rejection_code(l_j) := p_forecast_hdr_tab(l_j).rev_rejection_code;
653 l_tp_rejection_code(l_j) := p_forecast_hdr_tab(l_j).tp_rejection_code;
654 l_burden_rejection_code(l_j) := p_forecast_hdr_tab(l_j).burden_rejection_code;
655 l_other_rejection_code(l_j) := p_forecast_hdr_tab(l_j).other_rejection_code;
656 l_delete_flag(l_j) := p_forecast_hdr_tab(l_j).delete_flag;
657 l_provisional_flag(l_j) := p_forecast_hdr_tab(l_j).provisional_flag;
658 l_error_flag(l_j) := p_forecast_hdr_tab(l_j).error_flag;
659 l_JOB_ID(l_j) := p_forecast_hdr_tab(l_j).JOB_ID;
660 l_TP_AMOUNT_TYPE(l_j) := p_forecast_hdr_tab(l_j).TP_AMOUNT_TYPE;
661 l_OVERPROVISIONAL_QTY(l_j) := p_forecast_hdr_tab(l_j).OVERPROVISIONAL_QTY;
662 l_OVER_PROV_CONF_QTY(l_j) := p_forecast_hdr_tab(l_j).OVER_PROV_CONF_QTY;
663 l_CONFIRMED_QTY(l_j) := p_forecast_hdr_tab(l_j).CONFIRMED_QTY;
664 l_PROVISIONAL_QTY(l_j) := p_forecast_hdr_tab(l_j).PROVISIONAL_QTY;
665 l_asgmt_sys_status_code(l_j) := p_forecast_hdr_tab(l_j).asgmt_sys_status_code;
666 l_capacity_quantity(l_j) := p_forecast_hdr_tab(l_j).capacity_quantity;
667 l_overcommitment_quantity(l_j) := p_forecast_hdr_tab(l_j).overcommitment_quantity;
668 l_availability_quantity(l_j) := p_forecast_hdr_tab(l_j).availability_quantity;
669 l_overcommitment_flag(l_j) := p_forecast_hdr_tab(l_j).overcommitment_flag;
670 l_availability_flag(l_j) := p_forecast_hdr_tab(l_j).availability_flag;
671
672 END LOOP;
673
674 FORALL l_J IN p_forecast_hdr_tab.FIRST..p_forecast_hdr_tab.LAST
675 UPDATE PA_FORECAST_ITEMS
676 SET
677 forecast_item_type = l_forecast_item_type(l_j) ,
678 project_org_id = l_project_org_id(l_j) ,
679 expenditure_org_id = l_expenditure_org_id(l_j) ,
680 expenditure_organization_id = l_expenditure_organization_id(l_j) ,
681 project_organization_id = l_project_organization_id(l_j) ,
682 project_id = l_project_id(l_j) ,
683 project_type_class = l_project_type_class(l_j) ,
684 person_id = l_person_id(l_j) ,
685 resource_id = l_resource_id(l_j) ,
686 borrowed_flag = l_borrowed_flag(l_j) ,
687 assignment_id = l_assignment_id(l_j) ,
688 item_date = l_item_date(l_j) ,
689 item_uom = l_item_uom(l_j) ,
690 item_quantity = l_item_quantity(l_j) ,
691 pvdr_period_set_name = l_pvdr_period_set_name(l_j) ,
692 pvdr_pa_period_name = l_pvdr_pa_period_name(l_j) ,
693 pvdr_gl_period_name = l_pvdr_gl_period_name(l_j) ,
694 rcvr_period_set_name = l_rcvr_period_set_name(l_j) ,
695 rcvr_pa_period_name = l_rcvr_pa_period_name(l_j) ,
696 rcvr_gl_period_name = l_rcvr_gl_period_name(l_j) ,
697 global_exp_period_end_date = l_global_exp_period_end_date(l_j) ,
698 expenditure_type = l_expenditure_type(l_j) ,
699 expenditure_type_class = l_expenditure_type_class(l_j) ,
700 cost_rejection_code = l_cost_rejection_code(l_j) ,
701 rev_rejection_code = l_rev_rejection_code(l_j) ,
702 tp_rejection_code = l_tp_rejection_code(l_j) ,
703 burden_rejection_code = l_burden_rejection_code(l_j) ,
704 other_rejection_code = l_other_rejection_code(l_j) ,
705 delete_flag = l_delete_flag(l_j) ,
706 provisional_flag = l_provisional_flag(l_j) ,
707 error_flag = l_error_flag(l_j) ,
708 JOB_ID = l_JOB_ID(l_j) ,
709 TP_AMOUNT_TYPE = l_TP_AMOUNT_TYPE(l_j) ,
710 OVERPROVISIONAL_QTY = l_OVERPROVISIONAL_QTY(l_j) ,
711 OVER_PROV_CONF_QTY = l_OVER_PROV_CONF_QTY(l_j) ,
712 CONFIRMED_QTY = l_CONFIRMED_QTY(l_j) ,
713 PROVISIONAL_QTY = l_PROVISIONAL_QTY(l_j) ,
714 asgmt_sys_status_code = l_asgmt_sys_status_code(l_j) ,
715 capacity_quantity = nvl(l_capacity_quantity(l_j),0),
716 overcommitment_quantity = l_overcommitment_quantity(l_j),
717 availability_quantity = l_availability_quantity(l_j),
718 overcommitment_flag = l_overcommitment_flag(l_j),
719 availability_flag = l_availability_flag(l_j),
720 last_update_date = sysdate ,
721 last_updated_by = fnd_global.user_id ,
722 last_update_login = fnd_global.login_id ,
723 FORECAST_AMT_CALC_FLAG = 'N',
724 COST_TXN_CURRENCY_CODE = null,
725 REVENUE_TXN_CURRENCY_CODE = null,
726 TXN_RAW_COST = null,
727 TXN_BURDENED_COST = null,
728 TXN_REVENUE = null,
729 TP_TXN_CURRENCY_CODE = null,
730 TXN_TRANSFER_PRICE = null,
731 PROJECT_CURRENCY_CODE = null,
732 PROJECT_RAW_COST = null,
733 PROJECT_BURDENED_COST = null,
734 PROJECT_REVENUE = null ,
735 PROJECT_TRANSFER_PRICE = null ,
736 PROJFUNC_CURRENCY_CODE = null,
737 PROJFUNC_RAW_COST = null,
738 PROJFUNC_BURDENED_COST = null,
739 PROJFUNC_REVENUE = null,
740 PROJFUNC_TRANSFER_PRICE = null,
741 EXPFUNC_CURRENCY_CODE = null,
742 EXPFUNC_RAW_COST = null,
743 EXPFUNC_BURDENED_COST = null,
744 EXPFUNC_TRANSFER_PRICE = null
745 WHERE forecast_item_id = l_forecast_item_id(l_j);
746
747 PA_FORECASTITEM_PVT.print_message('end of update row .... ');
748 PA_DEBUG.Reset_Err_Stack;
749 EXCEPTION
750 WHEN l_empty_tab_record THEN
751 NULL;
752 /* Bug 2390990 Begin */
753 WHEN l_fi_rejected_prd_missing THEN
754 PA_UTILS.add_message(p_app_short_name => 'PA',
755 p_msg_name => lv_rejection_code,
756 p_token1 => 'DATE_LIST',
757 p_value1 => l_token);
758 x_return_status := FND_API.G_RET_STS_ERROR;
759 x_msg_data := lv_rejection_code;
760 x_msg_count := FND_MSG_PUB.Count_Msg;
761 If x_msg_count = 1 THEN
762 pa_interface_utils_pub.get_messages
763 (p_encoded => FND_API.G_TRUE,
764 p_msg_index => 1,
765 p_msg_count => x_msg_count,
766 p_msg_data => x_msg_data,
767 --p_data => x_msg_data, * added for bug 4537865
768 p_data => l_new_msg_data, -- added for bug 4537865
769 p_msg_index_out => l_msg_index_out );
770 -- added for bug 4537865
771 x_msg_data := l_new_msg_data;
772 -- added for bug 4537865
773 End If;
774 /* Bug 2390990 End */
775
776 WHEN l_fi_rejected then
777 PA_UTILS.add_message('PA',lv_rejection_code);
778 x_return_status := FND_API.G_RET_STS_ERROR;
779 x_msg_data := lv_rejection_code;
780 x_msg_count := FND_MSG_PUB.Count_Msg;
781 If x_msg_count = 1 THEN
782 pa_interface_utils_pub.get_messages
783 (p_encoded => FND_API.G_TRUE,
784 p_msg_index => 1,
785 p_msg_count => x_msg_count,
786 p_msg_data => x_msg_data,
787 --p_data => x_msg_data, * commented for Bug 4537865
788 p_data => l_new_msg_data, -- added for bug 4537865
789 p_msg_index_out => l_msg_index_out );
790 -- added for bug 4537865
791 x_msg_data := l_new_msg_data;
792 -- added for bug 4537865
793 End If;
794 WHEN OTHERS THEN
795 x_msg_count := 1;
796 x_msg_data := SQLERRM;
797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
798 FND_MSG_PUB.add_exc_msg
799 (p_pkg_name => 'PA_FORECAST_HDR_PKG.Update_Rows',
800 p_procedure_name => PA_DEBUG.G_Err_Stack);
801 RAISE;
802
803
804 PA_FORECASTITEM_PVT.print_message('ERROR in update row '||sqlerrm);
805 END update_rows;
806
807 -- This procedure will update the record in pa_schedules table
808 -- Input parameters
809 -- Parameters Type Required Description
810 -- P_Sch_Record_Tab ScheduleTabTyp YES It contains the schedule record
811 --
812 PROCEDURE update_schedule_rows ( p_schedule_tab IN PA_FORECAST_GLOB.ScheduleTabTyp,
813 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
814 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
815 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
816
817 IS
818 l_schedule_id PA_PLSQL_DATATYPES.IdTabTyp;
819 l_forecast_txn_version_number PA_PLSQL_DATATYPES.NumTabTyp;
820
821
822 BEGIN
823 PA_DEBUG.Init_err_stack( 'PA_FORECAST_HDR_PKG.Update_Schedule_Rows');
824 x_return_status := FND_API.G_RET_STS_SUCCESS;
825
826 /* Checking for the empty table of record */
827 IF (nvl(p_schedule_tab.count,0) = 0 ) THEN
828 PA_FORECASTITEM_PVT.print_message('count 0 ... before return ... ');
829 RAISE l_empty_tab_record;
830 END IF;
831
832 PA_FORECASTITEM_PVT.print_message('start of the schedule inser row .... ');
833
834 FOR l_j IN p_schedule_tab.FIRST .. p_schedule_tab.LAST LOOP
835 PA_FORECASTITEM_PVT.print_message('inside loop');
836 l_schedule_id(l_j) := p_schedule_tab(l_j).schedule_id;
837 l_forecast_txn_version_number(l_j) := p_schedule_tab(l_j).forecast_txn_version_number ;
838
839 END LOOP;
840
841 PA_FORECASTITEM_PVT.print_message('after loop');
842 FORALL l_j IN p_schedule_tab.FIRST..p_schedule_tab.LAST
843 UPDATE PA_SCHEDULES
844 SET
845 forecast_txn_version_number = NVL(l_forecast_txn_version_number(l_j),0) + 1 ,
846 forecast_txn_generated_flag = 'Y' ,
847 last_update_date = sysdate ,
848 last_update_by = fnd_global.user_id ,
849 last_update_login = fnd_global.login_id
850 WHERE schedule_id = l_schedule_id(l_j)
851 AND forecast_txn_version_number = l_forecast_txn_version_number(l_j);
852
853 PA_FORECASTITEM_PVT.print_message('after update');
854 PA_DEBUG.Reset_Err_Stack;
855 -- PA_FORECASTITEM_PVT.print_message('end of update schedule row .... ');
856 EXCEPTION
857 WHEN l_empty_tab_record THEN
858 NULL;
859 WHEN OTHERS THEN
860 x_msg_count := 1;
861 x_msg_data := SQLERRM;
862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863 FND_MSG_PUB.add_exc_msg
864 (p_pkg_name => 'PA_FORECAST_HDR_PKG.Update_Schedule_Rows',
865 p_procedure_name => PA_DEBUG.G_Err_Stack);
866 RAISE;
867
868 PA_FORECASTITEM_PVT.print_message('ERROR in update row '||sqlerrm);
869 END update_schedule_rows;
870
871 PROCEDURE update_rows(p_assignment_id IN NUMBER,
872 p_forecast_amt_calc_flag IN VARCHAR2,
873 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
874 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
875 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
876
877 l_msg_index_out NUMBER;
878 l_new_msg_data VARCHAR2(2000);
879
880 BEGIN
881 update pa_forecast_items
882 set
883 forecast_amt_calc_flag = 'N'
884 where assignment_id = p_assignment_id;
885
886 EXCEPTION
887
888 WHEN OTHERS THEN
889 PA_FORECASTITEM_PVT.print_message('Failed in update_rows api');
890 PA_FORECASTITEM_PVT.print_message('SQLCODE'||sqlcode||sqlerrm);
891
892 x_msg_count := 1;
893 x_msg_data := sqlerrm;
894 FND_MSG_PUB.add_exc_msg
895 (p_pkg_name =>
896 'PA_FORECAST_HDR_PKG.update_rows',
897 p_procedure_name => PA_DEBUG.G_Err_Stack);
898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899
900 If x_msg_count = 1 THEN
901 pa_interface_utils_pub.get_messages
902 (p_encoded => FND_API.G_TRUE,
903 p_msg_index => 1,
904 p_msg_count => x_msg_count,
905 p_msg_data => x_msg_data,
906 -- p_data => x_msg_data, * commented for Bug: 4537865
907 p_data => l_new_msg_data, -- added for bug 4537865
908 p_msg_index_out => l_msg_index_out );
909 -- added for bug 4537865
910 x_msg_data := l_new_msg_data;
911 -- added for bug 4537865
912 End If;
913
914 PA_FORECASTITEM_PVT.Print_message(x_msg_data);
915
916 RAISE;
917
918 END update_rows;
919
920 END PA_FORECAST_HDR_PKG;